Mysql系列：Mysql运维常用命令

type

status

date

slug

summary

1、基本运行情况

查看正在执行的 SQL 线程信息

查看 InnoDB 存储引擎信息

用于显示 InnoDB 存储引擎的状态信息，包括信号量（CPU自旋、OSWAITARRAYINFO）、死锁情况、外键冲突、事务状态、I/O操作和缓冲池等，这些都是数据库性能诊断的关键指标。通过对这些信息的理解，可以帮助优化数据库性能，解决死锁和资源争用问题。

查看数据库表的详细信息

字段说明：

字段名	描述
Name	表名
Engine	表的存储引擎 (如 InnoDB, MyISAM)
Version	.frm 文件版本号
Row_format	行存储格式 (Compact, Dynamic, Fixed 等)
Rows	表中的行数（对于某些存储引擎如 InnoDB 是估计值）
Avg_row_length	平均每行长度（字节）
Data_length	数据总长度（字节）
Max_data_length	表的最大容量（字节）
Index_length	索引总长度（字节）
Data_free	已分配但未使用的空间（字节），可以简单理解为碎片空间，定期执行 `OPTIMIZE TABLE` 可以回收这部分空间
Auto_increment	下一个 AUTO_INCREMENT 值
Create_time	表创建时间
Update_time	表最后更新时间
Check_time	表最后检查时间
Collation	表的默认字符集和排序规则
Checksum	表的校验和（如果启用）
Create_options	创建表时指定的其他选项
Comment	表的注释信息

使用示例：

查看内存配置参数

这些变量分别表示InnoDB缓冲池大小、键缓冲区大小和查询缓存大小。如果这些值设置得过高，会导致MySQL占用大量内存。

innodb_buffer_pool_size ：这个参数控制了InnoDB存储引擎使用的内存量。根据系统可用内存和数据库的读写负载情况，适当调整这个值可以减少内存占用。建议将其设置为系统总内存的50%-80%。

key_buffer_size：这个参数控制了键缓冲区的大小，主要用于MyISAM存储引擎。如果您的数据库主要使用InnoDB存储引擎，可以将这个值设置为较小的值或完全关闭（设置为0）。

query_cache_size ：查询缓存用于存储SELECT语句和对应的结果。如果您的数据库经常更新数据，查询缓存可能会占用大量内存。在这种情况下，可以关闭查询缓存以减少内存占用。通过将query_cache_size设置为0来关闭查询缓存。

查看字符集

查看数据库/表大小

查看表最后更新时间

2、事务和锁

确认表是否在被使用

查看当前运行的所有事务

INNODB_TRX表中主要记录了当前正在执行的事务信息，包括只读事务。首先来看字段和字段所表示的含义:

字段名称	字段含义
TRX_ID	InnoDB存储引擎内部的唯一事务ID
TRX_WEIGHT	事务权重(与事务修改的行数和锁定的行数有关)，当两个事务执行发生死锁时，InnoDB会选择权重较低的事务进行回滚
TRX_STATE	当前的事务执行状态，包括RUNNING, LOCK WAIT, ROLLING BACK, 以及COMMITTING，LOCK WAIT表示当前事务正等待某个锁的释放
TRX_STARTED	事务开始时间，格式如2000-01-01 14:01:08
TRX_REQUESTED_LOCK_ID	当前事务所等待的锁ID，该字段只有在状态为LOCK WAIT才有值，否则为NULL。可与`INNODB_LOCKS`通过LOCK_ID字段进行关联查询，获取更为详细的锁信息。
TRX_WAIT_STARTED	当前事务等待锁的起始时间，在状态为LOCK WAIT时才有值，否则为NULL。
TRX_QUERY	当前事务正在执行的SQL语句(不是事务所有的执行语句)
TRX_OPERATION_STATE	事务的当前操作状态，包括PREPARING, UPDATING, DELETING, COMMITTING以及NULL，该字段在绝大部分情况下均为NULL，捕捉某一事务的瞬间执行状态还是比较困难的(除非是大事务)
TRX_TABLES_IN_USE	正在执行的SQL语句所操作的表数量，是一个动态变化值，通常很难观测
TRX_TABLES_LOCKED	当前事务在各个表中添加行锁的表数量
TRX_LOCK_STRUCTS	当前事务持有的锁数量
TRX_LOCK_MEMORY_BYTES	当前事务中锁结构的内存总占用
TRX_ROWS_LOCKED	当前事务锁住的近似数据总行数
TRX_ROWS_MODIFIED	当前事务插入、修改的总行数
TRX_CONCURRENCY_TICKETS	表示当前事务在换出之前所能做的工作之和
TRX_ISOLATION_LEVEL	当前事务隔离级别，包括READ UNCIMMITTED、READ COMMITTED、READ REPEATABLE以及SERIALIZABLE
TRX_UNIQUE_CHECKS	当前事务是否开启唯一性检查
TRX_FOREIGN_KEY_CHECKS	当前事务是否开启外键检查
TRX_LAST_FOREIGN_KEY_ERROR	当前事务执行时最后发生的外键错误
TRX_ADAPTIVE_HASH_LATCHED	当前事务是否锁定了自适应哈希索引

在这20多个字段中，较为重要的包括事务ID，事务执行状态，事务等待锁的起始时间，事务锁定的近似总行数。

查看当前出现的锁

在 MySQL 5.5 以上、5.7.14以下的版本中，用户可以通过 information_schema 下的 INNODB_TRX、INNODB_LOCKS 以及INNODB_LOCK_WAITS这三张表简单地监控并分析可能存在的锁问题。

在 MySQL 8.0 版本中，则需要使用performance_schema下的data_locks以及data_lock_waits获取相关的锁以及锁等待信息。

MySQL版本在 5.7.14 到 8.0 之间的用户，只能通过其它手段间接的获取上述信息。

以 MySQL 8.0 版本的 performance_schema.data_locks 为例

字段名称	字段含义
ENGINE	申请或持有锁的存储引擎类型
ENGINE_LOCK_ID	存储引擎内部的锁ID，该值会发生动态变化，外部系统不应该依赖该值
ENGINE_TRANSACTION_ID	持有锁的事务ID，与INNODB_TRX中的TRX_ID对应
THREAD_ID	持有锁的线程ID
EVENT_ID	事件ID，该字段将于下方进行详细描述
OBJECT_SCHEMA	锁所在的schema(database)
OBJECT_NAME	锁所在的表名称
PARTITION_NAME	锁所在分片名称
SUBPARTITION_NAME	锁所在的子分片名称
INDEX_NAME	被添加锁的索引名称
OBJECT_INSTANCE_BEGIN	锁的内存空间起始地址
LOCK_TYPE	锁类型，包含TABLE和RECORD
LOCK_MODE	锁的模式，包括S,X,IS,IX,AUTO_INC以及UNKNOWN
LOCK_STATUS	锁的状态，InnoDB引擎中包括GRANTED(已添加)和WAITING(等待中)
LOCK_DATA	锁覆盖的范围，该字段将于下方详细描述

查看由于锁占用导致等待的表

data_lock_waits相比于INNODB_TRX以及data_locks而言则要更复杂一些，该表实际上是一个ManyToMany的关系表，记录了data_locks中锁之间的等待以及依赖关系，同时也记录了锁所对应的事务/会话信息。

字段名称	字段含义
ENGINE	存储引擎类型
REQUESTING_ENGINE_LOCK_ID	存储引擎内锁ID，对应于data_locks表中的ENGINE_LOCK_ID
REQUESTING_ENGINE_TRANSACTION_ID	存储引擎内事务ID
REQUESTING_THREAD_ID	线程ID
REQUESTING_EVENT_ID	事件ID
REQUESTING_OBJECT_INSTANCE_BEGIN	锁的内存空间起始地址
BLOCKING_ENGINE_LOCK_ID	等待释放的锁ID
BLOCKING_ENGINE_TRANSACTION_ID	等待结束的事务ID
BLOCKING_THREAD_ID	等待结束的线程ID
BLOCKING_EVENT_ID	等待结束的事件ID
BLOCKING_OBJECT_INSTANCE_BEGIN	等待结束的锁的内存空间起始地址

查看事务等待锁的超时时间

当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

开启主动死锁检测

主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

3、Mysql日志

查看慢日志参数

slow_query_log：慢日志开关，默认为OFF关闭

slow_query_log_file：慢日志保存文件；

long_query_time：慢日志判断阀值，执行时长超过此值的sql都将记录入慢日志文件；

slow_launch_time：此值不是慢日志的判断阀值，注意区分，好像是废弃的；

启用慢日志

方法一：全局变量设置，只在当前会话生效。

方法二：配置文件设置，然后重启Mysql服务

修改配置文件 my.cnf，在 [mysqld] 的下方加入

查看慢日志记录

执行超过时长阀值（默认10s）的sql语句

此时查看 slow_query_log_file 文件，多出如下记录：

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。使用 mysqldumpslow --help 来查看用法

常见用法

4、主从复制

主服务器配置命令

查看主服务器状态：

这会显示二进制日志文件名和位置，从服务器需要这些信息。

创建复制用户：

从服务器配置命令

传统复制-配置从服务器连接主服务器：

基于 GTID 的复制-配置从服务器连接主服务器：

启动/停止复制：

重置从服务器：

查看从服务器状态：

关键字段：

Slave_IO_Running

Slave_SQL_Running

Last_IO_Error

Last_SQL_Error

Seconds_Behind_Master

Binlog相关命令

1、开启 binlog

有两种方式，第一种是修改配置文件 my.cnf ，修改后需要重启数据库。

如果不想重启数据库，可以使用第二种方式，执行以下命令

2、查看binlog的状态和文件位置

3、查看binlog的日志格式

4、查看/修改binlog的过期时间

5、查看binlog文件列表

5、查看binlog的内容

另一种方式是通过 mysqlbinlog 工具。

6、查看当前正在写入的binlog文件

7、刷新binlog（创建新文件）

下面情况也会重新生成一个新的 binlog 文件：

MySQL服务器停止或重启时。

当 binlog 文件大小超过 max_binlog_size 系统变量配置的上限时。

8、手动清理binlog

9、重置binlog（慎用）

其他有用命令

设置只读模式（从服务器）：

修改复制过滤规则：

半同步复制相关：

GTID复制相关（MySQL 5.6+）：