How To Skip Certain Errors In MySQL Replication

http://www.howtoforge.com/how-to-skip-certain-errors-in-mysql-replication


MySQL replication is nice, however it can happen that it stops because of an error, and restoring a working replication can be hard - you need to set locks on the master to get a consistent MySQL dump, and during that time websites are not accessible. However there's a way to make the MySQL slave ignore certain errors using the slave-skip-errors directive.
I do not issue any guarantee that this will work for you!

1 Preliminary Note

Using slave-skip-errors you can make the replication slave ignore certain error codes (you can find the list of MySQL error codes here: Server Error Codes and Messages). However, you should use this with caution - it should be your last instrument trying to restore replication because it can make your slave inconsistent. You should first try one of these: How To Repair MySQL Replication or use the replicate-ignore-db or replicate-ignore-table statements in the slave's my.cnf file to skip replication for databases/database tables that cause replication to fail (if you don't need replication for these databases/database tables).

2 Using slave-skip-errors

Let's assume replication has stopped, and you are getting something like this on the slave's MySQL shell:

SHOW SLAVE STATUS \G

mysql> SHOW SLAVE STATUS \G
*************************** 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.000024
          Read_Master_Log_Pos: 933201702
               Relay_Log_File: mysqld-relay-bin.000113
                Relay_Log_Pos: 63519994
        Relay_Master_Log_File: mysql-bin.000021
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,test
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1054
                   Last_Error: Error 'Unknown column 'tx_feuserbranch_agb' in 'field list'' on query. Default database: 'dbtest'.
Query: 'INSERT INTO fe_users (email,first_name,last_name,address,ip,city,country,telephone,fax,company,tx_feuserbranch_brancha,
tx_feuserbranch_customernr,tx_feuserbranch_agb,username,password,name,usergroup,disable,by_invitation,tx_srfeuserregister_password,
tstap,crdate,cruser_id,fe_cruser_id,pid) VALUES ('test333@example.com','John','Doe','test','55555','test','Deutschland','49111111111',
'','test','0','55','on','test333@example.com','xxxxxxxxxxxxxx','John Doe','0','1','0','','1361359747','1361359747','0','0','33')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120460827
              Relay_Log_Space: 3500841420
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/newcerts/client-key.pem
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1054
               Last_SQL_Error: Last_Error: Error 'Unknown column 'tx_feuserbranch_agb' in 'field list'' on query. Default database: 'dbtest'.
Query: 'INSERT INTO fe_users (email,first_name,last_name,address,ip,city,country,telephone,fax,company,tx_feuserbranch_brancha,
tx_feuserbranch_customernr,tx_feuserbranch_agb,username,password,name,usergroup,disable,by_invitation,tx_srfeuserregister_password,
tstap,crdate,cruser_id,fe_cruser_id,pid) VALUES ('test333@example.com','John','Doe','test','55555','test','Deutschland','49111111111',
'','test','0','55','on','test333@example.com','xxxxxxxxxxxxxx','John Doe','0','1','0','','1361359747','1361359747','0','0','33')'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql>

As you can see in the Last_Errno row the error code of the error that caused replication to stop is 1054 (Unknown column '%s' in '%s'). If you want the MySQL slave to ignore such errors, just open your my.cnf (on Debian and Ubuntu it's /etc/mysql/my.cnf)...

vi /etc/mysql/my.cnf

... and add the line slave-skip-errors = 1054:

[...] expire_logs_days = 10 max_binlog_size = 100M server-id=2 replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema replicate-ignore-db = test slave-skip-errors = 1054 [...]

Restart MySQL afterwards:

/etc/init.d/mysql restart

Log into MySQL again...

mysql -u root -p

... and check the slave's status again - it should now be working again:

SHOW SLAVE STATUS \G

To make the slave ignore multiple error codes, just separate them by commas:

slave-skip-errors = 1062,1054

You can find the list of MySQL error codes here: Server Error Codes and Messages

3 Links

• MySQL: http://www.mysql.com/
• MySQL Error Codes and Messages: http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
• slave-skip-errors: http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_slave-skip-errors