Restoring MySQL database with db dump and binlogs

Question

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.

• RolandoMySQLDBA: Is all your data InnoDB ??? (Please say yes)
• Safado: We have 2 of 169 that are MyISAM ...
• RolandoMySQLDBA: The two MyISAM tables, are they being constantly written with INSERTs/UPDATEs/DELETEs, or are you doing SELECTs only against the MyISAM tables?
• RolandoMySQLDBA: Side question: Can those two MyISAM tables be converted to InnoDB? If not, are they using FULLTEXT indexing? If not, why can they not be converted to InnoDB ???
• Safado: The two MyISAM tables are constantly using insert/update/delete. One is an access log for one very small part of our application and the other is used for the search feature on our app. As to whether or not they can be converted, I'm not sure, I'd need to discuss it with some developers (we've been sans DBA for the last couple months, unfortunately..)

Answer 1

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 !!!

• Safado: So basically the difference here from what I already tried is that by adding the --master-data=1 option, I will now know the log file and position I need to use when I try and restore? I'll give it a shot.
• RolandoMySQLDBA: The tables should be converted to InnoDB first because the tables can still be changed during the dump. If the two tables are InnoDB, then the mysqldump's content will all be in the same point-in-time as for the log filename and position recorded.
• Safado: Well the above steps fail if those two aren't InnoDB? Or do we just run the risk of the data not being exact to what exists on our master? If it's the latter, we could probably live with it as long as we can get the replication back up and running.
• Safado: I've done the above steps and so far so good (hope I don't jinx myself). It's still in the process of catching up, but I haven't had any conflicts yet. Sorry about the noob level questions (really, I'm a sysadmin stuck in a dba world!) but given that this step worked, am I going to have any problems because of those 2 MyISAM tables? To be honest, I don't even know what the differences are. Thanks for your help.
• RolandoMySQLDBA: Check out my answer to your sysadmin/dba role: dba.stackexchange.com/a/2913/877