MySQL 备份恢复实战

数据库备份是运维的基本功，数据丢了就找不回来了。

1. 备份方式

1）物理备份：

• 直接拷贝数据文件
• 速度快，适合大数据库

2）逻辑备份：

• 导出 SQL 语句
• 体积小，跨平台

2. mysqldump 备份

备份整个数据库：

mysqldump -u root -p 数据库名 > backup.sql

备份单个表：

mysqldump -u root -p 数据库名 表名 > table.sql

备份所有数据库：

mysqldump -u root -p --all-databases > all.sql

3. 恢复数据

mysql -u root -p 数据库名 < backup.sql

注意： 恢复前要先创建数据库。

4. 压缩备份

# 备份并压缩
mysqldump -u root -p 数据库名 | gzip > backup.sql.gz

# 恢复压缩文件
gunzip < backup.sql.gz | mysql -u root -p 数据库名

5. 定时备份脚本

#!/bin/bash
# 数据库备份脚本

DB_USER="root"
DB_PASS="123456"
DB_NAME="test"
BACKUP_DIR="/data/backup"
DATE=$(date +%Y%m%d)

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

# 备份
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/$DB_NAME_$DATE.sql.gz

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

加 crontab 定时执行：

# 每天凌晨3点备份
0 3 * * * /bin/bash /root/backup.sh

6. 常见问题

1）备份太大：

• 用 --quick 参数
• 压缩备份

2）备份报错：

• 检查用户名密码
• 检查权限

3）恢复失败：

• 检查 SQL 文件是否完整
• 检查数据库字符集

7. 最佳实践

• 每天定时备份
• 备份文件存到异地
• 定期测试恢复
• 重要数据做双备份

总结

MySQL 备份恢复要点：

• mysqldump 导出 SQL
• gzip 压缩减小体积
• crontab 定时执行
• 定期测试恢复

做运维，数据备份永远是第一位的。

MySQL 备份与恢复实战

数据库备份是运维的基本功，万一数据丢了，没有备份就完蛋了。这篇整理常用的备份恢复方法。

1. mysqldump 逻辑备份

最常用的备份工具，生成 SQL 文件。

备份整个数据库：

mysqldump -u root -p123456 blog > blog_$(date +%Y%m%d).sql

备份单张表：

mysqldump -u root -p123456 blog users > users.sql

备份所有数据库：

mysqldump -u root -p123456 --all-databases > all.sql

常用参数：

• --databases：指定多个数据库
• --single-transaction：InnoDB 一致性备份，不锁表
• --routines：备份存储过程和函数
• --triggers：备份触发器

2. 恢复数据

# 恢复整个数据库
mysql -u root -p123456 blog < blog_20261007.sql

# 登录后恢复
mysql -u root -p123456
mysql> use blog;
mysql> source /root/blog_20261007.sql;

3. 压缩备份

数据大的时候，直接存 SQL 太占空间，压缩一下：

# 备份并压缩
mysqldump -u root -p123456 blog | gzip > blog_$(date +%Y%m%d).sql.gz

# 恢复压缩备份
gunzip < blog_20261007.sql.gz | mysql -u root -p123456 blog

4. 全量+增量备份

大数据量不能每天全量备份，用 binlog 做增量。

全量备份：

mysqldump -u root -p123456 --single-transaction --master-data=2 blog > full.sql

增量恢复：

# 先恢复全量
mysql -u root -p123456 blog < full.sql

# 再按时间点恢复 binlog
mysqlbinlog --start-datetime="2026-10-07 10:00:00" \
            --stop-datetime="2026-10-07 14:00:00" \
            /var/lib/mysql/binlog.000001 | mysql -u root -p123456

5. 物理备份（冷备）

直接拷贝数据文件，速度快，但需要停库。

# 停库
systemctl stop mysqld

# 拷贝数据目录
cp -r /var/lib/mysql /backup/mysql_$(date +%Y%m%d)

# 启动
systemctl start mysqld

6. 定时备份脚本

写个脚本每天自动备份：

#!/bin/bash
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d)
USER="root"
PASS="123456"

# 创建目录
mkdir -p $BACKUP_DIR

# 备份并压缩
mysqldump -u$USER -p$PASS --single-transaction blog | gzip > $BACKUP_DIR/blog_$DATE.sql.gz

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

加到 crontab：

0 3 * * * /bin/bash /root/mysql_backup.sh

7. 恢复注意事项

• 恢复前先测试备份文件能不能用，别等真出事了才发现备份坏了
• 大表恢复慢，别在线上高峰期操作
• 字符集要一致，恢复前先检查
• 恢复前先备份当前数据，防止恢复出错

总结

MySQL 备份三件事：

• 定期备份，别偷懒
• 备份后要测试能不能恢复
• 重要数据异地备份

数据无价，备份一定要做好。

前言

数据库备份是生产环境的生命线。很多人没出事的时候不备份，出事了才后悔。本文讲清楚 MySQL 最常用的备份工具 mysqldump。

一、为什么要备份

• 误操作：手滑删了表、删了数据
• 服务器挂了：硬盘坏了、机器报废
• 被攻击： ransomware 加密了数据库
• 升级失败：新版本不兼容，数据回退

记住：没有备份的数据库，就是在裸奔。

二、mysqldump 是什么

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

优点：简单、通用、跨版本
缺点：大数据量慢

适合：中小数据库（< 50G）

三、备份数据库

1. 备份整个数据库

mysqldump -u root -p 数据库名 > backup.sql

输入密码后，就会生成 backup.sql 文件。

2. 备份所有数据库

mysqldump -u root -p --all-databases > all_backup.sql

3. 备份单个表

mysqldump -u root -p 数据库名 表名 > table_backup.sql

4. 备份多个表

mysqldump -u root -p 数据库名 表1 表2 > multi_backup.sql

四、恢复数据库

1. 恢复整个数据库

mysql -u root -p 数据库名 < backup.sql

注意：数据库要先存在。

CREATE DATABASE 数据库名;

2. 恢复所有数据库

mysql -u root -p < all_backup.sql

五、常用参数

--single-transaction

InnoDB 引擎下，保证备份一致性，不锁表：

mysqldump -u root -p --single-transaction 数据库名 > backup.sql

生产环境一定要加这个！

--routines

备份存储过程和函数：

mysqldump -u root -p --routines 数据库名 > backup.sql

--triggers

备份触发器：

mysqldump -u root -p --triggers 数据库名 > backup.sql

--events

备份事件调度器：

mysqldump -u root -p --events 数据库名 > backup.sql

--no-data

只备份表结构，不备份数据：

mysqldump -u root -p --no-data 数据库名 > schema.sql

--where

按条件备份：

mysqldump -u root -p 数据库名 表名 --where="id > 100" > partial.sql

六、完整备份命令

生产环境推荐用这个：

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  数据库名 > backup.sql

七、压缩备份

备份文件太大？压缩一下：

# 备份并压缩
mysqldump -u root -p 数据库名 | gzip > backup.sql.gz

# 解压并恢复
gunzip < backup.sql.gz | mysql -u root -p 数据库名

八、定时备份

写个脚本每天自动备份：

#!/bin/bash

# 备份目录
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d)

# 备份
mysqldump -u root -p密码 --single-transaction 数据库名 | gzip > $BACKUP_DIR/db_$DATE.sql.gz

# 删除 7 天前的备份
find $BACKUP_DIR -name "db_*.sql.gz" -mtime +7 -delete

加到 crontab：

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

九、常见坑

1. 备份不锁表：大表备份会锁表，一定要加 --single-transaction
2. 字符集不对：备份文件乱码，加 --default-character-set=utf8mb4
3. 没备份存储过程：导出后丢了，加 --routines
4. 备份没验证：备份完一定要试恢复，不然真出事的时候哭都来不及

十、备份策略

• 每天全量备份：小数据库
• 每周全量 + 每天增量：大数据库
• 异地备份：备份文件要存到别的服务器
• 定期演练恢复：每个月测一次能不能恢复

总结

MySQL 备份的核心要点：

1. mysqldump 是最常用的备份工具
2. 生产环境加 --single-transaction，不锁表
3. 备份要压缩，节省空间
4. 定时备份，自动删除旧备份
5. 备份一定要验证，能恢复才算备份成功

数据是公司最值钱的资产，备份这事儿千万别偷懒。