Fixing mysql replication errors in a master/slave setup

If you have a mysql master/slave replication setup and have run into replication errors, you can follow the below instructions to fix up the replication break and sync up the data.
1) Stop mysql on the slave.

service mysql stop

2) Login to the master.

3) Lock the master by running the following command

mysql> flush tables with read lock;

NOTE: No data can be written at this time to the master, so be quick in the following steps if your site is in production. It is important that you do not close mysql or this running ssh terminal. Login to a separate ssh terminal for the next part.

4) Now, in a separate ssh screen, type the following command from the master mysql server.

rsync -varPe ssh /var/lib/mysql root@IP_ADDRESS:/var/lib/ —delete-after

5) After this is done, now you must get the binlog and position from the master before you unlock the tables. Run the following command on master

mysql> show master status\G;

Then you’ll get some information on master status. You need the file and the position because that’s what you’re going to use on the slave in a moment. See step 10 on how this information is used, but please do not skip to step 10.

6) Unlock the master. It does not need to be locked now that you have the copy of the data and the log position.

mysql> unlock tables;

7) Login to the slave now via ssh. First remove the two files : /var/lib/mysql/master.info and /var/lib/mysql/relay-log.info

8) Start mysql on the slave.

service mysqld start

9) Immediately login to mysql and stop slave

mysql> slave stop; 

10) Now, you have to run the following query filling in the information from the show master status above (Step 5.)

mysql> CHANGE MASTER TO MASTER_HOST=MASTER_IP,
mysql> MASTER_USER=‘replicate’, MASTER_PASSWORD=‘replicate’,
mysql> MASTER_LOG_FILE=‘mysql-bin.000001’, MASTER_LOG_POS=1234512351;

11) Now start the slave.

mysql > slave start

12) Check slave status.

mysql> show slave status\G;