Analysis of common faults in master-slave synchronization

Master-slave Sync, common fault!

1. Delete a record on master, not found on the slave.

Last_sql_error.could not execute Delete_rows the event on table ... error_code:1032 ...

2, primary key conflict, in slave already have this record, and in master inserted the same record

Last_sql_error.could not execute Write_rows the event on table ... error_code:1062 ...

3. Update a record on master, no data found on slave

Last_sql_error.could not execute Update_rows event ... error_code:1032 ...

The above three kinds of cases in the HA switching process, because of asynchronous replication and sync_binlog=0, will cause a small number of Binlog did not accept the end of the error.

4, Slave relay log Damage

Last_sql_error:error Initializing relay Log postion:i/o Error reading the header from the binary log

Last_sql_error:error Initializing relay log positon:binlog has bad magic number:it ' s not a binary log file, can be use D by this version of MySQL.

Slave downtime or illegal shutdown, power failure, hardware failure, resulting in damage to the relay log.

Workaround:

For the first case: Master will delete a record, and the slave can not find the corresponding record on the error. Master has been deleted, slave did not change the record, you can skip directly:

Stop slave; Set global Sql_slave_skip_counter=1;start slave;

For monitoring the situation: the situation alarm, how to control it? Call script?

Processing for the second case: for duplicate records, duplicate key values need to be removed.

The third case: Master Update record, not found on the slave. Through Mysqlbinlog analysis of real statements, slave missing records for manual filling.

Fourth: Find the location where the salve synchronization executes to master. Redo the synchronization.

Find variables:

Relay_master_log_file:

Exec_master_log_pos:

Resynchronize from these two locations.

If the business allows, you can set the following two variables:

slave-skip-errors=1022,1032,1062 (must be set in MY.CNF, read-only system variable)

(1022:message:can ' t write; Duplicate key in table '%s ')

(1032:message:can ' t find record in '%s ')

(1062:message:duplicate entry '%s ' for key%d)

Slave_exec_mode (can be set in all variables)

The options are strict (default) and idempotent mode, and strict encounters any problems that can cause replication to stop.

Idempotent skipped for duplicate key and No-key-found error.

Note: The above two parameter settings, if skipped, are logged in the error log. How to monitor the problem when it appears? Question one! ）

We can solve the error, the data consistency of the master and how should we verify it? can refer to this article here:

http://weipengfei.blog.51cto.com/1511707/976545

In the case of inconsistencies between master and subordinate data, how to fix it?

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1068258

See more highlights of this column: http://www.bianceng.cn/database/basis/