MySQL 备份恢复与慢查询优化

概述

备份是数据库运维的生命线，慢查询分析是性能优化的起点。本文覆盖逻辑/物理备份、时间点恢复（PITR）、慢查询日志配置与分析、pt-query-digest 使用。

备份策略

备份类型对比

类型	工具	原理	恢复速度	增量	一致性
逻辑备份	`mysqldump`, `mydumper`	导出 SQL 语句	慢	❌	需 `--single-transaction`
物理备份	`xtrabackup` (Percona)	拷贝数据文件	快	✅	一致性快照
快照备份	LVM / 云盘快照	磁盘快照	最快	✅	需 `FLUSH TABLES WITH READ LOCK`
Binlog	原生	增量日志	—	✅ 天然增量	按时间/位点

mysqldump 常用命令

# 单库备份（生产推荐参数）
mysqldump -h 127.0.0.1 -u root -p \
  --single-transaction \        # InnoDB 一致性快照，不锁表
  --routines --triggers \       # 备份存储过程和触发器
  --master-data=2 \             # 注释记录 Binlog 位点（用于恢复）
  --databases mydb \
  | gzip > mydb_$(date +%Y%m%d).sql.gz

# 只备份表结构
mysqldump --no-data --databases mydb > schema.sql

# 只备份数据（不含建表语句）
mysqldump --no-create-info --databases mydb > data.sql

xtrabackup 生产级备份脚本

#!/bin/bash
# mysql_backup.sh —— 全量 + 增量备份

BACKUP_DIR="/data/backup/mysql"
FULL_DIR="${BACKUP_DIR}/full/$(date +%Y%m%d)"
INCR_DIR="${BACKUP_DIR}/incr/$(date +%Y%m%d_%H%M)"

# 全量备份（每周日）
if [ $(date +%u) -eq 7 ]; then
  xtrabackup --backup \
    --target-dir=${FULL_DIR} \
    --user=backup --password=xxx \
    --compress --compress-threads=4 \
    --parallel=4
  echo "Full backup completed: ${FULL_DIR}"
fi

# 增量备份（每天）
xtrabackup --backup \
  --target-dir=${INCR_DIR} \
  --incremental-basedir=${FULL_DIR} \
  --user=backup --password=xxx \
  --compress
echo "Incremental backup completed: ${INCR_DIR}"

恢复流程

# 1. 准备全量备份（apply-log 回放 Redo Log）
xtrabackup --prepare --apply-log-only --target-dir=${FULL_DIR}

# 2. 应用增量备份
xtrabackup --prepare --apply-log-only \
  --target-dir=${FULL_DIR} \
  --incremental-dir=${INCR_DIR}

# 3. 最终准备
xtrabackup --prepare --target-dir=${FULL_DIR}

# 4. 恢复（需先停 MySQL 并清空数据目录）
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=${FULL_DIR}
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# 5. 时间点恢复（通过 Binlog）
mysqlbinlog --start-datetime="2026-07-14 10:00:00" \
            --stop-datetime="2026-07-14 12:30:45" \
            /var/log/mysql/mysql-bin.* | mysql -u root -p

备份 Checklist

每周全量备份 + 每天增量备份
Binlog 保留至少 7 天，开启 expire_logs_days=7
定期演练恢复（至少每季度一次）
备份文件加密（xtrabackup --encrypt 或磁盘加密）
备份异地存储（对象存储/其他机房）
从 Slave 备份，避免影响 Master
备份完成后做一致性校验

慢查询分析

开启慢查询日志

# my.cnf
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5                 # 超过 0.5s 记录
log_queries_not_using_indexes = ON    # 记录未使用索引的查询
log_slow_admin_statements = ON        # 记录 DDL 慢操作
min_examined_row_limit = 1000         # 扫描超过 1000 行才记录（避免噪音）

慢查询日志内容

# Time: 2026-07-14T10:30:00.123456Z
# User@Host: app[app] @ 192.168.1.100 [192.168.1.100]
# Query_time: 5.234567  Lock_time: 0.000123  Rows_sent: 100  Rows_examined: 1000000
SET timestamp=1753098600;
SELECT * FROM orders WHERE user_id = 100 ORDER BY id LIMIT 1000000, 10;

pt-query-digest 分析慢查询

# 安装 Percona Toolkit
# macOS: brew install percona-toolkit

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

# 分析最近 1 小时的慢查询
pt-query-digest --since '1h ago' /var/log/mysql/slow.log

# 只显示 TOP 10
pt-query-digest --limit 10 /var/log/mysql/slow.log

# 分析 tcpdump 抓取的网络流量
tcpdump -i any port 3306 -s 65535 -w mysql.pcap
pt-query-digest --type=tcpdump mysql.pcap

pt-query-digest 输出解读

# Profile
# Rank  Query ID              Response time  Calls  R/Call  V/M   Item
# ====  ===================== ============== ====== ======= ===== ====
#    1  0x...                  50.0000 50.0%    100   0.5000  0.05 SELECT orders
#    2  0x...                  30.0000 30.0%    500   0.0600  0.02 SELECT users

# 关注：
# - Response time %：该查询占总慢查询时间的百分比（优先优化占比大的）
# - Rows examine vs Rows sent：扫描/返回比（比值大 → 索引问题）
# - Calls：调用频率（高频查询优先优化）

实时慢查询定位

-- 查看当前正在执行的慢查询
SELECT
    id, user, host, db, command, time, state,
    LEFT(info, 200) AS query
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep' AND time > 10
ORDER BY time DESC;

-- KILL 慢查询
KILL <id>;

-- 查看语句执行阶段
SELECT * FROM performance_schema.events_statements_current
WHERE timer_wait > 1000000000000  -- 超过 1 秒（单位：皮秒）
ORDER BY timer_wait DESC\G

常见问题 / 坑点

问题	原因	解决方案
`mysqldump` 备份期间锁表	未使用 `--single-transaction`（MyISAM 表除外）	仅 InnoDB 环境加此参数
xtrabackup 恢复时磁盘满	准备阶段需要额外空间	预留 20% 磁盘空间
Binlog 太大	`max_binlog_size` 默认 1G	设置 `expire_logs_days` 自动清理
慢查询日志拖垮磁盘 IO	日志量太大	采样（`log_slow_rate_limit`）+ 控制阈值
恢复时字符集不一致	备份和恢复环境不同	统一 `character_set_server=utf8mb4`

关联知识

MySQL 体系架构与存储引擎 — Redo Log 是 xtrabackup 一致性快照的物理基础
MySQL 索引原理与优化 — 慢查询分析 → EXPLAIN → 索引优化闭环
MySQL 主从复制与高可用 — 从 Slave 备份 vs 从 Master 备份

参考资源

Percona XtraBackup 文档：https://docs.percona.com/percona-xtrabackup/8.0/
pt-query-digest 手册：https://docs.percona.com/percona-toolkit/pt-query-digest.html

学习时间

阶段	时间	备注
初次学习	2026-07-14	xtrabackup + PITR + 慢查询分析

状态: 📖 已掌握 下次复习日期: 2026-08-14

MySQL 备份恢复与慢查询优化

概述

备份策略

备份类型对比

推荐组合策略

mysqldump 常用命令

xtrabackup 生产级备份脚本

恢复流程

备份 Checklist

慢查询分析

开启慢查询日志

慢查询日志内容

pt-query-digest 分析慢查询

pt-query-digest 输出解读

实时慢查询定位

常见问题 / 坑点

关联知识

参考资源

学习时间