1 introduction

mysql during master-slave replication, due to various reasons, the slave server may encounter SQL errors in BINLOG execution. By default, the server will stop the replication process, no longer synchronize, and wait until the user handles it by himself.

slave-skip-errors is used to define the error number that can be automatically skipped from the server during the replication process. When the error number defined during the replication process is encountered, it can be automatically skipped and directly execute the following SQL statement.

2 Official Reference

Command-Line Format	–slave-skip-errors=name
System Variable Name	slave_skip_errors
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
Type	string
Default	OFF
Valid Values	OFF
[list of error codes]
all
ddl_exist_errors

The slave_skip_errors option has four values available, which are:
Off, all, ErorCode, DDL_exist_errors.

by default, this parameter value is off, we can list the specific error code, you can also choose all, mysql5.6 and MySQL Cluster NDB ddl_exist_errors version 7.3 and subsequent increase the parameter, the parameter contains a series of error code (1007100 8105 0105 1105 4106 0106 1106 8109 4114 6)

some error codes represent the following errors:

Database exists, database creation failed

Database does not exist, database deletion failed

Data table exists. Failed to create data table

Data table does not exist. Deleting data table failed

1054: The field does not exist, or the program file conflicts with the database

1060: Field duplicates and cannot be inserted

1061: Duplicate key name

1068: Multiple primary keys are defined

1094: Location thread ID

1146: Data table missing, please restore database

1053: The primary server went down during replication

1062: Primary key conflict Duplicate entry ‘%s’ for key %d

My. Written in CNF:

slave_skip_errors=1062,1053
slave_skip_errors=all
slave_skip_errors=ddl_exist_errors

As a mysql startup parameter:

--slave-skip-errors=1062,1053
--slave-skip-errors=all
--slave-skip-errors=ddl_exist_errors

View the value of this parameter from the database:

mysql> show variables like 'slave_skip%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| slave_skip_errors | 1007  |
+-------------------+-------+

3. Analysis of Examples

3.1 test instructions
configure mysql master-slave synchronization, and then write data on the slave, causing master-slave inconsistency.
3.2 prepare test table structure
create table on host:

create table replication (c1 int not null primary key, c2 varchar(10));

3.3 Preparation of test data

inserts the underlying data on the host

mysql> insert into replication values (1, 'test1');
mysql> insert into replication values (2, 'test2');

At this point, the host has two records
from the machine replication table. 3.4 starts testing
and inserts one record
from the machine

mysql> insert into replication values (3, 'test3');

Then do the same on the host

mysql> insert into replication values (3, 'test3');

View the replication status on the slave

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.222
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 16700
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 16595
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: mysql.ibbackup_binlog_marker
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.backup_%
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '3' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into replication values (3, 'test3')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 16425
              Relay_Log_Space: 17544

You can see that the SQL thread has stopped working Slave_SQL_Running: No

error number is: Last_Errno: 1062

Error message: Last_Error: Error ‘Duplicate entry ‘3’ for key ‘PRIMARY’ on query.default database: ‘test’. Query: ‘insert into replication values (3, ‘test3′)’

if we add the following option in my.cnf, we can skip this error and the data synchronization continues.

[mysqld]
slave_skip_errors=1062

The specific test method is the same as above, you can verify by yourself.

4 Some explanations for copying errors from machine while recovering from BACKUP
Meb, the mysql Enterprise edition backup tool, provides online hot standby function. If DDL operation is performed during the backup process, the slave machine may be abnormal when it needs to restore from the backup of the host machine, thus causing the slave machine to fail to synchronize data. Reason from the machine need to recover from a backup file when recovery (DDL statements contained in the backup process execution), synchronization is not from perfect after the last position synchronization, but from the last position of the DDL synchronization, if again the DDL statements from the machine will not result in a conflict, which synchronous continue, if can lead to conflict, synchronization is terminated. The solution to this conflict is to add a line to the my.cnF file

[mysqld]
slave_skip_errors=ddl_exist_errors

5 Matters needing Attention

5.1 this parameter is a global static parameter, which cannot be dynamically adjusted. It can take effect by restarting mysql server after adding this parameter list in my.cnf.

5.2 it must be noted that if this parameter is started improperly, it is likely to cause the data in the master-slave database to be out of sync. In the application, it needs to be based on the actual situation. If the data integrity requirements are not very strict, then this option can indeed reduce the maintenance cost

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
the original address: http://blog.csdn.net/jesseyoung/article/details/40585809
the blog home page: http://blog.csdn.net/jesseyoung
****************************************************************************************