mysqltoolkit用法[备忘]_MySQL
bitsCN.com
mysql toolkit 是一个小插件，用于对数据库进行监控，分析的小工具， 通常都是调用 perl 对当前的环境，数据库进行分析，比对，值得学习一下。
下载地址
http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.5-2.noarch.rpm
安装时可能需要补充下面软件
yum install -y perl-DBD-mysql perl-IO-Socket-SSLrpm -ivh percona-toolkit-2.2.5-2.noarch.rpm
命令列表
/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
语法及作用 (颜色红色表示需关注，蓝色表示作用不大)
/usr/bin/pt-align
用于对文件进行格式化输出
如文件 t 内容如下
[root@db2 aaa]# cat ta abc aaa 123d2 5 d2wf wwwxcca233 ddcqq 55 23ds2 55aaa 5tty655 ccqqq
格式化后输出如下
[root@db2 aaa]# /usr/bin/pt-align ta abc aaa 123d2 5 d2wf wwwxcca233 ddcqq 55 23ds2 55aaa 5tty655 ccqqq
/usr/bin/pt-archiver
把一个库中的表复制到另外一个服务器中
下面例子把 192.168.200.163 中 dbd.t1 复制至 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"
注，必须在 source 中为表创建索引，否则报下面错误信息
Cannot find an ascendable index in table at /usr/bin/pt-archiver line 3175.
/usr/bin/pt-config-diff
用于显示两台数据库中配置文件不一样的地方
[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
能够长期记录死锁信息到另外一个表中, 创建表格式如下
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
语法
pt-deadlock-logger h=10.1.1.29 --dest h=10.1.1.29,D=monitor,t=deadlocks --user=terry --ask-pass
注: 当前需安装 perl-Term-ReadKey 应用才能够支持密码输入
用户 terry 除授权对 daedlocks 表具备写操作还需要具备 process 权限
参考发生死锁时候的信息:
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
调用 /proc/diskstats 文件用于监控磁盘io
直接执行命令则返回下面信息
/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
能够快速校验某个数据库或表具有多少个索引
[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
类似 linux 下 find 功能，能够根据需要对数据库中表进行过滤，搜索
查询表大小
[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`
最近 3 天内创建的表
[root@mini189a ~]# pt-find --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --ctime -3`monitor`.`deadlocks``xszl`.`appreleasetable``xszl`.`bulletintable``xszl`.`companytable`
最近 35 分钟内修改过的表
[root@mini189a ~]# pt-find --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --mmin -35`mysql`.`db``mysql`.`user`
数据库中空的表
[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
用于检测外键报错信息, 需创建下面相关表进行数据存储
CREATE TABLE foreign_key_errors ( ts datetime NOT NULL, error text NOT NULL, PRIMARY KEY (ts))
语法测试
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
注: 用户 terry 除授权对 foreign_key_errors 表具备写操作还需要具备 process 权限
参考下面返回信息
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
用于监控 ab 复制延时信息，需创建下面表用于信息存储
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
根据日志分析查询过程中是否使用到索引
/usr/bin/pt-ioprofile
分析并打印最近活跃的 IO 与相关进程信息 （只针对 mysql 进程）
[root@mini189a ~]# pt-ioprofile2013年 12月 20日 星期五 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 0.000000 /data/ibdata1 0.107560 0.000000 0.000297 0.000000 0.107263 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 0.000000 /var/log/mysqld/mysql5-access.log 0.015691 0.000000 0.000000 0.000000 0.000000 0.000689 0.000480 0.014522 0.000000 0.000000 /data/189mini/ 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 0.000000 /data/mysql5.000097 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
用于 kill 某些指定的 SQL 查询
/usr/bin/pt-mext
用于记录固定时间间隔内 status 返回值的变化 (下面例子将会计算 10 秒内的变化)
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
给当前数据库进行一些数据统计，不一一对统计进行讨论
注: 执行该命令时，需要调用 mysqldump, 需具备 mysqldump 命令执行路径
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
允许在不锁定表条件下修改表
/usr/bin/pt-query-digest
从日志中进行 SQL 分析
/usr/bin/pt-show-grants
必须要以管理员身份登录，能够把数据库之前授权信息进行打印
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
令从服务器延时复制
/usr/bin/pt-slave-find
校验 mysql 从服务器信息
/usr/bin/pt-slave-restart
监控并重启从服务器
/usr/bin/pt-summary
统计计算机信息，如内存，CPU，网卡，分区，文件节点，IP地址，网线连接状态，端口连接情况，网络连接状态
/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 主从复制校验
/usr/bin/pt-table-sync
进行两个主机中的 MySQL 数据库，或者表数据同步
注， 目标服务器中必须存在与源服务器一样的数据库及表，否则，不存在的不表无法进行同步
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
给与对数据库参数定义的建议
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
对 sql 进行 explain 分析并以树目录结构显示
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1666
14
1425
52
1327
25
1273
29
1252
24
iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos
Jul 18, 2024 am 05:48 AM
Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps
How to handle database connection errors in PHP
Jun 05, 2024 pm 02:16 PM
To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.
Detailed tutorial on establishing a database connection using MySQLi in PHP
Jun 04, 2024 pm 01:42 PM
How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())
How to use database callback functions in Golang?
Jun 03, 2024 pm 02:20 PM
Using the database callback function in Golang can achieve: executing custom code after the specified database operation is completed. Add custom behavior through separate functions without writing additional code. Callback functions are available for insert, update, delete, and query operations. You must use the sql.Exec, sql.QueryRow, or sql.Query function to use the callback function.
How to save JSON data to database in Golang?
Jun 06, 2024 am 11:24 AM
JSON data can be saved into a MySQL database by using the gjson library or the json.Unmarshal function. The gjson library provides convenience methods to parse JSON fields, and the json.Unmarshal function requires a target type pointer to unmarshal JSON data. Both methods require preparing SQL statements and performing insert operations to persist the data into the database.
How to connect to remote database using Golang?
Jun 01, 2024 pm 08:31 PM
Through the Go standard library database/sql package, you can connect to remote databases such as MySQL, PostgreSQL or SQLite: create a connection string containing database connection information. Use the sql.Open() function to open a database connection. Perform database operations such as SQL queries and insert operations. Use defer to close the database connection to release resources.
PHP connections to different databases: MySQL, PostgreSQL, Oracle and more
Jun 01, 2024 pm 03:02 PM
PHP database connection guide: MySQL: Install the MySQLi extension and create a connection (servername, username, password, dbname). PostgreSQL: Install the PgSQL extension and create a connection (host, dbname, user, password). Oracle: Install the OracleOCI8 extension and create a connection (servername, username, password). Practical case: Obtain MySQL data, PostgreSQL query, OracleOCI8 update record.
How to handle database connections and operations using C++?
Jun 01, 2024 pm 07:24 PM
Use the DataAccessObjects (DAO) library in C++ to connect and operate the database, including establishing database connections, executing SQL queries, inserting new records and updating existing records. The specific steps are: 1. Include necessary library statements; 2. Open the database file; 3. Create a Recordset object to execute SQL queries or manipulate data; 4. Traverse the results or update records according to specific needs.
