MySQL
故障排查手册

-- 看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启,无需重启
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;          -- 1 秒以上算慢查询
SET GLOBAL log_queries_not_using_indexes = 1;  -- 没用索引的也记
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# my.cnf 持久化配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1     # 记录管理类慢操作
min_examined_row_examined = 1000  # 扫描行数大于此才记录

# 1. 用 mysqldumpslow (自带)
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t 按总执行时间排序  -t 10 取前 10

# 2. 用 pt-query-digest (推荐,更详细)
pt-query-digest /var/log/mysql/slow.log | less
# 按查询类型聚合,看 Query_time 和 Rows_examined

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

-- 看当前正在执行,且时间超过 5 秒的查询
SELECT * FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;

-- 杀掉特定查询(谨慎!)
KILL <process_id>;

-- 1. 看当前配置和使用
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';        -- 当前活跃
SHOW STATUS LIKE 'Max_used_connections';     -- 历史峰值
SHOW STATUS LIKE 'Threads_running';          -- 实际执行中

-- 2. 临时调大(无需重启)
SET GLOBAL max_connections = 2000;

-- 3. 看谁占用了连接
SELECT user, host, command, count(*)
FROM information_schema.processlist
GROUP BY user, host, command
ORDER BY count(*) DESC;

# my.cnf 配置
[mysqld]
max_connections = 2000             # 总连接数
max_user_connections = 1800        # 单用户连接
wait_timeout = 600                 # 空闲多久断开(默认 28800,太长)
interactive_timeout = 600

# 应用端连接池(以 HikariCP 为例)
# maximum-pool-size = 20 (单实例)
# minimum-idle = 10
# idle-timeout = 300000 (5 分钟)
# max-lifetime = 1800000 (30 分钟,小于 wait_timeout)

-- MySQL 5.7+ / 8.0 通用
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 查谁在阻塞谁
SELECT
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_pid,
  r.trx_query AS waiting_sql,
  b.trx_id AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_pid,
  b.trx_query AS blocking_sql
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;

-- 杀掉阻塞源
KILL <blocking_pid>;

SHOW ENGINE INNODB STATUS\G
-- 找 "LATEST DETECTED DEADLOCK" 段,会列出两个事务的 SQL 和锁信息

-- 模式 A:不同顺序更新同样的行
-- 事务 1:
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 事务 2:
UPDATE account SET balance = balance - 100 WHERE id = 2;  -- 顺序反了
UPDATE account SET balance = balance + 100 WHERE id = 1;

-- 模式 B:间隙锁冲突(范围更新)
UPDATE orders SET status='paid' WHERE user_id BETWEEN 100 AND 200;
-- 同时插入 user_id=150 的订单,会被间隙锁阻塞

-- 锁等待超时(默认 50 秒,根据业务调)
SET GLOBAL innodb_lock_wait_timeout = 30;

-- 死锁自动检测(默认开启,高并发时考虑关闭)
SET GLOBAL innodb_deadlock_detect = ON;

-- 在从库执行,关键字段
SHOW SLAVE STATUS\G

-- 重点看这几个字段
-- Slave_IO_Running: Yes        (IO 线程,接收 binlog)
-- Slave_SQL_Running: Yes       (SQL 线程,应用 binlog)
-- Seconds_Behind_Master: 0     (延迟秒数,关键!)
-- Last_IO_Error / Last_SQL_Error

-- Step 1. 看延迟趋势
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master 持续增加 → 从库追不上
-- 数值跳动但不大 → 正常网络抖动

-- Step 2. 找主从位点差
-- 主库 Position vs 从库 Exec_Master_Log_Pos
-- 差距大 → 从库 SQL 线程慢
-- 主库 Position vs 从库 Read_Master_Log_Pos
-- 差距大 → 主从网络慢或 IO 线程慢

-- Step 3. 看从库在执行什么
SELECT * FROM performance_schema.replication_applier_status_by_worker;
-- 找 LAST_APPLIED_TRANSACTION,看正在执行的事务

[mysqld]
slave_parallel_workers = 8                    # 并行线程数
slave_parallel_type = LOGICAL_CLOCK           # 基于组提交
slave_preserve_commit_order = ON              # 保持提交顺序
binlog_transaction_dependency_tracking = WRITESET  # 5.7.22+

-- 1. 先看具体错误
SHOW SLAVE STATUS\G
-- Last_SQL_Errno: 1062 (Duplicate entry)
-- Last_SQL_Error: Error 'Duplicate entry...' on query 'INSERT INTO...'

-- 2. 临时跳过错误(仅紧急救急,谨慎!)
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
-- 跳过一个事件,然后看是否继续

# 主库执行,自动对比所有表的校验值
pt-table-checksum --host=master \
  --user=root --password=xxx \
  --databases=mydb \
  --replicate=test.checksums

# 检查不一致的行
pt-table-sync --print \
  --replicate=test.checksums \
  h=master,u=root,p=xxx
# 输出修复 SQL,人工确认后再 --execute

-- Step 1. 看当前在跑啥
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC
LIMIT 20;

-- Step 2. 看哪些 SQL 在频繁执行
SELECT digest_text, count_star, sum_timer_wait/1e12 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY count_star DESC
LIMIT 10;

-- Step 3. 看锁等待
SELECT count(*) FROM information_schema.innodb_lock_waits;

# MySQL 进程的 CPU 分布
top -H -p $(pidof mysqld)
# 看是哪个线程在猛吃 CPU

# 把线程 ID 转 16 进制(用于关联 MySQL 内部线程)
printf "%x\n" <thread_id>

# mysqladmin 看实时状态
mysqladmin -uroot -p extended-status -i1 | \
  grep -E "Questions|Com_select|Com_insert|Com_update|Com_delete|Threads_running"

# 计算 QPS
mysqladmin -uroot -p extended-status -i1 -r | grep Questions

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 应为物理内存的 50-70%(独占数据库的机器)

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 计算命中率 = 1 - reads / read_requests
-- 低于 99% 说明 buffer pool 不够大

SHOW STATUS LIKE 'Innodb_row_lock_waits';
SHOW STATUS LIKE 'Innodb_row_lock_time_avg';

-- 1. 看 binlog 列表和大小
SHOW BINARY LOGS;

-- 2. 看 binlog 保留天数
SHOW VARIABLES LIKE 'expire_logs_days';        -- 5.7
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- 8.0

-- 方法 A:删除指定时间之前的(推荐)
PURGE BINARY LOGS BEFORE '2026-04-01 00:00:00';

-- 方法 B:删除指定文件之前的
PURGE BINARY LOGS TO 'mysql-bin.000100';

-- 方法 C:设置自动过期(永久生效)
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7 天

[mysqld]
# 8.0+
binlog_expire_logs_seconds = 604800     # 7 天自动清理

# 5.7
expire_logs_days = 7

# 单个 binlog 文件大小(默认 1G)
max_binlog_size = 256M                  # 调小便于清理

# 推荐配置(数据安全 + 性能平衡)
sync_binlog = 1                         # 每次事务都刷盘(最安全)
binlog_format = ROW                     # 行级,推荐
binlog_row_image = MINIMAL              # 节省空间

# 解析 binlog 为可读 SQL
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000123

# 找出某段时间的所有变更
mysqlbinlog --start-datetime="2026-05-01 10:00:00" \
            --stop-datetime="2026-05-01 11:00:00" \
            mysql-bin.000123

# 找出哪个表被改最多(诊断 binlog 暴增)
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000123 | \
  grep -oP '### (INSERT INTO|UPDATE|DELETE FROM) \K\S+' | sort | uniq -c | sort -rn

-- 看当前所有字符集设置
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

# my.cnf,记住:必须用 utf8mb4 不是 utf8
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

-- 看表的字符集
SHOW CREATE TABLE users;

-- 转换整张表(数据量大时慎用,会锁表)
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 只改默认值,不动现有数据(快,但旧数据字符集不变)
ALTER TABLE users DEFAULT CHARACTER SET utf8mb4;

-- 加索引(默认就是 in-place,不锁表读写)
ALTER TABLE users ADD INDEX idx_phone(phone),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 加字段(8.0+ 支持 INSTANT 算法,瞬间完成)
ALTER TABLE users ADD COLUMN nickname VARCHAR(50),
  ALGORITHM=INSTANT;

-- 算法选择
-- INSTANT  (8.0)  瞬时完成,只改元数据
-- INPLACE  原地修改,无需复制表,但要时间
-- COPY     复制表,最慢,会锁表

# 1. 加字段不锁表
pt-online-schema-change \
  --alter="ADD COLUMN age INT" \
  D=mydb,t=users \
  --execute

# 原理:
# 1. 创建影子表 _users_new (结构含新字段)
# 2. 在原表加触发器,记录后续变更
# 3. 分批从原表复制数据到新表
# 4. 触发器持续同步增量
# 5. 一瞬间 rename 完成切换

# 2. 主从延迟自适应
pt-online-schema-change \
  --alter="ADD INDEX idx_name(name)" \
  D=mydb,t=users \
  --max-lag=2 \
  --check-interval=2 \
  --execute

# 不用触发器,基于 binlog 同步
gh-ost \
  --user=root --password=xxx \
  --host=replica.host \
  --database="mydb" \
  --table="users" \
  --alter="ADD COLUMN age INT" \
  --execute

# 优势:可暂停可继续、读从库 binlog 不影响主库性能