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'

Passing Values manually in the pl/sql code.

this script has a limitation when running from sqlplus command line. But no issues when running using sql developer.

--- Limitation on substitution variable value length with 240 characters. so proc list should be limited.

 --- JUST EDIT THE TOP 3 LINES AND MAKE SURE THEY ARE CORRECT FOR THAT ENVIRONMENT: 

--variable M2 varchar2(30);

--DEFINE M2 = &&1;

-- DEFINE SCHEMA_NAME = &&2;

DEFINE SCHEMA_NAME = SCHEMA_NAME;

DEFINE USERS_LIST = 'BIMUSER,AIO98J,JMUSER'

DEFINE PROC_LIST = 'UPDATE_COL_PROC,UPDATE_TAB_PROC'

SET TRIMSPOOL ON TRIMOUT ON DEFINE "&" CONCAT "." SERVEROUTPUT ON;

COL SPOOL_NAME  NEW_VALUE SPOOL_NAME;

SET FEEDBACK OFF

SET HEADING OFF

VARIABLE SPOOL_NAME  VARCHAR2(100);

SELECT ''||(SELECT PDB_NAME FROM DBA_PDBS)||'-&&SCHEMA_NAME-ASSIGN_GRANTS_TO_APP-SPECIALISTS.LOG' SPOOL_NAME  FROM DUAL;

SPOOL &SPOOL_NAME

ALTER SESSION SET CURRENT_SCHEMA=&&SCHEMA_NAME;

SET LINESIZE 200

SET SERVEROUTPUT ON SIZE 1000000

SET FEEDBACK ON ECHO OFF VERIFY OFF

DECLARE

check_user varchar2(10);

  CURSOR C_F IS (

SELECT REGEXP_SUBSTR ('&USERS_LIST', '[^,]+', 1, LEVEL) AS USER_L

FROM DUAL CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('&USERS_LIST','[^,]*'))+1);

CURSOR C_G IS (

SELECT REGEXP_SUBSTR ('&PROC_LIST', '[^,]+', 1, LEVEL) AS PROC_L

FROM DUAL CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('&PROC_LIST','[^,]*'))+1);

BEGIN

FOR X IN  C_F

 LOOP

     select count(*) into check_user from dba_users where username in (''||X.USER_L||'');

  if (check_user = 1) then

      DBMS_OUTPUT.PUT_LINE ('USER:-> '||X.USER_L||' - Exists in the DB');

   BEGIN

      FOR Y IN C_G

         LOOP

             BEGIN

                 EXECUTE IMMEDIATE 'GRANT EXECUTE ON  "&&SCHEMA_NAME".'||Y.PROC_L||' TO  '||X.USER_L||'';

                 COMMIT;

              EXCEPTION

                 WHEN NO_DATA_FOUND THEN

                    DBMS_OUTPUT.PUT_LINE ('ROW NOT FOUND FROM THE LIST');

                 WHEN OTHERS THEN

                    DBMS_OUTPUT.PUT_LINE ('ERROR OCCURRED WHEN RUNNING FOR USER: '||X.USER_L||' for '||Y.PROC_L||'');

                    ROLLBACK;

                    CONTINUE;

                    END;

         END LOOP;

 COMMIT;

      EXCEPTION

        WHEN OTHERS THEN

         ROLLBACK;

         CONTINUE;

   END;

 else

  DBMS_OUTPUT.PUT_LINE (' USER:-> '||X.USER_L||' - Does not exists in the DB. ');

 end if;

END LOOP;

END;

/

SPOOL OFF;

UNDEFINE;

CRS_LIMIT_NPROC=16384 in s_crsconfig_hostname_env.txt file

 

Looks like there is a bug in the Oracle. Even after increasing the OS variables, “ORACLE” is not picking up the values.

Looks like Listener has a default max limit of 16384 in Oracle file , so it will not allowing any listener connections more than the said limit.

 

• ps -ef| grep LOCAL |wc -l               ---------- make sure this is less than 16384  ( if this value is close then new sessions will not be made using listener.

          Note: this is nothing to do with processes that added in the linux config files.

------Here are the steps we need to do to increase the value in this file.

 

$grid_home/crs/install/s_crsconfig_hostname_env.txt

 

Change this value “CRS_LIMIT_NPROC=16384"  To more value to accept more connections.

 This value is picked up by CRS when we start the oracle DB.

Note: we need to Bounce the CRS to take effect.

avoid Cartesian merge join in the explain plan.

Cartesian Merge Join are sometimes more expensive. takes long time to run and uses resources.

To avoid the cartesian merge join you can disable  using below command to make it run in that session. You dont want to apply this change to the DB as it would need oracle support to disable it.

alter session set "_optimizer_cartesian_enabled"=FALSE;