Mysql – Restoring MySQL database with db dump and binlogs

MySQLmysqldumpreplication

I have a database running on our production web server that I have set up as a master with a single slave in another datacenter. When reading through the MySQL replication setup instructions, they advice to take the server offline, place a read lock, do the dump, and then create the slave with it. I did that, got the replication set up and we were happy.

Today I decided that having that replicate in clear over the WAN probably isn't good practice. So I looked into setting up replication through an SSH tunnel (temporary). This required me to change master_host to 127.0.0.1 instead of my Master's IP. In doing this, I borked my replication and now I have to start over. Problem is, the Master only had expire_logs_days=1, so I can't repeat the original process since it was last week. I do have backups of the other binlogs, but using mysqlbinlog to restore all of them keeps failing due to temporary tables problems.

So now I'm trying to get the slave backup and running without taking the master down. Every 3 hours, on the master, we do a database dump for backups. We use mysqldump -v --flush-logs --single-transaction --routines .... so the current binary log is cut off and a new one created with every backup we do. However, if I restore a database dump, then try and start the replication backup with the new binary log that was created with the last –flush-logs command, I still run into key collisions, just like they warned.

Given this information, is there way that I can successfully start the slave back up with dumps we have without taking the server down again? I'm not going to be in a good place if I have to go ask for more down time.

Best Answer

They are two things that can be good for you in this instance

  • GOOD THING #1 : You can convert the two MyISAM tables to InnoDB
  • GOOD THING #2 : The two MyISAM tables are never changing

If either one of these good things apply, then I have good news for you

STEP01) Zap All Binary Logs on the Master (OPTIONAL)

Run this command on the Master:

mysql> RESET MASTER;

If this part scares you, you could either skip it or make a copy of the binary logs before doing it

STEP02) Create the mysqldump as follows:

echo "STOP SLAVE;" > MySQLSlaveReload.sql
mysqldump -v --master-data=1 --single-transaction --routines ....  >> MySQLSlaveReload.sql
echo "START SLAVE;" >> MySQLSlaveReload.sql

STEP03) Move the MySQLSlaveReload.sql to the Slave

STEP04) Load MySQLSlaveReload.sql on the Slave

On the Slave, load the script as follows:

mysql -u... -p... -A < MySQLSlaveReload.sql

The script will stop the slave, load the data, and start the slave. What about the log file and position? Before you do STEP02, look at line 22 of MySQLSlaveReload.sql

head -22 MySQLSlaveReload.sql | tail -1

The mysqldump option --master-data=1 recorded the log file and position as of the start of the mysqldump on line 22.

Give it a Try !!!