mysql备份详解
mysqldump工具
介绍
mysqldump是一个逻辑备份工具，其备份的是SQL语句。
mysqldump备份方式:
- 对于InnoDB存储引擎的表: 可以采取快照备份的方式，在备份期间可以开启一个独立的事务，获取当前最新的一致性快照，将快照数据放在临时表中，而后转换为SQL语句(比如DDL,DML等语句)并保存到文件中。综上所述，mysqldump工具在备份时不需要锁原来的表，因为它只是一个读的操作。因此也不会影响到其他事物。
- 对于非InnoDB存储引擎的表: 需要进行短暂全局锁表才能进行备份。即触发"Flush Table With Read Lock"(简称"FTWRL")机制。将备份的数据放在临时表中，转换为SQL语句(比如DDL,DML等语句)并保存到文件中。因为非InnoDB存储引擎并不支持事务，它没有快照功能。所以只能锁表备份，相当于"温备份"(即可以查询但不能修改)。
注意：
- 生产环境中对于非InnoDB存储引擎的表相对来说比较少，大多数都是MySQL内置的系统表，因此锁表的时间并不会特别长;
- 综上所述，我们不能说使用mysqldump工具在备份时不锁表，除非是在单独备份InnoDB的表。因为对于非InnoDB(例如MyIsam)的表进行备份会触发全局锁表FTWRL机制;
mysqldump工具的使用场景
建议选择数据量较小的场景，比如100GB左右的数据选择mysqldump是一个不错的方案，如果数据量较大，比如超过200GB的话，建议采用物理备份，而当数据量巨大时(通常指TB级别甚至更大数据量场景)建议采用分布式架构集合mysqldump工具进行备份。
如果数据在100GB以内，使用mysqldump备份时间大概在1小时以内，就算数据量能达到200GB左右，备份时间应该也控制在1-2小时之间。在生产环境中，恢复时间可能是备份时间的双倍是很常见的情况。
mysqldump工具的优缺点:
- 优点:
- 可读性比较强;
- 相比物理备份压缩比更高，即节省存储空间;
- MySQL内置的工具，无需下载安装;
- 相比物理备份移植性更强，可以跨存储引擎;
- 缺点:
- 相比物理备份，逻辑备份消耗的时间相对较长;
- 恢复时间更长，通常是备份时间的双倍甚至更长的时间;
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定时备份。
# 禁止写入binlog，避免恢复过程本身产生的日志污染binlog
SET sql_log_bin=0;
# 恢复全量备份
mysql -uroot -p1qaz@WSX < all.sql
# 确认 binlog 文件和位置
# 起始位置：一般是全量备份结束时的 binlog pos
cat all.sql |head -n 50 |grep CHANGE
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=156;
# 结束位置：你需要恢复到的时刻（比如误操作前一条 DDL/DML 的前位置）
SHOW MASTER STATUS; # 当前正在写的binlog文件名、Position
SHOW BINLOG EVENTS IN 'binlog.000005' LIMIT 10; # 查看事件，定位起始点和结束点
# 2. 查看现有的binlog事件日志位置信息，获取结束位置
# 查看目前写入的binlog文件
SHOW MASTER STATUS;
SHOW BINLOG EVENTS IN 'binlog.000005';
# 通过上述两条命令获取到获取需要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
MySQL物理备份工具Percona Xtrabackup
Percona Xtrabackup简介
Percona Xtrabackup（简称PXB）工具采用perl语言开发。它是一款物理备份工具，简单来讲就是用来拷贝Linux本地的数据文件。因此使用它备份MySQL数据瓶颈在于磁盘的I/O速度。
对于InnoDB存储引擎的表:
(1)Percona Xtrabackup工具采用热备份方式，即无需锁表进行备份，这种备份方式在业务正常发生的时候，影响较小的备份方式。
(2)Percona Xtrabackup的工作流程如下所示:
1)先执行检查点(checkpoint)，将已提交的数据页刷写到磁盘，并记录一个LSN编号;
2)拷贝InnoDB表相关的文件，例如:ibdata1,".frm",".idb"等文件;
3)备份期间产生新的数据变化的redo也会备份走;
4)再次统计LSN编号，写入到专用文件;
5)将二进制日志位置记录下来;
6)将所有备份文件统一存放在一个目录下;
对于非InnoDB存储引擎的表:
(1)Percona Xtrabackup工具采用温备份方式，即需要暂时性锁表备份，对业务是有一定影响的。幸运的是，对于非InnoDB表相对较少，比如MySQL内置的系统表均是MyIsam，因此只会有短暂的锁表现象。
(2)Percona Xtrabackup的工作流程如下所示:
1)触发全局读锁(即"FTWRL")，而后执行检查点(checkpoint)，将已提交的数据页刷写到磁盘，并记录一个LSN编号;
2)拷贝非InnoDB表的数据;
3)拷贝数据完成后需要解锁;
4)将二进制日志位置记录下来;
5)将所有备份文件统一存放在一个目录下;
Percona XtraBackup工具的官方下载地址: https://www.percona.com/downloads/
Percona XtraBackup 2.4工具官方文档:https://www.percona.com/doc/percona-xtrabackup/2.4/index.html
基于yum的方式安装Percona XtraBackup工具
wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
yum -y install percona-xtrabackup-*.rpm
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
yum -y localinstall percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
Percona Xtrabackup全量备份
使用Percona Xtrabackup工具的前提
(1)启动数据库,因为XBK需要连接数据库去读取当前数据库实例有效的数据库名称及对应的表名称;
(2)能连接上数据库，还需要再"/etc/my.cnf"配置文件指定"client":
cat /etc/my.cnf
[client]
socket=/tmp/mysql23307.sock
(3)Percona Xtrabackup属于服务端工具，因此不要想着将它向mysqldump工具一样，安装再客户端也能进行备份操作，我们应该将Percona Xtrabackup工具直接安装在MySQL服务端实例上;
使用innobackupex命令全量备份
innobackupex /backup/xbk
使用此命令备份时：
使用innobackupex工具备份时可以自动创建"/backup/xbk"目录
备份的目录大小和源目录的大小并不相同
--no-timestap： 取消默认备份目录名称，而是手动指定备份目录名称
innobackupex --no-timestap /backup/xbk/full_date +%F
备份结果
在使用innobackupex命令备份完成之后，在查看备份目录的结构时，不难发现多出来了一些列以"xtrabackup_"开头的文件
xtrabackup_binlog_info：记录的是备份时二进制日志位置点信息:
xtrabackup_checkpoints：记录的是检查点日志信息，包含备份类型及LSN编号(方便后期做增量备份)等
xtrabackup_info：记录的总体的一些信息
xtrabackup_logfile：是一个二进制文件，使用文本工具打开强行打开也获取不到我们想要的信息，该文件记录的是Redo日志的信息
基于全量备份来恢复数据
innobackupex --apply-log /backup/xbk/2021-02-13_12-00-56/
cp -a /backup/xbk/2021-02-13_12-00-56/ /usr/local/mysql/data/
chown -R mysql:mysql /usr/local/mysql/data/
Percona Xtrabackup增量备份
XBK增量备份及恢复逻辑
备份时:
(1)增量备份必须依赖于全量备份;
(2)每次增量备份都是参照上一次备份的LSN号码，在此基础上变化的数据页才会备份;
(3)需要注意的是，会将备份过程中产生新的变化的Redo也一并备份走;
恢复时:
如下图所示，需要将所有需要的增量备份按顺序合并到全量备份中，并且需要将每个备份进行prepare后，才能后续的恢复操作。
温馨提示: 所谓的增量备份并不是指数据增加了就备份，数据被删除了就不备份。增量备份是针对LSN编号而言的，因为每个操作都唯一对应一个LSN编号，尽管删除了数据，这个LSN编号是在持续增大，而我们是已于LSN编号来进行备份，所以才有增量备份一说。
使用innobackupex命令增量备份
在做增量备份时，可能会用到以下几个参数:
innobackupex --no-timestamp --incremental --incremental-basedir=~/full_2021-02-13
--user='admin': 指定用户名为'admin'。
--password='1qaz!QAZ':指定用户名所对应的密码为'1qaz!QAZ'，请根据你数据库实际授权备份用户填写即可。
--no-timestamp:表示不使用默认的时间戳，我们可以自定义指定备份的路径及备份目录名称。
--incremental:表示启用增量备份功能。
--incremental-basedir=~/full_2021-02-13:指定基于哪个现有目录做增量备份，第一次做增量备份时，指定的目录应该为全量备份目录。换句话说，就是指定上一次备份的LSN编号的存储目录。
增量备份恢复案例
场景说明:现有某业务数据存储在xbk数据库中，周期性计划周日做全量备份，周一到周六做增量备份，我们此处请忽略备份损坏的情况，即假设所有的备份文件都是有效可用的。周三下午18:00有同学删除了xbk数据库。
恢复数据步骤参考思路:
mysql 5.7全量备份到指定目录:
xtrabackup --defaults-file=~/etc/mysql3307/my.cnf --backup --target-dir=/tmp/date +%F
mysql 5.7增量备份到指定目录:
(1)准备测试数据
mysql -S /tmp/mysql3307.sock < homework-sql-init
(2)全量备份
xtrabackup --defaults-file=/etc/mysql3307/my.cnf --backup --target-dir=/backup/movie
(3)模拟生产环境的数据
for i in `ls *.sql`;do mysql -S /tmp/mysql3307.sock < $i;done
mysql -S /tmp/mysql3307.sock -e "SELECT COUNT(*) FROM movie.user;"
(4)增量备份
xtrabackup --defaults-file=/etc/mysql3307/my.cnf --backup --incremental-basedir=/backup/movie --target-dir=/backup/movie_first
(5)再次修改数据
INSERT user VALUES (1,'孙悟空',3,5.8),(2,'猪八戒',10,7.3),(3,'唐僧',15,9.1);
(6)再次做增量备份
xtrabackup --defaults-file=/etc/mysql3307/my.cnf --backup --incremental-basedir=/backup/movie_first --target-dir=/backup/movie_second
(7)删除数据
DROP DATABASE movie;
(8)开始恢复
1)将最新的全量备份日志进行prepare整理
innobackupex --apply-log --redo-only movie
2)将第一次的(incremental)增量备份日志合并到最新的全量备份日志并进行prepare整理
innobackupex --apply-log --redo-only --incremental-dir=/backup/movie_first movie
3)将第二次的(incremental)增量备份日志合并到最新的全量备份日志并进行prepare整理,合并最后一次增量时无需使用"--redo-only"参数
innobackupex --apply-log --incremental-dir=/backup/movie_second movie
4)整体再次预处理(prepare)整个备份
innobackupex --apply-log movie
浙公网安备 33010602011771号