Repair broken MySQL Replication
There are times, when replication stop working between mysql servers because of the invalid queries.
First of all find out whether mysql replication is running and if not what caused it to stop.
Usually the logs are located at /var/log/mysql
In order to make sure.. log in to MySQL:
mysql > SHOW SLAVE STATUS \G
If the replication is broken , stop the slave :
mysql> STOP SLAVE;
Now skip the query that caused the replication to stop :
mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; (if you need to skip 2 or more, type the number, 2 or more )
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G
Now the replication should be working…….
Note: There is another way of doing this, we can tell MySQL to ignore all error with specific error codes. In other words we can tell MySQL to skip all error with 1062 error code which is “duplicate entry” error code.
slave-skip-errors = 1062