4. PT table checksum to verify master-slave data

4.0 references and installation

Reference documents:

https://www.percona.com/doc/percona-toolkit/3.0/pt-table-checksum.html
https://www.cnblogs.com/dbabd/p/10653408.html
http://seanlook.com/2015/12/29/mysql_replica_pt-table-checksum/
http://keithlan.github.io/2016/05/25/pt_table_checksum/

Installation:

Download the latest rpm package at https://www.percona.com/downloads/percona-toolkit/LATEST;

yum install percona-toolkit-*.rpm -y

4.1. Pt table checksum verification principle

PT table checksum is one of the percona toolkit tools, which can be used to check the consistency of data in the master and slave databases. By opening the general log to observe the actions of the master-slave database, you can learn:

1. Connect the master database and slave database, query the current database server information, including parameter settings, load information, etc;
2. Set the session level parameters according to the tool options, and set the binlog row format of the session level to STATEMENT;
3. Create a validation result table (percona.checksums table by default) according to the tool options to view the current database server running status;
4. Obtain the database and table for verification and check them one by one;
5. Start to analyze the table, and divide the table into multiple chunks according to the index of the table (if any). Each chunk contains multiple rows, which is 1000 by default. The number of table rows corresponding to the chunk can be dynamically adjusted according to the performance status of the database;
6. Generate table verification statements according to the above analysis, and check the table structure;
7. Start the table verification analysis. In order to ensure the consistency, in this stage, the row contained in the current chuck will be added with an uplink lock, and the verification result will be stored in the verification result table in the replace into mode;
8. Query the verification result table again, and update the values of master ﹣ CRC and master ﹣ CNT (master database). If it is a slave database, it is this ﹣ CRC and this ﹣ CNT;
9. Adjust the number of rows contained in the next chunk according to the running status of the database;
10. Continue to check the next chunk until all chunks in the table are verified;
11. Run the verification check from the database and summarize the results of this table;
12. Cycle through all the tables to be verified until all the tables are verified.

4.2 notes and common parameters

4.2.1 precautions

1,After verifying the data, the master-slave database must be synchronized to ensure that the slave database IO and SQL The process is YES Status, otherwise the script will wait
2,If--recursion-method=processlist perhaps hosts You need an account that can log in to both the master database and the slave database.
3,--host Only one parameter can be specified host，Must be primary IP;also--port --user --databases --password All refer to the information of the main database.
4,Add to the table during inspection S Lock.
5,If master and slave Of binlog Log is not STATEMENT Format, use--no-check-binlog-format Options.
6,The table must have a primary key index or a unique key index. For one chunk It's a small watch that can fit. It can't have any opinions. If there is no primary key in a large table, it will be ignored, with the following alarm information:
 Cannot checksum table mtest.bbb: There is no good index and the table is oversized. at /bin/pt-table-checksum line 6743
7,Data validation of cascading slave libraries is not supported.

4.2.2 description of common parameters

About the use of - recursion method = DSN method

Official note:

The dsn method is special: rather than automatically discovering replicas, this method specifies a table with replica DSNs. 
The tool will only connect to these replicas. 
This method works best when replicas do not use the same MySQL username or password as the master, or when you want to prevent the tool from connecting to certain replicas. 
The dsn method is specified like: --recursion-method dsn=h=host,D=percona,t=dsns. The specified DSN must have D and t parts, or just a database-qualified t part, which specify the DSN table. 
The DSN table must have the following structure:

This means that dsn is a special and flexible way to store the slave information in a table, and then use dsn=h=host In this way, you can connect to the specified database to get the table data. Whether it is the master database, slave database or other databases, as long as you have permission to access them. The table structure is as follows:

CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Give an example:

--recursion-method dsn=h=1.1.3.9,u=test,p=Test_123,P=3306,D=mydb9,t=dsns

The slave database information involved in the verification data can be inserted into the dsn field. There can be multiple information, sorted by id:

insert into dsns(dsn) values ('h=1.1.3.111,u=root,p=1234.C0m,P=3306');
insert into dsns(dsn) values ('h=1.1.3.9,u=root,p=1234.C0m,P=3306');

Inspection result description

Diff? Rows data special note:

In the test of 1 master 2 slave architecture, it is found that diffs is marked as 1 as long as the data of the master database and any slave database are inconsistent;
But the diff rows count is not the sum of the differences between the master database and all the slave databases or the slave database with the smallest difference. Instead, the master database data is compared with the slave database one by one and the last comparison result is selected. For example:

From library 2 in second place:

+------------+-----------+------+------------+--------------------------------------+
| Server_id  | Host      | Port | Master_id  | Slave_UUID                           |
+------------+-----------+------+------------+--------------------------------------+
| 2020021325 | 1.1.3.111 | 3306 | 2019010155 | e34b371d-4e4f-11ea-b9e6-000c29aa5c5d |
| 2020022219 | 1.1.3.9   | 3306 | 2019010155 | b6031e42-5560-11ea-aa8a-000c2921de41 |
+------------+-----------+------+------------+--------------------------------------+

Main library: 0

root@localhost [mtest]> select * from cqy;
Empty set (0.00 sec)

Slave 1 (server ID 2020021325): 3

root@localhost [mtest]> show variables like '%server_id%';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| server_id      | 2020021325 |
| server_id_bits | 32         |
+----------------+------------+

root@localhost [mtest]> select * from cqy;
+------+------+
| name | id   |
+------+------+
| test |    1 |
| test |    2 |
| test |    3 |
+------+------+

Slave 2 (server ID 2020022219): 1

mysql> select * from cqy;
+------+------+
| name | id   |
+------+------+
| test |    1 |
+------+------+

Verification result: diff? Rows = 1

            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-22T22:31:10      0      0   262144          0       5       0   0.801 mtest.aaa
02-22T22:31:11      0      0        0          0       1       0   0.327 mtest.bbb
02-22T22:31:11      0      1        0          1       1       0   0.328 mtest.cqy
02-22T22:31:11      0      0        0          0       1       0   0.330 mtest.dsns
02-22T22:31:12      0      0        1          0       1       0   0.344 mtest.inttest
02-22T22:31:12      0      0        0          0       1       0   0.367 mtest.medivac
02-22T22:31:12      0      1        2          0       1       0   0.338 mtest.t

If the parameter – replicate check only is added, only the results with differences will be displayed, and all the differences from the slave database will be displayed:

Checking if all tables can be checksummed ...
Starting checksum ...
Differences on onetest
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
mtest.cqy 1 3 1   
mtest.t 1 -2 1   

Differences on rpmtest
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
mtest.cqy 1 1 1   

4.3 best practices

Example:

1.1.3.111 3309 main warehouse
 1.1.3.111 3306 slave Library
 1.1.3.9 3306 slave Library

First you need to create users and authorize:

create user root@'1.1.3.111' identified by 'xxxx';
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'1.1.3.111';

– recursion method = processlist mode, the primary and secondary databases need to have the same user name and password, and the ports are the same:

pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=processlist --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=bigbangdata.cn

– recursion method = hosts mode, the primary and secondary databases need to have the same user name and password, and the primary database show slave hosts can see the slave ip and port:

pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=hosts --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=bigbangdata.cn

– recursion method DSN = H = host, d = percona, t = DSNs, which is a flexible way. The primary and secondary databases can have different users, passwords and ports:

1) , table for storing dsn information

CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

2) , dsn column information, for example: "H = replica host, u = repl u user, P = repl u u pass"

insert into dsns(dsn) values ('h=1.1.3.111,u=root,p=1234.C0m,P=3306');

You can insert the connection information of multi row and slave libraries.
3) , execute script

pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method dsn=h=1.1.3.9,u=test,p=Test_123,P=3306,D=mydb9,t=dsns --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=bigbangdata.cn

Script description:

dsn=h=1.1.3.9,u=test,p=Test_123,P=3306,D=mydb9,t=dsns

Get the connection string of dsn information table, which can be independent of the master-slave database.

– replicate check only, only the results with differences are displayed:

pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=hosts --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=bigbangdata.cn --replicate-check-only

4.4 troubleshooting of common problems

4.4.1,Diffs cannot be detected because no slaves were found

This is the most common problem encountered when using the PT table checksum tool. When you see the following prompt, it means that the verification fails

Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.

The error also gives a hint. You need to look at the official document - recursion method. In fact, you can see the usage clearly and know how to find the information from the database, and the problem will be solved.

Find the slave database information in processlist mode:

root@localhost [mtest]> show processlist;

+-----+------+-----------------+-------+------------------+-------+---------------------------------------------------------------+------------------+
| Id  | User | Host            | db    | Command          | Time  | State                                                         | Info             |
+-----+------+-----------------+-------+------------------+-------+---------------------------------------------------------------+------------------+
|  97 | repl | 1.1.3.111:12759 | NULL  | Binlog Dump GTID | 49911 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 115 | repl | 1.1.3.9:35554   | NULL  | Binlog Dump GTID | 45133 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 126 | root | localhost       | mtest | Query            |     0 | starting                                                      | show processlist |
+-----+------+-----------------+-------+------------------+-------+---------------------------------------------------------------+------------------+
3 rows in set (0.04 sec)

When show processlist is used on the main database to discover the slave database information, only the slave database ip information can be obtained. But only the user name, password, port and other information of the primary database are passed in the command line parameters, so the primary and secondary databases are required to have the same user name, password, port, or the secondary database information will not be found.

To find the slave database information in hosts mode:

root@localhost [mtest]> show slave hosts;
+------------+-----------+------+------------+--------------------------------------+
| Server_id  | Host      | Port | Master_id  | Slave_UUID                           |
+------------+-----------+------+------------+--------------------------------------+
| 2020021325 | 1.1.3.111 | 3306 | 2019010155 | e34b371d-4e4f-11ea-b9e6-000c29aa5c5d |
| 2020022219 |           | 3306 | 2019010155 | b6031e42-5560-11ea-aa8a-000c2921de41 |
+------------+-----------+------+------------+--------------------------------------+
2 rows in set (0.00 sec)

When you use the show slave hosts command, you may not see the ip information from the library. If you do not get the information, you cannot connect to the slave library. In this case, you need to add parameters to the / etc/my.cnf configuration file of the slave Library: the ip and port of the slave library

report_host=1.1.3.9
report_port=3306

After modifying the parameters, restart the slave database and look again: it is found that the slave database information already exists

root@localhost [mtest]> show slave hosts;
+------------+-----------+------+------------+--------------------------------------+
| Server_id  | Host      | Port | Master_id  | Slave_UUID                           |
+------------+-----------+------+------------+--------------------------------------+
| 2020021325 | 1.1.3.111 | 3306 | 2019010155 | e34b371d-4e4f-11ea-b9e6-000c29aa5c5d |
| 2020022219 | 1.1.3.9   | 3306 | 2019010155 | b6031e42-5560-11ea-aa8a-000c2921de41 |
+------------+-----------+------+------------+--------------------------------------+
2 rows in set (0.00 sec)

Finally, it is also necessary to have the same user and password on the master-slave database.

5. PT table sync repair data

5.0 references and installation

Reference documents:

https://www.percona.com/doc/percona-toolkit/3.0/pt-table-sync.html

https://www.hellojava.com/a/75316.html

Installation:

Download the latest rpm package at https://www.percona.com/downloads/percona-toolkit/LATEST;

yum install percona-toolkit-*.rpm -y

5.1. Pt table sync principle

PT table sync is one of the percona toolkit tools, which can be used to repair data. By opening the general log and observing the information of the master-slave database, you can know:

1. Open two session connections, one session is responsible for verifying synchronization, and one session is responsible for continuously checking server status information;
2. Connect the main database corresponding to the DSN host, check the current server load information, parameter setting information, and turn off the automatic submission function;
3. Set the binary log format as STATEMENT and the session level isolation level as REPEATABLE READ;
4. Check the permissions of the current connected user, and check whether the operation table is constrained by foreign keys;
5. Determine the upper and lower boundaries of the chunk through the primary key or unique key (if any), so as to better chunk the chunk operation;
6. The table is divided into multiple chunks for verification and synchronous repair. The chunk size is controlled by the option chunk size;
7. Complete all chunk verification and synchronous repair, and exit.

5.2 summary of tool use

5.2.1 precautions

Particular attention

1. In the process of verification analysis, FOR UPDATE statement will be executed to lock the table row of the operation, so it is better to operate in the low peak period of the business and avoid operating data in the high concurrency scenario to avoid blocking.
2. When using the -- sync to master or -- replicate option, the copy format needs the statement format. The script will automatically set binlog [format = statement at the session level, so the user must have super permission.
3. If you want to repair a table without a primary key or unique index in the master master dual master mode, you need to add the option -- no bin log, in order to prevent the modified data from being synchronized back to the source.

What can be done:

1. To synchronize data between MySQL tables, you can do one-way and two-way synchronization of table data. It can synchronize a single table or the entire library.
2. The data between the master and slave databases can be synchronized; the database name, table name and table structure between the non master and slave databases are the same, or they can be synchronized.
3. If there is no unique key on the table, the change has to be made in the slave database. However, if you need to specify the option -- no check slave, and you can no longer specify the parameter -- replicate or -- sync to master, you need to specify two dsn addresses to represent the source database and the target database. The data of the target database is the same as that of the source database.

What can't be done:

1. Cannot synchronize a table structure, index, or any other schema object. So you need to make sure their tables exist before you fix the consistency.
2. When the option -- replicate or -- sync to master is specified, if there is no unique index or primary key in the table, an error will be reported and data repair is not allowed.

5.2.2 description of common parameters

Basic usage:

pt-table-sync [OPTIONS] DSN [DSN]

Options

--[no]bin-log
 Default: yes
 Specifies that binary logs are recorded for synchronization operations, which is equivalent to executing set SQL log bin = 1. If '-- no bin log' is specified, set SQL log bin = 0 will be executed accordingly.

--channel
 Specifies which master database data needs to be synchronized when the master-slave replication environment is multi-source replication, which is applicable to the case where multiple master databases correspond to one slave database in multi-source replication.

--charset，-A
 Specifies the connection character set.

--[no]check-master
 Default: yes
 Specifies that when the option '- sync to master' is used, an attempt is made to verify that the main library to which the tool is connected is the real one.

--[no]check-slave
 Default: yes
 Specifies whether to check whether the target server is from the same server as the library.
If the target server is a slave database, it is not safe to change it, but it must be done in some cases. For example, when there is no unique index on the table that the master database needs to synchronize, the specified option '-- replace' will not work, so in this case, the master database cannot be changed. By default, if you need to make changes to the slave library, the tool will prompt you to specify the option '-- no check slave' to disable checking.

--chunk-size
 Default: 1000
 Specifies the chunk size of the table, the number of table rows corresponding to each chunk, or the data block size. When specifying the size, the allowed suffix units are k, M, G.

--host，-h
 Specifies the database IP address of the connection.

--port，-P
 Specifies the database Port to connect to.

--user，-u
 Specifies the database user to connect to.

--password，-p
 Specifies the database user password for the connection.

--socket，-S
 Specifies to connect using a SOCKET file.

--databases，-d
 Specify the database to be synchronized. If there are multiple databases, separate them with ',' (comma).

--tables，-t
 Specifies the tables that only need to be synchronized, separated by ',' (comma) if there are multiple tables. Table names can be qualified with database names.

--columns，-c
 Specify the table fields to compare, and separate them with ',' (comma).

--where
 Restrict the synchronization content of the table through the where statement condition.

--dry-run
 Analyze and select synchronization algorithm, print information and exit.
This means that specifying the option '-- verbose' yields the results of tool analysis. The output format of the analysis result is the same as that of the actual execution of the tool, but no data will be affected.

--execute
 Specifies that the tool performs a synchronization operation to make the table data reach a consensus state. Without this parameter, the synchronization operation is not performed.
Tool using this option means that the table with inconsistent data will be synchronized, so the data of the table will be changed. Unless option '-- verbose' is specified, the data of the table will be changed in a silent way.

--ignore-databases
 Specify the database to ignore the comparison. If there are multiple databases, use ',' (comma) to separate them. The system database information u schema and performance u schema are ignored by default.

--ignore-engines
 Default: fed, MRG ﹣ MyISAM
 Specifies the tables of the storage engine type that need to ignore synchronization, separated by ',' (comma) if there are more than one.

--ignore-tables
 Specify the tables that need to ignore synchronization. If there are multiple tables, separate them with ',' (comma). Table names can be qualified with database names.

--lock
 Specifies the process of lock tables operation, mainly including the following values:
'
VALUE  MEANING
=====  ===========================================================================
0 will never lock the table;
1. Lock the table in each synchronization cycle, for example, lock the table row of each synchronization chunk pair, which is the most fine-grained locking level;
2. Lock the table when the table is in operation;
3 lock the table for each DSN connected server. Specify the statement FLUSH TABLES WITH READ LOCK.
'
When the options' -- replicate 'or' -- sync to master 'are specified, the tables from the library are not locked. If the option '-- wait' is specified, the table corresponding to the main library is locked, and the tool tentatively performs the operation until it catches up with the main library.
If the option '-- transaction' is specified, the lock tables operation will not be executed. Instead, the lock operation is performed by transaction start and commit. The exception is' -- lock=3 '. If the option' -- no transaction 'is specified, the lock tables operation is applicable to all' - lock 'values.

--print
 Specify which query statements the printing tool needs to execute to synchronize the table, and solve the data inconsistency. Only print output will not be executed.

--recursion-method
 Default value: processlist, hosts
 Specifies how to get from the library.
'
METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST   
hosts        SHOW SLAVE HOSTS   
none         Do not find slaves
==========================================================
'
Processlist: find slave through SHOW PROCESSLIST, which is the default mode. When SHOW SLAVE HOSTS is not available. Once the instance is running on a non 3306 port, the hosts mode will change to the default mode;
Hosts: find the slave through SHOW SLAVE HOSTS. The hosts mode requires the configuration of the two parameters' -- report'host 'and' -- report'port 'from the library.

--replicate
 Specifies that table synchronization is performed by referencing the tables listed in this option.
The tool will query the data inconsistent table information in the table specified by this option and perform synchronization operation. This option has the same meaning as the option with the same name in the tool Pt table checksum. This option automatically sets the option '-- wait=60' and ensures that changes are made in the main library.
If you specify the option '-- sync to master', the tool will assume that the specified connection is from the database, and will find the main database connection for table data synchronization. If the specified connection is not from the database, the tool will use the option '-- recursion method' to find the database method for lookup, and then find the table that needs to be synchronized.

--sync-to-master
 Specifies that DSN connection information is confirmed as a slave and synchronized to the master.
This option treats the specified server as a slave library, and checks the master library of the slave library to connect to the master library. The master database is the source of data synchronization, and the slave database is the target of data synchronization. Option will set options' -- wait=60 'and' -- lock=1 'by default and option' -- no transaction '.

--timeout-ok
 Specifies to skip failure and continue execution when option '-- wait' causes tool execution to fail.
If the option '-- wait' is specified, but the slave library fails to catch up with the master library within the specified time and the master-slave delay still exists, the tool will abort the operation and exit. Specifying the option '-- timeout OK' will continue the operation.

--verbose，-v
 Specifies to print more detailed operation information.

DSN option (DSN) Description:

You can use the DSN method to connect to the database. The DSN option is key=value. Spaces are not allowed on both sides of the equal sign. It is case sensitive. Multiple options are separated by ',' (comma). The main options are as follows:

Example:

h=192.168.58.3,P=3306,D=employees,t=employees

5.2.3 – replicate option description

PT table sync tool is a bit complicated to use. It can work in many different ways, among which the use of option – replicate is very important for the tool. The use of this option will be further explained below.

The logic of using this option is as follows:

1, If the -- replicate option is not specified, and table option t is involved in DSN option, only the specified table will be synchronized:
  1. If DSN has only one host information, specify the option -- sync to master:
    1) . if the DSN represents a slave library, the tool will also connect its master library and synchronize;
    2) . if the DSN represents the master database, the tool will report an error and cannot find the master database.
  2. If there is more than one host information in DSN:
    1) . the first DSN host is the source database (no distinction between the master database and the slave database), which is synchronized to the subsequent DSN hosts in order. If the first is that the DSN host is a slave database, the data from the slave database will be synchronized to the master database;
    2) . if the first DSN host is the primary database, ensure that the synchronized table has a unique index to perform the REPLACE change operation in the primary database, and specify the option -- no check slave.
2, If you specify the -- replicate option, you can prove that there is already a table for saving data difference results (you can use the tool Pt table checksum to verify first):
  1. Specify -- sync to master option:
    1) . when the two options are used together, only one DSN host is allowed. Otherwise, the tool will exit with an error. DSN represents the slave library. The tool will connect to its main library, find out the difference data and repair it synchronously.
  2. Do not specify -- sync to master option:
    1).DSN represents the main library. The tool will find out all the slave libraries and connect them, find out the difference data and make synchronous repair.
3, In other cases, do not specify the options -- replicate and -- sync to master:
  1. There are multiple DSN hosts:
    1) . find out the differences of all the table data of the specified database through the -- databases or -- ignore databases and other filtering options, mainly the first host of DSN, and synchronize the differences to all other hosts of DSN.

Conclusion:

1. If DSN has only one host information, you must specify one of the options – sync to master or – replicate, otherwise an error will be reported;

2. If there are specified options – replicate and – sync to master, or one of them, refer to the above logic;

3. If the DSN has more than one host information and does not specify the options – replicate and – sync to master, then the first host is the main host, and the option – no check slave is specified at the same time to synchronize the difference data on other hosts.

5.3 use cases

If there is a unique key (primary key) on the table, the best way for the master-slave replication architecture is to specify the option – replicate or – sync to master to put the change statements that need to be synchronized on the master database for execution, and pass the changed operations from the master-slave replication to the slave database for execution.

5.3.1. Only one DSN host

1) . specify only options – sync to master

Because there is only one DSN host and the option – sync to master is specified, the DSN host corresponds to the connection string of the slave library. You can use the option – dry run to view the execution information first. Using the – dry run parameter will overwrite the – execute parameter, so the following statement will not execute synchronization:

pt-table-sync --execute --sync-to-master --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose --dry-run

Remove - dry run, you can see that the table cqy has been fixed, and a row of records has been deleted from the Library:

[mysql@onetest ~]$ pt-table-sync --execute --sync-to-master --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose
# Syncing A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     15:53:19 15:53:22 0    mtest.aaa
#      0       0      0      0 Chunk     15:53:22 15:53:22 0    mtest.bbb
#      1       0      0      0 Chunk     15:53:22 15:53:22 2    mtest.cqy
[mysql@onetest ~]$ 

2) . specify only option - replicate

If only the option – replicate is specified, then the DSN host corresponds to the connection string of the main database. Before that, the table specified by the option – replicate has inconsistent data verification results before saving. You can use the tool Pt table checksum to verify first, or synchronous change repair will not be performed.

First, use the PT table checksum tool to perform a master-slave verification. It is found that the data of bbb and cqy are inconsistent:

[mysql@onetest ~]$ pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=hosts --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=1234.C0m
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-23T15:59:32      0      0   262144          0       5       0   0.826 mtest.aaa
02-23T15:59:33      0      1        0          1       1       0   0.333 mtest.bbb
02-23T15:59:33      0      1        0          1       1       0   0.332 mtest.cqy
02-23T15:59:33      0      0        0          0       1       0   0.326 mtest.dsns
02-23T15:59:34      0      0        1          0       1       0   0.326 mtest.inttest
02-23T15:59:34      0      0        0          0       1       0   0.330 mtest.medivac
02-23T15:59:34      0      0        2          0       1       0   0.347 mtest.t
02-23T15:59:35      0      0        0          0       1       0   0.323 mtest.t0
02-23T15:59:35      0      0        3          0       1       0   0.329 mtest.t1
02-23T15:59:35      0      0        1          0       1       0   0.345 mtest.t_tab_test
02-23T15:59:36      0      0        1          0       1       0   0.332 mtest.tab0
02-23T15:59:36      0      0        1          0       1       0   0.337 mtest.tab1

Execute repair script: 1.1.3.111 3309 main library

pt-table-sync --execute --replicate=mtest.testchk --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 --databases=mtest --tables=aaa,bbb,cqy --verbose

As can be seen from the following information, the data of the following two slave databases in the main database has been repaired:

# Syncing via replication A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:06:32 16:06:32 2    mtest.bbb
#      1       0      0      0 Chunk     16:06:32 16:06:33 2    mtest.cqy
# Syncing via replication A=utf8,P=3306,h=1.1.3.9,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:06:33 16:06:33 2    mtest.bbb
#      1       0      0      0 Chunk     16:06:33 16:06:33 2    mtest.cqy

3) . specify options – sync to master and – replicate

Because the option - sync to master appears, the DSN host corresponds to the connection string of the slave database. Before the test, the master-slave inconsistency is made again, and the PT table checksum is used to verify the data, because if there is no verification data information, no operation will be performed: the same, the bbb and cqy tables are inconsistent, and both the slave databases are inconsistent

[mysql@onetest ~]$ pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=hosts --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=1234.C0m
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-23T16:19:51      0      0   262144          0       5       0   0.859 mtest.aaa
02-23T16:19:51      0      1        0          1       1       0   0.329 mtest.bbb
02-23T16:19:52      0      1        0          1       1       0   0.325 mtest.cqy
02-23T16:19:52      0      0        0          0       1       0   0.330 mtest.dsns
02-23T16:19:52      0      0        1          0       1       0   0.337 mtest.inttest
02-23T16:19:53      0      0        0          0       1       0   0.325 mtest.medivac
02-23T16:19:53      0      0        2          0       1       0   0.341 mtest.t
02-23T16:19:53      0      0        0          0       1       0   0.330 mtest.t0
02-23T16:19:54      0      0        3          0       1       0   0.325 mtest.t1
02-23T16:19:54      0      0        1          0       1       0   0.339 mtest.t_tab_test
02-23T16:19:54      0      0        1          0       1       0   0.332 mtest.tab0

Execute repair script: 1.1.3.111 3306 is slave 1

pt-table-sync --execute --sync-to-master --replicate=mtest.testchk --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose

After executing the script, it is found that only the specified slave database 1 data has been repaired, and slave database 2 (1.1.3.9 3306) has not been repaired:

[mysql@onetest ~]$ pt-table-sync --execute --sync-to-master --replicate=mtest.testchk --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose
# Syncing via replication A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:22:08 16:22:08 2    mtest.bbb
#      1       0      0      0 Chunk     16:22:08 16:22:08 2    mtest.cqy

Summary: – sync to master and – replicate are specified together. You can repair a slave database directionally, and specify separately – replicate can repair all slave databases under the master database.
4) . do not specify the options - sync to master and - replicate?

When DSN has only one host, at least one of the options must be specified, otherwise the tool will exit with an error message.

[mysql@onetest ~]$ pt-table-sync --execute  --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 --databases=mtest --tables=aaa,bbb,cqy --verbose
Usage: pt-table-sync [OPTIONS] DSN [DSN]

Errors in command-line arguments:
  * At least one DSN is required, and at least two are required unless --sync-to-master or --replicate is specified

pt-table-sync synchronizes data efficiently between MySQL tables.  For more
details, please use the --help option, or try 'perldoc /bin/pt-table-sync' for
complete documentation.

5.3.2 specify multiple DSN hosts

1) . specify only options – sync to master

Because there are multiple DSN hosts, it is necessary to ensure that all the listed DSN hosts are from the library, otherwise, the tool reports an error and exits.

pt-table-sync --execute --sync-to-master --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 h=1.1.3.9,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose

As can be seen from the following results, both the specified slave database data have been fixed, which is the same as the effect of using - replicate for the specified master database dsn:

# Syncing A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     16:30:26 16:30:29 0    mtest.aaa
#      1       0      0      0 Chunk     16:30:29 16:30:29 2    mtest.bbb
#      1       0      0      0 Chunk     16:30:29 16:30:29 2    mtest.cqy
# Syncing A=utf8,P=3306,h=1.1.3.9,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     16:30:29 16:30:32 0    mtest.aaa
#      1       0      0      0 Chunk     16:30:32 16:30:32 2    mtest.bbb
#      1       0      0      0 Chunk     16:30:32 16:30:33 2    mtest.cqy

2) . specify only option - replicate

- the replicate parameter must ensure that the first dsn host is the master database and the other dsn addresses are the slave databases.

pt-table-sync --execute --replicate=mtest.testchk  --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 h=1.1.3.9,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose

The test shows that even if a slave dsn is specified, the data of other slave databases will be repaired. Therefore, whether there is a dsn address of the slave database or not behind the master database dsn, the effect is the same as - replicate only specifies a master database dsn.

[mysql@onetest ~]$ pt-table-sync --execute --replicate=mtest.testchk  --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 h=1.1.3.9,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose
# Syncing via replication A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:43:20 16:43:20 2    mtest.bbb
#      1       0      0      0 Chunk     16:43:20 16:43:20 2    mtest.cqy
# Syncing via replication A=utf8,P=3306,h=1.1.3.9,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:43:20 16:43:20 2    mtest.bbb
#      1       0      0      0 Chunk     16:43:20 16:43:21 2    mtest.cqy

3) . specify options – sync to master and – replicate

When these two options are specified at the same time, only one DSN host is allowed, and it must be a DSN address from the library, otherwise, the tool reports an error and exits.

4) . do not specify options – sync to master and – replicate

Because neither of these options is specified, the order of DSN hosts must be specially noted. It is better to write the master database first and then the slave database, or determine the synchronization direction. At the same time, if there is no unique key (primary key) on the table requiring synchronous change and repair, you need to specify the option - no check slave to perform change and repair directly from the database.

No – no check slave parameter added

pt-table-sync --execute --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy,t --verbose

When synchronizing the data from the primary database to the secondary database, because there is no primary key on the table, an error is reported as follows:

# Syncing A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
Can't make changes on A=utf8,P=3306,h=1.1.3.111,p=...,u=root because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /bin/pt-table-sync line 10879.  while doing mtest.aaa on 1.1.3.111
#      0       0      0      0 0         16:52:01 16:52:01 1    mtest.aaa
Can't make changes on A=utf8,P=3306,h=1.1.3.111,p=...,u=root because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /bin/pt-table-sync line 10879.  while doing mtest.bbb on 1.1.3.111
#      0       0      0      0 0         16:52:01 16:52:01 1    mtest.bbb
Can't make changes on A=utf8,P=3306,h=1.1.3.111,p=...,u=root because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /bin/pt-table-sync line 10879.  while doing mtest.cqy on 1.1.3.111
#      0       0      0      0 0         16:52:01 16:52:01 1    mtest.cqy
Can't make changes on A=utf8,P=3306,h=1.1.3.111,p=...,u=root because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /bin/pt-table-sync line 10879.  while doing mtest.t on 1.1.3.111
#      0       0      0      0 0         16:52:01 16:52:01 1    mtest.t

Add the – no check slave parameter

pt-table-sync --execute --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy,t --no-check-slave --verbose

As can be seen from the following results, the repair of mtest.t without primary key or unique index table has been completed:

# Syncing A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     16:55:10 16:55:11 0    mtest.aaa
#      0       0      0      0 Chunk     16:55:11 16:55:11 0    mtest.bbb
#      0       0      0      0 Chunk     16:55:11 16:55:11 0    mtest.cqy
#      1       0      1      0 GroupBy   16:55:11 16:55:11 2    mtest.t

summary

1. Use only one DSN host information, the connected main database, and the option replicate. You need to verify it first, and use it with the PT table checksum tool;

2. Only one DSN host information is used. The attached slave database, plus the option - sync to master, does not need to be verified first;
3. Using multiple DSN host information, write the host information in order according to the direction of data synchronization, and add the option no check slave according to the actual needs;

4. It is recommended to use multiple DSN host information. At the same time, the options – replicate and – sync to master are avoided. There is no need to check first, only the direction of data synchronization needs to be considered.