Usage of mysqltoolkit [memo]

Mysql toolkit is a small plug-in used to monitor and analyze databases. It is usually used to call perl to analyze and compare the current environment and database. It is worth learning.

Http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.5-2.noarch.rpm

The following software may need to be added during installation

yum install -y perl-DBD-mysql perl-IO-Socket-SSLrpm -ivh percona-toolkit-2.2.5-2.noarch.rpm

Command list

/usr/bin/pt-agent/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-mysql-summary/usr/bin/pt-online-schema-change/usr/bin/pt-pmp/usr/bin/pt-query-digest/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/usr/bin/pt-table-sync/usr/bin/pt-table-usage/usr/bin/pt-upgrade/usr/bin/pt-variable-advisor/usr/bin/pt-visual-explain

Syntax and function (red color indicates attention, and blue indicates little effect)

/Usr/bin/pt-align
Used to format and output files

For example, the content of file t is as follows:

[root@db2 aaa]# cat ta abc   aaa  123d2 5 d2wf wwwxcca233 ddcqq 55  23ds2  55aaa 5tty655 ccqqq

After formatting, the output is as follows:

[root@db2 aaa]# /usr/bin/pt-align ta    abc   aaa 123d2 5     d2wf    wwwxcca233 ddcqq  55 23ds2 55aaa 5tty655 ccqqq

/Usr/bin/pt-archiver
Copy tables in one database to another server

In the following example, copy dbd. t1 in 192.168.200.163 to 192.168.200.171.

[root@db2 ~]# /usr/bin/pt-archiver --source h=192.168.200.163,D=dbd,t=t1,u=terry,p=123 --dest h=192.168.200.171,D=dbd,t=t1,u=terry,p=123 --where "1=1"

Note: You must create an index for the table in source. Otherwise, the following error message is returned.
Cannot find an ascendable index in table at/usr/bin/pt-archiver line 3175.

/Usr/bin/pt-config-diff
Used to display different configuration files in two databases

[root@db2 ~]# /usr/bin/pt-config-diff h=192.168.200.163 h=192.168.200.171 --user=root --password=1235 config differencesVariable                  db2.mytest.com          db4========================= ======================= ============general_log_file          db2.log                 db4.loghostname                  db2.mytest.com          db4log_error                 /mdb/db2.mytest.com.err /mdb/db4.errslow_query_log_file       db2-slow.log            db4-slow.logwsrep_node_name           db2.mytest.com          db4

/Usr/bin/pt-deadlock-logger
The deadlock information can be recorded in another table for a long time. The table format is as follows:

            CREATE TABLE monitor.deadlocks (              server char(20) NOT NULL,              ts datetime NOT NULL,              thread int unsigned NOT NULL,              txn_id bigint unsigned NOT NULL,              txn_time smallint unsigned NOT NULL,              user char(16) NOT NULL,              hostname char(20) NOT NULL,              ip char(15) NOT NULL, -- alternatively, ip int unsigned NOT NULL              db char(64) NOT NULL,              tbl char(64) NOT NULL,              idx char(64) NOT NULL,              lock_type char(16) NOT NULL,              lock_mode char(1) NOT NULL,              wait_hold char(1) NOT NULL,              victim tinyint unsigned NOT NULL,              query text NOT NULL,              PRIMARY KEY  (server,ts,thread)            ) ENGINE=InnoDB

Syntax

pt-deadlock-logger h=10.1.1.29 --dest h=10.1.1.29,D=monitor,t=deadlocks --user=terry --ask-pass

Note: currently, the perl-Term-ReadKey application must be installed to support password input.
User terry must have the process permission in addition to authorizing write operations on the daedlocks table

Refer to the information when a deadlock occurs:

mysql> select * from monitor.deadlocks \G*************************** 1. row ***************************   server: 10.1.1.29       ts: 2013-12-16 03:41:05   thread: 24473   txn_id: 0 txn_time: 5     user: mini189 hostname:       ip: 10.1.1.25       db: 189mini      tbl: index_species_situaction      idx: GEN_CLUST_INDEXlock_type: RECORDlock_mode: Xwait_hold: w   victim: 0    query: update index_species_situaction s set s.new_version= '13121603410024300001' where s.new_version != 'DELETE' and s.species_id ='W6021601'*************************** 2. row ***************************   server: 10.1.1.29       ts: 2013-12-16 03:41:05   thread: 25270   txn_id: 0 txn_time: 5     user: mini189 hostname:       ip: 10.1.1.25       db: 189mini      tbl: index_species_situaction      idx: GEN_CLUST_INDEXlock_type: RECORDlock_mode: Xwait_hold: w   victim: 1    query: update INDEX_SPECIES_SITUACTION h set h.new_version='DELETE' where h.specification_id = NAME_CONST('_specification_id',_utf8'S5F60811' COLLATE 'utf8_general_ci') and h.species_id= NAME_CONST('_species_id',_utf8'W6045FF3' COLLATE 'utf8_general_ci')2 rows in set (0.00 sec)

/Usr/bin/pt-diskstats
Call the/proc/diskstats file to monitor disk io
Directly execute the command and return the following information:

/usr/bin/pt-diskstats  #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  1.0 sda        0.0     0.0     0.0     0%    0.0     0.0     7.0     8.0     0.1    50%    0.1     8.6   4%      0     7.0    5.7   2.9  1.0 sda2       0.0     0.0     0.0     0%    0.0     0.0     7.0     8.0     0.1    50%    0.1     8.6   4%      0     7.0    5.7   2.9  1.0 dm-0       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0  1.0 dm-2       0.0     0.0     0.0     0%    0.0     0.0     4.0     4.0     0.0     0%    0.1    20.0   3%      0     4.0   11.2   8.8  1.0 dm-3       0.0     0.0     0.0     0%    0.0     0.0    10.0     4.0     0.0     0%    0.2    19.0   4%      0    10.0   14.9   4.1  1.0 dm-4       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0  1.0 dm-6       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0  1.0 drbd0      0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0  1.0 sda        0.0     0.0     0.0     0%    0.0     0.0     4.0     6.0     0.0    33%    0.0     5.3   3%      0     4.0    0.0   5.3  1.0 sda2       0.0     0.0     0.0     0%    0.0     0.0     4.0     6.0     0.0    33%    0.0     5.3   3%      0     4.0    0.0   5.3  1.0 dm-0       0.0     0.0     0.0     0%    0.0     0.0     3.0     4.0     0.0     0%    0.0     7.3   1%      0     3.0    2.7   4.7  1.0 dm-2       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0  1.0 dm-3       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0  1.0 dm-4       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0  1.0 dm-6       0.0     0.0     0.0     0%    0.0     0.0     3.0     4.0     0.0     0%    0.0    10.0   2%      0     3.0    4.0   6.0  1.0 drbd0      0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0

/Usr/bin/pt-duplicate-key-checker
The ability to quickly check the number of indexes in a database or table

[root@mini189a ~]# pt-duplicate-key-checker h=10.1.1.29 --database=189mini --user=terry --password=terry123# ######################################################################### Summary of indexes# ######################################################################### Total Indexes  262

/Usr/bin/pt-find
Similar to the find function in linux, you can filter and search tables in the database as needed.

Query table size

[root@mini189a ~]#  pt-find  --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --tablesize +1M`189mini`.`pre_ware_broswers``189mini`.`system_regions``crazy`.`sys_log_bs`

Tables created in the last three days

[root@mini189a ~]#  pt-find  --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --ctime -3`monitor`.`deadlocks``xszl`.`appreleasetable``xszl`.`bulletintable``xszl`.`companytable`

Table modified in the last 35 minutes

[root@mini189a ~]#  pt-find  --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --mmin -35`mysql`.`db``mysql`.`user`

Empty database tables

[root@mini189a ~]# pt-find  --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123  --empty`189mini`.`ware_tag_assign``189mini`.`ware_tag_content``monitor`.`deadlocks``mysql`.`columns_priv``mysql`.`event`

/Usr/bin/pt-fk-error-logger
Used to detect foreign key error messages. You need to create the following table for data storage.

CREATE TABLE foreign_key_errors ( ts datetime NOT NULL, error text NOT NULL, PRIMARY KEY (ts))

Syntax Test

pt-fk-error-logger h=10.1.1.29 --dest h=10.1.1.29,D=monitor,t=foreign_key_errors --user=terry --ask-pass

Note: In addition to authorizing user terry to perform write operations on the foreign_key_errors table, user terry must also have the process permission.

Refer to the returned information below

MariaDB [terry]> select * from monitor.foreign_key_errors \G*************************** 1. row ***************************   ts: 2013-12-20 10:32:42error: Transaction:TRANSACTION 2F1D0, ACTIVE 0 sec insertingmysql tables in use 1, locked 13 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1MySQL thread id 1329, OS thread handle 0x7fe89805a700, query id 7612 localhost root updateinsert into t4 values (5, 'aaaaa')Foreign key constraint fails for table `terry`.`t4`:,  CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t3` (`id`)Trying to add in child table, in index `id` tuple:DATA TUPLE: 2 fields; 0: len 4; hex 80000005; asc     ;; 1: len 6; hex 0000000007e9; asc       ;;But in parent table `terry`.`t3`, in index `PRIMARY`,the closest match we can find is record:PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000004; asc     ;; 1: len 6; hex 00000002f1cd; asc       ;; 2: len 7; hex f4000001a90134; asc       4;; 3: len 1; hex 64; asc d;;1 row in set (0.00 sec)

/Usr/bin/pt-heartbeat
Used to Monitor AB replication delay information. You need to create the following table for information storage.

             CREATE TABLE heartbeat (               ts                    varchar(26) NOT NULL,               server_id             int unsigned NOT NULL PRIMARY KEY,               file                  varchar(255) DEFAULT NULL,    -- SHOW MASTER STATUS               position              bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS               relay_master_log_file varchar(255) DEFAULT NULL,    -- SHOW SLAVE STATUS               exec_master_log_pos   bigint unsigned DEFAULT NULL  -- SHOW SLAVE STATUS             );

/Usr/bin/pt-index-usage
Whether the index is used during log analysis and query

/Usr/bin/pt-ioprofile
Analyze and print the latest active IO and related process information (only for mysql processes)

[Root @ mini189a ~] # Pt-ioprofile Friday December 20, 2013 11:27:52 CSTTracing process ID 44802 total read pwrite write fsync open close getdents lseek ftruncate filename 0.190553 0.000000 0.000903 0.000000 0.189650 0.000000 0.000000 0.000000 0.000000/data/ibdata1 0.000000 0.107560 0.000000 0.000297 0.000000 0.000000 0.000000 0.000000 0.000000/data/ib_logfile0 0.027070 0.000000 0.000000 0.027070 0.000000 0.000000 0.000000 0.000000 0.000000/var/log/mysqld/mysql5-access.log 0.000000 0.015691 0.000000 0.000000 0.000000 0.000000 0.000689 0.000480 0.014522 0.000000/data/0.000000 mini/0.000362 0.000058 0.000000 0.000159 0.000000 0.000000 0.000000 0.000000 0.000081 0.000064/tmp/ib4I3wV6 0.000192 0.000000 0.000000 0.000192 0.000000 0.000000 0.000000 0.000000 0.000000/data/mysql5.20.97 0.000000 0.000030 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000030 0.000000 /tmp/ibiybk6Q

/Usr/bin/pt-kill
Used to kill specified SQL queries

/Usr/bin/pt-mext
Used to record changes in status return values within a fixed interval (the following example calculates changes within 10 seconds)

pt-mext -r -- mysqladmin -p'password' --socket=/var/run/mysqld/mysql5.socket  ext -i10 -c2  | awk '{ if ($3 !~0) print $0}'Com_select                             19446417         198Com_show_status                              11           1Created_tmp_tables                      1015031           1Handler_commit                         20829923         198Handler_read_first                     21935275         198Handler_read_key                       83253660         198Handler_read_rnd_next               25490412028      257632Handler_write                          63925147         294Innodb_buffer_pool_read_requests    25739644905      259716Innodb_rows_read                    25421266296      257116Qcache_not_cached                      18701233         198Queries                                28860341         199Questions                              22720514         199Select_scan                            19915354         199Table_locks_immediate                  21238032         19

/Usr/bin/pt-mysql-summary
Conduct some data statistics for the current database, with different statistics discussed.
Note: To execute this command, you need to call mysqldump. You must have the mysqldump Command Execution path.

pt-mysql-summary --socket=/var/run/mysqld/mysql5.socket  --password='password'  Database            Tables Views SPs Trigs Funcs   FKs Partn  189mini                266    10  crazy                   14  login                    1  monitor                  1  mysql                   24  #mysql50#lost+found  openne                  18  test  xszl                    29                          60  Database            InnoDB MyISAM CSV  189mini                266     10  crazy                   14  login                    1  monitor                  1  mysql                          22   2  #mysql50#lost+found  openne                  18  test  xszl                    29  Database            BTREE  189mini               280  crazy                  14  login                   1  monitor                 1  mysql                  31  #mysql50#lost+found  openne                 25  test  xszl                  149

/Usr/bin/pt-online-schema-change
You can modify a table without locking the table.

/Usr/bin/pt-query-digest
SQL analysis from logs

/Usr/bin/pt-show-grants
You must log on as an administrator to print the previous database authorization information.

pt-show-grants -p'mini189!QAZ'  --socket=/var/run/mysqld/mysql5.socket-- Grants dumped by pt-show-grants-- Dumped from server Localhost via UNIX socket, MySQL 5.5.34-debug-log at 2013-12-20 14:43:22-- Grants for 'crazy'@'10.1.1.%'GRANT USAGE ON *.* TO 'crazy'@'10.1.1.%' IDENTIFIED BY PASSWORD '*4C1B9FACE717B2947CB8D52B32C3CFE8DA8DD8CB';GRANT ALL PRIVILEGES ON `crazy`.* TO 'crazy'@'10.1.1.%';-- Grants for 'mini145'@'%'GRANT ALL PRIVILEGES ON *.* TO 'mini145'@'%' IDENTIFIED BY PASSWORD '*AA2442B2AE9D3C29F895E57F366092819A3F6738';-- Grants for 'mini145'@'10.1.1.%'GRANT ALL PRIVILEGES ON *.* TO 'mini145'@'10.1.1.%' IDENTIFIED BY PASSWORD '*AA2442B2AE9D3C29F895E57F366092819A3F6738';-- Grants for 'minibackup'@'10.1.1.%'GRANT REPLICATION SLAVE, SUPER ON *.* TO 'minibackup'@'10.1.1.%' IDENTIFIED BY PASSWORD '*D5A75D5F1208A6763F64193744F94E57373C128E';

/Usr/bin/pt-slave-delay
Delayed replication of slave servers

/Usr/bin/pt-slave-find
Verify mysql slave server information

/Usr/bin/pt-slave-restart
Monitor and restart slave server

/Usr/bin/pt-summary
Count computer information, such as memory, CPU, Nic, partition, file node, IP address, network connection status, port connection status, and network connection status

/usr/bin/pt-summary# Percona Toolkit System Summary Report ######################        Date | 2013-12-20 07:03:47 UTC (local TZ: CST +0800)    Hostname | mini189a      Uptime | 42 days, 15:11,  2 users,  load average: 0.49, 0.47, 0.39      System | HP; ProLiant DL380p Gen8; vNot Specified (Rack Mount Chassis) Service Tag | 6CU304WPZN    Platform | Linux     Release | Red Hat Enterprise Linux Server release 6.3 (Santiago)      Kernel | 2.6.32-358.23.2.el6.centos.plus.x86_64Architecture | CPU = 64-bit, OS = 64-bit   Threading | NPTL 2.12    Compiler | GNU CC version 4.4.7 20120313 (Red Hat 4.4.7-4).     SELinux | Disabled Virtualized | No virtualization detected# Processor ##################################################  Processors | physical = 2, cores = 12, virtual = 24, hyperthreading = yes      Speeds | 24x1994.866      Models | 24xIntel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz      Caches | 24x15360 KB

/Usr/bin/pt-table-checksum
MySQL master-slave replication Verification

/Usr/bin/pt-table-sync
Synchronize MySQL databases or table data on two hosts
Note: The target server must have the same database and table as the source server. Otherwise, non-tables that do not exist cannot be synchronized.
Pt-table-sync -- execute h = 192.168.200.163 -- database terry h = 192.168.200.171 -- user = root -- password = 123

/Usr/bin/pt-variable-advisor
Suggestions on database parameter Definitions

pt-variable-advisor h=localhost -p'password' --socket=/var/run/mysqld/mysql5.socket# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.# WARN innodb_log_file_size: The InnoDB log file size is set to its default value, which is not usable on production systems.# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections.# NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows.# WARN slave_net_timeout: This variable is set too high.

/Usr/bin/pt-visual-explain
Explain the SQL statement and display it in the tree directory structure