MySQL 维护命令

系统状态命令

1. 服务器状态

SHOW GLOBAL STATUS：

显示全局服务器状态变量
包含连接、查询、缓存等统计信息
用于监控服务器运行状态

示例：

sql

-- 查看连接数相关状态
SHOW GLOBAL STATUS LIKE '%Connection%';

-- 查看查询相关状态
SHOW GLOBAL STATUS LIKE '%Queries%';

-- 查看缓存相关状态
SHOW GLOBAL STATUS LIKE '%Cache%';

SHOW SESSION STATUS：

显示当前会话的状态变量
反映当前连接的活动情况
用于调试当前会话问题

示例：

sql

-- 查看当前会话的状态
SHOW SESSION STATUS LIKE '%Bytes%';

2. 系统变量

SHOW GLOBAL VARIABLES：

显示全局系统变量
包含服务器配置、缓冲区大小等
用于查看服务器配置

示例：

sql

-- 查看缓冲池配置
SHOW GLOBAL VARIABLES LIKE '%buffer_pool%';

-- 查看连接配置
SHOW GLOBAL VARIABLES LIKE '%connection%';

-- 查看日志配置
SHOW GLOBAL VARIABLES LIKE '%log%';

SHOW SESSION VARIABLES：

显示当前会话的系统变量
反映当前连接的配置
用于调试当前会话配置

示例：

sql

-- 查看当前会话的隔离级别
SHOW SESSION VARIABLES LIKE '%isolation%';

3. 存储引擎状态

SHOW ENGINE INNODB STATUS：

显示 InnoDB 存储引擎的详细状态
包含缓冲池、锁、事务等信息
用于排查 InnoDB 相关问题

示例：

sql

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS\G

SHOW ENGINE MYISAM STATUS：

显示 MyISAM 存储引擎的状态
包含键缓存、并发等信息
用于排查 MyISAM 相关问题

示例：

sql

-- 查看 MyISAM 状态
SHOW ENGINE MYISAM STATUS\G

性能优化命令

1. 表优化

OPTIMIZE TABLE：

优化表，整理碎片
重建索引
回收未使用的空间

示例：

sql

-- 优化单个表
OPTIMIZE TABLE table_name;

-- 优化多个表
OPTIMIZE TABLE table1, table2, table3;

-- 优化所有表（使用存储过程）
DELIMITER //
CREATE PROCEDURE optimize_all_tables()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tbl_name VARCHAR(255);
  DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE();
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  
  read_loop:
  LOOP
    FETCH cur INTO tbl_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('OPTIMIZE TABLE ', tbl_name);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  
  CLOSE cur;
END //
DELIMITER ;

-- 执行存储过程
CALL optimize_all_tables();

ANALYZE TABLE：

更新表的统计信息
帮助优化器选择更好的执行计划
不重建索引，执行速度快

示例：

sql

-- 分析单个表
ANALYZE TABLE table_name;

-- 分析多个表
ANALYZE TABLE table1, table2, table3;

CHECK TABLE：

检查表的完整性
检测表结构和数据错误
支持不同的检查级别

示例：

sql

-- 检查表
CHECK TABLE table_name;

-- 检查表并修复（仅 MyISAM）
CHECK TABLE table_name EXTENDED;

2. 索引管理

SHOW INDEX：

显示表的索引信息
包含索引名称、类型、列等
用于分析索引结构

示例：

sql

-- 查看表的索引
SHOW INDEX FROM table_name\G

-- 查看特定索引
SHOW INDEX FROM table_name WHERE Key_name = 'index_name';

ALTER TABLE ... ADD INDEX：

添加新索引
支持复合索引和前缀索引
可以指定索引类型

示例：

sql

-- 添加单列索引
ALTER TABLE table_name ADD INDEX idx_column (column_name);

-- 添加复合索引
ALTER TABLE table_name ADD INDEX idx_col1_col2 (column1, column2);

-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX idx_unique (column_name);

ALTER TABLE ... DROP INDEX：

删除不需要的索引
减少索引维护开销
优化表结构

示例：

sql

-- 删除索引
ALTER TABLE table_name DROP INDEX idx_column;

3. 缓存管理

RESET QUERY CACHE：

重置查询缓存
清除所有缓存的查询结果
释放缓存内存

示例：

sql

-- 重置查询缓存
RESET QUERY CACHE;

FLUSH TABLES：

关闭所有打开的表
刷新表缓存
用于表维护操作前

示例：

sql

-- 刷新所有表
FLUSH TABLES;

-- 刷新特定表
FLUSH TABLES table_name;

-- 刷新表并锁定
FLUSH TABLES WITH READ LOCK;

FLUSH STATUS：

重置状态变量
清除历史统计信息
用于重新开始统计

示例：

sql

-- 刷新状态变量
FLUSH STATUS;

故障排查命令

1. 进程管理

SHOW PROCESSLIST：

显示当前正在执行的进程
包含进程 ID、用户、SQL 语句等
用于识别长时间运行的查询

示例：

sql

-- 查看所有进程
SHOW PROCESSLIST;

-- 查看特定用户的进程
SHOW PROCESSLIST LIKE '%user%';

-- 查看长时间运行的进程
SELECT id, user, host, db, command, time, state, info 
FROM information_schema.processlist 
WHERE time > 60 
ORDER BY time DESC;

KILL：

终止指定的进程
用于终止阻塞或长时间运行的查询
需要适当的权限

示例：

sql

-- 终止进程
KILL process_id;

-- 强制终止进程
KILL QUERY process_id;

2. 锁管理

SHOW GLOBAL STATUS LIKE '%lock%'：

显示锁相关的状态变量
包含锁等待、死锁等信息
用于监控锁情况

示例：

sql

-- 查看锁状态
SHOW GLOBAL STATUS LIKE '%lock%';

-- 查看死锁信息
SHOW GLOBAL STATUS LIKE '%deadlock%';

SELECT * FROM performance_schema.data_locks：

显示当前持有的锁
包含锁类型、模式、表等信息
用于分析锁竞争

示例：

sql

-- 查看当前锁
SELECT * FROM performance_schema.data_locks;

-- 查看特定表的锁
SELECT * FROM performance_schema.data_locks WHERE object_name = 'table_name';

SELECT * FROM performance_schema.data_lock_waits：

显示当前等待的锁
包含等待线程、锁类型等信息
用于识别锁等待

示例：

sql

-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;

3. 日志管理

SHOW BINARY LOGS：

显示二进制日志文件列表
包含文件名称和大小
用于管理二进制日志

示例：

sql

-- 查看二进制日志
SHOW BINARY LOGS;

SHOW MASTER STATUS：

显示当前主服务器的二进制日志状态
包含当前日志文件和位置
用于复制配置

示例：

sql

-- 查看主服务器状态
SHOW MASTER STATUS;

SHOW SLAVE STATUS：

显示从服务器的复制状态
包含复制延迟、错误等信息
用于监控复制健康

示例：

sql

-- 查看从服务器状态
SHOW SLAVE STATUS\G

SHOW ERROR LOGS：

显示错误日志文件列表
包含文件名称和大小
用于日志管理

示例：

sql

-- 查看错误日志
SHOW ERROR LOGS;

备份恢复命令

1. 逻辑备份

mysqldump：

逻辑备份工具
支持全库、单库、单表备份
可生成 SQL 或 CSV 格式

示例：

bash

# 备份全库
mysqldump -u root -p --all-databases > all_databases.sql

# 备份单个数据库
mysqldump -u root -p database_name > database.sql

# 备份单个表
mysqldump -u root -p database_name table_name > table.sql

# 备份并压缩
mysqldump -u root -p database_name | gzip > database.sql.gz

# 备份带二进制日志位置
mysqldump -u root -p --master-data=2 database_name > database.sql

mysqlpump：

并行备份工具
比 mysqldump 速度更快
支持并行导出和导入

示例：

bash

# 并行备份
mysqlpump -u root -p --parallel-schemas=2 --databases database1 database2 > databases.sql

# 备份带压缩
mysqlpump -u root -p --compress-output=GZIP --databases database_name > database.sql.gz

2. 物理备份

xtrabackup：

Percona 提供的物理备份工具
支持热备份
适用于大数据库

示例：

bash

# 全量备份
xtrabackup --backup --target-dir=/backup/full

# 增量备份
xtrabackup --backup --target-dir=/backup/incremental --incremental-basedir=/backup/full

# 恢复备份
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

3. 数据导入导出

mysql：

用于导入 SQL 文件
执行 SQL 语句
与 mysqldump 配合使用

示例：

bash

# 导入 SQL 文件
mysql -u root -p database_name < database.sql

# 执行 SQL 语句
mysql -u root -p -e "SELECT * FROM database_name.table_name"

mysqlimport：

用于导入 CSV、TSV 等格式数据
比 LOAD DATA INFILE 更方便
支持并行导入

示例：

bash

# 导入 CSV 文件
mysqlimport -u root -p --fields-terminated-by=, database_name table_name.csv

# 导入带字段名的 CSV
mysqlimport -u root -p --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=\n --ignore-lines=1 database_name table_name.csv

SELECT ... INTO OUTFILE：

将查询结果导出到文件
支持不同的文件格式
适用于数据导出

示例：

sql

-- 导出数据到文件
SELECT * FROM table_name INTO OUTFILE '/tmp/table_data.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导出查询结果
SELECT column1, column2 FROM table_name WHERE condition INTO OUTFILE '/tmp/result.csv';

LOAD DATA INFILE：

从文件导入数据到表
支持不同的文件格式
比 INSERT 语句更快

示例：

sql

-- 从文件导入数据
LOAD DATA INFILE '/tmp/table_data.csv' INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 跳过表头导入
LOAD DATA INFILE '/tmp/table_data.csv' INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

复制管理命令

1. 复制配置

CHANGE MASTER TO：

配置从服务器的主服务器信息
包含主机、端口、用户、密码等
用于设置或修改复制配置

示例：

sql

-- 配置从服务器
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_PORT=3306,
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;

START SLAVE：

启动复制进程
开始应用主服务器的二进制日志
用于复制的正常运行

示例：

sql

-- 启动复制
START SLAVE;

-- 仅启动 IO 线程
START SLAVE IO_THREAD;

-- 仅启动 SQL 线程
START SLAVE SQL_THREAD;

STOP SLAVE：

停止复制进程
暂停应用主服务器的二进制日志
用于复制的维护

示例：

sql

-- 停止复制
STOP SLAVE;

-- 仅停止 IO 线程
STOP SLAVE IO_THREAD;

-- 仅停止 SQL 线程
STOP SLAVE SQL_THREAD;

2. 复制监控

SHOW SLAVE STATUS：

显示复制状态的详细信息
包含 IO 线程和 SQL 线程状态
显示复制延迟和错误

示例：

sql

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 检查复制是否正常
SHOW SLAVE STATUS LIKE 'Slave_%';

SHOW BINARY LOGS：

显示主服务器的二进制日志文件
包含文件名称和大小
用于复制配置

示例：

sql

-- 查看二进制日志
SHOW BINARY LOGS;

SHOW RELAYLOG EVENTS：

显示中继日志的事件
包含事件类型、位置、内容等
用于排查复制错误

示例：

sql

-- 查看中继日志事件
SHOW RELAYLOG EVENTS IN 'relay-bin.000001';

3. 复制故障处理

SET GLOBAL sql_slave_skip_counter：

跳过指定数量的事件
用于处理复制错误
谨慎使用，可能导致数据不一致

示例：

sql

-- 跳过一个事件
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

RESET SLAVE：

重置复制配置
清除复制状态
用于重新配置复制

示例：

sql

-- 重置复制
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO ...;
START SLAVE;

PURGE BINARY LOGS：

清理过期的二进制日志
释放磁盘空间
避免日志占用过多空间

示例：

sql

-- 清理到指定日志文件
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 清理到指定时间
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

安全管理命令

1. 用户管理

CREATE USER：

创建新用户
可以指定密码和认证插件
用于用户管理

示例：

sql

-- 创建用户
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

-- 创建用户带认证插件
CREATE USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

-- 创建用户带过期时间
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE INTERVAL 90 DAY;

DROP USER：

删除不需要的用户
清理用户权限
提高安全性

示例：

sql

-- 删除用户
DROP USER 'user'@'localhost';

ALTER USER：

修改用户属性
更改密码、过期时间等
用于用户维护

示例：

sql

-- 修改用户密码
ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password';

-- 解锁用户
ALTER USER 'user'@'localhost' ACCOUNT UNLOCK;

-- 设置用户密码不过期
ALTER USER 'user'@'localhost' PASSWORD EXPIRE NEVER;

2. 权限管理

GRANT：

授予用户权限
可以指定数据库、表、列级权限
用于权限管理

示例：

sql

-- 授予数据库权限
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

-- 授予表权限
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'user'@'localhost';

-- 授予列权限
GRANT SELECT (column1, column2) ON database_name.table_name TO 'user'@'localhost';

-- 授予管理权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
GRANT SUPER, PROCESS ON *.* TO 'admin'@'localhost';

REVOKE：

撤销用户权限
减少权限滥用风险
遵循最小权限原则

示例：

sql

-- 撤销权限
REVOKE INSERT, UPDATE ON database_name.table_name FROM 'user'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'user'@'localhost';

SHOW GRANTS：

显示用户的权限
包含全局、数据库、表级权限
用于权限审计

示例：

sql

-- 查看用户权限
SHOW GRANTS FOR 'user'@'localhost';

-- 查看当前用户权限
SHOW GRANTS;

3. 安全审计

AUDIT LOG：

记录用户操作
用于安全审计
需要启用审计日志

示例：

sql

-- 启用审计日志（需要插件）
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';

PERFORMANCE_SCHEMA：

收集服务器性能和安全相关数据
包含用户操作、连接等信息
用于安全监控

示例：

sql

-- 查看用户连接
SELECT * FROM performance_schema.users;

-- 查看连接属性
SELECT * FROM performance_schema.session_connect_attrs;

性能分析命令

1. 慢查询分析

SHOW VARIABLES LIKE '%slow_query%'：

查看慢查询日志配置
包含慢查询阈值、日志文件等
用于慢查询监控

示例：

sql

-- 查看慢查询配置
SHOW VARIABLES LIKE '%slow_query%';

-- 查看慢查询阈值
SHOW VARIABLES LIKE 'long_query_time';

SET GLOBAL slow_query_log = 1：

启用慢查询日志
用于捕获慢查询
生产环境谨慎使用

示例：

sql

-- 启用慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;

mysqldumpslow：

分析慢查询日志
汇总相似的慢查询
按执行时间排序

示例：

bash

# 分析慢查询日志
mysqldumpslow /var/log/mysql/slow-query.log

# 按执行次数排序
mysqldumpslow -s c /var/log/mysql/slow-query.log

# 显示前10条
mysqldumpslow -t 10 /var/log/mysql/slow-query.log

2. 执行计划分析

EXPLAIN：

显示查询的执行计划
包含访问类型、索引使用等
用于查询优化

示例：

sql

-- 分析 SELECT 查询
EXPLAIN SELECT * FROM table_name WHERE column = 'value';

-- 分析 UPDATE 查询
EXPLAIN UPDATE table_name SET column = 'value' WHERE id = 1;

-- 分析复杂查询
EXPLAIN SELECT t1.*, t2.* FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id WHERE t1.column = 'value';

EXPLAIN ANALYZE：

执行查询并显示执行计划
包含实际执行时间和行数
比 EXPLAIN 更详细

示例：

sql

-- 分析并执行查询
EXPLAIN ANALYZE SELECT * FROM table_name WHERE column = 'value';

3. 性能模式

PERFORMANCE_SCHEMA：

提供详细的性能统计信息
包含事件、等待、语句等
用于深入性能分析

示例：

sql

-- 查看执行的语句
SELECT * FROM performance_schema.events_statements_history ORDER BY timer_wait DESC LIMIT 10;

-- 查看等待事件
SELECT * FROM performance_schema.events_waits_history ORDER BY timer_wait DESC LIMIT 10;

-- 查看索引使用
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema = 'database_name' ORDER BY count_star DESC;

SYS SCHEMA：

基于 PERFORMANCE_SCHEMA 的视图
提供更友好的性能信息
更易于使用

示例：

sql

-- 查看慢查询
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile ORDER BY avg_timer_wait DESC;

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes WHERE schema_name = 'database_name';

-- 查看表访问统计
SELECT * FROM sys.table_stats WHERE table_schema = 'database_name' ORDER BY rows_changed DESC;

常见问题（FAQ）

Q1: 如何查看 MySQL 服务器的当前状态？

A1: 查看 MySQL 服务器状态的方法：

使用 SHOW GLOBAL STATUS 查看全局状态变量
使用 SHOW ENGINE INNODB STATUS 查看 InnoDB 存储引擎状态
使用 SHOW PROCESSLIST 查看当前正在执行的进程
使用 SHOW VARIABLES 查看服务器配置

Q2: 如何优化 MySQL 表性能？

A2: 优化 MySQL 表性能的方法：

使用 OPTIMIZE TABLE 整理表碎片
使用 ANALYZE TABLE 更新统计信息
创建合适的索引
删除冗余索引
优化表结构，如合理设计字段类型

Q3: 如何排查 MySQL 复制故障？

A3: 排查 MySQL 复制故障的方法：

使用 SHOW SLAVE STATUS 查看复制状态和错误信息
检查主从服务器的网络连接
验证复制用户的权限
检查二进制日志和中继日志
使用 SHOW RELAYLOG EVENTS 查看中继日志事件
对于复制错误，可使用 SET GLOBAL sql_slave_skip_counter 跳过错误（谨慎使用）

Q4: 如何安全地备份 MySQL 数据库？

A4: 安全备份 MySQL 数据库的方法：

对于小数据库，使用 mysqldump 进行逻辑备份
对于大数据库，使用 xtrabackup 进行物理备份
定期执行备份，并验证备份的完整性
将备份存储在安全的位置，最好是异地存储
加密备份文件，保护敏感数据
制定详细的备份和恢复计划

Q5: 如何提高 MySQL 的安全性？

A5: 提高 MySQL 安全性的方法：

使用强密码策略
遵循最小权限原则，只授予必要的权限
定期审计用户权限
启用慢查询日志和审计日志
定期更新 MySQL 版本，修补安全漏洞
限制远程访问，使用防火墙
禁用不必要的功能和插件
定期备份数据，确保数据安全

MySQL 维护命令 ​

系统状态命令 ​

1. 服务器状态 ​

2. 系统变量 ​

3. 存储引擎状态 ​

性能优化命令 ​

1. 表优化 ​

2. 索引管理 ​

3. 缓存管理 ​

故障排查命令 ​

1. 进程管理 ​

2. 锁管理 ​

3. 日志管理 ​

备份恢复命令 ​

1. 逻辑备份 ​

2. 物理备份 ​

3. 数据导入导出 ​

复制管理命令 ​

1. 复制配置 ​

2. 复制监控 ​

3. 复制故障处理 ​

安全管理命令 ​

1. 用户管理 ​

2. 权限管理 ​

3. 安全审计 ​

性能分析命令 ​

1. 慢查询分析 ​

2. 执行计划分析 ​

3. 性能模式 ​

常见问题（FAQ） ​

Q1: 如何查看 MySQL 服务器的当前状态？ ​

Q2: 如何优化 MySQL 表性能？ ​

Q3: 如何排查 MySQL 复制故障？ ​

Q4: 如何安全地备份 MySQL 数据库？ ​

Q5: 如何提高 MySQL 的安全性？ ​

MySQL 维护命令

系统状态命令

1. 服务器状态

2. 系统变量

3. 存储引擎状态

性能优化命令

1. 表优化

2. 索引管理

3. 缓存管理

故障排查命令

1. 进程管理

2. 锁管理

3. 日志管理

备份恢复命令

1. 逻辑备份

2. 物理备份

3. 数据导入导出

复制管理命令

1. 复制配置

2. 复制监控

3. 复制故障处理

安全管理命令

1. 用户管理

2. 权限管理

3. 安全审计

性能分析命令

1. 慢查询分析

2. 执行计划分析

3. 性能模式

常见问题（FAQ）

Q1: 如何查看 MySQL 服务器的当前状态？

Q2: 如何优化 MySQL 表性能？

Q3: 如何排查 MySQL 复制故障？

Q4: 如何安全地备份 MySQL 数据库？

Q5: 如何提高 MySQL 的安全性？