mysql备份详解

mysqldump工具

介绍

mysqldump是一个逻辑备份工具，其备份的是SQL语句。

mysqldump备份方式:

1. 对于InnoDB存储引擎的表: 可以采取快照备份的方式，在备份期间可以开启一个独立的事务，获取当前最新的一致性快照，将快照数据放在临时表中，而后转换为SQL语句(比如DDL,DML等语句)并保存到文件中。综上所述，mysqldump工具在备份时不需要锁原来的表，因为它只是一个读的操作。因此也不会影响到其他事物。
2. 对于非InnoDB存储引擎的表: 需要进行短暂全局锁表才能进行备份。即触发"Flush Table With Read Lock"(简称"FTWRL")机制。将备份的数据放在临时表中，转换为SQL语句(比如DDL,DML等语句)并保存到文件中。因为非InnoDB存储引擎并不支持事务，它没有快照功能。所以只能锁表备份，相当于"温备份"(即可以查询但不能修改)。

注意：

1. 生产环境中对于非InnoDB存储引擎的表相对来说比较少，大多数都是MySQL内置的系统表，因此锁表的时间并不会特别长;
2. 综上所述，我们不能说使用mysqldump工具在备份时不锁表，除非是在单独备份InnoDB的表。因为对于非InnoDB(例如MyIsam)的表进行备份会触发全局锁表FTWRL机制;

mysqldump工具的使用场景

建议选择数据量较小的场景，比如100GB左右的数据选择mysqldump是一个不错的方案，如果数据量较大，比如超过200GB的话，建议采用物理备份，而当数据量巨大时(通常指TB级别甚至更大数据量场景)建议采用分布式架构集合mysqldump工具进行备份。
如果数据在100GB以内，使用mysqldump备份时间大概在1小时以内，就算数据量能达到200GB左右，备份时间应该也控制在1-2小时之间。在生产环境中，恢复时间可能是备份时间的双倍是很常见的情况。

mysqldump工具的优缺点:

1. 优点:
   • 可读性比较强;
   • 相比物理备份压缩比更高，即节省存储空间;
   • MySQL内置的工具，无需下载安装;
   • 相比物理备份移植性更强，可以跨存储引擎;
2. 缺点:
   • 相比物理备份，逻辑备份消耗的时间相对较长;
   • 恢复时间更长，通常是备份时间的双倍甚至更长的时间;

mysqldump工具的核心参数

# 连接参数：
-u: 指定连接MySQL服务端的用户名。
-p: 指定连接MySQL服务端的用户名所对应的密码。
-h: 指定连接MySQL服务端的主机地址。
-P: 指定连接MySQL服务端的端口。
-S: 指定连接MySQL服务端的本地套接字文件，和mysql工具类似，该参数通常是在MySQL服务器端本地操作时使用。# 备份参数:
-A: 全量备份
-B: 部分备份数据库。
注意：
mysqldump -B  test >  test.sql # 语句会比mysqldump test > test2.sql多出两条SQL，即CREATE DATABASE test;和 use test，会创建"test"数据库，并备份"test"数据库下的所有表。
mysqldump test > test2.sql # 备份"test"数据库下的所有表，但并不会创建"test"数据库。# 备份表: 如果只备份某个数据库下的某张表或多张表，则无需指定任何参数，语法格式为: "数据库名称 表1[ 表2 表3 ...]"。# 常用参数：
--master-data: 自动记录备份时 MySQL 二进制日志（binlog）的文件名和位置，这是主从复制场景中 “基于备份搭建从库” 的关键配置，避免了手动查询 binlog 位置的误差。该参数有三个值可选，即0，1，2。
1. 当选项的值为0时: 若不指定则默认值就为0，即表示不记录备份时的日志的位置信息，不会自动加锁,适用于普通备份。
2. 当选项的值为1时: 会将"CHANGE MASTER TO ..."命令写入到备份文件中，自动触发 --lock-all-tables（全局读锁），使用于搭建从库时（需直接复用备份文件导入）。
3. 当选项的值为2时: 将"CHANGE MASTER TO ..."命令以注释的方式写入到备份文件中。自动触发 --lock-all-tables（全局读锁）生产环境中，在主从搭建的场景下，通常也是将"--master-data"设置为2。
注意！！！
--master-data=1/2 会自动启用 --lock-all-tables，对所有库的所有表加全局读锁（FTWRL），直到备份开始后（InnoDB 表在快照建立后会释放锁，MyISAM 表需锁至备份结束）。
对于生产环境中的InnoDB存储引擎没有必要给所有的表加锁，因此我们可以考虑使用"--single-transaction"来控制以减少锁表时间。--single-transaction: 该参数是为 InnoDB 存储引擎设计的，利用其 MVCC（多版本并发控制）特性，通过开启一个独立事务获取 “一致性快照”，实现 “备份不锁表（或仅短时间锁表）”，极大降低对生产业务的影响。
核心原理与优势：
1. 备份开始时，MySQL 会开启一个事务，并执行 START TRANSACTION WITH CONSISTENT SNAPSHOT（InnoDB 特有），获取当前时间点的 “数据快照”。
2. 备份过程中，InnoDB 表通过快照读取历史版本数据，无需加表锁 / 行锁，业务可正常读写（写入的新数据会生成新版本，不影响快照）。
3. 仅在事务启动瞬间，会对所有表加极短时间的 “意向共享锁”（IS 锁），用于确认表结构无变更，几乎不影响业务。
关键注意事项：
1. 严禁 DDL 操作：备份期间若有其他连接执行 ALTER TABLE、DROP TABLE、RENAME TABLE 等 DDL 语句，会破坏一致性快照，导致备份失败或数据不一致（InnoDB 无法对 DDL 做 MVCC 隔离）。
2. 仅支持 InnoDB：对 MyISAM、MEMORY 等非事务引擎，--single-transaction 无效，仍需依赖 --lock-tables 或 --lock-all-tables 保证一致性（否则备份数据会错乱）。
3. 与 --master-data 的搭配：生产环境中，InnoDB 主库备份常用组合是 --master-data=2 --single-transaction，既记录 binlog 位置（用于搭建从库），又避免全局读锁对业务的影响（仅快照建立瞬间短锁）。-R: 在备份时一起备份存储过程和函数。
-E: 备份事件。
--triggers: 备份触发器。
--max_allowed_packet: 既属于MySQL服务端参数，也属于MySQL客户端参数.如果客户端执行DML语句，数据由客户端发往服务端，比如INSERT超过1000w条数据，如果服务端设置"max_allowed_packet"过小就会抛出异常。
而MySQL5.7及以下版本的服务端"max_allowed_packet"的默认值是4MB。
而MySQL8.0及以上版本的服务端"max_allowed_packet"的默认值是64MB。

全量备份案例

mysqldump -uroot -p'1qaz@WSX' -A > ~/all.sql

只备份部分数据库案例

mysqldump -uroot -p'1qaz@WSX'  -B school world  > ~/db.sql

只备份某个数据库的单表或多表

mysqldump -uroot -p'1qaz@WSX' school course student > ~/school-course_student.sql

对于InnoDB存储引擎表备份时，开启一个独立事务，获取一致性快照，进行备份

mysqldump -uroot -p'1qaz@WSX' -A --master-data=2 --single-transaction > ~/all.sql
# 在备份InnoDB存储引擎的表时，我们通常会将"--master-data"和"--single-transaction"两个参数搭配使用

在备份时一起备份存储过程，函数，事件，和触发器

mysqldump -uroot -p'1qaz@WSX' -A --master-data=2 --single-transaction -E -R --triggers > ~/all.sql

备份时指定客户端接收数据包大小限制

mysqldump -uroot -p'1qaz@WSX' -A --master-data=2 --single-transaction -E -R --triggers --max_allowed_packet=64M > ~/all.sql# max_allowed_packet参数无论是在MySQL客户端还是在MySQL服务端，都有同名参数控制，如果在"[mysqld]"标签中设置，表示配置的是服务端，这意味着客户端执行DML操作，允许最大的packet数默认是4MB。
# --max_allowed_packet参数在mysqldump通常设置64MB即可，如果数据表较大，也可以修改为128MB测试;
# 如果报错说是由于packet较大导致的错误要先分析到底是客户端还是服务端设置较小，找到原因后在还原即可;

基于mysqldump，mysqlbinlog工具进行恢复的思路

备份思路： mysqldump每天全备，binlog定时备份。

# 1. 先恢复全量备份
mysql> SET sql_log_bin=0;
mysql> SOURCE ~/full-2021-02-12.sql# 2. 查看现有的binlog事件日志位置信息，获取结束位置
# 查看目前写入的binlog文件
SHOW MASTER STATUS;
SHOW BINLOG EVENTS IN 'mysqld-binary.000019';
# 通过上述两条命令获取到获取需要binlog需要恢复的数据的开始位置和结束位置
# 3. 使用mysqlbinlog工具截取日志
# 生产环境中如果开启了GTID功能，切记要添加"--skip-gtids"参数
# 基于"--start-position"和"--stop-position"截取日志:
mysqlbinlog --skip-gtids --start-position=795 --stop-position=1221  mysqld-binary.000019 > /tmp/recover_demo.log# 基于"--include-gtids"截取日志:
mysqlbinlog --skip-gtids --include-gtids='ecaf563f-5345-11eb-a106-000c29820c67:104-105' mysqld-binary.000019 > /tmp/recover_demo2.log
# 4. 通过截取的日志文件"/tmp/recover_demo.log"恢复数据
mysql> SOURCE /tmp/recover_demo.log;

做全量备份实例（常用参数）

mysqldump -uroot -p'1qaz@WSX' -A --master-data=2 --single-transaction -E -R --triggers --max_allowed_packet=64M > ~/all.sql