Sunday 24 November 2013

MySQL Master Slave Synchronization

In MySQL when Master/slave synchronization brakes, we can start the debugging with show slave status command like the following example illustrates:

[root@my-db-slave ~]# echo "show slave status\G" | mysql -u root -ppass
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: my-db-master.mydomain.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000522
          Read_Master_Log_Pos: 50224067
               Relay_Log_File: relay.000005
                Relay_Log_Pos: 10328614
        Relay_Master_Log_File: binlog.000520
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1032
                   Last_Error: Could not execute Delete_rows event on table mydatabase.example_table; Can't find record in 'example_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000520, end_log_pos 10330060
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 10328471
              Relay_Log_Space: 259941828
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table mydatabase.example_table; Can't find record in 'example_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000520, end_log_pos 10330060
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 49


On Slave MYSQL_HOME/logs/mysql.err this can be shown as:

131108 18:22:55 [Note] Slave I/O thread: connected to master 'replication@my-db-master.mydomain.com:3306',replication started in log 'binlog.000522' at position 50224067
131109  2:26:19 [ERROR] Could not execute Delete_rows event on table mydatabase.example_table; Can't find record in 'example_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000520, end_log_pos 10330060, Error_code: 1032
131109  2:26:19 [Warning] Slave: Can't find record in 'alf_node' Error_code: 1032
131109  2:26:19 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000522' at position 50224067

There are several treatments for this issue that depend on what is the state of binlogs on master and how long the slave has been out of synchronization.
The following were tested on  MySQL 5.5.31 /  RedHat  2.6.32-358.2.1.el6.x86_64

The simple case : Restart Slave 


When synchronization fails between MASTER/SLAVE due to some error, normally the slave retries to reconnect after MASTER_CONNECT_RETRY seconds.
Slave will try to reconnect MASTER_RETRY_COUNT times.

In some cases, when synchronization lost within a small time frame and not many updates have been to the Master, then a simple "stop slave" / "start slave" may restore sync:

On Slave:
[root@my-db-slave ~]# echo "stop slave" | mysql -u root -ppass
[root@my-db-slave ~]# echo "start slave" | mysql -u root -ppass
[root@my-db-slave ~]# echo "show slave status\G" | mysql -u root -ppass



Resetting the Slave (Ignore Master Updates )

Another treatment is to reset the Slave to follow the master in the future: doing this you are lossing all updates from master and you just want slave to ignore all changes happened to the master while slave was out of sync!

On Master
[root@my-db-master ~]#echo "SHOW MASTER STATUS" | mysql -uroot -ppass 

On Slave:
[root@my-db-slave ~]#Take from master the binlog and pos
[root@my-db-slave ~]#echo "slave stop"| mysql -uroot -ppass
[root@my-db-slave ~]#echo "CHANGE MASTER TO MASTER_HOST='my-db-master',MASTER_USER='replication', MASTER_PASSWORD='apassword', MASTER_LOG_FILE='binlog.001006', MASTER_LOG_POS=  102018129;"| mysql -uroot -ppass
[root@my-db-slave ~]#echo "start slave"| mysql -uroot -ppass
[root@my-db-slave ~]#echo "SHOW SLAVE STATUS\G" | mysql -uroot -ppass  


Forcing Slave to get binlog files from Master

Sometimes, it might help to force slave get the binlogs again from the master. This will work if master has not rotate its binlogs: in other words if master have not started writting the binlogs again. That may happened because for example someone issued a RESET MASTER  on master. To force SLAVE retrieve the binlogs from start do the following steps:

On Slave:
Stop slave & Database
[root@my-db-slave ~]#echo "slave stop"| mysql -uroot -ppass
[root@my-db-slave ~]#/etc/init.d/mysql stop

Be sure that you have enough space when you do the next step:
[root@my-db-slave ~]#rm -rf $MYSQL-HOME/relaylogs/* 
[root@my-db-slave ~]#rm -rf $MYSQL-HOME/mysqld-relay-bin.*  $MYSQL-HOME/mysqld-relay-bin.index  $MYSQL-HOME/relay-log.info
[root@my-db-slave ~]#/etc/init.d/mysql start
[root@my-db-slave ~]#echo "stop slave" | mysql -u root -ppass
[root@my-db-slave ~]#echo "reset slave" | mysql -u root -ppass
[root@my-db-slave ~]#echo "start slave" | mysql -u root -ppass



Restore Slave from clear export from Master

Use this when you cannot restore the slave with any of the above ways.


On Master:
I need only mydatabase database, do not care about the others. Even if mydatabase db is in usage from application clients:

Take the dump of current Master database
[root@my-db-master ~]# sudo -i
[root@my-db-master ~]# cd /dbexport/
[root@my-db-master ~]# nohup mysqldump --master-data  -u root  -ppass mydatabase > mydatabase.dump &

With the previous command mysql dumps mydatabase database to the mydatabase.dump by:
Reserving the master binlog  status at the moment of backup transaction
Locking each backuped table in mydatabase, so it corresponds to the binlog state.

Note that: with the previous command I export only one database instance : mydatabase
In case your server hosts more that one databases and those are replicated on different slaves, you should consider taking an export for them also.

Check the last command output and also the dump status:
[root@my-db-master ~]# head mydatabase.dump
[root@my-db-master ~]# tail mydatabase.dump

Put a new password for trasport the dump
[root@my-db-master ~]# passwd

On Slave:
[root@my-db-slave ~]# sudo -i
[root@my-db-slave ~]# cd /monsoon/dbexport/
[root@my-db-slave ~]# scp root@my-db-master:/dbexport/mydatabase.dump .

[root@my-db-slave ~]# echo "show slave status\G" |  mysql -u root -ppass | egrep "Slave_IO_Running|Slave_SQL_Running"

             Slave_IO_Running: No
            Slave_SQL_Running: No

[root@my-db-slave ~]# nohup mysql  -u root  -ppass mydatabase < mydatabase.dump &


[root@my-db-slave ~]#  cat nohup.out

--there must be nothing here--

[root@my-db-slave ~]# echo "start slave" |  mysql -u root -ppass

[root@my-db-slave ~]# echo "show slave status\G" |  mysql -u root -ppass | egrep "Slave_IO_Running|Slave_SQL_Running"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

No comments: