o rebuild a MySQL slave, you will need to take the following steps:
STOP SLAVE;
Reset the slave’s master coordinates:
RESET SLAVE;
Set the slave’s new master details, including the master hostname or IP address, the replication user and password, and the master log file and position to which the slave should start replicating:
CHANGE MASTER TO MASTER_HOST='master_hostname_or_IP',
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='master_log_file',
MASTER_LOG_POS=master_log_position;
Start the slave
START SLAVE;
Check the slave’s status to ensure that it is replicating correctly:
SHOW SLAVE STATUS\G
It is important to note that these steps will only work if the slave has not been too far behind the master, as in that case it may not be possible to simply reset the slave and start replicating from a new position in the master’s logs. In such cases, it may be necessary to perform a more complex procedure to rebuild the slave, such as using mysqldump to dump the data from the master and then importing it into the slave.
Full Rebuild
STOP SLAVE;
RESET SLAVE;
mysqldump -h master_hostname_or_IP -u dump_user -p --all-databases > dumpfile.sql
mysql -h slave_hostname_or_IP -u slave_user -p < dumpfile.sql
CHANGE MASTER TO MASTER_HOST='master_hostname_or_IP',
MASTER_USER='replication_user',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='master_log_file',
MASTER_LOG_POS=master_log_position;
START SLAVE;
SHOW SLAVE STATUS\G;
This procedure will rebuild the slave from the master, effectively resetting the slave and starting replication from the current position of the master. It is important to note that this will overwrite any existing data on the slave, so it should only be used if the slave needs to be reset and synchronized with the master.
