MySQL master-slave replication, often encounter errors resulting in slave-side replication interrupt, this time generally requires manual intervention, skipping errors to continue
There are two ways of skipping errors:
1. Skip a specified number of transactions:
Mysql>slave stop;
Mysql>set GLOBAL sql_slave_skip_counter = 1 #跳过一个事务
Mysql>slave start
2. Modify the MySQL configuration file by slave_skip_errors parameters to jump all errors or specify types of errors
Vi/etc/my.cnf
[Mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error No type error
#slave-skip-errors=all #跳过所有错误
Example: Simulate an error scenario below
Environment (a well-configured master-slave replication environment)
master database ip:192.168.247.128
Slave database ip:192.168.247.130
MySQL version: 5.6.14
Binlog-do-db = MyDB
Execute the following statement on master:
Mysql>use MySQL;
Mysql>create table t1 (id int);
Mysql>use MyDB;
Mysql>insert into mysql.t1 select 1;
Viewing the replication status on slave
Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.247.128
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000017
read_master_log_pos:2341
relay_log_file:dbtest1-relay-bin.000011
relay_log_pos:494
relay_master_log_file:mysql-bin.000017
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:1146
Last_error:error ' Table ' mysql.t1 ' doesn ' t exist ' on query. Default database: ' MyDB '. Query: ' INSERT into MYSQL.T1 select 1 '
skip_counter:0
exec_master_log_pos:1919
relay_log_space:1254
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:0
Last_io_error:
last_sql_errno:1146
Last_sql_error:error ' Table ' mysql.t1 ' doesn ' t exist ' on query. Default database: ' MyDB '. Query: ' INSERT into MYSQL.T1 select 1 '
Replicate_ignore_server_ids:
Master_server_id:1
Master_uuid:f0f7faf6-51a8-11e3-9759-000c29eed3ea
Master_info_file:/var/lib/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
last_sql_error_timestamp:131210 21:37:19
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)
From the results can be seen, read_master_log_pos:2341,exec_master_log_pos:1919 error last_sql_error:error ' Table ' mysql.t1 ' doesn ' t exist ' On query.
Because only the binlog is recorded for MyDB, when the table in the MyDB library is operated on the other database, the table is faulted when it does not exist on the slave.
We can look at the contents of the transaction in Binlog, where a row represents a transaction
Mysql> SHOW BINLOG EVENTS in ' mysql-bin.000017 ' from 1919\g
1. Row ***************************
log_name:mysql-bin.000017
pos:1919
Event_type:query
Server_id:1
end_log_pos:1999
Info:begin
2. Row ***************************
log_name:mysql-bin.000017
pos:1999
Event_type:query
Server_id:1
end_log_pos:2103
Info:use ' MyDB '; INSERT INTO MYSQL.T1 Select 1
3. Row ***************************
log_name:mysql-bin.000017
pos:2103
Event_type:xid
Server_id:1
end_log_pos:2134
Info:commit/* xid=106 */
4. Row ***************************
log_name:mysql-bin.000017
pos:2134
Event_type:query
Server_id:1
end_log_pos:2213
Info:begin
5. Row ***************************
log_name:mysql-bin.000017
pos:2213
Event_type:query
Server_id:1
end_log_pos:2310
Info:use ' MyDB '; INSERT INTO T1 Select 9
6. Row ***************************
log_name:mysql-bin.000017
pos:2310
Event_type:xid
Server_id:1
end_log_pos:2341
Info:commit/* xid=107 */
6 rows in Set (0.00 sec)
From the above results, we need to skip two transactions (pos:1999 insert,pos:2103 commit)
Skip Operation:
Mysql>slave stop;
Mysql>set GLOBAL Sql_slave_skip_counter = 2 skips a transaction
Mysql>slave start
Mysql> Show Slave Status\g
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection Id:3
Current Database:mydb
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.247.128
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000017
read_master_log_pos:3613
relay_log_file:dbtest1-relay-bin.000018
relay_log_pos:283
relay_master_log_file:mysql-bin.000017
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:3613
relay_log_space:458
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:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
Master_server_id:1
Master_uuid:f0f7faf6-51a8-11e3-9759-000c29eed3ea
Master_info_file:/var/lib/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.01 sec)
Replication status is normal.
MySQL master-slave replication skipped error
