服务器运维笔记：MySQL 运维速查

服务器运维笔记

本文是 MySQL 运维的实战速查手册，涵盖安装部署、权限管理、性能调优、备份恢复、主从复制、慢查询分析、事务锁机制、监控告警及常见故障排查。所有命令和配置均可直接复用，建议收藏备用。

一、安装部署

1.1 CentOS / RHEL 安装（YUM）

# 添加 MySQL 官方仓库（以 MySQL 8.0 为例）
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm

# 安装 MySQL Server
yum install -y mysql-community-server

# 启动并设置开机自启
systemctl start mysqld
systemctl enable mysqld

# 获取临时密码
grep 'temporary password' /var/log/mysqld.log

1.2 Ubuntu / Debian 安装（APT）

# 下载并安装 MySQL APT 仓库配置包
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
dpkg -i mysql-apt-config_0.8.29-1_all.deb
apt-get update

# 安装 MySQL Server
apt-get install -y mysql-server

# 启动服务
systemctl start mysql
systemctl enable mysql

1.3 Docker 快速部署

docker run -d \
  --name mysql8 \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=YourStrongPassword \
  -v /data/mysql/conf:/etc/mysql/conf.d \
  -v /data/mysql/data:/var/lib/mysql \
  -v /data/mysql/log:/var/log/mysql \
  --restart=always \
  mysql:8.0

1.4 安全初始化

# 运行安全配置向导（交互式）
mysql_secure_installation

该向导会引导你完成以下操作：

设置 root 密码强度策略
删除匿名用户
禁止 root 远程登录
删除测试数据库
刷新权限表

1.5 初始配置模板（my.cnf）

[mysqld]
# 基础设置
port = 3306
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 日志
log-error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

[client]
default-character-set = utf8mb4

二、用户与权限

2.1 创建用户

-- 创建本地用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongP@ss!2024';

-- 创建允许特定网段访问的用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongP@ss!2024';

-- 创建允许任意主机访问的用户（慎用）
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongP@ss!2024';

2.2 授权管理

-- 授予特定库的所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'localhost';

-- 只授予读权限
GRANT SELECT ON mydb.* TO 'readonly_user'@'%';

-- 授予特定表的特定权限
GRANT SELECT, INSERT, UPDATE ON mydb.orders TO 'app_user'@'%';

-- 授予创建临时表的权限
GRANT CREATE TEMPORARY TABLES ON mydb.* TO 'app_user'@'%';

-- 刷新权限（GRANT 后建议执行）
FLUSH PRIVILEGES;

2.3 查看与回收权限

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

-- 回收特定权限
REVOKE INSERT ON mydb.* FROM 'app_user'@'localhost';

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

2.4 角色管理（MySQL 8.0+）

-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 给角色授权
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';

-- 将角色赋给用户
GRANT 'app_read', 'app_write' TO 'app_user'@'localhost';

-- 设置默认角色（登录时自动激活）
SET DEFAULT ROLE ALL TO 'app_user'@'localhost';

-- 查看角色
SELECT * FROM mysql.role_edges;

2.5 密码策略配置

-- 查看当前密码策略
SHOW VARIABLES LIKE 'validate_password%';

-- 设置密码策略（需安装 validate_password 组件）
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
SET GLOBAL validate_password.policy = MEDIUM;

-- 设置密码过期
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 修改用户密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewStr0ng!Pass';

三、配置调优

3.1 my.cnf 核心参数速查

[mysqld]
# ========== InnoDB 引擎 ==========
innodb_buffer_pool_size = 8G           # 物理内存的 60%~80%
innodb_buffer_pool_instances = 8       # buffer pool 实例数（>=8G 时建议 8）
innodb_log_file_size = 2G              # redo log 大小
innodb_log_buffer_size = 64M           # redo log 缓冲
innodb_flush_log_at_trx_commit = 1     # 1=最安全，2=高性能
innodb_flush_method = O_DIRECT          # 跳过 OS 缓存
innodb_io_capacity = 2000               # SSD 建议 2000+
innodb_io_capacity_max = 4000
innodb_file_per_table = 1               # 独立表空间
innodb_open_files = 65535
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# ========== 连接与线程 ==========
max_connections = 500                   # 最大连接数
max_connect_errors = 100                # 连续错误后封禁
wait_timeout = 600                      # 空闲连接超时（秒）
interactive_timeout = 600
thread_cache_size = 64                  # 线程缓存
table_open_cache = 4096                 # 表缓存
table_definition_cache = 2048

# ========== 查询缓存（MySQL 8.0 已移除） ==========
# query_cache_type = 0                  # 8.0 以下建议关闭
# query_cache_size = 0

# ========== 排序与临时表 ==========
sort_buffer_size = 4M                   # 每连接排序缓冲
join_buffer_size = 4M                   # 每连接 JOIN 缓冲
tmp_table_size = 64M                    # 内存临时表上限
max_heap_table_size = 64M               # 与 tmp_table_size 保持一致

# ========== 日志 ==========
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                     # 慢查询阈值（秒）
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
general_log = 0                         # 生产环境关闭

# ========== 字符集 ==========
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

3.2 关键参数详解

innodb_buffer_pool_size

这是 InnoDB 最重要的参数，决定了数据和索引在内存中的缓存大小。建议设为物理内存的 60%~80%。

-- 查看当前 buffer pool 使用情况
SHOW ENGINE INNODB STATUS;

-- 查看命中率（应 > 99%）
SELECT
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_rate
FROM (
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a, (
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;

连接数调优

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

-- 查看历史最大连接数
SHOW STATUS LIKE 'Max_used_connections';

-- 查看连接相关变量
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';

-- 动态调整（无需重启）
SET GLOBAL max_connections = 500;
SET GLOBAL wait_timeout = 600;

3.3 慢查询配置

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 查看慢查询状态
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

四、索引优化

4.1 索引类型

-- B-Tree 索引（默认，最常用）
CREATE INDEX idx_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 前缀索引（适用于长字符串）
CREATE INDEX idx_addr ON users(address(20));

-- 联合索引（最左前缀原则）
CREATE INDEX idx_name_age ON users(name, age);

-- 全文索引（InnoDB 5.6+ 支持）
CREATE FULLTEXT INDEX idx_content ON articles(title, body);

4.2 联合索引与最左前缀

联合索引 (a, b, c) 可以被以下查询命中：

-- ✅ 可命中
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3      -- 只命中 a 部分
WHERE a = 1 ORDER BY b     -- a 等值 + b 排序

-- ❌ 无法命中（跳过了 a）
WHERE b = 2
WHERE b = 2 AND c = 3
WHERE c = 3

4.3 覆盖索引

当查询的所有列都包含在索引中时，MySQL 可以直接从索引返回结果，无需回表。

-- 联合索引 (name, age)
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引查询（Using index）
SELECT name, age FROM users WHERE name = '张三';

-- 非覆盖查询（需要回表）
SELECT name, age, email FROM users WHERE name = '张三';

4.4 EXPLAIN 详解

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

关键字段解读：

type（访问类型，从好到差）：system > const > eq_ref > ref > range > index > ALL
key：实际使用的索引，NULL 表示未使用索引
rows：预估扫描行数，越小越好
Extra：
- Using index：覆盖索引 ✅
- Using where：在存储引擎返回后过滤
- Using temporary：使用临时表 ⚠️
- Using filesort：额外排序 ⚠️
- Using index condition：索引下推 ✅

-- 查看实际执行计划（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

4.5 索引失效的常见场景

-- ❌ 对索引列使用函数
WHERE YEAR(created_at) = 2024
-- ✅ 改写为
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- ❌ 隐式类型转换
WHERE phone = 13800138000    -- phone 是 VARCHAR 类型
-- ✅ 改写为
WHERE phone = '13800138000'

-- ❌ LIKE 左模糊
WHERE name LIKE '%张'
-- ✅ 改写为前缀匹配
WHERE name LIKE '张%'

-- ❌ OR 连接不同索引列
WHERE a = 1 OR b = 2
-- ✅ 改写为 UNION 或创建联合索引

-- ❌ NOT IN / NOT EXISTS / !=  可能导致全表扫描
-- ❌ IS NULL / IS NOT NULL 在某些场景下失效

-- 使用 FORCE INDEX 强制走索引（应急手段）
SELECT * FROM orders FORCE INDEX(idx_user_status) WHERE user_id = 100;

五、备份恢复

5.1 mysqldump 逻辑备份

# 备份单个数据库
mysqldump -u root -p --single-transaction --routines --triggers \
  --databases mydb > /backup/mydb_$(date +%F).sql

# 备份所有数据库
mysqldump -u root -p --all-databases --single-transaction \
  --routines --triggers --events > /backup/all_$(date +%F).sql

# 备份特定表
mysqldump -u root -p mydb orders products > /backup/tables.sql

# 压缩备份
mysqldump -u root -p mydb | gzip > /backup/mydb.sql.gz

# 只导出表结构
mysqldump -u root -p --no-data mydb > /backup/schema.sql

# 只导出数据
mysqldump -u root -p --no-create-info mydb > /backup/data.sql

关键参数说明：

--single-transaction：InnoDB 一致性备份，不锁表
--routines：包含存储过程和函数
--triggers：包含触发器
--events：包含定时事件
--master-data=2：记录 binlog 位点（注释形式）
--flush-logs：备份前刷新 binlog

5.2 恢复数据

# 从 SQL 文件恢复
mysql -u root -p mydb < /backup/mydb_2024-01-01.sql

# 从压缩文件恢复
gunzip < /backup/mydb.sql.gz | mysql -u root -p mydb

# 恢复单个表（从完整备份中提取）
mysql -u root -p mydb --table=orders < /backup/orders_only.sql

5.3 Percona XtraBackup 物理备份

# 安装（CentOS）
yum install -y percona-xtrabackup-80

# 全量备份
xtrabackup --backup --target-dir=/backup/full \
  --user=root --password=YourPass

# 准备备份（应用 redo log）
xtrabackup --prepare --target-dir=/backup/full

# 恢复（先停止 MySQL）
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# 增量备份
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=root --password=YourPass

5.4 Binlog 恢复（基于时间点恢复）

# 查看 binlog 列表
SHOW BINARY LOGS;

# 查看 binlog 事件
SHOW BINLOG EVENTS IN 'binlog.000001';

# 使用 mysqlbinlog 工具
mysqlbinlog --start-datetime="2024-01-01 10:00:00" \
  --stop-datetime="2024-01-01 11:00:00" \
  /var/lib/mysql/binlog.000001 | mysql -u root -p

# 基于位点恢复
mysqlbinlog --start-position=154 --stop-position=1024 \
  /var/lib/mysql/binlog.000001 | mysql -u root -p

# 跳过某个误操作事务
mysqlbinlog --start-position=154 --stop-position=999 binlog.000001 | mysql -u root -p
mysqlbinlog --start-position=1050 binlog.000001 | mysql -u root -p

5.5 逻辑备份 vs 物理备份对比

mysqldump：跨版本兼容、可读性强、单表恢复灵活；速度慢、大库耗时长
XtraBackup：速度快、不锁表（InnoDB）、支持增量；版本限制、文件较大
建议：小库用 mysqldump，大库（>50GB）用 XtraBackup，关键库两种都备

六、主从复制

6.1 复制原理

主从复制基于三个线程协作：

Binlog Dump Thread（主库）：读取 binlog 发送给从库
I/O Thread（从库）：接收 binlog 写入 relay log
SQL Thread（从库）：重放 relay log 中的事件

6.2 传统复制配置

主库配置（my.cnf）：

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800    # 7 天
sync_binlog = 1

从库配置（my.cnf）：

[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1
super_read_only = 1
log_slave_updates = 1

配置复制：

-- 主库创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'ReplStr0ng!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

-- 主库查看 binlog 位点
SHOW MASTER STATUS;

-- 从库配置主库信息
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'ReplStr0ng!',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 154;

-- 启动复制
START SLAVE;

6.3 GTID 复制（推荐）

# 主库和从库都要配置
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
binlog_format = ROW

-- 从库配置（无需指定 binlog 位点）
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'ReplStr0ng!',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

6.4 延迟从库（灾备）

-- 设置延迟 1 小时的从库
CHANGE MASTER TO MASTER_DELAY = 3600;
START SLAVE;

6.5 复制监控

-- 查看从库状态
SHOW SLAVE STATUS\G

-- 关键指标
-- Slave_IO_Running: Yes          ← I/O 线程正常
-- Slave_SQL_Running: Yes         ← SQL 线程正常
-- Seconds_Behind_Master: 0       ← 延迟秒数
-- Last_IO_Error                  ← I/O 错误信息
-- Last_SQL_Error                 ← SQL 错误信息

-- 查看复制延迟
SHOW SLAVE STATUS\G  -- 看 Seconds_Behind_Master

-- GTID 模式下查看已执行的 GTID
SELECT @@global.gtid_executed;

-- 跳过一个错误（谨慎使用）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- GTID 模式下跳过
STOP SLAVE;
SET GTID_NEXT = 'uuid:transaction_id';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

七、慢查询分析

7.1 开启慢查询日志

-- 动态开启
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 100;  -- 至少扫描 100 行才记录

7.2 使用 mysqldumpslow

# 按执行时间排序的 Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按出现次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按返回行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

7.3 使用 pt-query-digest（推荐）

# 安装 Percona Toolkit
yum install -y percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt

# 分析特定时间段
pt-query-digest --since '2024-01-01 00:00:00' \
  --until '2024-01-02 00:00:00' /var/log/mysql/slow.log

# 分析 binlog
mysqlbinlog mysql-bin.000001 | pt-query-digest --type binlog

# 分析 general log
pt-query-digest --type genlog /var/log/mysql/general.log

7.4 常见慢查询优化技巧

-- 1. 分页优化（避免大 offset）
-- ❌ 慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- ✅ 使用游标分页
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- ✅ 延迟关联
SELECT o.* FROM orders o
  INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
  ON o.id = t.id;

-- 2. 避免 SELECT *
SELECT id, name, email FROM users WHERE id = 1;

-- 3. 小表驱动大表
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);
-- ✅ 改写为 EXISTS（当子查询表较小时用 IN，较大时用 EXISTS）

-- 4. 批量操作代替循环
-- ❌ 逐条插入
INSERT INTO logs VALUES (1, 'a');
INSERT INTO logs VALUES (2, 'b');
-- ✅ 批量插入
INSERT INTO logs VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- 5. 使用 UNION ALL 代替 UNION（无需去重时）
SELECT id FROM table_a UNION ALL SELECT id FROM table_b;

八、事务与锁

8.1 ACID 特性

A（Atomicity 原子性）：事务要么全部成功，要么全部回滚
C（Consistency 一致性）：事务前后数据保持一致
I（Isolation 隔离性）：并发事务互不干扰
D（Durability 持久性）：提交后数据永久保存

8.2 隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

四个隔离级别从低到高：

READ UNCOMMITTED：读未提交，可能脏读
READ COMMITTED：读已提交，解决脏读（Oracle 默认）
REPEATABLE READ：可重复读，解决不可重复读（MySQL 默认）
SERIALIZABLE：串行化，解决幻读，性能最差

8.3 死锁排查

-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS\G

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

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

-- 查看正在等待锁的事务
SELECT
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 杀掉阻塞线程（应急）
KILL <thread_id>;

8.4 锁等待分析

-- 查看 innodb_lock_wait_timeout
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 设置锁等待超时（默认 50 秒）
SET SESSION innodb_lock_wait_timeout = 10;

-- 查看行锁状态
SHOW STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_time        锁等待总时间（毫秒）
-- Innodb_row_lock_waits       锁等待次数
-- Innodb_row_lock_time_avg    平均锁等待时间
-- Innodb_row_lock_time_max    最大锁等待时间

-- 开启死锁日志（记录到 error log）
SET GLOBAL innodb_print_all_deadlocks = ON;

8.5 查看长事务

-- 查看运行时间超过 60 秒的事务
SELECT
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;

-- 杀掉长事务
KILL <thread_id>;

九、监控与告警

9.1 SHOW STATUS 常用指标

-- 连接相关
SHOW STATUS LIKE 'Threads_connected';     -- 当前连接数
SHOW STATUS LIKE 'Threads_running';        -- 正在执行的线程
SHOW STATUS LIKE 'Max_used_connections';   -- 历史最大连接
SHOW STATUS LIKE 'Connection_errors%';     -- 连接错误

-- 查询相关
SHOW STATUS LIKE 'Questions';              -- 查询总数
SHOW STATUS LIKE 'Com_select';             -- SELECT 次数
SHOW STATUS LIKE 'Com_insert';             -- INSERT 次数
SHOW STATUS LIKE 'Com_update';             -- UPDATE 次数
SHOW STATUS LIKE 'Com_delete';             -- DELETE 次数
SHOW STATUS LIKE 'Slow_queries';           -- 慢查询数

-- InnoDB 相关
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  -- 逻辑读
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';          -- 物理读（未命中）
SHOW STATUS LIKE 'Innodb_row_lock_waits';             -- 行锁等待
SHOW STATUS LIKE 'Innodb_rows_read%';                 -- 行读取

-- 临时表与排序
SHOW STATUS LIKE 'Created_tmp_disk_tables';  -- 磁盘临时表
SHOW STATUS LIKE 'Created_tmp_tables';       -- 内存临时表
SHOW STATUS LIKE 'Sort_merge_passes';        -- 排序合并

9.2 performance_schema 常用查询

-- Top 10 最耗时 SQL
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_time_s,
  ROUND(AVG_TIMER_WAIT / 1e12, 4) AS avg_time_s,
  SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看表 I/O 统计
SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY COUNT_READ + COUNT_WRITE DESC
LIMIT 20;

-- 查看等待事件 Top 10
SELECT
  EVENT_NAME,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_time_s
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看内存使用
SELECT
  EVENT_NAME,
  CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_mb
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;

9.3 常用监控指标清单

必须监控的核心指标：

连接数使用率：Threads_connected / max_connections
缓冲池命中率：(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
慢查询增长速率：Slow_queries 增量
复制延迟：Seconds_Behind_Master
磁盘空间：数据目录和 binlog 目录
QPS / TPS：Questions 和事务相关指标
死锁次数：Innodb_deadlocks（MySQL 8.0+）
行锁等待：Innodb_row_lock_waits

十、常见问题排查

10.1 连接数耗尽

-- 诊断
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
SHOW PROCESSLIST;

-- 看谁占用了连接
SELECT user, host, db, command, time, state, info
FROM information_schema.processlist
ORDER BY time DESC;

-- 杀掉空闲连接
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 600;

-- 临时增加最大连接数
SET GLOBAL max_connections = 1000;

10.2 表锁问题

-- 查看表锁状态
SHOW STATUS LIKE 'Table_locks%';
SHOW OPEN TABLES WHERE In_use > 0;

-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 查找阻塞源
SELECT
  waiting_pid, waiting_query,
  blocking_pid, blocking_query
FROM sys.innodb_lock_waits;

-- 紧急处理：杀掉阻塞线程
KILL <blocking_pid>;

10.3 磁盘空间不足

# 查看数据目录大小
du -sh /var/lib/mysql/*

# 查看 binlog 占用
SHOW BINARY LOGS;

# 清理过期 binlog
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

# 设置 binlog 过期时间
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7 天

# 查找大表
SELECT
  table_schema,
  table_name,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY data_length + index_length DESC
LIMIT 20;

# 回收碎片空间
ALTER TABLE my_big_table ENGINE=InnoDB;  -- 注意会锁表

10.4 主从延迟

-- 诊断延迟
SHOW SLAVE STATUS\G  -- 看 Seconds_Behind_Master

-- 检查是否有大事务
SELECT * FROM information_schema.innodb_trx
ORDER BY trx_started;

-- 检查从库是否有锁等待
SHOW SLAVE STATUS\G  -- 看 Slave_SQL_Running_State

-- 常见原因与对策：
-- 1. 大事务拆分：将大批量 UPDATE/DELETE 拆成小批次
-- 2. 从库配置不足：提升从库硬件或优化参数
-- 3. 并行复制：开启多线程复制
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
STOP SLAVE; START SLAVE;

-- 4. 检查从库 I/O 和 SQL 线程状态
SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes

十一、运维命令速查表

11.1 服务管理

# 启动 / 停止 / 重启
systemctl start mysql
systemctl stop mysql
systemctl restart mysql
systemctl status mysql

# 安全重启（等待当前查询完成）
mysqladmin shutdown
mysqld_safe &

# 查看 MySQL 版本
mysql --version
SELECT VERSION();

11.2 连接与诊断

# 本地连接
mysql -u root -p
mysql -u root -p -h 127.0.0.1 -P 3306

# 指定数据库
mysql -u root -p mydb

# 执行 SQL 文件
mysql -u root -p mydb < script.sql

# 命令行执行 SQL
mysql -u root -p -e "SHOW DATABASES;"

11.3 信息查询速查

-- 数据库与表
SHOW DATABASES;
SHOW TABLES;
SHOW TABLE STATUS;
DESC table_name;
SHOW CREATE TABLE table_name\G

-- 索引
SHOW INDEX FROM table_name;

-- 进程与连接
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SHOW STATUS LIKE 'Threads%';

-- 变量
SHOW VARIABLES LIKE '%keyword%';
SHOW GLOBAL VARIABLES LIKE '%keyword%';
SET GLOBAL var_name = value;

-- 状态
SHOW STATUS LIKE '%keyword%';
SHOW GLOBAL STATUS LIKE '%keyword%';

-- 引擎
SHOW ENGINES;
SHOW ENGINE INNODB STATUS\G

11.4 表维护

-- 检查表
CHECK TABLE my_table;

-- 修复表（MyISAM）
REPAIR TABLE my_table;

-- 优化表（回收碎片）
OPTIMIZE TABLE my_table;
ALTER TABLE my_table ENGINE=InnoDB;  -- InnoDB 推荐方式

-- 分析表（更新统计信息）
ANALYZE TABLE my_table;

-- 查看表大小
SELECT
  table_name,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb,
  table_rows
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC;

11.5 Binlog 操作

-- 查看 binlog 状态
SHOW BINARY LOGS;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE 'log_bin%';

-- 清理 binlog
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 刷新 binlog（产生新文件）
FLUSH BINARY LOGS;

11.6 用户管理速查

-- 查看所有用户
SELECT user, host, plugin, authentication_string FROM mysql.user;

-- 查看当前用户
SELECT CURRENT_USER();
SELECT USER();

-- 创建用户
CREATE USER 'name'@'host' IDENTIFIED BY 'password';

-- 授权
GRANT privileges ON db.table TO 'user'@'host';

-- 回收权限
REVOKE privileges ON db.table FROM 'user'@'host';

-- 修改密码
ALTER USER 'user'@'host' IDENTIFIED BY 'newpass';

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

-- 刷新权限
FLUSH PRIVILEGES;

11.7 性能诊断速查

-- QPS
SHOW GLOBAL STATUS LIKE 'Questions';
-- 计算：(当前 Questions - 上次 Questions) / 时间间隔

-- TPS
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';

-- Buffer Pool 命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

-- 临时表使用
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- 排序统计
SHOW GLOBAL STATUS LIKE 'Sort%';

-- 表扫描
SHOW GLOBAL STATUS LIKE 'Handler_read%';

Tips： 建议将本手册加入收藏夹，遇到问题时按章节快速定位。MySQL 运维的核心原则是：先备份、再操作、看监控、防误删。生产环境任何变更操作前，务必先确认备份可用。

如果觉得文章对你有用，请随意赞赏

运维

服务器运维笔记：MySQL 运维速查

https://acf1sh.top/console/overview/archives/fu-wu-qi-yun-wei-bi-ji-mysql-yun-wei-su-cha

作者

fish

发布于

2026-06-13

更新于

2026-06-10

许可协议

CC BY 4.0

服务器运维笔记：MySQL 运维速查

一、安装部署

1.1 CentOS / RHEL 安装（YUM）

1.2 Ubuntu / Debian 安装（APT）

1.3 Docker 快速部署

1.4 安全初始化

1.5 初始配置模板（my.cnf）

二、用户与权限

2.1 创建用户

2.2 授权管理

2.3 查看与回收权限

2.4 角色管理（MySQL 8.0+）

2.5 密码策略配置

三、配置调优

3.1 my.cnf 核心参数速查

3.2 关键参数详解

3.3 慢查询配置

四、索引优化

4.1 索引类型

4.2 联合索引与最左前缀

4.3 覆盖索引

4.4 EXPLAIN 详解

4.5 索引失效的常见场景

五、备份恢复

5.1 mysqldump 逻辑备份

5.2 恢复数据

5.3 Percona XtraBackup 物理备份

5.4 Binlog 恢复（基于时间点恢复）

5.5 逻辑备份 vs 物理备份对比

六、主从复制

6.1 复制原理

6.2 传统复制配置

6.3 GTID 复制（推荐）

6.4 延迟从库（灾备）

6.5 复制监控

七、慢查询分析

7.1 开启慢查询日志

7.2 使用 mysqldumpslow

7.3 使用 pt-query-digest（推荐）

7.4 常见慢查询优化技巧

八、事务与锁

8.1 ACID 特性

8.2 隔离级别

8.3 死锁排查

8.4 锁等待分析

8.5 查看长事务

九、监控与告警

9.1 SHOW STATUS 常用指标

9.2 performance_schema 常用查询

9.3 常用监控指标清单

十、常见问题排查

10.1 连接数耗尽

10.2 表锁问题

10.3 磁盘空间不足

10.4 主从延迟

十一、运维命令速查表

11.1 服务管理

11.2 连接与诊断

11.3 信息查询速查

11.4 表维护

11.5 Binlog 操作

11.6 用户管理速查

11.7 性能诊断速查

作者

发布于

更新于

许可协议

评论