MySQL Forums
Forum List  »  Backup

Mysqldump of Slave Server - Replay Binary Logs
Posted by: MySQL Help
Date: September 10, 2014 09:37AM


I am having an issue figuring out how to replay logs in case of an emergency when doing mysqldumps on a slave server.

Currently, we are backing up our MySQL servers by doing a msqldump on our slave server (we have a single master and a single slave configuration) on a nightly basis. Our databases are INNODB and are backed up on the slave with the following command:

mysqldump --add-drop-database --create-options --complete-insert --extended-insert --flush-privileges --single-transaction --routines --tz-utc –master-data=1 <DATABASENAME>

Recently, we had a failover and the master server is now the slave. When doing backups on the slave (the old master), and looking at the log position information in the mysqldump, it looks like this:


If I look at the date of the “old-master.00381” log, it’s dated June 28th (when the failover occurred). On the new master server, I have binary logs that go back to the 7th of September (below are some of the logs that have been generated today):

9-10-2014 new-master.002025
9-10-2014 new-master.002026
9-10-2014 new-master.002027
9-10-2014 new-master.002028
9-10-2014 new-master.002029
9-10-2014 new-master.002030

If something happens, and I need to replay the most recent logs, after restoring the database from the mysqldump, how do I replay the logs from the master server? Do I need to first replay the old-master.000381 log before replaying the logs from the master? For example:

mysqlbinlog old-master.00381 --start-position=107 | mysql <DATABASENAME>

Considering, the old-master.00381 log is from June, I was concerned about the data rolling back to that date.

Thank you.

Options: ReplyQuote

Written By
Mysqldump of Slave Server - Replay Binary Logs
September 10, 2014 09:37AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.