What is the best way to recover from a mysql replication fail?
        Posted  
        
            by 
                Itai Ganot
            
        on Server Fault
        
        See other posts from Server Fault
        
            or by Itai Ganot
        
        
        
        Published on 2013-06-30T16:00:42Z
        Indexed on 
            2013/06/30
            16:22 UTC
        
        
        Read the original article
        Hit count: 382
        
Today, the replication between our master mysql db server and the two replication servers dropped. I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to recover for this issue. I'd like to share with you the procedure and I'd appreciate if you could give your thoughts about it and maybe even tell me how it can be done quicker.
At the master:
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
And copy the values of the result of the last command somewhere.
Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:
mysqldump mysq
Now you can release the lock, even if the dump hasn't end. To do it perform the following command in the mysql client:
UNLOCK TABLES;
Now copy the dump file to the slave using scp or your preferred tool.
At the slave:
Open a connection to mysql and type:
STOP SLAVE;
Load master's data dump with this console command:
mysql -uroot -p < mysqldump.sql
Sync slave and master logs:
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
Where the values of the above fields are the ones you copied before.
Finally type
START SLAVE;
And to check that everything is working again, if you type
SHOW SLAVE STATUS;
you should see:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
That's it!
At the moment i'm in the stage of copying the db from the master to the other two replication servers and it takes more than 6 hours to that point, isn't it too slow? The servers are connected through a 1gb switch.
© Server Fault or respective owner