Usage of mysqltoolkit [memo] bitsCN.com
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
BitsCN.com