Analysis of common faults in master-slave synchronization

Source: Internet
Author: User

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/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.