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
****************************************************************************************
