备份恢复与性能调优

备份策略

备份类型

类型	说明	特点
全量备份	备份整个数据库	恢复简单，耗时长、占空间
增量备份	备份上次备份后的变更	速度快、空间小，恢复需依赖全量
差异备份	备份上次全量后的变更	介于两者之间

推荐方案

周日 02:00  全量备份（XtraBackup）
周一 ─ 周六 02:00  增量备份（binlog）

mysqldump

适合小数据量或逻辑备份：

# 备份单库
mysqldump -u root -p --single-transaction --routines --triggers dbname > backup.sql

# 压缩备份
mysqldump ... | gzip > backup.sql.gz

# 恢复
mysql -u root -p dbname < backup.sql

--single-transaction 保证 InnoDB 一致性读，不锁表。

XtraBackup

适合大数据量的物理备份（Percona 出品）：

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

# 准备恢复
xtrabackup --prepare --target-dir=/backup/full

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

binlog 增量备份

-- 查看当前 binlog 文件
SHOW MASTER STATUS;

-- 导出 binlog 为 SQL
mysqlbinlog binlog.000001 > incremental.sql

-- 从指定位置导出
mysqlbinlog --start-position=12345 binlog.000001 > incremental.sql

-- 按时间范围导出
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-02 00:00:00"  \
            binlog.000001 > recovery.sql

---

性能调优

内存相关参数

参数	说明	建议值
innodb_buffer_pool_size	InnoDB 缓冲池大小	物理内存的 60%-80%
innodb_buffer_pool_instances	缓冲池实例数	每个实例 ≥ 1GB
innodb_log_file_size	Redo Log 文件大小	1GB-4GB
innodb_log_buffer_size	Redo Log 缓冲区	16MB-64MB
query_cache_type	查询缓存	8.0 已移除，设为 0

[mysqld]
# 假设物理内存 16GB
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M

日志与 I/O 相关参数

参数	说明	建议值
innodb_flush_log_at_trx_commit	日志刷新策略	1（安全）/ 2（性能）
innodb_flush_method	数据刷新方式	O_DIRECT
sync_binlog	binlog 同步策略	1（安全）/ N（性能）

innodb_flush_log_at_trx_commit 取值：

值	说明	安全	性能
1	每次事务提交都刷盘	✅ 最高	最慢
2	每秒刷盘（默认）	⚠️ 崩盘丢 1s 数据	较快

连接相关参数

参数	说明	建议值
max_connections	最大连接数	200-1000
wait_timeout	非交互连接超时	300s
interactive_timeout	交互连接超时	300s
thread_cache_size	线程缓存数	64-128

---

监控方案

Prometheus + MySQL Exporter

MySQL → MySQL Exporter → Prometheus → Grafana

部署步骤：

# 1. 启动 MySQL Exporter
docker run -d \
  --name mysql_exporter \
  -p 9104:9104 \
  -e DATA_SOURCE_NAME="user:password@tcp(mysql_host:3306)/" \
  prom/mysqld-exporter

# 2. Prometheus 配置添加 target
# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']

# 3. Grafana 导入仪表盘（ID: 7362）

关键监控指标

指标	说明	关注阈值
mysql_global_status_threads_connected	当前连接数	> 80% max_connections
mysql_global_status_innodb_buffer_pool_pages_free	Buffer Pool 空闲页	< 10%
mysql_global_status_questions	QPS 查询量	环比异常增长
mysql_global_status_slow_queries	慢查询数量	持续增长
Seconds_Behind_Master	主从延迟	> 10s

---

常见性能瓶颈与排查

1. 查询慢

排查步骤：

1. 开启慢查询日志
2. EXPLAIN 分析执行计划
3. 检查索引使用情况
4. 优化 SQL（参见索引优化篇）

2. CPU 飙升

可能原因：

• 大量慢查询
• 无索引导致的全表扫描
• 排序操作（Using filesort）

查看当前执行中的查询：

SHOW FULL PROCESSLIST;

3. 连接数打满

排查：

-- 查看当前连接状态
SHOW STATUS LIKE 'Threads_connected';

-- 查看各 IP 连接数
SELECT host, COUNT(*) FROM information_schema.PROCESSLIST GROUP BY host;

-- 查看各用户连接数
SELECT user, COUNT(*) FROM information_schema.PROCESSLIST GROUP BY user;

4. I/O 瓶颈

排查：

• 检查 Buffer Pool 命中率
• 检查 Redo Log 是否过小（频繁切换）
• 考虑升级 SSD 或增加内存

-- Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 公式: (read_requests - reads) / read_requests * 100%
-- 命中率应 > 99%

5. 死锁

-- 查看最近死锁
SHOW ENGINE INNODB STATUS;

预防：

• 缩短事务执行时间
• 保持一致的加锁顺序
• 适当降低隔离级别（使用 RC）