Restart mysql replication after sql_error

Question

I have two mysql servers, one master and one slave.

Someone went on to the slave and created a table, then subsequently went on to the master and created the same table. Of course this DDL statement was replicated to the slave, causing an error, causing replication to stop at the point of the error.

How shoud I restart the replication process after either dropping the table on the slave or starting replication after that statement?

show slave status output:

mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: xx.xx.xx.xx Master_User: buildbot Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.024536 Read_Master_Log_Pos: 33489509 Relay_Log_File: mysqld-relay-bin.049047 Relay_Log_Pos: 32575097 Relay_Master_Log_File: mysql-bin.024476 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1050 Last_Error: Error 'Table 'checklist' already exists' on query. Default database: 'dbname'. Query: 'CREATE TABLE `checklist` ( `checklist_id` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(768) NOT NULL, `url` varchar(512) NOT NULL, `active` bit(1) NOT NULL, `insert_date` datetime NOT NULL, `xcred` int(11) NOT NULL, PRIMARY KEY (`checklist_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1' Skip_Counter: 0 Exec_Master_Log_Pos: 32574952 Relay_Log_Space: 6766519525 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 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2013 Last_IO_Error: error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 86400 Last_SQL_Errno: 1050 Last_SQL_Error: Error 'Table 'checklist' already exists' on query. Default database: 'dbname'. Query: 'CREATE TABLE `checklist` ( `checklist_id` int(11) NOT NULL AUTO_INCREMENT, `description` varchar(768) NOT NULL, `url` varchar(512) NOT NULL, `active` bit(1) NOT NULL, `insert_date` datetime NOT NULL, `xcred` int(11) NOT NULL, PRIMARY KEY (`checklist_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1' 

Answer 1

You can use the following commands (on mysql prompt):

mysql> STOP SLAVE; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> START SLAVE; mysql> SHOW SLAVE STATUS; 

The value 1 represents the number of statements to skip. You can do it repeatedly until the replication is fixed. You can see this page.

Answer 2

You don't. Effectively, you need to setup replication again from scratch as you did the first time, because if you just skip statements, you might lose integrity. In order to be safe, you have to replicate from a known safe starting point.

• Lock the master
• Dump the data using --master-data and noting the binlog coordinates (e.g. show master status)
• Unlock the master
• Load the dump into the slave
• Start slaving using 'change master' and the binlog coordinates you recorded earlier

Answer 3

If the masted still has the bin logs the correct way to restart replica without skipping transactions (which kind of defeats the whole purpose of the replica) is:

Form the output of "SHOW SLAVE STATUS" take note of

Relay_Master_Log_File: mysql-bin.024476 Exec_Master_Log_Pos: 32574952 

and then restart the replica with the information noted from the slave status:

STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_log_file='mysql-bin.024476', master_log_pos=32574952; START SLAVE;