跳到主要内容

MySQL 生产性能调优与备份

MySQL 是最常用的关系型数据库。本文档主要记录了在 Linux 服务器上部署 MySQL 的生产环境核心参数、慢查询分析和常规备份方案（mysqldump）。

1. 核心性能调优参数（my.cnf）​

以下是 8C 16G 物理服务器上 MySQL 8.0 推荐的生产环境核心配置：

[mysqld]
# 基础目录配置
user = mysql
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 1. 内存核心缓冲区优化 (物理内存的 50% ~ 70%)
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8  # 降低锁争用，提高多线程并发性能

# 2. 日志与事务提交策略
innodb_flush_log_at_trx_commit = 1 # 双1配置（最安全，保证不丢数据）
sync_binlog = 1                    # 双1配置
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

# 3. 连接与缓存限制
max_connections = 2000             # 最大连接数
max_user_connections = 1500
thread_cache_size = 64
table_open_cache = 4000

# 4. 慢查询日志配置（分析 SQL 性能的终极利器）
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1.0              # 运行超过 1.0 秒的 SQL 会被记录
log_queries_not_using_indexes = 1  # 记录没有使用索引的查询

2. 慢查询日志分析（mysqldumpslow）​

当发现数据库 CPU 飙高时，应首先查看慢查询。

# 1. 查找访问次数最多、最慢的 10 个 SQL
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

# 2. 按照返回记录数最多的 5 个含有 LEFT JOIN 的 SQL
mysqldumpslow -s c -t 5 -g "left join" /var/log/mysql/mysql-slow.log

3. 生产备份方案​

运维的核心生命线是数据备份。建议每周做一次全量备份，每日做增量 binlog 备份。

导出：全量物理一致性热备份（mysqldump）​

# 使用 mysqldump 备份全库，排除锁表，保证事务一致性 (InnoDB)
mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --quick \
  --events \
  --routines \
  --triggers \
  --master-data=2 \
  | gzip > /backup/mysql/mysql_all_$(date +%F).sql.gz

导入：备份恢复验证​

# 解压并恢复数据
gunzip /backup/mysql/mysql_all_2026-07-19.sql.gz
mysql -u root -p < /backup/mysql/mysql_all_2026-07-19.sql