fixing mySQL replication (large DB)

Question

I have a large mySQL database and I am replicating to two slaves. All three servers are windows (i know). One of my slaves seems to have corruption, the other is fine. This is huge DB, a few hundred gigs. I am trying to figure out the best way to fix the replication on the one slave that is corrupted. I need to minimize downtime to the master so a mySQLdump seems problematic unless its the only way. I was wondering if because I have two slaves and I am pretty sure all 3 environments are close to identical there might be another creative way to solve this. It is InnoDB but might I be able to shut all three down and do a directory copy of the good slave (datadir) and use it on the bad slave, restart? I think that might only take a few hours as opposed to a mySQLdump. Any thoughts on how to accomplish this correctly would be helpful.

• akuzminsky: run pt-table-checksum on the master, it will show your inconsistencies between the master and all slaves. If there are any you can fix them with pt-table-sync

Answer 1

Since you have a functioning Slave, you can rebuild the bad Slave in two different methods

For these examples

• MAS : Master:
• SL1 : Slave (Good one)
• SL2 : Slave (Bad one)

METHOD #1

This method requires that you stop using the good Slave for the duration of the recovery

• STEP 01 : On SL2, Run STOP SLAVE;
• STEP 02 : On SL1, Run STOP SLAVE;
• STEP 03 : On SL1, Run SHOW SLAVE STATUS\G
• STEP 04 : Record Read_Master_Log_File and Exec_Master_Log_Pos From STEP 03
• STEP 05 : mysqldump data from SL1 to a SQL text file
• STEP 06 : Load mysqldump into SL2
• STEP 07 : ON SL2, CHANGE MASTER TO master_log_file='(Read_Master_Log_File From STEP 03)',master_log_pos=(Exec_Master_Log_Pos From STEP 03)
• STEP 08 : On SL2, Run START SLAVE;
• STEP 09 : On SL2, Run SHOW SLAVE STATUS\G and Look at Seconds_Behind_Master
• STEP 10 : Repeat STEP 09 until Seconds_Behind_Master is 0

After this, you can use SL1 and SL2 for reads

METHOD #2

This method requires that you shutdown mysqld on SL1 and SL2

• STEP 01 : On SL1, Run STOP SLAVE;
• STEP 02 : On SL2, Run STOP SLAVE;
• STEP 03 : On SL1, Run SHOW SLAVE STATUS\G
• STEP 04 : On SL1, Run net stop mysql
• STEP 05 : On SL2, Run net stop mysql
• STEP 06 : Copy entire datadir of SL1 over to SL2. Make sure you copy master.info file because it contains replication coordinates needed of SL2 to continue replication on startup. Make sure you copied the relay logs as well.
• STEP 07 : On SL1, Run net start mysql
• STEP 08 : On SL2, Run net start mysql

CAVEAT

If every table is InnoDB, MAS does not need to be shutdown at all. SL1 just needs a stable place to stop replication before copying to SL2.

Give it a Try !!!

UPDATE 2014年03月14日 15:51 EDT

If you are planning to use the Master (since you are moving the Master to another Data Center), here is some advice.

• STEP 01 : Stop application writes to MAS
• STEP 02 : On MAS, run RESET MASTER; (Deletes all binary logs)
• STEP 03 : On MAS, net stop mysql
• STEP 04 : Copy the MAS server's datadir to the Slave's datadir
• STEP 05 : On the new MAS, net start mysql (DO not have any application write to MAS yet)
• STEP 06 : ON the New MAS, SHOW MASTER STATUS;
• STEP 07 : On the Slave, net start mysql
• STEP 08 : On the Slave, run CHANGE MASTER TO master_log_file='binlog from STEP 06',master_log_pos=(Position from STEP 06);
• STEP 09 : On the Slave, run START SLAVE;
• STEP 10 : Start application writes to MAS

No need to copy binlogs doing it like this. All a Slave cares about is the latest binlog.

• jmr: thanks you! method 2 seems close to what i was thinking. I assume in step 7 and 8 you mean net start mysql
• RolandoMySQLDBA: I changed net stop to net start. Thanks !!!
• jmr: i assume i could do the same basic thing if i were moving a master from one server to a new server as long as it was windows to windows and same mysql version? copy the entire datadir without an issue? Anything with bin logs I would have to do special in this case? (i ask because we are thinking of moving our hosting to a new datacenter as well.
• RolandoMySQLDBA: I updated my answer with a third method
• jmr: much appreciated friend.