Shulou(Shulou.com)05/31 Report--

This article is about how to use pt-table-checksum and pt-table-sync in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Pt-table-checksum and pt-table-sync are features in the percona-toolkit tool that are used to detect master-slave data consistency and fix master inconsistencies.

Let's learn the use of these two functions through a set of experiments.

1. The main library creates tables and inserts data

CREATE TABLE `NewTable` (

`id`int (8) NULL

`name` varchar (32) NULL

PRIMARY KEY (`id`)

)

Insert into newtable values (1 recorder leo')

Insert into newtable values (2)

Insert into newtable values (3 recordings Jack)

2. Execute the following statement from the slave library to make the master inconsistent

Insert into newtable values (4)

Insert into newtable values (5)

Update newtable set name='john' where id = 1

3. The test data are as follows

Main library:

Mysql > select * from test.newtable

+-+ +

| | id | name |

+-+ +

| | 1 | leo |

| | 2 | mike |

| | 3 | jack |

+-+ +

3 rows in set (0.00 sec)

From the library:

Mysql > select * from test.newtable

+-+ +

| | id | name |

+-+ +

| | 1 | john |

| | 2 | mike |

| | 3 | jack |

| | 4 | lucy |

| | 5 | petter |

+-+ +

5 rows in set (0.00 sec)

4. Introduction and use of pt-table-checksum parameters

-- port= main library port

-- IP, main library of host=

-- Database verified by databases=

-- the name of the table verified by tables=. Only specify the database, not the table name. Verify all tables under the database.

-- user= user name (this user also needs permission on the slave library)

-- password= user password

-- replicate specifies the libraries and tables stored by checksum

-- no-check-binlog-format ignores the format of binlog. If binlog is ROW or MIXED does not add this parameter, an error will be reported.

-- no-check-replication-filters ignores replication filtering, such as binlog_ignore_db, slave-skip-errors, etc.

For more and more detailed parameters of help, please see the help help documentation.

Check:

[root@trcloud] # pt-table-checksum-- port=3306-- host=192.168.129.15-- databases=test-- tables=newtable-- user=root-- password='123456'-- replicate=test.check-- no-check-binlog-format-- no-check-replication-filters

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

12-13T13:44:07 0 13 1 0 0.059 test.newtable

Note: when checking, an S lock will be added to the table.

TS: the time the check was completed

ERRORS: the number of errors and alarms that occurred during the check

DIFFS: 0 means consistent, 1 means inconsistent

ROWS: number of table rows

CHUNKS: the number of blocks divided into the table

SKIPPED: number of skips due to error

TIME: execution time

TABLE: table name

5. Use pt-table-sync to fix inconsistencies

-- replicate= specifies the table obtained by pt-table-checksum

H = main library IP

U = user name

P = password

-- execute performs repair

-- print prints out sql statements

For more and more detailed parameters of help, please see the help help documentation.

[root@trcloud] # pt-table-sync-- replicate=test.check hobby 192.168.129.15 recorder upright rootjournal pamphlet 123456'--execute-- print

DELETE FROM `test`.`newtable` WHERE `id` ='4' LIMIT 1 / * percona-toolkit src_db:test src_tbl:newtable src_dsn:h=192.168.129.15,p=...,u=root dst_db:test dst_tbl:newtable dst_dsn:h=172.30.249.5,p=...,u=root lock:1 transaction:1 changing_src:test.check replicate:test.check bidirectional:0 pid:19211 user:root host:trcloud*/

DELETE FROM `test`.`newtable` WHERE `id` ='5' LIMIT 1 / * percona-toolkit src_db:test src_tbl:newtable src_dsn:h=192.168.129.15,p=...,u=root dst_db:test dst_tbl:newtable dst_dsn:h=172.30.249.5,p=...,u=root lock:1 transaction:1 changing_src:test.check replicate:test.check bidirectional:0 pid:19211 user:root host:trcloud*/

REPLACE INTO `test`.`newtable` (`id`, `name`) VALUES ('1th,' leo') / * percona-toolkit src_db:test src_tbl:newtable src_dsn:h=192.168.129.15,p=...,u=root dst_db:test dst_tbl:newtable dst_dsn:h=172.30.249.5,p=...,u=root lock:1 transaction:1 changing_src:test.check replicate:test.check bidirectional:0 pid:19211 user:root host:trcloud*/

From the point of view of the printed sql, if too much delete is used to delete data from the library, REPLACE INTO is not used consistently in other cases. Therefore, there must be a primary key on the table, otherwise it will not only report an error, but also cause master-slave synchronization to fail (because the execution of REPLACE INTO in the slave library cannot pass)

6. Check the consistency between master and slave

[root@trcloud] # pt-table-checksum-- port=3306-- host=192.168.129.15-- databases=test-- tables=newtable-- user=root-- password='123456'-- replicate=test.check-- no-check-binlog-format-- no-check-replication-filters

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

12-13T13:48:18 0 0 3 10 0.108 test.newtable

The data is normal.

Thank you for reading! This is the end of the article on "how to use pt-table-checksum and pt-table-sync in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.