← 返回首页
🔧

MySQL 备份与恢复

📂 devops ⏱ 3 min 488 words
MySQL备份恢复数据安全mysqldump

MySQL 备份与恢复

备份类型

逻辑备份

使用 SQL 语句备份，文件为文本格式：

# mysqldump 备份
mysqldump -u root -p mydb > backup.sql

# 带时间戳的备份
mysqldump -u root -p mydb > "mydb_$(date +%Y%m%d_%H%M%S).sql"

物理备份

直接复制数据文件，速度更快：

# 使用 xtrabackup
xtrabackup --backup --target-dir=/backup/full

# 使用 mysqlhotcopy（仅 MyISAM）
mysqlhotcopy mydb /backup/

使用 mysqldump

基本备份

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

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

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

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 db3 > multi_backup.sql

高级选项

# 仅备份表结构
mysqldump -u root -p --no-data mydb > schema.sql

# 仅备份数据
mysqldump -u root -p --no-create-info mydb > data.sql

# 带触发器和存储过程
mysqldump -u root -p --triggers --routines --events mydb > full_backup.sql

# 压缩备份
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

# 远程备份
mysqldump -h 192.168.1.100 -u root -p mydb > remote_backup.sql

备份脚本

#!/bin/bash
# mysql_backup.sh

# 配置
DB_USER="backup_user"
DB_PASS="backup_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7

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

# 备份所有数据库
mysqldump -u $DB_USER -p$DB_PASS --all-databases \
  --triggers --routines --events \
  | gzip > "$BACKUP_DIR/all_databases_$DATE.sql.gz"

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

echo "备份完成: $BACKUP_DIR/all_databases_$DATE.sql.gz"

使用恢复

恢复数据库

# 恢复单个数据库
mysql -u root -p mydb < mydb_backup.sql

# 恢复压缩备份
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# 恢复所有数据库
mysql -u root -p < all_backup.sql

恢复特定表

# 恢复特定表（需要修改备份文件）
# 从备份中提取特定表
mysqldump -u root -p mydb users > users_only.sql
mysql -u root -p mydb < users_only.sql

增量备份

使用 binlog

# 启用 binlog
# 在 my.cnf 中添加
# log-bin=mysql-bin
# binlog_format=ROW

# 查看 binlog 文件
SHOW BINARY LOGS;

# 查看 binlog 内容
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
            --stop-datetime="2023-01-01 12:00:00" \
            mysql-bin.000001

# 使用 binlog 恢复
mysqlbinlog mysql-bin.000001 | mysql -u root -p

使用 xtrabackup 增量备份

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

# 增量备份
xtrabackup --backup --target-dir=/backup/inc1 \
           --incremental-basedir=/backup/full

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

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

定时备份

Cron 定时任务

# 编辑 crontab
crontab -e

# 每天凌晨 2 点备份
0 2 * * * /path/to/mysql_backup.sh

# 每小时备份
0 * * * * /path/to/mysql_backup.sh

Systemd Timer

# /etc/systemd/system/mysql-backup.service
[Unit]
Description=MySQL Backup

[Service]
Type=oneshot
ExecStart=/path/to/mysql_backup.sh

# /etc/systemd/system/mysql-backup.timer
[Unit]
Description=Run MySQL backup daily

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target

实践案例

完整备份脚本

#!/bin/bash
# comprehensive_backup.sh

# 配置
DB_HOST="localhost"
DB_USER="backup_user"
DB_PASS="backup_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql_backup.log"

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

# 创建备份目录
mkdir -p $BACKUP_DIR/{full,incremental,binlog}

log "开始备份..."

# 1. 逻辑备份
log "执行逻辑备份..."
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS \
  --all-databases \
  --triggers --routines --events \
  --single-transaction \
  | gzip > "$BACKUP_DIR/full/full_$DATE.sql.gz"

# 2. 备份 binlog
log "备份 binlog..."
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "FLUSH BINARY LOGS;"
mysqlbinlog --read-from-remote-server --host=$DB_HOST \
  --user=$DB_USER --password=$DB_PASS \
  mysql-bin.* > "$BACKUP_DIR/binlog/binlog_$DATE.sql"

# 3. 清理旧备份（保留 7 天）
log "清理旧备份..."
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
find $BACKUP_DIR -name "*.sql" -mtime +7 -delete

log "备份完成: $BACKUP_DIR/full/full_$DATE.sql.gz"

Docker 环境备份

# Docker 容器备份
docker exec mysql-container mysqldump -u root -p$MYSQL_ROOT_PASSWORD \
  --all-databases | gzip > backup.sql.gz

# 使用 Docker 卷备份
docker run --rm \
  -v mysql-data:/var/lib/mysql \
  -v $(pwd)/backup:/backup \
  mysql:8.0 \
  tar czf /backup/mysql-data.tar.gz /var/lib/mysql

监控备份

# 检查备份文件
ls -lh /backup/mysql/full/

# 验证备份完整性
gunzip -t backup.sql.gz

# 检查备份大小变化
du -sh /backup/mysql/full/*

常见问题

备份失败

# 检查权限
SHOW GRANTS FOR 'backup_user'@'localhost';

# 检查磁盘空间
df -h

# 检查错误日志
tail -f /var/log/mysql/error.log

恢复失败

# 检查备份文件格式
file backup.sql.gz

# 检查 MySQL 版本兼容性
mysql --version

最佳实践

总结

MySQL 备份是数据安全的重要保障。选择合适的备份策略和工具，定期执行备份并测试恢复流程，可以有效防止数据丢失。