Shulou(Shulou.com)06/01 Report--

These inconsistent data tables were found through the pt-table-checksums above.

(https://blog.51cto.com/li02yu/2340403)

How to synchronize data That is, how to fix the inconsistent data of MySQL masters and make them consistent? Use another tool, pt-table-sync.

How to use it:

Pt-table-sync [OPTIONS] DSN [DSN]

Pt-table-sync: efficient synchronization of data between MySQL tables, it can do one-way and two-way synchronization of table data. He can synchronize a single table or an entire library. It does not synchronize table structures, indexes, or any other schema objects. So you need to make sure that their tables exist before fixing the consistency.

A sql statement that repairs the data of a table through (--print) printing

[lxh@MySQL-01] # pt-table-sync-- print-- databases=test-- table=test1-- sync-to-master hobbies 172.31.153.164packs, mysql.163packs, mysql.163packs, 3306.

Use (--print) to print out a sql statement to repair a library data.

[lxh@MySQL-01] # pt-table-sync-- print-- databases=test-- sync-to-master hobbies 172.31.153.164 packs mysql.163packs 3306

Use (--print) to print out sql statements to repair all databases (not recommended)

[lxh@MySQL-01 ~] # pt-table-sync-- print-- sync-to-master hackers 172.31.153.164Mysql.163 copy code-- replicate=: specifies the table obtained through pt-table-checksum, and both tools will be used almost all the time. If not specified, the default-- databases=: specifies the database to perform synchronization, separated by commas. -- tables=: specifies the tables that perform synchronization, separated by commas. -- sync-to-master: specify a DSN, the slave IP, who will automatically find the master through show processlist or show slave status. Slave 172.31.153.164: server address, address of the server. U=lxh: account number. P=Mysql.163: password. -- print: prints, but does not execute commands. -- execute: execute the command.

Explain the effect of the execution together: the sql statement to repair the data is printed out through (--print), which can be manually executed from the row to keep their data consistent. Can it be carried out directly? Of course, through (--execute)

[lxh@MySQL-01] # pt-table-sync hobby 172.31.153.164 execute

No exceptions are found, and then check the consistency of the master-slave data:

[lxh@MySQL-01] # pt-table-checksum-uplift lxh'-pendant Mysql.163'-h272.31.153.163-- databases=test-- nocheck-binlog-format-- nocheck-plan-- nocheck-replication-filters-- recursion-method=processlist TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE04-13T16:27:28 0031 10 0.097 test.test1

OK, the data are consistent. However, it is recommended to print it out with-- print, so that you can know that there is something wrong with the data and can intervene artificially. Otherwise, it will be carried out directly, and it will be even more difficult to deal with when problems arise. In short, the backup of the data should be done before processing.

Note: if there is no unique index in the table or the primary key will report an error:

Can't make changes on the master because no unique index exists at / usr/local/bin/pt-table-sync line 10591. You need to add a primary key or unique index to the table

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.