Shulou(Shulou.com)05/31 Report--

This article is about how to skip errors in master-slave synchronization of mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Mysql master-slave synchronization skips errors: 1, skip a specified number of transactions, the code is [mysql > SET GLOBAL SQL_SLAVE_SKIP..]; 2, modify the configuration file of mysql, and skip all errors through the [slave_skip_errors] parameter.

Mysql master-slave synchronization skips the wrong method:

1. Skip the specified number of transactions:

Mysql > slave stop;mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 # Skip a transaction mysql > slave start

2. Modify the configuration file of mysql and skip all errors or specified types of errors through the slave_skip_errors parameter

Vi / etc/my.cnf [mysqld] # slave-skip-errors=1062,1053,1146 # Skip errors of specified error no type # slave-skip-errors=all # skip all errors

Example: let's simulate an error scenario.

Environment (a master-slave replication environment that has been configured)

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

View replication status on slave

Mysql > show slave status\ gateway * 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: NULLMaster_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: 01 row in set (131210 sec)

As can be seen from the results, there was an error in Last_SQL_Error: Error 'Table' mysql.t1' doesn't exist' on query in Read_Master_Log_Pos: 2341.

Because only binlog is recorded for mydb, an error occurs when a table from another database is manipulated in the mydb library, but the table does not exist on slave.

Thank you for reading! This is the end of the article on "how to skip errors in mysql master-slave synchronization". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.