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

The following mainly brings you how to use the percona-toolkit tool to check and repair the main inconsistency of the MySQL database. I hope these contents can bring you practical use. This is also the main purpose of this article that I use the percona-toolkit tool to check and repair the MySQL database. All right, don't talk too much nonsense, let's just read the following.

Pt-table-checksum is one of the components of Percona-Toolkit, which is used to detect whether the data of MySQL master and slave libraries are consistent. The principle is that the sql statement based on statement is executed in the master database to generate the checksum of the master database block, the same sql statement is passed to the slave database for execution, and the checksum of the same data block is calculated on the slave database. Finally, the checksum value of the same data block on the master-slave database is compared to judge whether the master-slave data is consistent. The detection process divides the table into blocks (chunk) according to the row according to the unique index, which is calculated as a unit, and the locking of the table can be avoided. During the detection, the replication delay and the load of the master will be automatically determined. When the threshold is exceeded, the detection will be automatically suspended to reduce the impact on the online service.

Pt-table-checksum can handle most scenarios by default. Officials say that even if there are thousands of libraries and trillions of rows, it can still work well. This is due to the fact that the design is very simple. One table at a time does not require too much memory and redundant operations. If necessary, pt-table-checksum will dynamically change the size of chunk according to the load of the CVM to reduce the latency of slave libraries.

To reduce interference with the database, pt-table-checksum also automatically detects and connects to the slave library, and of course, if it fails, you can specify the-- recursion-method option to tell the slave library where it is. Its ease of use is also reflected in that if there is a delay in replication, the checksum in the slave library will be paused until it catches up with the calculation time of the master library (also through the option-set a maximum tolerable delay, which is also considered inconsistent).

In order to ensure the security of the main database service, the tool implements many protective measures: automatically setting innodb_lock_wait_timeout to 1s to avoid causing default when the database has more than 25 concurrent queries, pt-table-checksum will be suspended. You can set this threshold by setting the-- max-load option. After stopping the task with Ctrl+C, the tool will normally complete the current chunk test. Next time, use the-- resume option to start to resume the next chunk2, work process 1, connect to the master library: the pt tool connects to the master library, and then automatically discover all slave libraries of the master library. Show full processlist is used by default to find the slave library, but this is valid only if the master and slave instance ports are the same. 3. Find whether the master library or slave library has replication filtering rules: this is the option to check by default for security. You can turn off this check, but this may cause checksum's sql statements to either not be synchronized to the slave library, or to find tables from the slave library that are not going to be checksum, which will cause the slave library to synchronize the card library. 5. Start to get tables and calculate them one by one. 6. If it is the first chunk of the table, then the chunk-size is generally 1000; if it is not the first chunk of the table, then use the results analyzed in step 19. 7. Check the table structure, convert the data type, etc., and generate the sql statement of checksum. 8. According to the index on the table and the distribution of data, choose the most suitable method of split table. 9. Start checksum table. 10. Delete the calculation results related to the last table before chunk a table by default. Unless-resume. 14. Based on the results of explain, determine whether the size of chunk exceeds the upper limit of your defined chunk-size. If it is exceeded, the chunk will be ignored in order not to affect online performance. 15. Add the for update lock to the line to checksum and calculate. 17-18. Store the calculation results in the master_crc master_count column. 19. Resize the next chunk. 20. Wait for the slave library to catch up with the main library. If there is no delayed backup of the slave library running, it is best to check all slave libraries, and if it is found that the maximum delay is more than max-lag seconds, the pt tool will be paused here. 21. If the max-load of the main library exceeds a certain threshold, the pt tool will be suspended here. 22. Continue with the next chunk until the table is finished by chunk. 23-24. Wait for the slave library to execute the checksum to facilitate the generation of summary statistical results. Each table is summarized and counted once. 25-26, cycle through each table until the end.

After the verification, execute the following sql statement on each slave library to see if any master inconsistencies occur: select * from percona.checksums where master_cnt this_cnt OR master_crc this_crc OR ISNULL (master_crc) ISNULL (this_crc)\ G3, Environment IPPort hostname role 192.168.1.1013306node1master192.168.1.1023306node2slave Note: in order to reduce unnecessary trouble Make sure that your ptuser@'xxx' users can log in to both the master and slave libraries Only one host can be specified, and the IP; of the master library must be S-locked to the table when checked; if the binlog logs of master and slave are not in STATEMENT format, the synchronous IO and SQL processes of the slave library that need to be run with the-- no-check-binlog-format option are in YES state. The table should have a primary key index or a unique key index 4. Download

Open the official website: https://www.percona.com/downloads/percona-toolkit/LATEST/

Select the software version: Version, which is generally the latest version by default

Choose the system version: Software, or compile with source code; my CentOS6

System architecture: Hardware; my 64-bit

My download is:

Https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/6/x86_64/percona-toolkit-debuginfo-3.0.13-1.el6.x86_64.rpm

Https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/6/x86_64/percona-toolkit-3.0.13-1.el6.x86_64.rpm

5. Install yum install percona-toolkit-3.0.13-1.el6.x86_64.rpm-yyum install percona-toolkit-debuginfo-3.0.13-1.el6.x86_64.rpm-y

CentOS6.* dependencies:

Perl-DBD-MySQLperl-DBIperl-IO-Socket-SSLperl-Net-LibIDNperl-Net-SSLeayperl-Time-HiResCentOS7.* dependency: perl-Compress-Raw-Bzip2perl-Compress-Raw-Zlibperl-DBD-MySQLperl-DBIperl-Digestperl-Digest-MD5perl-IO-Compressperl-IO-Socket-IPperl-IO-Socket-SSLperl-Mozilla-CAperl-Net-Daemonperl-Net-LibIDNperl-Net-SSLeayperl-PlRPC

View the installed files:

[root@node1 ~] # rpm-ql percona-toolkit/usr/bin/pt-align/usr/bin/pt-archiver/usr/bin/pt-config-diff/usr/bin/pt-deadlock-logger/usr/bin/pt-diskstats/usr/bin/pt-duplicate-key-checker/usr/bin/pt-fifo-split/usr/bin/pt-find/usr/bin/pt-fingerprint/usr/bin/pt-fk-error-logger/usr/bin/pt-heartbeat/usr/bin/ Pt-index-usage/usr/bin/pt-ioprofile/usr/bin/pt-kill/usr/bin/pt-mext/usr/bin/pt-mongodb-query-digest/usr/bin/pt-mongodb-summary/usr/bin/pt-mysql-summary/usr/bin/pt-online-schema-change/usr/bin/pt-pmp/usr/bin/pt-query-digest/usr/bin/pt-secure-collect/usr/bin/pt-show-grants/usr/bin/pt-sift/ Usr/bin/pt-slave-delay/usr/bin/pt-slave-find/usr/bin/pt-slave-restart/usr/bin/pt-stalk/usr/bin/pt-summary/usr/bin/pt-table-checksum # verifies data consistency / usr/bin/pt-table-sync # repair inconsistent data; / usr/bin/pt-table-usage/usr/bin/pt-upgrade/usr/bin/pt-variable-advisor/usr/bin/pt-visual-explain... The main library master:root@node1 10:56: [(none)] > create database pt_check;Query OK, 1 row affected (0.04 sec) root@node1 10:57: [(none)] > use pt_checkDatabase changedroot@node1 10:58: [pt_check] > create table test1 (id int auto_increment primary key,name varchar (20) not null) Query OK, 0 rows affected (0.86 sec) root@node1 11:03: [pt_check] > insert into test1 values (null,'will'); Query OK, 1 row affected (0.00 sec) root@node1 11:03: [pt_check] > insert into test1 values (null,'jim'); Query OK, 1 row affected (0.00 sec) root@node1 11:03: [pt_check] > insert into test1 values (null,'tom') Query OK, 1 row affected (0.05sec) root@node1 11:03: [pt_check] > select * from pt_check.test1 +-+ | id | name | +-+-+ | 1 | will | 2 | jim | | 3 | tom | +-+-- + 3 rows in set (0.00 sec) root@node1 11:04: [pt_check] > 6.2, slave slave:root@node1 11:03: [pt_check] > select * from pt_check.test1 +-+-+ | id | name | +-+-+ | 1 | will | 2 | jim | 3 | tom | +-+ 3 rows in set (0.00 sec) root@node1 11:04: [pt_check] > delete from pt_check.test1 where id='2';Query OK, 1 row affected (0.02 sec) root@node2 12:23: [(none)] > select * from pt_check.test1 +-+-+ | id | name | +-+ | 1 | will | 3 | tom | +-+ 2 rows in set (0.00 sec) root@node2 12:23: [(none)] > 6.3.Creating verification user masterroot@node1 12:28: [pt_check] > GRANT CREATE,INSERT,SELECT,DELETE,UPDATE,LOCK TABLES,PROCESS,SUPER REPLICATION SLAVE ON *. * TO 'ptuser'@'192.168.1.101' IDENTIFIED BY' 123456' Query OK, 0 rows affected (0.00 sec) root@node1 12:29: [pt_check] > flush privileges;Query OK, 0 rows affected (0.00 sec) root@node1 12:29: [pt_check] > select Host,User from mysql.user +-+-+ | Host | User | +-+-+ | localhost | root | | localhost | mysql.session | | localhost | mysql.sys | | 172.16.156.% | rep | |% | java | | 192.168.1.101 | ptuser | +-+-+ 9 rows in set (0.00 sec) root@node1 12:29: [pt_check] > slaveroot@node2 12:48: [(none)] > select Host | User from mysql.user +-+-+ | Host | User | +-+-+ | localhost | root | | localhost | mysql.session | | localhost | mysql.sys | | 172.16.156.% | rep | |% | java | | 192.168.1.101 | ptuser | +-+-+ 8 rows in set (0.00 sec) root@node2 12:48: [(none)] > permission explanation: select / / View the tables of all libraries | Principle can add explain option to view process / / automatically discover slave database information Show processlistsuper / / set binlog_format='statement'replication slave / / show slave hosts7, pt-table-checksum check 7.1, pt-table-checksum parameter explanation-replicate-check: after executing the checksum query in the percona.checksums table, do not necessarily check the results immediately-yes will immediately compare the crc32 value of chunk and output the DIFFS column, otherwise it will not be output. The default yes, if specified as-- noreplicate-check, is usually followed by the following-- replicate-check-only to output the DIFF result. -- nocheck-replication-filters: do not check the replication filter. It is recommended to enable it. Later, you can use-- databases to specify the database to be checked. -- no-check-binlog-format: log format is not detected. This option is important for ROW mode replication, because pt-table-checksum will set binlog_format=STATEMENT on Master and Slave (ensure that the slave library will also execute checksum SQL), and the MySQL restriction slave library cannot be set, so if the row copies the slave library, and then copies the new slave library as the master library (A-> B-> C), B's checksums data will not be transferred. (no verification)-- replicate-check-only: do not do checksum query in the master-slave database, only query the results in the original percona.checksums table, and output information about data inconsistencies. May be used to periodically check for consistency. -- replicate=: write the information of checksum to the specified table. If it is not specified, the default is percona.checksums. It is recommended to write it directly to the database being checked. -- databases=,-d: the databases to be checked are separated by commas;-- databases-regex regular matches the databases to be tested, and-- ignore-databases [- regex] ignores the checked libraries. Filter option. -- tables=,-t

The table to check, separated by commas. If the tables you want to check are distributed in different db, you can use the form of-- tables=dbname1.table1,dbnamd2.table2. In the same way, there is tables-regex,--ignore-tables,--ignore-tables-regex. -- the checksum table specified by replicate is always filtered. -- tables=: specifies the tables that need to be checked, multiple addresses separated by commas for hashes 192.168.1.101: Master u=ptuser: username pendant 123456: password Prun3306: Port-- the-- create-replicate-table option automatically creates the percona.checksums table, but it also means giving additional CREATE TABLE permissions to the percona_tk@'xxx' user. The default yes--no-check-replication-filters means that there is no need to check whether Filter is specified in the Master configuration. It will be checked by default. If Filter is configured, such as replicate_do_db,replicate-wild-ignore-table,binlog_ignore_db, etc., the checksum from the library will exit with an error because the table does not exist, so the official default is yes (--check-replication-filters), but we actually specify-- databases=, in the test, so there is no problem, just don't detect it. -- empty-replicate-table: before each table checksum starts, clear the detection data before it (does not affect the checksum data of other tables), the default yes. Of course, if you use-- resume to start the test data will not be emptied. When-- noempty-replicate-table is not empty, the chunk is not calculated, only.

-- recursion-method: discover the way from the library. By default, pt-table-checksum can find the slave library replication process in the processlist of the master library to identify which slave libraries, but if you use a non-standard 3306 port, the slave library information will not be found. At this point, host will be used automatically, but you need to configure report_host and report_port information in slave my.cnf in advance, such as:

Report_host = MASTER_HOSTreport_port = 13306

The ultimate solution is that dsn,dsn specifies a table (such as percona.dsns), and the table row records the connection information of the master library (multiple) slave libraries. Any of the following situations apply:

Master library cannot automatically discover slave library does not want to add additional configuration (because to restart) master and slave detection connection user information is not the same when multiple slave libraries just want to verify the consistency of the specified slave library

I prefer to use the DSN approach. This dsns table only needs to be accessible on the cloud server where the pt-table-checksum command is executed. Here to correct an understanding, many people on the Internet say that pt-table-checksum should be executed on the master database, but in fact, it is not. I have more mysql instances. You only need to install percona-toolkit on a certain CVM, and this service can access both the master database and the slave database. For specific usage, see the following example.

7.2.Execute the data check command [root@node1] # pt-table-checksum-- nocheck-replication-filters-- replicate=test.checksums-- databases=pt_check-- tables=test1 hackers 192.168.1.101 on the main library. 123456 people 3306 checking if all tables can be checksummed... Starting checksum... Replica node2 has binlog_format ROW which could cause pt-table-checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify-- no-check-binlog-format to disable this check. [root@node1 ~] #

The bbinlog log from the library node2 is ROW, which may cause pt-table-checksum to interrupt replication. You can specify-- no-check-binlog-format to disable this check.

[root@node1 ~] # pt-table-checksum-- nocheck-replication-filters-- replicate=test.checksums-- databases=pt_check-- tables=test1 hackers 192.168.1.101 reparant upright ptusername no-check-binlog-formatChecking if all tables can be checksummed 123456 Prun3306-- no-check-binlog-formatChecking if all tables can be checksummed... Starting checksum... TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE02-19T18:23:22 0 1 30 0 0 0.045 pt_ check.test1 [root @ node1 ~] # indicates that: TS: the time it took to complete the check. ERRORS: the number of errors and warnings that occurred during the check. DIFFS: inconsistent number of chunk. When you specify-- no-replicate-check, that is, check but do not immediately output the results, it will always be 0; when you specify-- replicate-check-only, you will not check that only crc32 is calculated from the checksums table, and only inconsistent information will be displayed (after all, most of the output should be consistent and easy to cause interference). ROWS: the number of table rows compared. CHUNKS: the number of blocks divided into the table. SKIPPED: the number of blocks skipped due to errors or warnings or too large. TIME: time of execution. TABLE: the name of the table being checked.

See that the master-slave data has been checked for inconsistency. The value under DIFFS is 1. How can it be inconsistent? By specifying the-- replicate=test.checksums parameter, the check information is written to the checksums table.

7.3 、 Master's test.checksums table: root@node1 09:19: [(none)] > select * from test.checksums\ gateway * 1. Row * * db: pt_check tbl: test1 chunk: 1 chunk_time: 0.005212 Chunk_index: NULLlower_boundary: NULLupper_boundary: NULL this_crc: b9a54161 this_cnt: 3 # Native 3 lines of data master_crc: b9a54161 master_cnt: 3 # master 3 lines of data ts: 2019-02-20 09 sec 18 sec 011 row in set (0.00 sec) root@node1 09:19: [(none)] > 7.4, Slave's test.checksums table: root@node2 09:20: [(none)] > select * from test.checksums\ gateway * 1. Row * * db: pt_check tbl: test1 chunk: 1 chunk_time: 0.005212 Chunk_index: NULLlower_boundary: NULLupper_boundary: NULL this_crc: d49ddeb7 this_cnt: 2 # Native 2 rows of data master_crc: b9a54161 master_cnt: 3 # master 3 rows of data ts: 2019-02-20 09 sec 18 sec 011 row in set (0.01 sec) root@node2 09:20: [(none)] > 8, Pt-table-sync fixes 8.1. differences in print data

The master library uses the pt-table-sync command and the-- print option to print out the inconsistent data of the check_sum.test1 under master and the check_sum.test1 of the slave library, as follows:

[root@node1 ~] # pt-table-sync-- replicate=test.checksums hobby 192.168.1.101 VALUES VALUES ('2years,' jim') / * percona-toolkit src_db:pt_check src_tbl:test1 src_dsn:P=3306,h=192.168.1.101,p=...,u=ptuser dst_db:pt_check dst_tbl:test1dst_dsn:P=3306,h=node2,p=... U=ptuser lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:20377 user:root host:node1*/ [root@node1 ~] # pt-table-sync parameter description:-- replicate=: specify the table obtained through pt-table-checksum.-- databases=: specify 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. Ip 127.0.0.1: CVM address. There are two CVMs in the command. The first one is the address of Master, and the second is the address of Slave. U=root: account number. Password 123456: password. -- print: prints, but does not execute commands. -- execute: execute the command. 8.2. Pt-table-sync repairs different data

The next operation is to synchronize the less data on slave from master (master operation), and to keep their data consistent through (--execute):

[root@node1 ~] # pt-table-sync-- replicate=test.checksums hobby 192.168.1.101 replicate=test.checksums hobby 192.168.1.102 # pt-table-checksum pt-table-checksum-- nocheck-replication-filters-- replicate=test.checksums-- databases=pt_check-- tables=test1 hype 192.168.1.101 Prun3306-no-check-binlog-formatChecking if all tables can be checksummed... Starting checksum... TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE02-20T10:03:02 0 03 0 10 0.083 pt_ check.test1 [root @ node1 ~] #

You can see that when you check again, the DIFFS is already 0.

9.2. Main library master:root@node1 10:05: [(none)] > select * from pt_check.test1 +-+-+ | id | name | +-+ + | 1 | will | 2 | jim | 3 | tom | +-+ 3 rows in set (0.00 sec) root@node1 10:05: [(none)] > 9.3.Slave slave:root@node2 10:02: [(none)] > select * from pt_check.test1 +-+ | id | name | +-+-+ | 1 | will | | 2 | jim | | 3 | tom | +-+ 3 rows in set (0.00 sec) root@node2 10:05: [(none)] >

It's consistent with the data on master.

10. Error 10.1, user permission issues

No permission to create CREATE table

[root@node1] # pt-table-checksum-- nocheck-replication-filters-- replicate=test.checksums-- databases=pt_check-- tables=test1 hobby 192.168.1.101 DBD::mysql::db do failed 3306-- no-check-binlog-formatChecking if all tables can be checksummed... Starting checksum... 02-19T18:08:22-- create-replicate-table failed: CREATE command denied to user' ptuser'@'node1' for table 'checksums' [for Statement "CREATE TABLE IF NOT EXISTS `test`.`checksums` (db CHAR (64) NOT NULL, tbl CHAR (64) NOT NULL, chunk INT NOT NULL) Chunk_time FLOAT NULL, chunk_index VARCHAR NULL, lower_boundary TEXT NULL, upper_boundary TEXT NULL, this_crc CHAR (40) NOT NULL, this_cnt INT NOT NULL, master_crc CHAR (40) NULL, master_cnt INT NULL Ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ENGINE=InnoDB DEFAULT CHARSET=utf8 "] at / usr/bin/pt-table-checksum line 12272.02-19T18:08:22-- replicate table checksums does not exist and it cannot be created automatically. You need to create the table. [root@node1 ~] # 10.2, Diffs cannot be detected because no slaves were found

Cannot find slave library automatically, make sure that processlist or host or dsns mode is used correctly.

10.3. Cannot connect to hobbies slave1. Comding.comed pamphlet..

You can add PTDEBUG=1 before the pt-table-checksum command to see the detailed execution process, such as port, user name, permission error.

10.4. Waiting for the-replicate table to replicate to XXX

The problem is that the percona.checksums table does not exist in the slave library, and the root cause is that it is not synchronized from the master library, so check to see if the slave library has a serious delay.

10.5 、 Pausing because Threads_running=25

Repeatedly print out a message similar to the stop check above. This is because the number of threads running in the current database is greater than the default 25ptMurtableMutual checksum paused to reduce the pressure on the library. Wait for the database pressure to pass, or you can directly Ctrl+C the terminal, next time add-- resume to continue execution, or increase-- max-load= value.

10.6. Character set problem

For the above on how to use percona-toolkit tools to check and repair MySQL database master inconsistent, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.