Mysql: find tables with no primary key

 SELECT t.table_schema,
       t.table_name,
       k.constraint_name,
       k.column_name
FROM information_schema.tables t
     LEFT JOIN information_schema.key_column_usage k
          ON t.table_schema = k.table_schema
             AND t.table_name = k.table_name
             AND k.constraint_name = 'PRIMARY'
 WHERE t.table_schema NOT IN ( 'mysql', 'information_schema',
                                       'performance_schema' )
       AND k.constraint_name IS NULL
       AND t.table_type = 'BASE TABLE'
       order by table_schema desc;

how to skip commented lines in linux in loop for shell script.

How to skip commented input lines from a input file in loop in Linux script:

 

below is the simple code with small example:

#!/bin/ksh
# Script to check required ORACLE - RHEL RPMs:
#
while read rpm_c
  do
case "$rpm_c" in \#*) continue ;; esac
rpm_check=`rpm -qa $rpm_c | wc -l`
if [[ $rpm_check != 0 ]]; then
rpm_check=YES
echo $'\e[1;36m' rpm: $'\e[1;34m' $rpm_c   $'\e[1;32m' $rpm_check $'\e[0m'
else
rpm_check=NO
echo $'\e[1;36m' rpm: $'\e[1;34m' $rpm_c  $'\e[1;31m'  $rpm_check $'\e[0m'
fi
done<RHEL-7_ORACLE19C_RPMS.log

input in the logfile:

libgcc
libstdc++
libstdc++-devel

libxcb
make
smartmontools
sysstat
#

#Optional Packages for Red Hat Enterprise Linux 7:
#

#ipmiutil (for Intelligent Platform Management Interface)
#net-tools (for Oracle RAC and Oracle Clusterware)

#nfs-utils (for Oracle ACFS)
#python (for Oracle ACFS Remote)

Mysql: Kill a session

 How to find and kill a session in Mysql.

select * from information_schema. processlist;

if we want to generate kill statement for 'IDs' the use below sql statement.

 SELECT GROUP_CONCAT(CONCAT('KILL ',id ,'  ' ,HOST , '   ',command,';')) FROM information_schema. processlist WHERE user <> 'system user'

 group by ID; 

Mysql: Kill a session

 In Mysql, If we want to kill the multiple sessions quickly. Use below sql statement to generate the "KILL statements for the ID's and decide based on the once to kill.

 SELECT GROUP_CONCAT(CONCAT('KILL ',id ,'  ' ,HOST ,';')) FROM information_schema. processlist WHERE user <> 'system user'

 group by ID; 

 

example:

KILL 1  localhost;

KILL 24905  localhost2:1234;

Mysql: Replication setup between two mysql servers( Master - Slave ) setup.

Setting up replication between two mysql servers is easy. Not many steps involved.

below are the steps that need to be done to have replication working in Master-Slave configuration.

1) In order to setup the replication between the 2 servers. First we need to make sure everything is stopped on the source(Master) and the Target(Slave) side.

2) Bounce the Mysql on both Source and target.

3) Take the backup of the Source database( I used MEB method for the backup).

4) Restore the backup of the Source database in to Target database ( Used MEB backup).

5) we need to add Master configuration on the source side by editing /etc/my.cnf file

On Source(Master side):

server-id=121212   # if it is not present

sync_binlog=1  # enable "1" if replication is used for master

binlog_checksum = NONE

binlog_format = ROW

log-bin=/opt/mysql/log/hostname-bin.log

log-bin-index=/opt/mysql/log/hostname-bin.index

log_error=/opt/mysql/log/mysqld.log    --- if not present

expire_logs_days = 10

log_error_verbosity=2   # added it so that you can watch for errors and warnings. "1" just prints errors.

6) On source side, create replication user for the target with IP like below and grant replication grant.

mysql> create user 'TSTREPL_user'@'targetIP' identified by 'TSTREPL#1234';    --- run on master

mysql> grant replication slave on *.* to 'TSTREPL_user'@'targetIP';

mysql> Flush privileges;

 mysql> FLUSH TABLES WITH READ LOCK;

   show variables like 'sql_log_bin';

  set sql_log_bin=0;  --- to disable binary logging    we are doing this to disable replication for sometime till we setup slave with master backup and config etc.

   -- when ready to run command on step 5 on Slave-side

   mysql> unlock tables;

7) On Target side/ Slave side.

Run the below command to connect to Source side/Master.

CHANGE MASTER TO MASTER_HOST='sourceIP',MASTER_USER='TSTREPL_user',MASTER_PASSWORD='TSTREPL#1234',MASTER_LOG_FILE='hostname-bin.000001',MASTER_LOG_POS=150; 

8) On Source Side(Master side), reset the Master position and Log.

reset master

show master status \G;

(confirm bin is now reset to 000001)

 

9) On Target Side(Slave Side).

start slave;

show slave status \G;

  

10) Test replication by creating a table and insert rows on Master side(Source side) and check on slave side(target side).

On master..

use mysql;

create table TEST (id int);

    insert into TEST(1);

    select count(*) from TEST;  --- 1 row

11) On Target side (Slave), Validate the table got created and row got inserted.

use mysql;

select count(*) from TEST;  --- 1 row

end......

Mysql: generating sql statements for each row from simple select statement

 In Mysql, to generate sql statements for each row of the select statement. we need to use group_concat function. It is easier to generate the sql statements in oracle but it is little different in Mysql. 

Below is the statement that we need to use to make it work.

select group_concat(concat('select count(*) from ',table_schema,'.',table_name,';') ) from 
 information_schema.tables where table_schema='TEST' group by table_name;

output will be :

select count(*) from table_schema.table_name;     -- for each row from the above statement.

mysql: stored procedure block to get table row counts

 In Mysql we cannot use PLSQL. we need to make use of procedures to get the desired results that we need.

To get the table row counts of all the tables in mysql. Use below code.  Put below code in *.sql file and run as "source *.sql" after logging into mysql.

added a few check points to see the proc simulation.

------ code  

use mysql

drop procedure TestExampleProc2;

DELIMITER //
CREATE PROCEDURE TestExampleProc2()
   BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE tabname,table_n,tabsch,tabcount,tabsch_n VARCHAR(70);
      DECLARE cur CURSOR FOR SELECT table_schema,table_name FROM information_schema.tables where table_schema not in ('mysql','Performance_schema','sys','information_schema') and table_type = 'BASE TABLE'  order by table_schema asc;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      OPEN cur;
      label: LOOP
      FETCH cur INTO tabsch,tabname;
      set @table_n = tabname;
      set @tabsch_n = tabsch;
      set @query = CONCAT('select count(*) into @tabcount from ', @tabsch_n,".",@table_n);
     -- select @tabsch_n,@table_n;
     -- select @query;
      PREPARE stmt1 FROM @query;
      execute stmt1;
     -- select @query;
      select tabsch,tabname,@tabcount;
      DEALLOCATE PREPARE stmt1;
      IF done = 1 THEN LEAVE label;
      END IF;
      END LOOP;
      CLOSE cur;
   END//
DELIMITER ;

tee  mysql_table_count.log

call TestExampleProc2();

notee;

----------------------------- code ends

this will create a logfile with all the table counts.

Script: auto-generate a sql script for all individual sql scripts with spool file and pause

 

#!/bin/ksh

# USAGE: Script is used to generate a file to add spool to all files.

# check for filenames for spaces:

x=$(find . -type f -name "* *" |wc -l)

if [ $x != 0 ]; then

echo $x

j=$(find . -type f -name "* *")

echo -e "Correct the filenames with spaces:  \n$j "

exit;

fi

touch run_all_scripts.sql

echo "set echo on" >>run_all_scripts.sql

echo "set serveroutput on size unlimited" >>run_all_scripts.sql 

for i in `ls *.sql | grep -v run_all_scripts.sql`

do

j=${i%.*}

echo spool ELS_GIL_$j"."log >>run_all_scripts.sql

echo "@$i" >>run_all_scripts.sql

echo "spool off" >>run_all_scripts.sql

echo "pause;" >>run_all_scripts.sql

echo " " >>run_all_scripts.sql

done

echo "set echo off" >>run_all_scripts.sql

echo $'\e[1;33m\e[4m' "Script executed fine- check the file to confirm" $'\e[0m'