One of the most important procedure in setting up master-slave replication on Mysql database successfully is to have a consistent dump or backup of database(s) from the master server by locking all tables during mysqldump so write access is not possible.
master log file and log position will be taken from the master and configure it on the slave server. But it’s not always good times. There are some bad times too. And one of it is sudden stop of your slave and no longer synching with the master database.
I’ll give you a scenario. Before the replication starts, my slave server has an existing database named “flt”. Then you setup your replication without any problems and all goes well. Then one time, somebody created a database server named “flt” on the master server with a different tables or structure. Then that might confuse now the mysql process. You can see errors like cannot create database etc..
Another common problem also are duplicate entries caused by inconsistent backups that was restored on the slave server.
To check, inspect your mysqld.logs for errors, something like
100318 5:34:46 [ERROR] Slave: Error ‘Duplicate entry ‘flt-2010-01-30′ for key 1’ on query. Default database: ‘fltdb’. Query: ‘INSERT INTO flt_tracking(keyword,requested_date,request_count,flt_id) VALUES (‘flt’,’2010-01-30”)’, Error_code: 1062
On mysql prompt ,try to check the status of the slave by:
#mysql -u root -p
mysql> show slave status \G;
You will see from that the Slave_SQL_Running is set to No, indicating that the replication is broken.
To repair the replication, here are the steps that you can try:
mysql> SLAVE STOP;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql>SHOW SLAVE STATUS \G
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
1 row in set (0.00 sec)
As appeared above, Slave_IO_Running is Yes and Slave_SQL_Running is now set to Yes. Slave State is waiting for master to send Event.
From the mysqld log file, a similar output such as this will appear
100318 23:15:45 [Note] Slave SQL thread initialized, starting replication in log ‘FLT-bin.000058’ at position 603115919, relay log ‘./mysqld-relay-bin.000001’ position: 4
100318 23:15:45 [Note] Slave I/O thread: connected to master ‘firstname.lastname@example.org:3306’, replication started in log ‘FLT-bin.000051’ at position 603115919
If you still have duplicate entries, just rerun the process. But how if are too many and doing all over again is quite stupid already. Then you have an option to put the skip errors to mysql configuration
add this line under [mysqld]:
Then restart mysql.