pt-deadlock-logger

官方：https://www.percona.com/doc/percona-toolkit/3.0/pt-deadlock-logger.html

主要参数

• --create-dest-table ：创建指定的表。
• --dest ：创建存储死锁信息的表。
• --database ：-D，指定链接的数据库。
• --table ：-t，指定存储的表名。
• --log ：指定死锁日志信息写入到文件。
• --run-time ：运行次数，默认永久
• --interval ：运行间隔时间，默认30s。

模拟死锁

打印死锁

pt-deadlock-logger --create-dest-table --dest D=percona_test,t=deadlocks u=yq,P=3306,h=192.168.66.101 --ask-pass --charset=utf8
Enter MySQL password: 
Enter MySQL password: 
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.66.101 2020-12-30T19:05:13 3 0 12 root localhost  abc tb1 PRIMARY RECORD X w 0 update tb1 set a = '隔壁老苗' where id =3
192.168.66.101 2020-12-30T19:05:13 4 0 9 root localhost  abc tb1 PRIMARY RECORD X w 1 update tb1 set a = '隔壁老响' where id =2

指定表中也记录着死锁信息

mysql> select * from percona_test.deadlocks;
+----------------+---------------------+--------+--------+----------+------+-----------+----+-----+-----+---------+-----------+-----------+-----------+--------+-----------------------------------------------+
| server         | ts                  | thread | txn_id | txn_time | user | hostname  | ip | db  | tbl | idx     | lock_type | lock_mode | wait_hold | victim | query                                         |
+----------------+---------------------+--------+--------+----------+------+-----------+----+-----+-----+---------+-----------+-----------+-----------+--------+-----------------------------------------------+
| 192.168.66.101 | 2020-12-30 19:05:13 |      3 |      0 |       12 | root | localhost |    | abc | tb1 | PRIMARY | RECORD    | X         | w         |      0 | update tb1 set a = '隔壁老苗' where id =3     |
| 192.168.66.101 | 2020-12-30 19:05:13 |      4 |      0 |        9 | root | localhost |    | abc | tb1 | PRIMARY | RECORD    | X         | w         |      1 | update tb1 set a = '隔壁老响' where id =2     |
+----------------+---------------------+--------+--------+----------+------+-----------+----+-----+-----+---------+-----------+-----------+-----------+--------+-----------------------------------------------+
2 rows in set (0.00 sec)

打印上一次死锁后退出

pt-deadlock-logger h=host1 --iterations 1

将死锁保存到另一个库中去

pt-deadlock-logger h=host1 --dest h=host2,D=percona_schema,t=deadlocks