mysql 命令备忘

╰+哭是因爲堅強的太久メ 2022-01-22 02:21 241阅读 0赞

### 目录 ###

*   *   *  1.开启Innodb monitor
         *  2.查看Innodb执行状态（包含最近的死锁日志）
         *  3.查看锁、事务、及其等待状态情况
         *  4.查看当前运行的线程信息
         *  5.查看/设置事务隔离级别
         *   *  5.1 查看事务隔离级别
             *  5.2 设置事务隔离级别
         *  6.查看auto\_increment机制模式
         *  7.查看表锁

### 1.开启Innodb monitor ###

附录：

*  [开启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 存储引擎][mysql]

SHOW ENGINE innodb STATUS ;

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

附录：

*  [The INFORMATION\_SCHEMA INNODB\_LOCKS Table][The INFORMATION_SCHEMA INNODB_LOCKS Table]
 *  [The INFORMATION\_SCHEMA INNODB\_LOCK\_WAITS Table][The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table]
 *  [The INFORMATION\_SCHEMA INNODB\_TRX 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.版本彻底移除。

下面是一个例子：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0X2JldHRlcg_size_16_color_FFFFFF_t_70]

### 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 _]
 *  [mysql 事务隔离级别的设置][mysql 1]

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

### 6.查看auto\_increment机制模式 ###

附录：[mysql 自增模式][mysql 2]

show variables like '%innodb_autoinc_lock_mode%'

### 7.查看表锁 ###

show OPEN TABLES where In_use > 0;

[Innodb monitor]: https://dev.mysql.com/doc/refman/5.7/en/innodb-enabling-monitors.html
[SHOW ENGINE Syntax]: https://dev.mysql.com/doc/refman/5.7/en/show-engine.html
[mysql]: https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html
[The INFORMATION_SCHEMA INNODB_LOCKS Table]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html
[The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table]: https://dev.mysql.com/doc/refman/5.7/en/innodb-lock-waits-table.html
[The INFORMATION_SCHEMA INNODB_TRX Table]: https://dev.mysql.com/doc/refman/5.7/en/innodb-trx-table.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0X2JldHRlcg_size_16_color_FFFFFF_t_70]: /images/20220122/ad07c11206b74e129e7f6eb90369e735.png
[SHOW PROCESSLIST Syntax]: https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html
[mysql _]: https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
[mysql 1]: https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
[mysql 2]: https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html