mysql 命令备忘

1.开启Innodb monitor

附录：

• 开启Innodb monitor

SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

SET GLOBAL innodb_status_output=OFF;
SET GLOBAL innodb_status_output_locks=OFF;

2.查看Innodb执行状态（包含最近的死锁日志）

附录：

• SHOW ENGINE Syntax
• mysql 存储引擎

SHOW ENGINE innodb STATUS ;

3.查看锁、事务、及其等待状态情况

附录：

• The INFORMATION_SCHEMA INNODB_LOCKS Table
• The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
• The INFORMATION_SCHEMA INNODB_TRX Table

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

需要注意的是：INNODB_LOCKS和INNODB_LOCK_WAITS表，在 MySQL 5.7.14版本废弃，并在MySQL 8.0.版本彻底移除。

下面是一个例子：

8.0版本可以参考如下几张表：
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-lock-tables.html

4.查看当前运行的线程信息

附录：SHOW PROCESSLIST Syntax

SHOW PROCESSLIST;

5.查看/设置事务隔离级别

5.1 查看事务隔离级别

SELECT @@transaction_isolation;
SELECT @@tx_isolation;

transaction_isolation was added in MySQL 5.7.20 as an alias for tx_isolation, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation in preference to tx_isolation.

所以建议使用SELECT @@transaction_isolation;进行查询。

5.2 设置事务隔离级别

附录：

• mysql 事务隔离级别
• mysql 事务隔离级别的设置

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

6.查看auto_increment机制模式

附录：mysql 自增模式

show variables like '%innodb_autoinc_lock_mode%'

7.查看表锁

show OPEN TABLES where In_use > 0;