Mysql备份

MySQL 备份

一、备份分类

MySQL 备份的类型多种多样，用途与场景也不同。

1. 按备份内容

类型	说明
完全备份	备份整个数据库（表、数据、索引、数据库对象）
部分备份	只备份部分数据或部分表

2. 按备份方式

类型	说明	特点
逻辑备份	导出为 SQL 语句（mysqldump）	兼容性强、可读、体积大、适合中小库
物理备份	直接复制数据文件与日志文件	速度快、体积小，通常需停机或专业工具

3. 按备份状态

类型	数据库状态	特点
热备份	正常运行，可读写	不影响业务，需要工具支持
温备份	允许有限操作，可读、限制写	折中方案
冷备份	停止服务后备份	简单、一致性好，但期间不可用

这里整理了一个简略的知识导图：

---

二、两种热备份工具

本节主要讲两种热备份方式的备份工具：mysqldump（逻辑备份） 与 XtraBackup（物理备份）。

1. mysqldump 逻辑备份

定义：把数据库的结构和数据导出为 SQL 文本文件（可读）。

因为本质是逐行处理 SQL 文本文件，所以兼容性强、支持跨平台；同时也因此导致备份与恢复比较慢。适用于小型数据库（GB 级以下）。

优点：

• 文件可读、可编辑
• 可单表恢复（可以思考一下单表恢复方式）
• 易传输

缺点：

• 大库耗时长、恢复慢
• 占用 CPU / IO 较高

核心功能

• 备份整个数据库、单个表、多个表，或按条件备份部分数据
• 支持导出表结构（CREATE 语句）和数据（INSERT 语句）
• 可一并备份存储过程、函数、触发器、事件等对象

核心参数详解

参数	说明
--single-transaction	事务快照保证一致性，仅对 InnoDB 有效，不锁表
--lock-tables	备份期间锁定所有表；MyISAM 需用此参数保证一致性，但会阻塞写入
--skip-lock-tables	不锁表，适合允许写入的场景，但可能数据不一致
--routines	包含存储过程和函数
--events	包含事件（定时任务）
--triggers	包含触发器
--no-data	仅备份表结构，不备份数据
--no-create-info	仅备份数据，不备份表结构
--databases	备份多个库，导出文件含 CREATE DATABASE
--all-databases	备份所有库
--ignore-table=库.表	忽略指定表，可重复使用
--where="条件"	按条件备份部分数据（对该次备份的所有表生效）
--default-character-set=utf8mb4	指定字符集，避免中文乱码
--source-data=2	以注释形式记录 binlog 位置，便于按时间点恢复
--set-gtid-purged=OFF	避免 GTID 相关信息导致导入报错

常用组合：--single-transaction --routines --triggers --events --default-character-set=utf8mb4

备份基本语法

mysqldump -u用户名 -p 选项 数据库名 [表名...] > 备份文件.sql

参考示例：

# 单个数据库(推荐参数组合)
mysqldump -uroot -p --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 mydb > /backup/mydb_full_$(date +%F).sql

# 多个数据库
mysqldump -uroot -p --databases db1 db2 > /backup/dbs_$(date +%F).sql

# 所有数据库
mysqldump -uroot -p --all-databases --single-transaction \
  --routines --triggers --events --default-character-set=utf8mb4 \
  > /backup/all_dbs_$(date +%F).sql

# 单个表
mysqldump -uroot -p mydb table1 > /backup/mydb_table1.sql

# 多个表(带条件,只备份 id < 1000 的数据)
mysqldump -uroot -p --where="id < 1000" mydb table1 table2 > /backup/mydb_tables_partial.sql

# 忽略某张表
mysqldump -uroot -p --ignore-table=mydb.logs mydb > /backup/mydb_no_logs.sql

# 仅备份表结构 / 仅备份数据
mysqldump -uroot -p --no-data mydb        > /backup/mydb_schema.sql
mysqldump -uroot -p --no-create-info mydb > /backup/mydb_data.sql

# 压缩备份,节省空间
mysqldump -uroot -p --single-transaction mydb | gzip > /backup/mydb_full_$(date +%F).sql.gz

# 记录 binlog 位置(便于后续增量恢复)
mysqldump -uroot -p --single-transaction --source-data=2 mydb > /backup/mydb_full.sql

怎么恢复

如果是恢复数据库，没有数据库得先创建数据库，因为 SQL 语句里没有建库语句；然后再恢复。恢复的语法只需要把 > 倒过来即可。

# 恢复完整数据库(库不存在时先创建)
mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -p --default-character-set=utf8mb4 mydb < /backup/mydb_full.sql

# 恢复压缩备份
gunzip < /backup/mydb_full.sql.gz | mysql -uroot -p mydb

# 恢复全部数据库
mysql -uroot -p < /backup/all_dbs.sql

恢复单个表：

mysqldump -uroot -p mydb table1 > table1.sql
mysql -uroot -p mydb < table1.sql

那如果只有整库备份文件，怎么恢复单表呢？直接从全量备份文件里用 sed 截取单表语句即可：

sed -n '/^-- Table structure for table `table1`/,/^-- Table structure for table/p' mydb_full.sql > table1.sql
mysql -uroot -p mydb < table1.sql

但需要注意：截取法依赖备份文件的标准格式，表名和反引号要写对；条件允许时优先用单独备份的方式。

验证备份是否可用

备份好后我们还需要验证备份是否可用，这样备份才算闭环。

# 文件大小是否正常
ls -lh /backup/mydb_full_2026-09-12.sql

# 统计建表语句数量
grep -c "CREATE TABLE" /backup/mydb_full_2026-09-12.sql

# 恢复到测试库验证(最可靠)
mysql -uroot -p -e "CREATE DATABASE mydb_verify;"
mysql -uroot -p mydb_verify < /backup/mydb_full_2026-09-12.sql
mysql -uroot -p -e "USE mydb_verify; SHOW TABLES;"

---

2. XtraBackup（物理备份）

先出到这，下次一定补