Percona-Toolkit series: Pt-table-checksum and Pt-table-sync

Version: Pt-table-checksum 2.1.5, Pt-table-sync 2.1.5

Pt-table-checksum and Pt-table-sync are twins. Pt-table-checksum is used to detect data synchronization between the master and slave tables. Pt-table-sync is used to synchronize non-synchronized table data. In addition, Pt-table-sync can use the Pt-table-checksum execution result.

1. Pt-table-checksum writes the check result to the checksum of a table, and then compares whether the value of the this_crc field is different from that of master_cnt. The final output result is as follows. If the diffs value is not 0, the table is not synchronized.

[Root @ analyze ~] # Pt-table-checksum -- nocheck-replication-filters -- replicate = pango_test.checksums -- databases = pango_test H = 192.168.10.21, u = dump, P = xxxx
TS errors diffs rows chunks skipped time table
10-18t15: 58: 40 0 0 4 1 0 0.123 pango_test.client_dictionary
10-18t15: 58: 40 0 0 348 4 0 0.203 pango_test.daily_data
10-18t15: 58: 40 0 0 1414 1 0 0.068 pango_test.data_tidy
10-18t15: 58: 40 0 0 601 1 0 0.067 pango_test.dreamer_use_count

-- Nocheck-replication-Filters: the synchronization database name is specified in my. CNF. -- Replicate specifies the table checksums and -- databases specifies the database to be checked for synchronization. If multiple entries are checked, separate them with commas. If you need to specify which table to check, add -- tables. -- Replicate-check-only: Only tables that are not synchronized are printed.

Note 1: According to my test, a super-good account is required to log on to the master database, log on to the slave database, and synchronize data. 2: Only one host can be specified and must be the IP address of the master database. 3: The s lock will be applied to the table during the check. 4: IO from the database is required before running, and the SQL thread is in the yes state.

2. Pt-table-sync can synchronize data in a single table or the entire database. But no table is created. Therefore, the database and table must be included in the slave database. Usage: Pt-table-sync [option...] DSN [DSN...]

Example: Pt-table-sync -- replicate = pango_test.checksums H = 192.168.10.21, u = dump, P = xxx H = 192.168.10.22, u = root, P = xxx -- print

Replace into 'pango _ test '. 'test1' ('id', 'user _ name', 'Password', 'name') values ('8', 'admin8', '123', 'administrator8 ') /* percona-Toolkit src_db: pango_test src_tbl: test1 src_dsn: H = 192.168.10.21, P = ..., U = dump dst_db: pango_test dst_tbl: test1 dst_dsn: H = 192.168.10.22, P = ..., U = dump lock: 1 transaction: 1 changing_src: pango_test.checksums replicate: pango_test.checksums bidirectional: 0 PID: 31773 User: Root HOST: analyse.rzw.com */;
Replace into 'pango _ test '. 'test1' ('id', 'user _ name', 'Password', 'name') values ('9', 'admin9', '123', 'administrator9 ') /* percona-Toolkit src_db: pango_test src_tbl: test1 src_dsn: H = 192.168.10.21, P = ..., U = dump dst_db: pango_test dst_tbl: test1 dst_dsn: H = 192.168.10.22, P = ..., U = dump lock: 1 transaction: 1 changing_src: pango_test.checksums replicate: pango_test.checksums bidirectional: 0 PID: 31773 User: Root HOST: analyse.rzw.com */;
Replace into 'pango _ test '. 'test2' ('id', 'user _ name', 'Password', 'name') values ('8', 'admin8', '123', 'administrator8 ') /* percona-Toolkit src_db: pango_test src_tbl: Test2 src_dsn: H = 192.168.10.21, P = ..., U = dump dst_db: pango_test dst_tbl: Test2 dst_dsn: H = 192.168.10.22, P = ..., U = dump lock: 1 transaction: 1 changing_src: pango_test.checksums replicate: pango_test.checksums bidirectional: 0 PID: 31773 User: Root HOST: analyse.rzw.com */;
Replace into 'pango _ test '. 'test2' ('id', 'user _ name', 'Password', 'name') values ('9', 'admin9', '123', 'administrator9 ') /* percona-Toolkit src_db: pango_test src_tbl: Test2 src_dsn: H = 192.168.10.21, P = ..., U = dump dst_db: pango_test dst_tbl: Test2 dst_dsn: H = 192.168.10.22, P = ..., U = dump lock: 1 transaction: 1 changing_src: pango_test.checksums replicate: pango_test.checksums bidirectional: 0 PID: 31773 User: Root HOST: analyse.rzw.com */;

The example uses the table checksums obtained by running Pt-table-checksum. The two are twins at the beginning. Run Pt-table-checksum to check the tables that are not synchronized, and then run Pt-table-sync to synchronize data using the non-synchronized table just obtained. If -- replicate is specified, Pt-table-sync only uses the data in the existing checksum table and does not check it again. Therefore, to ensure correctness, run Pt-table-checksum before running Pt-table-sync to obtain the latest checksum result. -- Print: prints the SQL statement to be executed, but does not. In this way, you can view the changes in advance.

Note: The previous DSN "H = 192.168.10.21, u = dump, P = xxx" points to the master table, and the last DSN "H = 192.168.10.22, u = root, P = xxx "points to the slave table.