mysql查看死锁的命令有哪些？

配置 MySQL Exporter 采集死锁相关的性能指标，并设置告警规则，可以按照以下步骤操作： ### 1. 配置 MySQL Exporter #### 安装 MySQL Exporter 1. 下载并安装 MySQL Exporter： ```bash wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz tar -xvzf mysqld_exporter-0.14.0.linux-amd64.tar.gz cd mysqld_exporter-0.14.0.linux-amd64 ``` 2. 配置 MySQL 用户： 在 MySQL 中创建一个用户用于 Exporter 的数据采集： ```sql CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password'; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost'; FLUSH PRIVILEGES; ``` 3. 启动 Exporter： ```bash ./mysqld_exporter --config.my-cnf="/path/to/.my.cnf" ``` `.my.cnf` 文件内容： ```ini [client] user=exporter password=password ``` #### 验证 Exporter 是否正常运行 访问 `http://<exporter_host>:9104/metrics`，确保可以看到 MySQL 的相关指标。 ### 2. 配置 Prometheus 采集指标 编辑 Prometheus 配置文件 `prometheus.yml`，添加 MySQL Exporter 的 job： ```yaml scrape_configs: - job_name: 'mysql' static_configs: - targets: ['<mysql_exporter_host>:9104'] ``` 重启 Prometheus： ```bash systemctl restart prometheus ``` 验证 Prometheus 中是否已经采集 MySQL 的指标，例如： ```bash mysql_global_status_locks_immediate mysql_global_status_locks_waited ``` ### 3. 死锁相关的指标和 SQL 打印 #### 添加自定义死锁查询 MySQL Exporter 支持通过查询自定义指标。例如： 1. 配置 `collector.locks` 模块，在启动参数中添加： ```bash --collect.info_schema.innodb_locks --collect.info_schema.innodb_trx --collect.info_schema.innodb_lock_waits ``` 2. 自定义指标示例（修改 MySQL 配置，记录死锁的 SQL）： 在 MySQL 中启用 `performance_schema` 并开启死锁日志： ```sql SET GLOBAL performance_schema = ON; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history'; ``` 3. 在 Exporter 中查询最近死锁的 SQL（示例配置）： 添加查询： ```sql SELECT * FROM performance_schema.events_statements_history WHERE SQL_TEXT LIKE '%deadlock%'; ``` 定制 Exporter 查询可以通过 fork 或修改 Exporter 实现。 ### 4. 配置 Grafana Dashboard 1. 在 Grafana 中添加 Prometheus 数据源。 2. 创建一个新的 Dashboard： - 添加 Panel： - 数据源：Prometheus - 查询： ```promql mysql_global_status_locks_waited ``` - 展示死锁等待的指标。 3. 显示死锁相关的 SQL： - 通过自定义的查询结果，将 SQL 的具体内容通过日志展示。 ### 5. 配置告警规则 #### 配置 Prometheus 告警规则 在 `prometheus.yml` 中添加告警规则： ```yaml alerting: alertmanagers: - static_configs: - targets: - '<alertmanager_host>:9093' rule_files: - "alert.rules" ``` 创建 `alert.rules` 文件： ```yaml groups: - name: mysql_alerts rules: - alert: DeadlockDetected expr: mysql_global_status_locks_waited > 0 for: 1m labels: severity: critical annotations: summary: "MySQL Deadlock Detected" description: "A deadlock has been detected in MySQL. Check the related logs for details." ``` 重启 Prometheus： ```bash systemctl restart prometheus ``` #### 配置 AlertManager 通知 在 AlertManager 中设置通知，例如邮件、钉钉或企业微信通知。 完成后，Grafana 上可以直观监控死锁相关的指标，Prometheus 检测到死锁时会触发告警，并通过 AlertManager 发送通知。

在 MySQL 中，**查看死锁**的最常用方式是通过 `SHOW ENGINE INNODB STATUS` 命令，该命令可以显示最近一次的死锁详细信息。此外，还可以借助 `performance_schema` 和其他方法来分析死锁情况。 ### **1. 使用 `SHOW ENGINE INNODB STATUS`** 这是 MySQL 查看死锁信息的核心命令，能够显示最近一次发生的死锁详情。 #### **命令** ```sql SHOW ENGINE INNODB STATUS\G ``` #### **输出示例** ```plaintext ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-11-16 12:34:56 0x7f8babc123 *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 678, OS thread handle 140234, query id 98765 localhost user updating UPDATE orders SET status = 'completed' WHERE id = 100 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 5 page no 123 n bits 72 index `PRIMARY` of table `test`.`orders` trx id 12345 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 *** (2) TRANSACTION: TRANSACTION 67890, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 1 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 679, OS thread handle 140235, query id 98766 localhost user inserting INSERT INTO orders (id, status) VALUES (100, 'pending') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 5 page no 123 n bits 72 index `PRIMARY` of table `test`.`orders` trx id 67890 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ``` #### **输出解析** - **TRANSACTION (1)** 和 **TRANSACTION (2)** 表示两个相互竞争资源的事务。 - **WAITING FOR THIS LOCK TO BE GRANTED**：事务正在等待的锁。 - **HOLDS THE LOCK(S)**：事务已经持有的锁。 - 具体 SQL 语句和表名、索引名、锁模式都会在这里显示。 #### **优点** - 能够快速了解最近一次死锁的详细信息。 - 输出包括事务详情、等待的锁、持有的锁和相关的 SQL。 ### **2. 使用 `performance_schema`** MySQL 的 `performance_schema` 提供了更精细的死锁监控信息，尤其适合需要持续分析的场景。 #### **查询命令** 1. **查看死锁详细信息** ```sql SELECT * FROM performance_schema.events_transactions_current WHERE EVENT_NAME LIKE 'wait/lock/%'; ``` 2. **查询死锁事件历史** ```sql SELECT * FROM performance_schema.events_transactions_history_long WHERE EVENT_NAME LIKE 'wait/lock/%'; ``` #### **示例输出** | THREAD_ID | EVENT_NAME | TIMER_START | TIMER_END | SQL_TEXT | |-----------|--------------------|-------------|-----------|-----------------------------------| | 12345 | wait/lock/record | 16094592000 | 16094602000 | UPDATE orders SET status = 'completed' WHERE id = 100 | #### **优点** - 能够持续查看历史死锁事件。 - 提供线程级别的死锁详情，便于结合其他性能事件一起分析。 ### **3. 使用 `INFORMATION_SCHEMA.INNODB_TRX`** `INNODB_TRX` 表记录了当前活跃事务的信息，可用于分析死锁的潜在原因。 #### **查询活跃事务** ```sql SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; ``` #### **示例输出** | TRX_ID | TRX_STATE | TRX_STARTED | TRX_QUERY | |---------|------------|---------------------|-------------------------------------| | 12345 | LOCK WAIT | 2024-11-16 12:30:00 | UPDATE orders SET status = 'completed' WHERE id = 100 | | 67890 | LOCK WAIT | 2024-11-16 12:30:03 | INSERT INTO orders (id, status) VALUES (100, 'pending') | #### **优点** - 可直接查看当前等待锁的事务及其运行时间。 - 适合实时监控。 ### **4. 配置 `innodb_print_all_deadlocks`** MySQL 默认只记录最近一次死锁的详细信息到 `SHOW ENGINE INNODB STATUS`，启用 `innodb_print_all_deadlocks` 后，可以将所有死锁信息记录到错误日志中。 #### **开启配置** ```sql SET GLOBAL innodb_print_all_deadlocks = ON; ``` #### **查看错误日志** ```bash tail -f /var/log/mysql/error.log ``` #### **优点** - 能够记录所有死锁，不再局限于最近一次。 - 适合长时间运行的系统，便于后续分析。 ### **5. 配合第三方工具监控** #### **PMM (Percona Monitoring and Management)** - **功能**：实时捕获死锁事件，图形化展示死锁信息。 - **特点**： - 提供详细的事务锁等待图。 - 支持历史死锁事件的查询和分析。 #### **Grafana + Prometheus** - 配置 MySQL Exporter，采集死锁相关的性能指标。 - 可设置告警规则，例如检测到死锁时触发通知。 ### **6. 常用脚本排查死锁** #### **查找锁等待情况** ```sql SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id; ``` ### **总结** | **方法** | **特点** | **适用场景** | |-------------------------------------|----------------------------------------------------------------------------------------|---------------------------------------| | **SHOW ENGINE INNODB STATUS** | 查看最近一次死锁的详细信息，包括事务、锁类型、SQL。 | 快速定位死锁问题。 | | **performance_schema** | 提供历史事件、锁等待分析和线程级别的死锁详情。 | 复杂场景下需要详细分析死锁历史。 | | **INFORMATION_SCHEMA.INNODB_TRX** | 查看当前活跃事务和等待状态，可实时排查潜在死锁。 | 需要实时监控当前事务和锁的状态。 | | **innodb_print_all_deadlocks** | 将所有死锁记录到日志文件，便于长期分析。 | 系统长期运行的死锁记录需求。 | | **PMM 或 Grafana + Prometheus** | 图形化监控，实时捕获和展示死锁事件，支持历史分析。 | 需要可视化展示和大规模集群监控。 | 综合来看，`SHOW ENGINE INNODB STATUS` 是最简单直接的方法，而 `performance_schema` 和第三方工具适合复杂场景下的长期监控和历史分析。