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 'user@xx.xx.xx.xx: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'

score 19 · Accepted Answer · answered Dec 06 '10 at 16:21

19

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.

answered Dec 06 '10 at 16:21

Khaled

35,688
8
69
98

3

+1 I've used this many times, however it's important to understand what you're doing when you run this. It can lead to data integrity issues. In cases when a dump file has been loaded into the wrong box (i.e. slave not master) I've skipped 100s of queries (having first checked this will do no harm!). This can avoid the process of re-setting up the whole of replication again. – Coops Feb 10 '11 at 22:06
1

Yes, you are right. This should be done with care. It is useful in case you have an invalid query which stopped the replication. You can just skip it. You can also check the relevant table(s) after this to make sure you did not lose data between master and slavee. – Khaled Feb 11 '11 at 07:17
This can also be done on phpMyAdmin in "replication" tab https://imgur.com/vonwUQU – Meloman Jun 04 '22 at 05:58

score 3 · Answer 2 · answered Dec 06 '10 at 16:24

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

Restart mysql replication after sql_error

2 Answers2