mysql-排查故障常用命令

整理的常用mysql排查命令

mysql 8.0 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
## 当前运行的所有事务
mysql> select * from information_schema.innodb_trx\G;
## 查看锁使用情况
mysql> show status like 'innodb_row_lock_%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1      |
| Innodb_row_lock_time          | 479764 |
| Innodb_row_lock_time_avg      | 39980  |
| Innodb_row_lock_time_max      | 51021  |
| Innodb_row_lock_waits         | 12     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

解释如下：
Innodb_row_lock_current_waits : 当前等待锁的数量
Innodb_row_lock_time : 系统启动到现在，锁定的总时间长度
Innodb_row_lock_time_avg : 每次平均锁定的时间
Innodb_row_lock_time_max : 最长一次锁定时间
Innodb_row_lock_waits : 系统启动到现在总共锁定的次数 

# 查询是否锁表
mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | tx1   |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)