We have 2 servers that will be configured as Circular Replication. It means both servers act as master and slave to another server. And Master server was going down because hardware failure and need restart Master server.
When Master server is going up, repication with slave is not running. 
Check replication on slave
And one of Slave_IO_Running or Slave_SQL_Running is set to No, then the replication is broken:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001079
Read_Master_Log_Pos: 269214454
Relay_Log_File: slave-relay.000130
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: mydb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error ‘Table ‘mydb.taggregate_temp_1212047760′ doesn’t exist’ on query. Default database: ‘mydb’.
Query: ‘UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
SET thread.views = thread.views + aggregate.views
WHERE thread.threadid = aggregate.threadid’
Skip_Counter: 0
Exec_Master_Log_Pos: 203015142
Relay_Log_Space: 166325247
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: NULL
1 row in set (0.00 sec)
So we can repair using “SET GLOBAL sql_slave_skip_counter Syntax” on slave.
Just to go sure, we stop the slave:
Fixing the problem is actually quite easy. We tell the slave to simply skip the invalid SQL query:
This tells the slave to skip one query (which is the invalid one that caused the replication to stop). If you’d like to skip two queries, you’d use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; instead and so on.
That’s it already. Now we can start the slave again…
… and check if replication is working again:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001079
Read_Master_Log_Pos: 447560366
Relay_Log_File: slave-relay.000130
Relay_Log_Pos: 225644062
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb
Replicate_Ignore_DB:
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: 447560366
Relay_Log_Space: 225644062
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: 0
1 row in set (0.00 sec)
We can ignore more than 1 query at a time, by simply change the number variable for “SQL_SLAVE_SKIP_COUNTER” to any number we want to skip.
There’s another way of doing this, we can tell MySQL to ignore all error with specific error codes. As example, we can tell MySQL to skip all error with 1062 error code, which is “duplicate entry” error number.
Beside error with 1062 error code,we can skip more than one error code by putting them in my.cnf “slave-skip-errors” option, put all error codes we want to skip, separated by comma (,). The complete list of MySQL error codes can be found here.
Great post! I’d like to see something that’s pretty simple and easy to understand at a glance, but with a lot of motion and activity to reflect the vibrancy of the community. Can’t wait to see what you and morgamic come up with.
Thanks a lot man! u solved my problem ;)
I am extremely inspired with your writing talents
as well as with the format in your blog. Is that this a paid topic or did you
modify it yourself? Either way keep up the nice high quality writing, it’s uncommon to peer a nice blog like this one nowadays..