MySQL 数据库备份与迁移

MySQL 数据库备份与迁移

数据库备份是运维中最重要的日常任务之一。本文详细介绍 MySQL 的备份与恢复方法，涵盖全量备份、增量备份、自动化脚本等场景。

全量备份与恢复

使用 mysqldump 备份

mysqldump 是 MySQL 自带的逻辑备份工具，将数据导出为 SQL 文件。

基本用法：

# 备份单个数据库
mysqldump -u root -p --databases mydb > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 备份指定表
mysqldump -u root -p mydb users orders > tables_backup.sql

推荐参数：

mysqldump -u root -p \
  --routines \              # 备份存储过程和函数
  --triggers \              # 备份触发器
  --events \                # 备份事件调度器
  --single-transaction \    # 使用事务确保一致性（InnoDB）
  --databases mydb \
  > backup.sql

关于 --single-transaction：对 InnoDB 表，它通过启动一个事务创建一致性快照，不会锁表，适合生产环境。注意此选项不适用于 MyISAM 等非事务引擎。

恢复数据

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

# 或通过 source 命令
mysql -u root -p
mysql> source /path/to/backup.sql

增量备份（基于 binlog）

开启 binlog

编辑 MySQL 配置文件 /etc/my.cnf 或 /etc/mysql/my.cnf：

[mysqld]
server-id = 1              # 必须设置，主从复制时保持唯一
log-bin = mysql-bin        # 开启 binlog，文件名前缀
binlog_format = ROW        # 推荐 ROW 格式，复制更可靠
expire_logs_days = 7       # 7 天后自动清理
max_binlog_size = 100M     # 每个日志文件 100M

重启 MySQL：

systemctl restart mysqld

验证：

SHOW VARIABLES LIKE '%log_bin%';

使用 binlog 恢复增量数据

# 查看 binlog 文件列表
mysqlbinlog /var/lib/mysql/mysql-bin.index

# 导出指定 binlog 中的 SQL
mysqlbinlog \
  --start-datetime="2026-06-01 00:00:00" \
  --stop-datetime="2026-06-02 00:00:00" \
  /var/lib/mysql/mysql-bin.000001 > incremental.sql

# 按位置恢复
mysqlbinlog \
  --start-position=0 \
  --database=mydb \
  /var/lib/mysql/mysql-bin.000001 > zengliang.sql

# 应用增量 SQL
mysql -u root -p < incremental.sql

自动化备份脚本

以下是一个每日自动备份的 Shell 脚本：

#!/bin/bash

# 配置
DB_USER="root"
DB_PASS="your_password"
DB_NAME="mydb"
BACKUP_DIR="/data/backup/mysql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines --triggers --events \
  --databases $DB_NAME \
  | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 删除旧备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

# 保留最近 7 天备份

配合 crontab 实现每日自动备份：

# 每天凌晨 2 点执行
0 2 * * * /usr/local/bin/backup_mysql.sh

数据库迁移

迁移到新服务器

# 1. 源服务器导出
mysqldump -u root -p --all-databases > all.sql

# 2. 压缩传输
gzip all.sql
scp all.sql.gz user@new-server:/tmp/

# 3. 目标服务器导入
gunzip all.sql.gz
mysql -u root -p < /tmp/all.sql

主从复制

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

-- 从库配置
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=0;

START SLAVE;
SHOW SLAVE STATUS\G

最佳实践

1. 定期验证备份：每月至少恢复一次备份到测试环境验证
2. 异地备份：将备份文件同步到其他机房或云存储
3. 监控备份：监控备份脚本执行状态，失败时自动告警
4. 版本管理：记录备份文件的元数据（数据库版本、备份时间等）

总结

备份是数据库管理的第一道防线。全量备份 + binlog 增量备份的组合策略可以覆盖大部分数据恢复场景。利用脚本 + crontab 实现自动化备份，配合定期恢复验证，确保数据安全可靠。