A brief Analysis of the practical Application of pt-table-checksum of percona-toolkit tool 01/16 Update SLTechnology News&Howtos

A brief Analysis of the practical Application of pt-table-checksum of percona-toolkit tool

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article is mainly to give you a brief analysis of the practical application of pt-table-checksum of percona-toolkit tools. You can check the relevant professional terms on the Internet or find some related books to supplement them. I will not dabble here and go straight to the topic. I hope I can bring you some practical help.

The most common contact in mysql work is that mysql replication,mysql still has some general problems in replication, such as downtime of master database or slave database, which may lead to replication interruption, which usually requires artificial repair, or often needs to promote a slave database to a master database, but the data consistency between the slave database and the master database cannot be guaranteed. In this case, you need to use the pt-table-checksum component of the percona-toolkit tool to check the consistency of master-slave data; if inconsistent data is found, you can fix it through pt-table-sync; you can also monitor master-slave replication latency through pt-heartbeat. Of course, if the amount of data is small and slave is only used as a backup, then data inconsistencies can be redone or resolved by other means. If the amount of data is very large, redoing is a very broken thing. For example, the online database is used as a master-slave synchronization environment. After the database is migrated, it needs to verify the data consistency after mysql migration (Replication), but it can not affect the use of the production environment. Pt-table-checksum has become an excellent and only checking tool.

Percona-toolkit introduction

Percona-toolkit is a collection of advanced command-line tools for performing a variety of very complex and cumbersome mysql and system tasks by hand, including:

1) check the consistency of master and slave data

2) filing records effectively

3) find duplicate indexes

4) summarize the information of CVM

5) analyze queries from logs and tcpdump

6) collect important system information when there is something wrong with the system

Percona-toolkit is derived from the Maatkit and Aspersa tools, which are the most famous tools for managing mysql. However, the Maatkit tool is no longer maintained, so it is recommended to use the percona-toolkit tool in the future!

These tools mainly include development, performance, configuration, monitoring, replication, system, practical six categories, as an excellent DBA, some tools are very useful, if you can master and apply flexibly, it will greatly improve work efficiency.

The three main components of the percona-toolkit tool are:

1) pt-table-checksum is responsible for monitoring the consistency of mysql master-slave data

2) pt-table-sync is responsible for repairing master-slave data when they are inconsistent so that they can preserve the consistency of the data.

3) pt-heartbeat is responsible for monitoring mysql master-slave synchronization delay

The following is a record of the use of these three components, and of course there are many other components of the percona-toolkit tool, which will be explained later.

Percona-toolkit tool installation (it is recommended to install both the master library and the slave library server)

Download the software and install it on the main library server [Baidu cloud disk download address: https://pan.baidu.com/s/1bp1OOgf (extraction password: Y462)]

[root@master-server src] # wget https://www.percona.com/downloads/percona-toolkit/2.2.7/RPM/percona-toolkit-2.2.7-1.noarch.rpm

[root@master-server src] # rpm-ivh percona-toolkit-2.2.7-1.noarch.rpm / / after installation, the various component commands of the percona-toolkit tool are available (enter ht-, and press TAB to display)

Install the software package on which the tool depends

[root@master-server src] # yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI-y

For specific installation methods, please refer to the official website address: https://www.percona.com/doc/percona-toolkit/LATEST/installation.html

The following is a direct yum installation:

Sudo yum install percona-toolkit

Installation reference address:

Https://blog.csdn.net/stevendbaguo/article/details/73122074

Https://www.cnblogs.com/piperck/p/5131289.html

Https://blog.csdn.net/zengxuewen2045/article/details/52029093

Https://blog.csdn.net/u010587433/article/details/46708563

Https://blog.csdn.net/zjq1985/article/details/79816242?utm_source=blogxgwz2

1. Combing the use of pt-table-checksum

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 chunk size according to the server load 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).

To secure the primary database service, the tool implements a number of protection measures:

1) automatically set innodb_lock_wait_timeout to 1s to avoid causing

2) by default, pt-table-checksum will be paused when the database has more than 25 concurrent queries. You can set this threshold by setting the-- max-load option

3) when the task is stopped with Ctrl+C, the tool will complete the current chunk test normally. Next time, use the-- resume option to start and resume the next chunk.

Pt-table-checksum [OPTIONS] [DSN]

Pt-table-checksum: check the consistency of the replication on the master (master) through the query that performs the check, and compare the master-slave check value to produce the result. DSN points to the primary address, and the exit status of the tool is not zero, if any differences are found, or if there are any warnings or errors. Note: the first time you run it, you need to add the-- create-replicate-table parameter to generate the checksums table! If you do not add this parameter, you need to add the table manually under the corresponding library. The table structure SQL is as follows:

CREATE TABLE checksums (db char (64) NOT NULL, tbl char (64) NOT NULL, chunk int NOT NULL, chunk_time float NULL, chunk_index varchar (200) 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, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ENGINE=InnoDB

The most important point is: commonly used parameter interpretation:

-- 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: does not check the replicated binlog mode. If the binlog mode is ROW, an error will be reported.

-- replicate-check-only: only messages that are out of sync are displayed.

-- replicate=: write the information of checksum to the specified table. It is recommended to write it directly to the database being checked.

-- databases=: specifies the databases to be checked, separated by commas.

-- tables=: specifies the tables to be checked, separated by commas

H =: address of Master

U =: user name

Password: password

P =: Port

To authorize on the main library, it can be accessed by the main library ip. We can't forget this! (experiments show that it is possible not to authorize from the library, but it is best to authorize it from the library.)

Note:

1) according to the test, you need an account that can log in to both the master and slave database.

2) only one host can be specified, which must be the IP of the main library

3) S lock will be added to the table during inspection

4) the synchronous IO and SQL processes that need to be run from the slave library before running are in YES state.

For example: (example in this article: 192.168.1.101 is the master library ip,192.168.1.102 is the slave library ip)

Perform authorization in the master library (be sure to authorize the master library ip. The authorized user name and password can be defined by yourself, but make sure that this permission can log in to both the master library and the slave library)

Mysql > GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *. * TO 'root'@'192.168.1.101' identified by' 123456'

Mysql > flush privileges

Perform authorization on the slave library

Mysql > GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *. * TO 'root'@'192.168.1.101' IDENTIFIED BY' 123456'

Mysql > flush privileges

As follows, a command to check the consistency of master and slave data is executed on the master library (don't forget to add the-- create-replicate-table parameter the first time you run it, but not when you run it later):

192.168.1.101 in the following command is the main library ip

Check the data of the table under the huanqiu library (of course, you can also directly check the data of an entire library without following the table in the command; remove the tables= table as follows, and directly check the data of the huanqiu library)

[root@master-server] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate=huanqiu.checksums-- create-replicate-table-- databases=huanqiu-- tables= hobbies 192.168.1.101

Diffs cannot be detected because no slaves were found. Please read the-- recursion-method documentation for information. TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE01-08T04:04:54 00 4 1 0 0.009 huanqiu.

No slave was found running. There is an error on it:

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

The above prompt message is clear, because the slave can not be found, so the execution failed, prompted with the parameter-- recursion-method can specify the mode to solve.

Actually, it's because the slave from the library is closed.

Execute on the main library:

Open slave on the slave library

Mysql > start slave

Mysql > show slave status\ G

Then execute on the main library:

Execute the check command again: find that slave is already running.

[root@master-server] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate=huanqiu.checksums-- databases=huanqiu-- tables= hobby 192.168.1.101

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE01-08T04:11:03 0 04 1 0 1.422 huanqiu.

2. Combing and explaining the usage of pt-table-sync:

TS: the time it took to complete the check.

ERRORS: the number of errors and warnings that occurred during the check.

DIFFS: 0 means consistent, 1 means inconsistent. When-- no-replicate-check is specified, it will always be 0, and when-- replicate-check-only is specified, different information will be displayed.

ROWS: the number of rows in the table.

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.

If an inconsistent data table is found through a pt-table-checksum check, how do you synchronize the data? That is, how to fix the inconsistent data of MySQL masters and make them consistent?

At this point, you can use another tool, pt-table-sync.

How to use it:

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.

If the above check the data and find that the master is not consistent

[root@master-server] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate=huanqiu.checksums-- databases=huanqiu-- tables= hobby 192.168.1.101

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE01-08T04:18:07 01 4 1 0 0.843 huanqiu.

Now we need a DIFFS of 1 to know that the master-slave data is inconsistent and needs to be repaired! The repair command is as follows:

First master's ip, user, password, then slave's ip, user, password

[root@master-server] # pt-table-sync-- replicate=huanqiu.checksums hobby 192.168.1.101 print print

REPLACE INTO `huanqiu`.`` (`id`, `name`) VALUES ('1mm,' wangshibo') / * percona-toolkit src_db:huanqiu src_tbl: src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl: dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/ REPLACE INTO `huanqiu`.`` (`id`, `name`) VALUES ('2mom,' wangshikui') / * percona-toolkit src_db:huanqiu src_tbl: src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl: dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/ REPLACE INTO `huanqiu`.`` (`id`, `name`) VALUES ('34th,' limeng') / * percona-toolkit src_db:huanqiu src_tbl: src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl: dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/ REPLACE INTO `huanqiu`.`` (`id`, `name`) VALUES ('44th,' wanghi') / * percona-toolkit src_db:huanqiu src_tbl: src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl: dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/

Parameter explanation:

-- replicate=: specifies the table obtained through pt-table-checksum, and both tools will be used almost all the time.

-- databases=: specifies the database on which synchronization is performed.

-- 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.

H =: server address, there are two ip in the command, the first is the address of Master, the second is the address of Slave.

U =: account number.

P =: password.

-- print: prints, but does not execute commands.

-- execute: execute the command.

After the introduction of the above command, let's start to perform the repair:

Print out the sql statement to repair the data through (--print), which can be manually executed on the slave slave library to keep their data consistent, which is more troublesome!

Repair operations can be performed directly on the master main library through the-- execute parameter, as follows:

[root@master-server] # pt-table-sync-- replicate=huanqiu.checksums hobby 192.168.1.101 execute execute

After the repair above, check again and find that the master and slave database data have been consistent!

[root@master-server] # pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate=huanqiu.checksums-- databases=huanqiu-- tables= hobby 192.168.1.101

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE01-08T04:36:43 0 04 1 0 0.040 huanqiu.

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

Recommendations:

When repairing data, it is best to use-- print to print it out, 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.

Can't make changes on the master because no unique index exists at / usr/local/bin/pt-table-sync line 10591.

In order to ensure the consistency of master-slave data, monitoring scripts can be written and checked regularly. When an inconsistency between master and slave data is detected, the data is forced to be repaired.

[root@master-server ~] # cat / root/pt_huanqiu.sh

#! / bin/bashNUM=$ (/ usr/bin/pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate=huanqiu.checksums-- databases=huanqiu hackers 192.168.1.101 if NUM-eq 1] Then / usr/bin/pt-table-sync-- replicate=huanqiu.checksums hype 192.168.1.101 replicate=huanqiu.checksums 123456-- print / usr/bin/pt-table-sync-- replicate=huanqiu.checksums hype 192.168.1.101-- executeelse echo "data is ok" fi

[root@master-server ~] # crontab-l [root@master-server ~] # cat / root/pt_huanpc.sh

#! / bin/bashNUM=$ (/ usr/bin/pt-table-checksum-- nocheck-replication-filters-- no-check-binlog-format-- replicate=huanpc.checksums-- databases=huanpc hackers 192.168.1.101 if NUM-eq 1] Then / usr/bin/pt-table-sync-- replicate=huanpc.checksums hype 192.168.1.101 replicate=huanpc.checksums 123456-- print / usr/bin/pt-table-sync-- replicate=huanpc.checksums hype 192.168.1.101-- executeelse echo "data is ok" fi

[root@master-server ~] # crontab-l

# check the data consistency of master-slave huanqiu database

* / bin/bash-x / root/pt_huanqiu.sh > / dev/null 2 > & 1

* sleep 10 / root/pt_huanqiu.sh bind Bash-x / dev/null 2 > & 1

* sleep 20 root/pt_huanqiu.sh bind Bash-x / root/pt_huanqiu.sh > / dev/null 2 > & 1

* sleep 30Tech bind Bash-x / root/pt_huanqiu.sh > / dev/null 2 > & 1

* sleep 40 bind bind Bash-x / root/pt_huanqiu.sh > / dev/null 2 > & 1

* sleep 50 / root/pt_huanqiu.sh bind Bash-x / dev/null 2 > & 1

# check the data consistency of master-slave huanpc database

* / bin/bash-x / root/root/pt_huanpc.sh > / dev/null 2 > & 1

* sleep 10 / root/pt_huanpc.sh bind Bash-x / dev/null 2 > & 1

* sleep 20 root/pt_huanpc.sh bind Bash-x / root/pt_huanpc.sh > / dev/null 2 > & 1

* sleep 30Tech bind Bash-x / root/pt_huanpc.sh > / dev/null 2 > & 1

* sleep 40 bind bind Bash-x / root/pt_huanpc.sh > / dev/null 2 > & 1

* sleep 50 / root/pt_huanpc.sh bind Bash-x / dev/null 2 > & 1

Finally, it is concluded that:

Pt-table-checksum and pt-table-sync tools are so powerful that they are often used at work. Note that authorization is required to use this tool, and general SELECT, PROCESS, SUPER, REPLICATION SLAVE and other permissions are sufficient.

Another question:

In the above operation, after the permission of pt-table-checksum check is added to the master library (slave database may not be authorized), the data consistency check operation will generate a checksums table under the operating library (huanqiu, huanpc in the example)!

This checksums table is generated during the pt-table-checksum check. Once this table is generated, it cannot be deleted by default, and the library in which the table is located cannot be deleted by default, and it will come out again soon after it is deleted.

If you want to delete it, you must first take back the permissions added before the pt-table-checksum check!

After the permission is deleted, you can successfully delete the checksums table and its library!

The checksums of the master library has been deleted, and the table of the slave library will be deleted as well.

In other words, once the checksums table is generated, not only the table cannot be deleted by default, but also the library in which it resides. If you want to delete them, you can only revoke the permission as above.

3. Pt-heartbeat monitors mysql master-slave replication delay carding

The monitoring of master-slave replication delay of MySQL database can be realized with the help of pt-heartbeat, a powerful weapon of percona.

Pt-heartbeat works by updating a specific table on the master library by using a timestamp, then reading the updated timestamp on the slave library and comparing it with the time of the local system to get its delay. Specific process:

1) create a heartbeat table on the master and update the fields of the table according to a certain time frequency (update the time). After the monitoring operation is running, the heartbeat table can promote the master-slave synchronization!

2) connect to check the copied time record from the library and compare it with the current system time from the library to get the time difference.

Usage (monitoring operations can be performed on both master and slave libraries):

Pt-heartbeat [OPTIONS] [DSN]-- update |-- monitor |-- check |-- stop

Note: you need to specify at least-- stop,--update,--monitor,--check. Where-- update,--monitor and-- check are mutually exclusive,-- daemonize and-- check are also mutually exclusive. -- ask-pass implicitly enters MySQL password-- charset character set setting-- check checks the delay of slaves and exits once, unless specified-- recurse will recursively check all slaves. -- check-read-only if read-only mode is enabled from the server, the tool will skip any insertion. -- create-table creates a heartbeat monitoring table on the master. If the table does not exist, you can create it manually. It is recommended that the storage engine be changed to memory. The master-slave delay gap is known by updating the table. CREATE TABLE heartbeat (ts varchar (26) NOT NULL, server_id int unsigned NOT NULL PRIMARY KEY, file varchar (255) DEFAULT NULL, position bigint unsigned DEFAULT NULL, relay_master_log_file varchar (255DEFAULT NULL, exec_master_log_pos bigint unsigned DEFAULT NULL); the heratbeat table changes ts and position all the time, and ts is the key to checking replication latency. -- when daemonize is executed, put it into the background to execute-- user=-u, the account number to connect to the database-- database=-D, the name of the connected database-- host=-h, the address of the connected database-- password=-p, the password to connect to the database-- port=-P, and the port to connect to the database-- socket=-S. The socket file that connects to the database-- file [--file=output.txt] prints-- monitor records the latest to the specified file, which is a good way to prevent data troubles all over the screen. The record section in the output [] of frames [--frames=1m,2m,3m] in-- monitor defaults to 1m, 5m, 15m. You can specify 1, such as:-- frames=1s, separated by commas. Available units are seconds (s), minutes (m), hours (h), days (d). -- the interval between interval checks and updates. The default is 1s. The smallest unit is 0.01s, and the maximum precision is two decimal places, so 0.015 will be adjusted to 0.02. -- log all logs that turn on daemonized mode will be printed to the established file. -- monitor continuously monitors the delay of slaves. The delay information of the slave is printed through the interval specified by-- interval, and the information can be printed to the specified file by-- file. -- master-server-id specifies the master's server_id. If not specified, the tool will connect to the master to find its server_id. -- print-master-server-id in-- monitor and-- check modes, specify this parameter to print out the main server_id. -- the check depth of recurse multilevel replication. Mode Mmurs, Smurs... Not the last one needs to turn on log_slave_updates so that it can be checked. -- recursion-method specifies the method of replication check. Default is processlist,hosts. -- update updates the master's heartbeat table. -- replace uses-- replace instead of-- update mode to update the time field in the heartbeat table, which has the advantage of not caring whether there are rows in the table. -- stop stops running the tool (--daemonize) and creates a "pt-heartbeat-sentinel" file in the / tmp/ directory. If you want to reopen it later, you need to delete the temporary file before you can open it (--daemonize). -- table specifies the heartbeat table name. Default is heartbeat.

An example shows:

Master:192.168.1.101

Slave:192.168.1.102

Synchronized libraries: huanqiu, huanpc

Both master and slave libraries can log in using root account and password of 123456.

First operate the check for huanqiu libraries, and the check operations for other synchronous libraries are similar!

Mysql > use huanqiu Database changedmysql > CREATE TABLE heartbeat (/ / create the heartbeat table under the corresponding library on the main library. Generally, the slave database will synchronize this table after creation (if not synchronized) Create it manually from the library)-> ts varchar (26) NOT NULL,-> server_id int unsigned NOT NULL PRIMARY KEY,-> file varchar (255) DEFAULT NULL,-> position bigint unsigned DEFAULT NULL,-> relay_master_log_file varchar (255DEFAULT NULL,-> exec_master_log_pos bigint unsigned DEFAULT NULL->) Query OK, 0 rows affected (0.02 sec)

Updating the heartbeat,--interval=1 on the main library means that it is updated every second (note that this startup operation should be performed on the main library server)

[root@master-server] # pt-heartbeat-user=root-ask-pass-host=192.168.1.101-create-table-D huanqiu-interval=1-update-replace-daemonize

Enter password:

[root@master-server ~] #

[root@master-server ~] # ps-ef | grep pt-heartbeat

Root 15152 10 19:49? 00:00:00 perl / usr/bin/pt-heartbeat-user=root-ask-pass-host=192.168.1.101-create-table-D huanqiu-interval=1-update-replace-daemonize

Root 15154 14170 0 19:49 pts/3 00:00:00 grep pt-heartbeat

Run in the main library to monitor synchronization latency:

[root@master-server] # pt-heartbeat-D huanqiu-table=heartbeat-monitor-host=192.168.1.102-user=root-password=123456

0.00s [0.00s, 0.00s, 0.00s]

Explanation: 0 means there is no delay. [0.00s, 0.00s, 0.00s] represents the average value of 1m, 5m, 15m. You can set it through-- frames.

Or add the-- master-server-id parameter (the server-id value configured in the main library my.cnf)

[root@master-server] # pt-heartbeat-D huanqiu-table=heartbeat-monitor-host=192.168.1.102-user=root-password=123456-master-server-id=101

0.00s [0.00s, 0.00s, 0.00s]

You can also print out the server-id of the main library (--print-master-server-id)

[root@master-server] # pt-heartbeat-D huanqiu-table=heartbeat-monit-host=192.168.1.102-user=root-password=123456-print-master-server-id

0.00s [0.00s, 0.00s, 0.00s] 101

[root@master-server] # pt-heartbeat-D huanqiu-table=heartbeat-check-host=192.168.1.102-user=root-password=123456-print-master-server-id

0.00 101

The above monitoring command will be running all the time. You can use-- check to monitor once and then exit.

Note: if you use-- check, you cannot use-- monit.

-- update,--monitor and-- check are mutually exclusive,-- daemonize and-- check are also mutually exclusive.

[root@master-server] # pt-heartbeat-D huanqiu-table=heartbeat-check-host=192.168.1.102-user=root-password=123456

0.00

[root@master-server ~] #

Note:

If you want to add this output to automatic monitoring, you can use the following command to write the monitoring output to a file, and then use the script to periodically filter the maximum value in the file as an early warning:

Note-- the log option will not be printed to the file until there is a-- daemonize parameter, and the path to this file is preferably under / tmp, otherwise it may not be created due to permission issues.

[root@master-server] # pt-heartbeat-D huanqiu-table=heartbeat-- monitor-- host=192.168.1.102-- user=root-- password=123456-- log=/opt/master-slave.txt-- daemonize

[root@master-server ~] # tail-f / opt/master-slave.txt / / you can test whether the data is synchronized from the database in time when updating the data on the main database. If not, you can see the monitored delay data here.

0.00s [0.00s, 0.00s, 0.00s]

The following is a master-slave synchronization delay monitoring script written, which is filtered periodically-the maximum value in the log file (this script runs on the premise of starting the update master library heartbeat command and the synchronization delay detection command with-log). If a delay occurs, send an alarm email. Sendemail mail delivery environment deployment reference: http://www.cnblogs.com/kevingrace/p/5961861.html

[root@master-server ~] # cat / root/check-slave-monit.sh

#! / bin/bashcat / opt/master-slave.txt > / opt/master_slave.txtecho > / opt/master-slave.txtmax_time= `cat / opt/master_slave.txt | grep-v'^ $'| awk'{print $1}'| sort-k1nr | head-1 `NUM = $(echo "$max_time" | cut-d "s"-F1) if [$NUM= = "0.00"] Then echo "Mysql master-slave data consistency" else / usr/local/bin/sendEmail-f ops@huanqiu.cn-t wangshibo@huanqiu.cn-s smtp.huanqiu.cn-u "Mysql master-slave synchronization delay"-o message-content-type=html-o message-charset=utf8-xu ops@huanqiu.cn-xp WEE78@12l$-m "Mysql master-slave data synchronization has delay" fi

[root@master-server ~] # chmod / root/check-slave-monit.sh

[root@master-server ~] # sh / root/check-slave-monit.sh

Mysql master and slave data are consistent

Combined with crontab, check every other minute

[root@master-server ~] # crontab-e#mysql Master-Slave synchronization delay check * / bin/bash-x / root/check-slave-monit.sh > / dev/null 2 > & 1

Run monitor synchronization delay on slave library (you can also add-master-server-id=101 or-- print-master-server-id after the command, same as above)

[root@slave-server src] # pt-heartbeat-D huanqiu-table=heartbeat-monitor-user=root-password=123456

0.00s [0.00s, 0.00s, 0.00s]

[root@slave-server src] # pt-heartbeat-D huanqiu-table=heartbeat-user=root-password=123456-check

0.00

[root@slave-server src] # pt-heartbeat-D huanqiu-table=heartbeat-monitor-user=root-password=123456-log=/opt/master-slave.txt-daemonize

[root@slave-server src] # tail-f / opt/master-slave.txt

0.00s [0.00s, 0.00s, 0.00s]

How do I turn off the heartbeat update process executed above on the main library?

Method 1: you can use the parameter-- stop to close it.

[root@master-server ~] # ps-ef | grep heartbeat

Root 15152 10 19:49? 00:00:02 perl / usr/bin/pt-heartbeat-user=root-ask-pass-host=192.168.1.101-create-table-D huanqiu-interval=1-update-replace-daemonize

Root 15310 10 19:59? 00:00:01 perl / usr/bin/pt-heartbeat-D huanqiu-table=heartbeat-- monitor-- host=192.168.1.102-- user=root-- password=123456-- log=/opt/master-slave.txt-- daemonize

Root 15555 31932 0 20:13 pts/2 00:00:00 grep heartbeat

[root@master-server] # pt-heartbeat-- stop

Successfully created file / tmp/pt-heartbeat-sentinel

[root@master-server ~] # ps-ef | grep heartbeat

Root 15558 31932 0 20:14 pts/2 00:00:00 grep heartbeat

[root@master-server ~] #

In this way, the process started in the Lord is killed.

But if you want to continue to open the background, remember to delete the / tmp/pt-heartbeat-sentinel file first, otherwise it won't start.

Method 2: directly kill the process pid (recommended)

[root@master-server ~] # ps-ef | grep heartbeat

Root 15152 10 19:49? 00:00:02 perl / usr/bin/pt-heartbeat-user=root-ask-pass-host=192.168.1.101-create-table-D huanqiu-interval=1-update-replace-daemonize

Root 15310 10 19:59? 00:00:01 perl / usr/bin/pt-heartbeat-D huanqiu-table=heartbeat-- monitor-- host=192.168.1.102-- user=root-- password=123456-- log=/opt/master-slave.txt-- daemonize

Root 15555 31932 0 20:13 pts/2 00:00:00 grep heartbeat

[root@master-server] # kill-9 15152

[root@master-server ~] # ps-ef | grep heartbeat

Root 15558 31932 0 20:14 pts/2 00:00:00 grep heartbeat

Finally, it is concluded that:

The problem of default master-slave delay can be well compensated by the pt-heartbeart tool, but the principle of the tool needs to be understood.

The default Seconds_Behind_ Master value is obtained by comparing the server's current timestamp with the event timestamp in the binary log, so the delay can only be reported when the event is executed. If the slave replication thread is not running, it will also report a delay null.

There is another situation: a large transaction, a transaction updates data for as long as an hour, and finally commits. This update will be recorded in the binary log an hour later than it actually occurs. When the standby library executes this statement, it temporarily reports that the repository delay is one hour, and then becomes 0 soon after execution.

-- percona-toolkit other component command usage--

The following tools are best not to be used directly online, but should be used as online aids or offline analysis tools after failures, and can also be used in conjunction with performance testing.

1) pt-online-schema-change

Function introduction:

The function is: when the alter operation changes the table structure, there is no need to lock the table, that is to say, the write and read operations will not be blocked when performing alter. Note that you must make a good backup when implementing this tool. It is best to fully understand its principle before operation.

The working principle is: create an empty table structure that is the same as the table you want to perform alter operation, execute the table structure modification, and then copy the original data from the original table to the table structure modified table. When the data copy is completed, the original table will be removed and the original table will be replaced with the new table. The default action is to drop the original table. During the copy data process, any update operation on the original table will be updated to the new table, because the tool will create a trigger on the original table, and the trigger will update the contents updated on the original table to the new table. If the trigger is already defined in the table, the tool will not work.

Usage introduction:

Pt-online-schema-change [OPTIONS] DSN

Options can check the help itself (or add-- help to see what options are available), and DNS is the database and table you want to manipulate.

There are two parameters to note:

-- the parameter dry-run does not create a trigger, copy data, and replace the original table. Just create and change the new table.

The function of the parameter execute is the same as described earlier in how it works, creating triggers to ensure that the most recently changed data will affect the new table. Note: if this parameter is not added, the tool will exit after performing some checks. This move is to make the use of this fully understand the principle of this tool.

Examples of use:

Change the engine of the table online, which is especially useful when sorting out innodb tables. The table of the following huanqiu library defaults to the Myisam storage engine and now needs to be modified online to Innodb type.

Mysql > show create table huanqiu. +- -- + | Table | Create Table | +-+-- - -+ | | CREATE TABLE `` (`id` int (10) NOT NULL AUTO_INCREMENT `name` varchar (50) NOT NULL PRIMARY KEY (`id`) ENGINE=MyISAM AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 | +-+- -+ 1 row in set (0.00 sec)

The modification operation is as follows:

[root@master-server] # pt-online-schema-change-- user=root-- password=123456-- host=localhost-- alter= "ENGINE=InnoDB" execute-- check-replication-filtersFound 1 slaves: slave-serverWill check slave lag on: slave-serverReplication filters are set on these hosts: slave-server slave_skip_errors = ALL replicate_ignore_db = mysql replicate_do_db = huanqiu,huanpcPlease read the-- check-replication-filters documentation to learn how to solve this problem. At / usr/bin/pt-online-schema-change line 8083.

If the above command operates on the master library, it will be prompted that it has a slave library, and you need to add the parameter-nocheck-replication-filters, that is, do not check the slave library. (note: in the following command, you can replace localhost with the main library ip. In addition: this command can only be modified for a table because it operates on alter, while alter is an operation command for tables. So the option "t = table name" in the command cannot be omitted)

[root@master-server] # pt-online-schema-change-- user=root-- password=123456-- host=localhost-- alter= "ENGINE=InnoDB" nocheck-replication-filters Found 1 slaves: slave-server.2017-01-16T10:36:33 Dropped old table `huanqiu`.` _ _ old` OK.2017-01-16T10:36:33 Dropping triggers...2017-01-16T10:36:33 Dropped triggers OK.Successfully altered `huanqiu`.``.

Then look at the storage engine of the huanqiu. table again and find that it is already of type Innodb.

Mysql > show create table huanqiu. +- -- + | Table | Create Table | +-+-- - -+ | | CREATE TABLE `` (`id` int (10) NOT NULL AUTO_INCREMENT `name` varchar (50) NOT NULL PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 | +-+- -+ 1 row in set (0.00 sec)

If you are on the slave library, you can execute it directly (or you can replace the following slave library ip with localhost)

[root@slave-server] # pt-online-schema-change-- user=root-- password=123456-- host=192.168.1.102-- alter= "ENGINE=InnoDB" Drunhuanqiu executeNo slaves found. See-recursion-method if host slave-server has slaves.Not checking slave lag because no slaves were found and-check-slave-lag was not specified.Operation, tries Wait:.2017-01-15T21:40:35 Swapped original and new tables OK.2017-01-15T21:40:35 Dropping old table...2017-01-15T21:40:35 Dropped old table `huanqiu`.` _ _ old` OK.2017-01-15T21:40:35 Dropping triggers...2017-01-15T21:40:35 Dropped triggers OK.Successfully altered `huanqiu`.``.

2) pt-duplicate-key-checker

Function introduction:

The function is to find duplicate indexes and foreign keys from the mysql table. This tool lists the duplicate indexes and foreign keys and generates a statement to delete the duplicate index, which is very convenient.

Usage introduction:

Pt-duplicate-key-checker [OPTION...] [DSN]

Contains more options, you can use the command pt-duplicate-key-checker-- help to see which options are supported, I will not list them one by one here. DNS is a database or table.

Examples of use:

To view the duplicate index and foreign key usage of the huanqiu library or huanqiu. table, use the following command:

[root@master-server] # pt-duplicate-key-checker-host=localhost-user=root-password=123456-databases=huanqiu# Summary of indexes # # # Total Indexes Total Indexes [root@master-server ~] # pt-duplicate-key-checker-host=localhost-user=root-password=123456-databases=huanqiu-table=# # # Summary of indexes # # Total Indexes 1

3) pt-slave-find

Function introduction:

Find and print all mysql replication hierarchies from the server

Usage introduction:

Pt-slave-find [OPTION...] MASTER-HOST

How it works: connect to the mysql master server and find all its slaves, and then print out the hierarchical relationships of all the slave servers.

Examples of use:

Find that the mysql with the master server 192.168.1.101 has all the slave hierarchical relationships (change the following 192.168.1.101 to localhost, that is, query the slave relationships of the native mysql):

[root@master-server] # pt-slave-find-- user=root-- password=123456-- host=192.168.1.101192.168.1.101Version 5.6.33-logServer ID 101Uptime 5502 password=123456 59 root@master-server 42 (started 2017-01-11T10:44:14) Replication Is not a slave, has 1 slaves connected, is not read_onlyFilters Binary logging MIXEDSlave status Slave mode STRICTAuto-increment increment 1 Offset 1InnoDB version 5.6.33 slaves connected-192.168.1.102 Version 5.6.34-log Server ID 102 Uptime 4 22 Uptime 22 Uptime 22 started 2017-01-11T15:21:38) Replication Is a slave, has 0 slaves connected, is not read_only Filters slave_skip_errors=ALL Replicate_ignore_db=mysql; replicate_do_db=huanqiu,huanpc Binary logging MIXED Slave status 265831 seconds behind, running, no errors Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 5.6.34

4) pt-show-grants

Function introduction:

Standardize and print mysql permissions to make you more efficient in copying, comparing mysql permissions, and versioning!

Usage introduction:

Pt-show-grants [OPTION...] [DSN]

The options can be viewed using help, and the DSN option can also be viewed in help, which is case-sensitive.

Examples of use:

View all user rights for the specified mysql:

[root@master-server ~] # pt-show-grants-- host='localhost'-- user='root'-- password='123456'-- Grants dumped by pt-show-grants-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11 user='root' 22 password='123456'-- Grants dumped by pt-show-grants-- Dumped from server Localhost via UNIX socket-Grants for'@ 'localhost'GRANT USAGE ON *. * TO' @ 'localhost';-- Grants for' data_check'@'%'

View the permissions to execute the database:

[root@master-server] # pt-show-grants-host='localhost'-user='root'-password='123456'-database='huanqiu'

Grants dumped by pt-show-grants-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11 Grants for'@ 'localhost'GRANT USAGE ON *. * TO''@ 'localhost';-- Grants for' data_check'@'%'GRANT SELECT ON *. * TO 'data_check'@'%' IDENTIFIED BY PASSWORD' * 36B94ABF70E8D5E025CF9C059E66445CBB05B54F' -- Grants for 'mksync'@'%'GRANT ALL PRIVILEGES ON *. * TO' mksync'@'%' IDENTIFIED BY PASSWORD'* B5E7409B1A22D47C6F1D8A693C6146CEB6570475

Look at the statement that each user right generates revoke to revoke the right:

[root@master-server ~] # pt-show-grants-- host='localhost'-- user='root'-- password='123456'-- revoke-- Grants dumped by pt-show-grants-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11 11 user='root' 24RV 58 localhost'.-Revoke statements for'@ 'localhost'REVOKE USAGE ON *. * FROM' @ 'localhost';-- Grants for' @ 'localhost'.

5) pt-upgrade

Function introduction:

This tool is used to check whether the SQL running in the new version returns the same results as the old version, and the best application scenario is when the data is migrated. This is very useful when upgrading the server, you can first install and import the data to the new server, and then use this tool to run sql to see what the differences are, and you can find out the differences between different versions.

Usage introduction:

Pt-upgrade [OPTION...] DSN [DSN...] [FILE]

Compare the results of each query statement in the file executed on each server (mainly for different versions of the execution results). (--help view option)

Examples of use:

View an example of the result of a sql file running on two servers:

[root@master-server] # pt-upgrade hobby 192.168.1.101 hm2 192.168.1.102-- user=root-- password=123456 / root/test.sql#----# Logs#- -host1: DSN: hype 192.168.1.101 hostname: master-server MySQL: Source distribution 5.6.33host2: DSN: hype 192.168.1.102 hostname: slave-server MySQL: Source distribution 5.6.34 .queries _ read 1queries_with_diffs 0queries_with_errors 0

Check the example of the running result of the corresponding query SQL in the slow query on two servers:

[root@master-server] # pt-upgrade hobby 192.168.1.101 hm2 192.168.1.102-- user=root-- password=123456 / data/mysql/data/mysql-slow.log

6) pt-index-usage

Function introduction:

This tool is mainly used to analyze the index usage of slow queries. Read the insert statements from the log file and use explain to analyze how they use the index. After the analysis is completed, a report is generated that the index has not been used by the query.

Usage introduction:

Pt-index-usage [OPTION...] [FILE...]

You can get the sql format in the sql,FILE file directly from the slow query. The sql format in the slow query must be the same as the one in the slow query, if you don't always need to convert it with pt-query-digest. You can also save the report directly to the database without generating a report. For details, see the example below.

Note: to use this tool, MySQL must have a password, in addition, the runtime may not find / var/lib/mysql/mysql.sock error, simply from the mysql boot sock file to do a soft link.

The important thing to note is that pt-index-usage can only analyze slow query logs, so if you want to fully analyze the index usage of all queries, you must set slow_launch_time to 0. Therefore, please use this tool carefully. If you use it online, it is best to analyze it in the early morning, especially when analyzing a large number of logs. It is very CPU consuming.

Overall, this tool is not recommended, and other third-party tools such as mysqlidxchx, userstat and check-unused-keys can be considered to implement similar analysis. Userstat, a patch contributed by Google, is recommended on the Internet.

Examples of use:

View the sample index usage from the sql in the full query:

[root@master-server] # pt-index-usage-- host=localhost-- user=root-- password=123456 / data/mysql/data/mysql-slow.log

Save the analysis results to the database example:

[root@master-server] # pt-index-usage-host=localhost-user=root-password=123456 / data/mysql/data/mysql-slow.log-no-report-create-save-results-database

7) pt-visual-explain

Function introduction:

The execution plan from the formatted explain is output in tree mode, which is easy to read.

Usage introduction:

Pt-visual-explain [OPTION...] [FILE...]

An example of viewing the explain output directly through the pipeline:

Mysql > select * from huanqiu. +-4 rows in set (0.00 sec) [root@master-server ~] # mysql-uroot-p123456-e "explain select * from huanqiu." | pt-visual-explainWarning: Using a password on the command line interface can be insecure.Table scanrows 4neighbors-Table table [root@master-server ~] # mysql-uroot-p123456-e "explain select * from huanqiu. where id=3" | pt-visual-explainWarning: Using a password on the command line interface can be insecure.Bookmark lookup+- Table | table | possible_keys PRIMARY+- Constant index lookup key - > PRIMARY possible_keys PRIMARY key_len 4 ref const rows 1

View an example of an test.sql file that contains query statements:

[root@master-server ~] # pt-visual-explain-- connect / root/test.sql-- user=root-- password=123456 example 2: compare the differences between local configuration files and remote servers:

8) pt-config-diff

Function introduction:

Compare mysql profile and server parameters

Usage introduction:

Pt-config-diff [OPTION...] CONFIG CONFIG [CONFIG...]

CONFIG can be a file or a data source name, and at least two profile sources must be specified, just like the diff command under unix. If the configuration is exactly the same, nothing will be output.

Examples of use:

Example 1: view the profile differences between the local and remote servers:

Root@master-server ~] # pt-config-diff h=localhost hackers 192.168.1.102-- user=root-- password=12345618 config differencesVariable master-server slave-server= binlog_checksum NONE CRC32general_log_file / data/mysql/data/maste... / data/mysql/data/slave...hostname master-server slave-serverinnodb_version 5.6.33 5.6.34log_bin_index / data/mysql/data/maste... / data/mysql/data/slave...log_slave_updates OFF ONrelay_log_recovery OFF ONsecure_file_priv NULLserver_id 101102server_uuid d8497104-d7a7-11e6-911. D8773e51-d7a7-11e6-911...slave_net_timeout 3600 5slave_skip_errors OFF ALLsync_binlog 1 0sync_master_info 10000 1sync_relay_log 10000 1sync_relay_log_info 10000 1system_time_zone CST ESTversion 5.6.33-log 5.6.34-log

Example 2: compare the differences between the local profile and the remote server:

[root@master-server ~] # pt-config-diff / usr/local/mysql/my.cnf h=localhost hackers 192.168.1.102-- user=root-- password=1234566 config differencesVariable / usr/local/mysql/my.cnf master-server slave-ser= = binlog_checksum none NONE CRC32innodb_read_io_threads 1000 64 64innodb_write_io_threads 1000 64 64log_bin_index master-bin.index / data/mysql/dat... / data/mysql/...server_id 101 101 102sync_binlog 1 10

9) pt-mysql-summary

Function introduction:

Fine summary of mysql configuration and sataus information, after the summary, you can see it at a glance.

How it works: after connecting to mysql, query out the status and configuration information and save it to a temporary directory, then format it with awk and other scripting tools. OPTIONS can check the relevant pages of the official website.

Usage introduction:

Pt-mysql-summary [OPTIONS] [--MYSQL OPTIONS]

Examples of use:

Summarize the status and configuration information for the local mysql server:

[root@master-server] # pt-mysql-summary-user=root-- password=123456-- host=localhost

10) pt-deadlock-logger

Function introduction:

Extract and record information about mysql deadlocks

Usage introduction:

Pt-deadlock-logger [OPTION...] SOURCE_DSN

By collecting and saving the latest deadlock information on mysql, you can directly print deadlock information and store deadlock information to the database. Deadlock information includes a lot of information, such as the server where the deadlock occurred, the time when the deadlock occurred, the deadlock thread id, the deadlock transaction id, how long the transaction executed when the deadlock occurred, and so on.

Examples of use:

View the deadlock information of the local mysql

[root@master-server ~] # pt-deadlock-logger-- user=root-- password=123456 h=localhost Dempest Encoding locksserver ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim querylocalhost 2017-01-11T11:00:33 18800 root 192.168.1.101 huanpc checksums PRIMARY RECORD X w 1 REPLACE INTO `huanpc`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'huanpc',' heihei','1, NULL, NULL, NULL COUNT (*) AS cnt, COALESCE (LOWER (CONV (CAST (CRC32 (CONCAT_WS ('#', `member`, `city`) AS UNSIGNED), 0) AS crc FROM `huanpc`.`heihei` / * checksum table*/localhost 2017-01-11T11:00:33 19800 root 192.168.1.101 huanpc checksums PRIMARY RECORD X w 0 REPLACE INTO `huanpc`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'huanpc',' heihei' '1customers, NULL, COUNT (*) AS cnt, COALESCE (CONV (BIT_XOR (CAST (CRC32 (CONCAT_WS (CONCAT_WS (' #', `member`, `city`) AS UNSIGNED)), 10,16)), 0) AS crc FROM `huanpc`.`heihei` / * checksum table*/

11) pt-mext

Function introduction:

View the information of multiple samples of SHOW GLOBAL STATUS in parallel.

Usage introduction:

Pt-mext [OPTIONS]-- COMMAND

Principle: pt-mext executes the COMMAND you specify, reads one line of results at a time, saves the contents of the blank line division into a temporary file, and finally combines these temporary files to view the results in parallel.

Examples of use:

Execute SHOW GLOBAL STATUS every 10 seconds and merge the results together to view

[root@master-server] # pt-mext-- mysqladmin ext-uroot-p123456-i10-c3

12) pt-query-digest

Function introduction:

Analyze the query execution log and produce a query report that filters, replays, or converts statements for MySQL, PostgreSQL, memcached.

Pt-query-digest can analyze queries from normal MySQL logs, slow query logs, and binary logs, and even from tcpdump of SHOW PROCESSLIST and MySQL protocols. If no file is specified, it reads data from the standard input stream (STDIN).

Usage introduction:

Pt-query-digest [OPTION...] [FILE]

Parsing and analyzing mysql log files

Use example: (suggestion: when the log is very large, it is best to move the log files to other machines for analysis, so as not to consume too much local performance.)

Example 1: analyze local slow log files (in this case, slow log, or other logs of mysql)

[root@master-server ~] # pt-query-digest-- user=root-- password=123456 / data/mysql/data/mysql-slow.log# 260ms user time, 30ms system time, 24.85m rss, 204.71m vsz# Current date: Mon Jan 16 13:20:39 2015m Hostname: master-server# Files: / data/mysql/data/mysql-slow.log# Overall: 18 total, 2 unique, 0 QPS 0x concurrency _ # Time range: all events occurred at 2017-01-11 11 Attribute total min max avg 95% stddev median# = # Exec time 812s 2s 92s 45s 80s 27s 52s# Lock time 00 "Rows sent 00" Rows examine 00 00 "Query size 1.10k 62 63 62.56 62.76 0.50 62.76 # Profile# Rank Query ID Response time Calls R/Call Item# M Item# = # 1 0x50B84661D4CCF34B 467.9075 57.6% 10 46.7907 16.48 CREATE DATABASE `huanqiu` # 2 0x9CC34439A4FB17E3 344.2984 42.4% 8 43.0373 16.22 CREATE DATABASE `huanpc` # Query 1: 0 QPS 0x concurrency ID 0x50B84661D4CCF34B at byte 2642 _ # This item is included in the report because it matches-- limit.# Scores: v Time range M = 16.4mm Time range: all events occurred at 2017-01-11 11VOUGO 00VOO Attribute pct total min max avg 95% stddev median# = # Count 55 1mm Exec time 57 468s 2s 92s 47s 80s 28s 52s# Lock time 00 00 # Rows sent 00 00 00 # Rows examine 00 00 00 # Query size 55 630 63 63 63 630 6 "String:# Hosts# Users root# Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s # 10s + # CREATE DATABASE IF NOT EXISTS `huanqiu` / * pt-table-checksum * /\ G# Query 2: 0 QPS 0x concurrency ID 0x9CC34439A4FB17E3 at byte 3083 _ # This item is included in the report because it matches-- limit.# Scores: v Time range M = 16.2mm Time range: all events occurred at 2017-01-11 11VOUGO 00VOO Attribute pct total min max avg 95% stddev median# = # Count 44 "Exec time 42 344s 2s 82s 82s 26s 56s# Lock time 00 00 # Rows sent 00 00 # Rows examine 00 00 00 # Query size 44 496 62 62 62 06 String:# Hosts# Users root# Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s # 10s + # CREATE DATABASE IF NOT EXISTS The whole output of `huanpc` / * pt-table-checksum * /\ G is divided into three parts: 1) the overall summary (Overall) this part is a rough summary (similar to the summary given by loadrunner) Through it, we can make a preliminary evaluation of the query performance of the current MySQL, such as maximum value of each index (max), average value (min), 95% distribution value, median (median), standard deviation (stddev). These metrics include the query execution time (Exec time), the lock occupied time (Lock time), the number of rows that the MySQL executor needs to check (Rows examine), the number of rows finally returned to the client (Rows sent), and the size of the query. 2) Summary of queries (Profile) this section provides a list of all "important" queries (usually slower ones). Each query has a Query ID, which is calculated by Hash. Pt-query-digest is based on this so-called Fingerprint to group by. The ranking of the "statement" in Rank's entire analysis is generally the most common. Response time and overall percentage of Response time "statements". Calls the number of times the statement was executed. The average response time per execution of the R/Call. The average contrast ratio of the difference in the response time of VBG M. There is a line of output at the end that shows the statistics of the other two queries that are relatively low and not worth displaying separately. 3) the details section lists the details of each query in the Profile table: including the information in the Overall, the distribution of the query response time, and the reason why the query is "listed". Pt-query-digest also has a lot of complex operations, which will not be covered here. For example: query the slowest query in a MySQL from PROCESSLIST:

Example 2: review the full query log again and save the results to query_review. Note that the table structure of the query_ query table must be built first, as follows:

Mysql > use test;Database changedmysql > CREATE TABLE query_review (- > checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,-> fingerprint TEXT NOT NULL,-> sample TEXT NOT NULL,-> first_seen DATETIME,-> last_seen DATETIME,-> reviewed_by VARCHAR (20),-> reviewed_on DATETIME,-> comments TEXT->); Query OK, 0 rows affected (0.02 sec) mysql > select * from query_review Empty set (0.00 sec) [root@master-server ~] # pt-query-digest-- user=root-- password=123456-- review hackers localhostMagneDlocalhostDlocalhostDlocalhostDifferencyReview / data/mysql/data/mysql-slow.logmysql > select * from query_review + -+-| checksum | fingerprint | sample | first_see+--+--+- -+ | 5816476304744969035 | create database if not exists `huanqiu` | CREATE DATABASE IF NOT EXISTS `huanqiu` / * pt-table-checksum * / | 2017-01-1 | 11295947304747079651 | create database if not exists `huanpc` | CREATE DATABASE IF NOT EXISTS `huanpc` / * pt-table-checksum * / | 2017-01-1 -+ -+-2 rows in set (0.00 sec)

Analyze from tcpdump:

[root@master-server] # tcpdump-s 65535-x-nn-Q-tttt-I any-c 1000 port 3306 > mysql.tcp.txttcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes then open another terminal window: [root@master-server ~] # pt-query-digest-type tcpdump mysql.tcp.txtPipeline process 3 (TcpdumpParser) caused an error: substr outside of string at / usr/bin/pt-query-digest line 3628 Chunk 93.Will retry pipeline process 2 (TcpdumpParser) 100 more times.# 320ms user time, 20ms system time, 24.93M rss, 204.84M vsz# Current date: Mon Jan 16 13:24:50 2018 Hostname: master-server# Files: mysql.tcp.txt# Overall: 31 total, 4 unique, 4.43 QPS 0.00x concurrency _ # Time range: 2017-01-16 13 to 2415 to 50.00120 Attribute total min max avg 95% stddev median# = # Exec time 30ms 79us 5ms 967us 4ms 1ms 159us# Rows affecte 14 0 2 0.45 1.96 0.82 "Query size 1.85k 17 200 61.16 192.76 72.25 17.65.

13) pt-slave-delay

Function introduction:

Sets the slave server to lag behind the master server for a specified time.

Usage introduction:

Pt-slave-delay [OPTION...] SLAVE-HOST [MASTER-HOST]

Principle: set the slave to lag behind the master by starting and stopping the replication sql thread. The default is based on the location of the binary log on the relay log, so you do not need to connect to the master server. If the IO process does not lag too much behind the master server, this check method works well. If the network is unobstructed, the IO thread usually lags behind the master in milliseconds. It is generally controlled by-- delay and-- delay "+"-- interval. -- interval specifies the frequency of checking whether to start or stop the sql thread from the top. The default is once every minute.

Examples of use:

Example 1: make the slave lag 1 minute behind the master, and check every 1 minute, run for 10 minutes

[root@master-server] # pt-slave-delay-user=root-password=123456-delay 1m-run-time 10m-host=192.168.1.1022017-01-16T13:32:31 slave running 0 seconds behind2017-01-16T13:32:31 STOP SLAVE until 2017-01-16T13:33:31 at master position mysql-bin.000005/102554361

Example 2: make the slave lag behind the master by 1 minute, and check every 15 seconds, run for 10 minutes:

[root@master-server] # pt-slave-delay-user=root-password=123456-delay 1m-interval 15s-run-time 10m-host=192.168.1.102

2017-01-16T13:38:22 slave running 0 seconds behind2017-01-16T13:38:22 STOP SLAVE until 2017-01-16T13:39:22 at master position mysql-bin.000005/102689359

14) pt-slave-restart

Function introduction:

Monitor mysql replication errors and try to restart mysql replication when replication stops

Usage introduction:

Pt-slave-restart [OPTION...] [DSN]

Monitor one or more mysql replication errors and try to restart replication when the slave stops. You can specify skipped errors and run from to the specified log location.

Examples of use:

Example 1: monitor the slave of 192.168.1.101, skip 1 error

[root@master-server] # pt-slave-restart-user=root-password=123456-host=192.168.1.101-skip-count=1

Example 2: monitor the slave of 192.168.1.101 and skip the error with error code 1062.

[root@master-server] # pt-slave-restart-user=root-password=123456-host=192.168.1.101-error-numbers=1062

15) pt-diskstats

Function introduction:

Is an interactive monitoring tool for GUN/LINUX

Usage introduction:

Pt-diskstats [OPTION...] [FILES]

Print disk io statistics for GUN/LINUX, similar to iostat, but this tool is interactive and more detailed than iostat. You can analyze data collected from remote machines.

Examples of use:

Example 1: check the status of all disks on this machine:

[root@master-server ~] # pt-diskstats

Example 2: view only the status of native sdc1 disks:

[root@master-server] # pt-diskstats-- devices-regex vdc1

# ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg io_s qtime stime 0.9 vdc1 0.0 0 0 0 5 9 4.0 0 0 0 5 9 0.6 0 0 0 vdc1 0.0 0 0 0 0.0 2.0 6.0 0.0 33% 0.0 0.7 0 0 0 2.0 0.0 0.7

16) pt-summary

Function introduction:

Friendly collection and display of system information profiles, this tool is not an tuning or diagnostic tool, this tool produces a report that can be easily compared and emailed.

Usage introduction:

Pt-summary

Principle: this tool will run and multiple commands to collect system status and configuration information, first save to a file in the temporary directory, and then run some unix commands to format these results, preferably with root users or authorized users.

Examples of use:

View local system information overview

[root@master-server ~] # pt-summary

17) pt-stalk

Function introduction:

Collect mysql data for diagnosis when there is a problem

Usage introduction:

Pt-stalk [OPTIONS] [--MYSQL OPTIONS]

Pt-stalk waits for the trigger condition to trigger, and then collects data to help diagnose errors. It is designed to run daemons with root privileges, so you can diagnose intermittent problems that you cannot observe directly. The default diagnostic trigger condition is SHOW GLOBAL STATUS. You can also specify processlist as the diagnostic trigger condition, using the-- function parameter.

Examples of use:

Example 1: specify that the diagnostic trigger condition is status, which is triggered when the running statement exceeds 20, and the collected data is stored in the target directory / tmp/test:

[root@master-server] # pt-stalk-- function status-- variable Threads_running-- threshold 20-- dest / tmp/test-uroot-p123456-h292.168.1.101

Example 2: specify that the diagnostic trigger condition is processlist, more than 20 states are statistics trigger, and the collected data is stored in the / tmp/test directory:

[root@master-server] # pt-stalk-function processlist-variable State-match statistics-threshold 20-dest / tmp/test-uroot-p123456-h292.168.1.101.2017_01_15_17_31_49-hostname2017_01_15_17_31_49-innodbstatus12017_01_15_17_31_49-innodbstatus22017_01_15_17_31_49-interrupts2017_01_15_17_31_49-log_error2017 _ 01_15_17_31_49-lsof2017_01_15_17_31_49-meminfo

18) pt-archiver

Function introduction:

Archive the records of a table in the mysql database to another table or file

Usage introduction:

Pt-archiver [OPTION...]-- source DSN-- where WHERE

This tool only archives old data and will not have too much impact on the OLTP query of online data. You can insert the data into other tables on another server, or you can write it to a file to facilitate the use of source commands to import data. You can also use it to perform delete operations. Special note: this tool deletes the data in the source by default!

Examples of use:

Example 1: transfer the records whose table id is less than 10 in the huanqiu library on 192.168.1.101 to the wang_test table under the wangshibo library on 192.168.1.102 and file to the / var/log/_archive_20170115.log file (note: before and after the transfer, the corresponding transfer fields of the two tables should be the same, and the field properties should be the same;)

Information of the table under the huanqiu library of the source database machine 192.168.1.101 before transfer: mysql > select * from huanqiu. +-+ | id | name | +-+ + | 1 | changbo | 2 | wangpengde | 4 | guocongcong | | 5 | kevin | | 8 | mamin | | 9 | shihonge | | 11 | zhanglei | | 15 | zhanghongmiao | +-- +-- + 8 rows in set (0.01sec) Target database machine 192.168. Information of wang_test table under wangshibo library of 1.102 before transfer: mysql > select * from wangshibo.wang_test +-+-+ | id | name | +-+-+ | 20 | guominmin | | 21 | gaofei | 22 | Li Mengnan | +-+-+ 3 rows in set (0.00 sec) then execute the transfer command on the 192.168.1.101 machine: [root@master-server ~] # pt-archiver-- source hobby 192.168.1.101 Magnum Drunhuanqiu T=-- user=root-- password=123456-- dest hobbies 192.168.1.102 where Dempwangshibogy tantalizing test-- file'/ var/log/_archive_20170115.log'-- where "id

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.