MySQL DBA - Tips and Techniques Magazine

11 Nov 2014

Recreate slave from static backup of master MYSQL Database



At around 5:05, the single threaded radius boxes (HX1, HX2, HX3, MK3 and

MK5) seemed to have stopped responding.  It looked like they were doing nothing at all or no radius requests were coming in for the whole 25 minutes.  This is very unusual as all our radius boxes are busy all the time.  Please can you investigate what happened.

The situation now is that  mk-xx-1 master server has recovered and working as normal, however to enable replication on the slave it requires the bin…14 file, where the master has moved on to 15.  Many attempts/tricks have been tried to get this to work , but we keep getting the following error :

           Last_error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

We have decided that the only way to make sure the 2 servers are sync'd is to rebuild the slave from a static backup of the master mk-xx-1.

During the day


1) Stop slave 


on mk-xx-2.server


Connect to mysql


mysql -uroot --p***




mysql > show slave status \G



mysql> stop slave;


Query OK, 0 rows affected (0.00 sec)


mysql> show slave status \G

*************************** 1. row ***************************

          Master_Host: mk-xx-1.server

          Master_User: repl

          Master_Port: 3306

        Connect_retry: 60

      Master_Log_File: mk-xx-1-bin.015

  Read_Master_Log_Pos: 98

       Relay_Log_File: mk-xx-2-relay-bin.001

        Relay_Log_Pos: 46

Relay_Master_Log_File: mk-xx-1-bin.015

     Slave_IO_Running: No

    Slave_SQL_Running: No



           Last_errno: 0


         Skip_counter: 0

  Exec_master_log_pos: 98

      Relay_log_space: 46

1 row in set (0.00 sec)



3) take backup of slave just radius database – mk-xx-2.



cd /home/backup/





bash-2.05# cat





date=`/bin/date +"%d-%m-%y-%H:%M"`

umask 066

( /usr/local/bin/mysqldump -uroot --p*** --single-transaction --add-drop-table --database radius | gzip ) > /home/backup/radius-$date-`hostname -s`.sql.gz 2> /home/backup/radius-$date-`hostname -s`.sql.err.log


ls -ltr /home/backup/radius-$date-`hostname -s`.sql.gz > /home/backup/filelist.out

ls -ltr /home/backup/radius-$date-`hostname -s`.sql.err.log >> /home/backup/filelist.out


mail -s "mySQL Backup on `hostname -s`" $EMAIL_LIST < /home/backup/filelist.out







3) Check Change Request is approved




4) Stop the application (took 1hr 20)


on mk-xx-1





(NB home is /var/db/mysql)



5) Take note of replication log information on master



Connect to mysql


mysql -uroot --p***



type in



show master status \G



"Keep information safe somewhere"



5) Kick off backup on prod

cd /home/backup/





Takes about 12 min



5A) restart App (took 1hr).  We can carry on with step 6.



6) copy file over to mk-xx-2


rename file first to get rid of colon


mv MYSQL-BACKUP-FILENAME.gz  restore.gz



scp restore.gz mk-xx-2.server:/home/backup



root MK.. pwd


7) do the restore on slave


on mk-xx-2.server


cd /home/backup


nohup gunzip < restore.gz | mysql -uroot --p***  &




v.slow box, will take about 3 days to run.  Have turned off monitoring and the backup.


ps aux |grep nohup



8) change master log and file position


Connect to mysql


mysql -uroot --p***



type in


--- use Exec_master_log_pos from above







start slave;



show slave status\G



flush privileges;   à make sure new settings from restore are used





mysql -uroot --p***   à  check you can login.



9. turn on backup and monitoring again.


Turn on monitoring and the backup.

No comments:

Post a Comment