Document Managing Mysql Replication Procedures

Auto-generated from the uploaded PDF Document_Managing_Mysql_Replication_Procedures.pdf. This is an extracted-text rendering for searchability; see the original PDF for exact formatting, diagrams, tables, and images.

Process Document
to Manage Mysql
   Replication
 Environments at
 Radico Rampur

Table of Contents
Infrastructure Used.....................................................................................................................................2
Mysql Replication Manual Monitoring......................................................................................................2
      Status for mysql slave status on Linux mysql Slave.........................................................................2
      Status for mysql slave status on Windows mysql Slave...................................................................4


Infrastructure Used
     •     Mysql 8.0.12 on Windows 2012 (IP 172.30.3.155 ) as MASTER Node .
     •     Mysql 8.0.12 on Windows 2012 (IP 172.30.3.220 ) as Ist SLAVE Node
     •     Mysql 8.0.12 on Linux / Centos 6.10 (IP 172.30.3.6 ) as 2nd SLAVE Node


Mysql Replication Manual Monitoring

Status for mysql slave status on Linux mysql Slave
     1. Login on Centos server as root
     2. login on mysql shell
           1. mysql -u root -p
           2. give password
     3. Run
           1. mysql>
           2. mysql> show slave status\G
           3. expect output as following with Seconds_Behind_Master: 0 sec or > 100 Sec
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
          Master_Host: 172.30.3.155
          Master_User: slave_user
          Master_Port: 3306

          Connect_Retry: 60
         Master_Log_File: binlog.000073
      Read_Master_Log_Pos: 127606919
         Relay_Log_File: mysql-relay-bin.000007
          Relay_Log_Pos: 12171808
    Relay_Master_Log_File: binlog.000073
        Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
         Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
    Replicate_Ignore_Table:
   Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
            Last_Errno: 0
            Last_Error:
          Skip_Counter: 0
      Exec_Master_Log_Pos: 127606919
         Relay_Log_Space: 12172016
         Until_Condition: None
         Until_Log_File:
          Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
         Master_SSL_Cert:
       Master_SSL_Cipher:
         Master_SSL_Key:
    Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
          Last_IO_Errno: 0
          Last_IO_Error:
         Last_SQL_Errno: 0
         Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
        Master_Server_Id: 1
           Master_UUID: bf9992ff-6c9b-11e9-8f14-30e1716afbdd
        Master_Info_File: mysql.slave_master_info
             SQL_Delay: 0
      SQL_Remaining_Delay: NULL
   Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
       Master_Retry_Count: 86400
           Master_Bind:
   Last_IO_Error_Timestamp:
  Last_SQL_Error_Timestamp:
         Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set:
       Executed_Gtid_Set:

          Auto_Position: 0
     Replicate_Rewrite_DB:
           Channel_Name:
       Master_TLS_Version:
    Master_public_key_path:
     Get_master_public_key: 0
1 row in set (0.00 sec)


   4. There should not be any error on the last line


Status for mysql slave status on Windows mysql Slave
   1. Login on Centos server as root
   2. Open the mysql cli of Windows Server ( 172.30.3.220 ) by
   3. mysql -h 172.30.3.220 -uroot -p
   4. give the password
   5. mysql> show slave status\G
   6. expect output as following with Seconds_Behind_Master: 0 sec or > 100 Sec
   7. There should not be any error on the last line


Contingency Plan and execution
The execution plan below is for any failures on Mysql Environment


Master node ( 172.30.3.155) Failure
In case the Server Hardware , Mysql Software or Long. Downtime Plan of Master Mysql Node , the
following plan will ensure the immediate availability of Mysql Environment .

Steps


   1. Shut down the Master node and ensure that Master node is not working .
   2. On the Ist Slave , I.E Windows Slave Server ( 172.30.3.220 ) , put the IP of Master Mysql
      Server for Client Access Availability
   3. Open Mysql CLI of Mysql Workbench and give password for login

4. On mysql> prompt
5. mysql>STOP SLAVE;
6. mysql>RESET MASTER;
7. That’s It Now your Master server is Running .
8. On Sencond Slave server , I.e Linux Mysql Server
   1. mysql> STOP SlAVE;
   2. mysql> START SLAVE;
9. check the slave status as per Mysql manual monitoring for Replication