Rebuilding a MySQL Slave

This is the general process to rebuild a slave. In this example, we are rebuilding a database called DBX03.
Stop MySQL on DBX03 and remove all of the current data.  Depending on the reason for the rebuild, you may also want to remove the mysql database folder. Wiping clean the /DBDATA/mysql/* files would do the trick.
Grab the most recent snapshot of the data from another server. Preferably a slave and we would rather not take a snapshot off of a master db due to the increased disk utilization as we copy the data off. If the database server that you’re rebuilding takes frequent snapshots, you may also use that data (we assume that DBX03 does not take any snapshots). Note the SLAVE STATUS output from the snapshot script. This information is mandatory to start the slave at the correct position
Mount the snapshot and copy over the data to DBX03. (rsync -azvpP –stats –delete slave-svr:/mnt/DB_SNAP/ /DBDATA/mysql/ ). It would be smart to stop the snapshot script from running again so it doesn’t interfere with your work.
Once the rsync is done, on DBX03, you need to cleanup the unnessesary files. These include: relaylog files, error log,,, mysql.sock, binlog files,, slow query logs, etc
Check that the permissions are correct for all files and folders (mysql should be the user and group owner). Ensure that the proper log folders have been created (defined in my.cnf)
Start MySQL on DBX03
Tail the end of the error log to ensure that MySQL starts up properly. It should take a few minutes to replay the innodb logs (assuming that the snapshot wasn’t taken while the database was shutdown). This will also notify you immediately if any necessary files or folders are missing so read the output carefully
If all started up correctly and the necessary logs have been replayed on DBX03 it’s time to configure the slave. This information is necessary for this to be configured correctly and can be found in the slave status of the snapshot log. For example, the slave status section of the snapshot log should look like this if taken off of a slave:

mysql> show slave status;
 stdClass Object
 [Slave_IO_State] =>
 [Master_Host] =>
 [Master_User] => repl_usr
 [Master_Port] => 3306
 [Connect_Retry] => 60
 [Master_Log_File] => master-binlog.001439
 [Read_Master_Log_Pos] => 899027494
 [Relay_Log_File] => slave-relaylog.004518
 [Relay_Log_Pos] => 899027022
 [Relay_Master_Log_File] => master-binlog.001439
 [Slave_IO_Running] => No
 [Slave_SQL_Running] => No
 [Replicate_Do_DB] =>
 [Replicate_Ignore_DB] => mysql
 [Replicate_Do_Table] =>
 [Replicate_Ignore_Table] =>
 [Replicate_Wild_Do_Table] =>
 [Replicate_Wild_Ignore_Table] =>
 [Last_Errno] => 0
 [Last_Error] =>
 [Skip_Counter] => 0
 [Exec_Master_Log_Pos] => 899026874
 [Relay_Log_Space] => 899027841
 [Until_Condition] => None
 [Until_Log_File] =>
 [Until_Log_Pos] => 0
 [Master_SSL_Allowed] => No
 [Master_SSL_CA_File] =>
 [Master_SSL_CA_Path] =>
 [Master_SSL_Cert] =>
 [Master_SSL_Cipher] =>
 [Master_SSL_Key] =>
 [Seconds_Behind_Master] =>
 [Master_SSL_Verify_Server_Cert] => No
 [Last_IO_Errno] => 0
 [Last_IO_Error] =>
 [Last_SQL_Errno] => 0
 [Last_SQL_Error] =>
 [Replicate_Ignore_Server_Ids] =>
 [Master_Server_Id] => 1001

These values can be used to populate the required data to start the slave.

CHANGE MASTER TO MASTER_HOST = '<master_host_ip>'; #IP of the master db server which will send binlogs
CHANGE MASTER TO MASTER_PORT = <master_host_port>; #Port of the master db server
CHANGE MASTER TO MASTER_USER = '<master_host_repl_username>'; #username as defined on master db
CHANGE MASTER TO MASTER_PASSWORD = '<master_host_repl_pwd>'; #password as defined on master db
CHANGE MASTER TO MASTER_LOG_FILE = '<[Master_Log_File]>'; #binlog name on master, as defined above
CHANGE MASTER TO MASTER_LOG_POS = <[Exec_Master_Log_Pos]>; #binlog location to start at. If new binlog, pos=0 works

Perform a SHOW SLAVE STATUS to confirm that the settings are all correct
Perform a START SLAVE to start the slave
You should know immediately if the slave crashes or not. The slave status will calculate the time it’s behind master and report back. If there’s a duplicate key issue then you most likely started the slave in the wrong spot but investigating will be needed to verify. You can also tail the MySQL error log to view any additional errors.

Leave a Reply

Your email address will not be published.