MySQL master-slave replication encounters an error handling method

MySQL in the master-slave replication often encountered errors caused by slave replication interrupt, this time the need for manual intervention to skip this error, in order to make the slave end of the replication, to continue;

To skip the wrong way:

MariaDB [(None)]> STOP SLAVE; MariaDB [(None)]> SET GLOBAL sql_slave_skip_counter=1;  MariaDB [(None)]> SHOW GLOBAL VARIABLES like ' sql_slave_skip_counter '; #跳过一个事务 +------------------------+-------+| variable_name | Value |+------------------------+-------+| Sql_slave_skip_counter | 1 |+------------------------+-------+mariadb [(none)]> START SLAVE;

For example:

Slave the state when the error occurred:

mariadb [(None)]> show slave status\g*************************** 1. row                  Slave_IO_State: Waiting for master to send event                   Master_Host:  192.168.180.100                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000004           read_master_log_pos: 658                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 601         Relay_Master_Log_File: mysql-bin.000001              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  ' test ';  database doesn ' t exist '  on query.  Default database:  ' test ' . query:  ' drop database test '                   Skip_Counter: 0           Exec_Master_Log_Pos: 313               Relay_Log_Space: 4079               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: nullmaster_ssl_verify_ server_cert: no                 last_io_errno: 0                 last_io_error:                 last_sql_errno :  1008               last_sql_ error: error  ' Can ' t drop database  ' test ';  database doesn ' t exist '  on query. Default database:  ' test ' . query:  ' drop database test '   Replicate_Ignore_Server_Ids:               Master_Server_Id: 1                Master_SSL_Crl:             Master_SSL_Crlpath:                    &nbSp using_gtid: no                   Gtid_IO_Pos:       Replicate_Do_Domain_Ids:    Replicate_Ignore_Domain_Ids:                  parallel_mode: conservative

Use the above method to restore the slave-side copy of MySQL to normal:

mariadb [(None)]> stop slave; mariadb [(None)]> set global  sql_slave_skip_counter=1; mariadb [(none)]> show global variables like  ' sql_slave_skip_counter '; +------ ------------------+-------+| variable_name          |  value |+------------------------+-------+| sql_slave_skip_counter | 1      |+------------------------+-------+mariadb [(none)]> start slave; mariadb [(None)]> show slave status\g*************************** 1. row                  Slave_IO_State: Waiting for master to send event                   Master_Host:  192.168.180.100                   master_user: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000004           Read_Master_Log_Pos: 658                Relay_Log_File: relay-bin.000006                 Relay_Log_Pos: 537         Relay_Master_Log_File: mysql-bin.000004              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: 658               Relay_Log_Space: 1526               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: 0master_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_SSL_Crl:             master_ssl_crlpath:                     Using_Gtid: No                   Gtid_IO_Pos:        Replicate_Do_Domain_Ids:   Replicate_Ignore_Domain_Ids:                  parallel_mode: conservative

To this, MySQL master-slave replication is back to normal ... Done

MySQL master-slave replication encounters an error handling method