Master-Slave Replication inconsistency resolution

Master-Slave Replication inconsistency resolution:

Reasons for inconsistency:

1, the master and slave switch, the fault occurred

2. Operation Error

3, machine failure, etc.

Method One: The use of MySQL native mysqldump can be done, for the individual table has a lock table situation.

can refer to: http://hcymysql.blog.51cto.com/5223301/951260

The core is mysqldump–single-transaction--master-data=2.

Start slave until master_log_file= ', master_log_pos=;

Until the sql_thread thread is no, this synchronization error is skipped.

Method Two: Tools: Pt-table-sync

Effective synchronization of data in MySQL tables.

It is a good idea to back up the operation before. For a master-slave structure, it only needs to operate on master (whether the option is –reolicate or –sync-to-master), which is often the safest way to keep master and slave synchronized. Direct modification of replica may cause many problems.

considerations;--dry-run view the tool to operate with that algorithm.

--print How to specifically solve the inconsistency between the master and the subordinate.

Be cautious about the form of a master copy.

When a table contains a foreign key and is on the delete or on update constraint, it is recommended that it be temporarily unused and may cause accidental damage to the table.

Part of the bug. --lock-and-rename does not work for db less than MySQL 5.5 (consider replacing with Pt-online-schema-change?). ）。

Detailed

Pt-table-sync can do a single or bidirectional synchronization of a table's data. It does not synchronize the structure, index, or other database objects of a table, just a row record.

The following one-way table data synchronization

Introduce three concepts first:--replicate, find differences, specifying

Here's the short logic:

If DSN has a T part, sync only that table:   if 1 DSN:      if--sync-to-master: The         DSN is a slave. Connect to its master and sync.   If more than 1 DSN: the The "The" is the      source. Sync each DSN in Turn.else if--replicate:   if--sync-to-master: The      DSN is a slave. Connect to it master, find records      of differences, and fix.   Else: The DSN is the      master. Find slaves and connect to each, find      records of differences, and fix.
else:
   if only 1 DSN and--sync-to-master: The      DSN is a slave. Connect to it master, find tables      and filter with--databases etc, and sync each table to the master.   else: Find      tables, filtering with--databases etc, and sync all      DSN to the

By default, there is no-replicate option (this option uses several algorithms to automatically discover the difference between master and slave), and this option can also take advantage of problems that Pt-table-checksum has checked out. Strictly speaking, we do not need to use the –replicate option because it automatically discovers inconsistencies between the data in the table. But the reason people combine two tools is: Pt-table-checksum regularly check table data, when found inconsistent with pt-table-sync to repair.

How to determine the direction of data synchronization; Use –sync-to-master or omit. This option value requires a slave DSN parameter. Automatically discovers the master of the slave and starts synchronizing with master. The way to do this is to make changes at the master and to resynchronize slave with master via the MySQL replication mechanism. Note: If there is only one slave that no problem, multiple slave, may also be subject to the same data changes.

If you do not use –sync-to-master, the first DSN parameter must be source host (with and only one), and without the –replicate option, we must write a DSN parameter as the destination host. Can have multiple destination host. Source and destination must be independent and not within the same replication structure. If Pt-table-sync detects that the target host is a slave in the same replication topology, the tool will complain. Because some of the changes made to the data are written directly to the destination host (the modified data is written directly to a slave, and if you refer to the Binlog log, you may get an error.) Or if we use the –replicate option, Pt-table-sync expects the DSN parameter to be master, so that the tool can discover all of the master's slave at once and let slave synchronize with master again. (--sync-to-master only update one slave)

The first DSN parameter of the tool would like to provide some default parameters for other DSNs, such as DB and table, we can write all of them manually, or let the tools be discovered automatically.

For example: Pt-table-sync--execute h=host1,u=msandbox,p=msandbox H=host2

The DSN parameter of Host 2 inherits the properties of U and P from host1. You can see how Pt-table-sync translates the parameters on the command line using the –explain-hosts option.

Output:

With –verbose, the details of the process for each action sheet are displayed:

# Syncing H=host1,d=test,t=test1

# DELETE REPLACE INSERT UPDATE algorithm START    end      EXIT DATABASE. TABLE

#      0       0      3      0 Chunk     13:00:00 13:00:17 2    test.test1

The Test1 table in test db on Host1 is inserted into 3 records. And the algorithm used is chunk, the time from 13:00:00 lasted 17 seconds. Because the difference between master and slave is found, the exit status is 2.