(go) use parameter sql_slave_skip_counter to handle MySQL SLAVE synchronization error Discussion

Handling MySQL SLAVE synchronization error discussions using parameter Sql_slave_skip_counter

This article link address: http://blog.chinaunix.net/uid-31396856-id-5753206.html

This article discusses using Sql_slave_skip_counter to cause SQL threads to skip event records that encounter errors
The cases are as follows:
Database data cannot be synchronized, check for synchronization status
MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.21.28
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:master-bin.000010
read_master_log_pos:137752106
relay_log_file:relay-bin.000004
relay_log_pos:670
relay_master_log_file:master-bin.000009
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:1008
Last_error:error ' Can ' t drop database ' EMODB '; Database doesn ' t exist ' on query. Default database: ' Emodb '. Query: ' Drop database Emodb '
skip_counter:0
exec_master_log_pos:1379
relay_log_space:320095465
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:1008
Last_sql_error:error ' Can ' t drop database ' EMODB '; Database doesn ' t exist ' on query. Default database: ' Emodb '. Query: ' Drop database Emodb '
Replicate_ignore_server_ids:
Master_server_id:28
1 row in Set (0.00 sec)


found that the repository does not exist when performing a delete emodb operation. As to why the repository has not been the cause of the emo database, press no table, continue processing down:
Preferred to turn off synchronization:
MariaDB [(none)]> stop slave;
Query OK, 0 rows Affected (0.00 sec)


Skip this error operation
MariaDB [(None)]> SET GLOBAL sql_slave_skip_counter = 1;
Query OK, 0 rows Affected (0.00 sec)

Then turn on sync to check if synchronization is working.
MariaDB [(None)]>
MariaDB [(None)]> start slave;
Query OK, 0 rows Affected (0.00 sec)


MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.21.28
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:master-bin.000010
read_master_log_pos:137752106
relay_log_file:relay-bin.000004
relay_log_pos:761
relay_master_log_file:master-bin.000009
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:1007
Last_error:error ' Can ' t create database ' confluence '; Database exists ' on query. Default database: ' Confluence '. Query: ' Create database confluence DEFAULT CHARACTER SET UTF8 '
skip_counter:0
exec_master_log_pos:1470
relay_log_space:320096038
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:1007
Last_sql_error:error ' Can ' t create database ' confluence '; Database exists ' on query. Default database: ' Confluence '. Query: ' Create database confluence DEFAULT CHARACTER SET UTF8 '
Replicate_ignore_server_ids:
Master_server_id:28
1 row in Set (0.00 sec)
Follow the above approach and proceed with the process
MariaDB [(none)]> stop slave;
Query OK, 0 rows Affected (0.00 sec)


MariaDB [(None)]> SET GLOBAL sql_slave_skip_counter = 1;
Query OK, 0 rows Affected (0.00 sec)


MariaDB [(None)]>
MariaDB [(None)]>
MariaDB [(None)]> start slave;
Query OK, 0 rows Affected (0.00 sec)


MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.21.28
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:master-bin.000010
read_master_log_pos:137752106
relay_log_file:relay-bin.000004
relay_log_pos:57581283
relay_master_log_file:master-bin.000009
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:57581992
relay_log_space:320096611
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:83248
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:28
1 row in Set (0.00 sec)


Okay, keep watching.
MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.21.28
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:master-bin.000010
read_master_log_pos:137752193
relay_log_file:relay-bin.000008
relay_log_pos:617
relay_master_log_file:master-bin.000010
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:137752193
relay_log_space:1190
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:28
1 row in Set (0.00 sec)


MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Confluence |
| Dbyang |
| MySQL |
| Performance_schema |
| Temp |
+--------------------+
6 rows in Set (0.00 sec)


Under test
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Confluence |
| MySQL |
| Performance_schema |
| Temp |
+--------------------+
5 rows in Set (0.00 sec)


MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.21.28
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:master-bin.000010
read_master_log_pos:137752278
relay_log_file:relay-bin.000008
relay_log_pos:702
relay_master_log_file:master-bin.000010
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:137752278
relay_log_space:1275
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:28
1 row in Set (0.00 sec)
Although the master and slave start synchronizing, but also need to check the master and slave data is consistent, but this is not within the scope of this article.
Description
1. ParametersSql_slave_skip_counter=n is a transaction synchronization that skips n transactions and continues into the back. Here is the main library in the operation of the drop database synchronization to Slave,slave has no drop of the database, this situation is skipped, does not affect the master-slave inconsistency.
2, if easy to use easily caused by the Lord is never consistent, need to use caution, should be used before specific problems specific analysis.
3, if need to use sql_slave_skip_counter, need to clearly skip the event is what operation;
4, if the use of parameter Sql_slave_skip_counter, you need to check the consistency of master-slave data.
---The end

(go) use parameter sql_slave_skip_counter to handle MySQL SLAVE synchronization error Discussion