10. MySQL - 备份恢复
目录
备份的类型
- 热备：即在线备份，也就是在数据库正常运行时，进行数据备份，并且能够一致性恢复(只能是InnoDB存储引擎)，对业务影响非常小。
- 温备：锁表备份，只能查询不能修改(myisam存储引擎)，影响业务中的写入操作。
- 冷备：关闭数据库，在数据库没有任何变更的情况下，进行数据备份，业务停止。
备份方式及备份工具介绍
- 逻辑备份工具，基于SQL语句进行备份：
- mysqldump+mysqlbinlog：
- 优点：软件自带，无需安装；备份出来的是文本类型的SQL，可读性高，便于备份处理；压缩比高，节省磁盘空间。
- 缺点：依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较消耗资源，数据量大的话，效率比较低。
- 建议：100G以内的数据量级，可以使用mysqldump，超过TB以上，如果使用mysqldump的话，搭配分布式系统也行。
- mysqldump+mysqlbinlog：
- 物理备份工具，基于磁盘数据文件备份：
- xtrabackup(XBK):percona，第三方。
- 优点：类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高。
- 缺点：可读性差；压缩比低，需要更多的磁盘空间。
- 建议：数据量级在TB以上使用。
- MySQL企业版(MySQL Enterprise Backup，MEB)备份工具。
- xtrabackup(XBK):percona，第三方。
备份策略
备份方式：
- 全备，全量备份，备份所有数据。
- 增量，备份变化的数据。
备份周期：根据数据量设计备份周期：
- 数据量较大，例如周日全备，周一到周六增量备份。
- 数据量小的话，每天全备都行。
逻辑备份工具 - mysqldump
mysqldump是MySQL数据库自带的客户端备份工具
创建一个用于存储备份数据的目录:
[root@cs ~]# mkdir -p /data/mysql/3306/backup
[root@cs ~]# chown -R mysql:mysql /data/mysql/3306/backup
mysqldump备份中常用的参数(不完全)：
| P | Description | 必加参数 |
|---|---|---|
-A |
备份全部数据库数据 | |
-B |
备份指定数据库 | |
--triggers |
备份触发器 | Yes |
--routines,-R |
备份函数和存储过程 | Yes |
-events,-E |
备份事件 | Yes |
-F |
备份时，自动为每个数据库都刷新一个binlog日志文件 | |
--master-data=2 |
当--master-data=2时，将以注释的方式将position号和binlog的日志文件写入到备份文件中去 |
Yes |
--single-transaction |
该参数针对于InnoDB存储引擎，实现了快照备份(也可以称之为热备)，特点就是备份时不会锁表 | Yes |
--set-gtid-purged=OFF |
进行备份时，是否同时备份gtid，默认值是ATUO，等价于ON | |
--max-allowed-packet |
备份时，设置从mysqld接收和发送包的大小 |
普通参数
A : 全备参数
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A > /data/mysql/3306/backup/full.sql
# 如果开一个mysql,可以不用指定socket -S
[root@cs ~]# mysqldump -uroot -p123 -A > /data/mysql/3306/backup/full.sql
# 查看大小
[root@cs ~]# du -sh /data/mysql/3306/backup/*
158M /data/mysql/3306/backup/full.sql
B : 备份指定库
备份MySQL数据库中的指定库(一个或多个库，多个库以空格分隔)到本地指定文件中：
[root@cs ~]# mysqldump -uroot -p123 -B db1 db2 > /data/mysql/3306/backup/db.sql
备份指定表
别分指定表，也就是只备份指定数据库下的指定表(一个或多个表,空格分隔)：
[root@cs ~]# mysqldump -uroot -p123 -B db1 t1 t2 > /data/mysql/3306/backup/ts.sql
高级参数
1.用户针对某些表或者自定义的触发器和函数之类"程序"该怎么办？-- 备份时,无脑加下面三个参数
--triggers：触发器。--routines，简写-R，存储过程和函数。--events，简写-E，事件(调度器)。
例如:
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A --triggers -R -E >/data/mysql/3306/backup/full.sql
2.--master-data, 当--master-data=2时，将以注释的方式将position号和binlog的日志文件写入到备份文件中去：-- 备份时无脑加
例如:
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A --triggers -R -E --master-data=2 >/data/mysql/3306/backup/full.sql
# 前几行文件中会有position号和binlog的日志文件
[root@cs ~]# head -n 30 /data/mysql/3306/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
3.--single-transaction -- 备份时无脑加
该参数针对于InnoDB存储引擎，实现了快照备份(也可以称之为热备)，特点就是备份时不会锁表，备份时加上这个参数就行。
4.--set-gtid-purged
在开启GTID后，进行备份时，是否同时备份gtid，它有三个值：
- 默认的
--set-gtid-purged=auto，等价于on，主要应用于主从复制环境中。 --set-gtid-purged=OFF，可以在日常的备份中，只回复数据。
5.-F
备份时，自动为每个数据库都刷新一个binlog日志文件。
案例: 从删库到磁盘损坏
模拟故障
1. 模拟昨晚23点的全备
[root@cs ~]# mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction >/data/mysql/3306/backup/full.sql
2. 模拟白天的数据变化
create database b4;
use b4
create table a1(id int);
insert into a1 values(1),(2);
3. 有个家伙，手一抖，删库了
drop database b4;
4. 手一抖，又把磁盘搞坏了
# 模拟磁盘损坏
[root@cs backup]# rm -rf /data/mysql/3306/data/*
现在，请你根据现有全备+binlog将数据恢复到删除之前的时间节点。
故障恢复
1. 确认二进制日和全备文件是否存在
[root@cs 3306]# ll /data/mysql/3306/logs/binlog/
total 12
-rw-r-----. 1 mysql mysql 201 May 14 15:16 mysql-bin.000001
-rw-r-----. 1 mysql mysql 893 May 14 15:34 mysql-bin.000002
-rw-r-----. 1 mysql mysql 92 May 14 15:16 mysql-bin.index
[root@cs 3306]# ll /data/mysql/3306/backup/fu*
-rw-r--r--. 1 root root 60702743 May 14 15:29 backup/full.sql
2. 恢复前的准备工作
把数据库从新搭起来：
# 查看进程
[root@cs backup]# netstat -nlp|grep 330
# 1.杀死现在的mysql进程
[root@cs backup]# pkill mysqld
[root@cs backup]# netstat -nlp|grep 330
# 2.清空数据库原因数据
[root@cs data]# rm -rf /data/mysql/3306/data/*
[root@cs data]# ll /data/mysql/3306/data/
total 0
# 3.初始化数据库
[root@cs data]# mysqld --initialize-insecure --user=mysql --basedir=/opt/software/mysql --datadir=/data/mysql/3306/data
# 4.恢复原来的用户密码
[root@cs data]# mysqladmin -uroot -p password 123
3. 先根据全备恢复数据到昨天23点
# 关闭开启binlog日志
set sql_log_bin=0;
source /data/mysql/3306/backup/full.sql
set sql_log_bin=1;
4. 根据二进制日志恢复从昨天23点到今天数据库删库之前的数据
定位pos的起点,终点
起始position号：
[root@cs ~]# head -n 40 /data/mysql/3306/backup/full.sql
...
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
# 日志文件时mysql-bin.000002, 起点pos为154
终点position号:
3306 [world]>show binlog events in "mysql-bin.000002";
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000002 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '95f21511-b12d-11eb-9b52-000c29b5bdfc:1' |
| mysql-bin.000002 | 219 | Query | 6 | 307 | create database b4 |
| mysql-bin.000002 | 307 | Gtid | 6 | 372 | SET @@SESSION.GTID_NEXT= '95f21511-b12d-11eb-9b52-000c29b5bdfc:2' |
| mysql-bin.000002 | 372 | Query | 6 | 465 | use `b4`; create table a1(id int) |
| mysql-bin.000002 | 465 | Gtid | 6 | 530 | SET @@SESSION.GTID_NEXT= '95f21511-b12d-11eb-9b52-000c29b5bdfc:3' |
| mysql-bin.000002 | 530 | Query | 6 | 600 | BEGIN |
| mysql-bin.000002 | 600 | Table_map | 6 | 643 | table_id: 330 (b4.a1) |
| mysql-bin.000002 | 643 | Write_rows | 6 | 688 | table_id: 330 flags: STMT_END_F |
| mysql-bin.000002 | 688 | Xid | 6 | 719 | COMMIT /* xid=4763 */ |
| mysql-bin.000002 | 719 | Gtid | 6 | 784 | SET @@SESSION.GTID_NEXT= '95f21511-b12d-11eb-9b52-000c29b5bdfc:4' |
| mysql-bin.000002 | 784 | Query | 6 | 870 | drop database b4 |
| mysql-bin.000002 | 870 | Stop | 6 | 893 | |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
14 rows in set (0.01 sec)
# 终点在删库之前,pos为719
截取日志,恢复数据：
# 截取日志
[root@cs ~]# mysqlbinlog --start-position=154 --stop-position=719 --skip-gtids /data/mysql/3306/logs/binlog/mysql-bin.000002 > /tmp/b2.sql
[root@cs ~]# ll /tmp/b*
-rw-r--r--. 1 root root 2158 May 14 15:57 /tmp/b2.sql
# 恢复数据,关闭开启binlog日志
set sql_log_bin=0;
source /tmp/b2.sql
set sqlin=1;
物理备份工具 - xtrabackup(XBK)
三方工具下载安装
xtrabackup是Perl语言开发，所以还需要配置相关环境和依赖：
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev -y
# 如果没有wget,就安装wget
yum install -y wget
然后下载安装即可：
# 下载或上传RPM包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
# 使用yum安装,可以安装没有的相关依赖
yum install -y percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
# 判断是否安装成功
[root@cs ~]# innobackupex --version
必要的修改
xtrabackup在执行innobackupex命令时，会自动使用MySQL的socket文件，但默认它去找'/var/lib/mysql/mysql.sock'文件，而我们的socket文件在/tmp下，所以，还需要对MySQL的客户端进行一些参数配置：
[root@cs ~]# vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock
无需重启MySQL服务。
备份方式
物理备份
xtrabackup采取的备份方式类似于cp命令，直接进行物理拷贝数据文件，与此同时，也会拷贝走undo log和redo log。
- 对于非InnoDB表来说，如myisam表，它在备份时会先锁表，然后
cp数据文件，这种形式属于温备的备份方式。 - 对于InnoDB表(支持事务的)来说，不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，算是热备的备份方式。
面试题：xtrabackup在InnoDB表备份的恢复流程
- xtrabackup备份执行的瞬间,立即触发ckpt，将已提交的数据脏页，从内存刷写到磁盘，并记录此时的LSN号
- 备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走，也就是checkpoint LSN之后的日志
- 在恢复之前，模拟Innodb"自动故障恢复"的过程，将redo（前滚）与undo（回滚）进行应用
- 恢复过程是
cp备份到原来数据目录下
1. 简单的全备示例
全备命令:
# 遇事不决，记得help
innobackupex --help
# 1.备份出的文件目录名称是日期形式的
[root@cs ~]# innobackupex --user=root --password=123 /data/mysql/3306/backup/xbk
[root@cs ~]# ll /data/mysql/3306/backup/xbk
# 2.自定义备份目录名full --no-timestamp
[root@cs ~]# innobackupex --user=root --password=123 --no-timestamp /data/mysql/3306/backup/xbk/full
备份产生的目录，我们称之为备份集。
xtrabackup_binlog_info:文件记录的是备份时的二进制日志的position号和GTID号(如果有的话)。
[root@cs full]# cat xtrabackup_binlog_info
mysql-bin.000003 194 98ddc71a-b12d-11eb-b85f-000c29b6f740:1-10
xtrabackup_checkpoints
[root@cs full]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 484039725
last_lsn = 484039734
compact = 0
recover_binlog_info = 0
各字段：
backup_type：备份类型，默认是全备。from_lsn：备份时的lsn号从哪个位置开的:- 全备，从0开始的。
- 如果是增量备份，该值是
last_lsn减9，也就是和to_lsn相等。
to_lsn：当备份命令开始执行时，记录的lsn号。last_lsn：备份结束时的lsn号。如果备份过程中，数据没有变更，那么last_lsn和to_lsn号差9(MySQL5.6这两个是一致的，但5.7中，备份自己使用了9个lsn号，所以二者差9)。
xtrabackup_info : 文件是备份时的各种信息的汇总。
根据全备恢复数据
模拟数据库全崩然后根据全备进行数据恢复
# 搞崩数据库
[root@cs ~]# pkill mysqld
# 磁盘损坏,清空数据
[root@cs ~]# rm -rf /data/mysql/3306/data/*
# 查看进程
[root@cs ~]# netstat -lnp|grep 330
- 在恢复之前，模拟Innodb"自动故障恢复"的过程，将redo（前滚）与undo（回滚）进行应用
那这里，xtrabackup也有相应的参数帮我们来做这件事：
[root@cs ~]# innobackupex --apply-log /data/mysql/3306/backup/xbk/full/
# 然后进行备份恢复就好了
[root@cs ~]# innobackupex --copy-back /data/mysql/3306/backup/xbk/full/
# 查看恢复数据
[root@cs ~]# ll /data/mysql/3306/data/*
但有个问题，就是你执行恢复命令的时候，使用的是root用户，所以，再启动MySQL之前，还需要进行授权：改成mysql
[root@cs ~]# chown -R mysql:mysql /data/mysql/3306/data/*
然后,重启服务,
[root@cs ~]# systemctl start mysqld
# 查看是否恢复数据数据
[root@cs ~]# mysql -uroot -p123 -e "show databases;"
2.增量备份和恢复
增量备份依赖全备。如现在的备份规则是周一到周六是增量备份，周日是全备
准备环境:
# 为了后续方便，删除之前的全备数据
rm -rf /data/mysql/3306/backup/xbk/*
# 将binlog日志清空
[root@cs ~]# mysql -uroot -p123
mysql> reset master;
mysql> show master status; -- 查看当前使用的binlog日志文件
1. 模拟周日之前的数据变更,并模拟上周日晚23点的全备
# 模拟数据变更 mysql>
create database y1 charset utf8;
use y1
create table a1(id int);
insert into a1 values(1),(2),(3);
# 模拟周日全备 [root@cs ~]#
innobackupex --user=root --password=123 --no-timestamp /data/mysql/3306/backup/xbk/full/
ll /data/mysql/3306/backup/xbk/ # 查看备份
2.模拟周一的数据变化,做增备
# 模拟数据变更 mysql>
create database y2 charset utf8;
use y2
create table a2(id int);
insert into a2 values(1),(2),(3);
# 模拟周一增备 [root@cs ~]#
innobackupex --user=root --password=123 --no-timestamp --incremental /data/mysql/3306/backup/xbk/inc1 --incremental-basedir=/data/mysql/3306/backup/xbk/full
ll /data/mysql/3306/backup/xbk/ # 查看备份
--incremental表示开启增量备份。/data/mysql/3306/backup/xbk/inc1是增量备份到指定目录。--incremental-basedir=/data/mysql/3306/backup/xbk/full表示，当前增量基于哪个全量日志。
3.模拟周二的数据变化,做增备
# 模拟数据变更 mysql>
create database y3 charset utf8;
use y3
create table a3(id int);
insert into a3 values(1),(2),(3);
# 模拟周二增备,基于周一数据做备份 [root@cs ~]#
innobackupex --user=root --password=123 --no-timestamp --incremental /data/mysql/3306/backup/xbk/inc2 --incremental-basedir=/data/mysql/3306/backup/xbk/inc1
ll /data/mysql/3306/backup/xbk/ # 查看备份
这里也要确认备份是否成功，查看列出了各自的checkpoints文件，注意观察几个lsn号的关系
4.到了周三的下午两点，数据库就又坏了....但在坏之前，还有数据变更
# 模拟数据变更 mysql>
create database y4 charset utf8;
use y4
create table a4(id int);
insert into a4 values(1),(2),(3);
# 模拟数据库损坏，这里模拟删除某个数据库
drop database y1;
根据增量备份恢复数据
首先：
- 增量备份不能单独恢复。
- 增量必须按照备份顺序合并到全备中，然后再根据全备恢复。
- 所有备份都需要进行
apply-log过程。 - 需要注意的是，除了最后一个增量备份不需要加
--redo-only，其他备份都需要加。
1. 数据恢复准备工作:
# 1. 按照顺序合并各个增量备份文件到全备文件中，除了最后一个增量备份文件不需要加--redo-only之外，其他都要加这个参数
# 整理周日的
innobackupex --apply-log --redo-only /data/mysql/3306/backup/xbk/full/
# 整理周一的
innobackupex --apply-log --redo-only --incremental-dir=/data/mysql/3306/backup/xbk/inc1 /data/mysql/3306/backup/xbk/full/
# 整理周二的,不需要加--redo-only
innobackupex --apply-log --incremental-dir=/data/mysql/3306/backup/xbk/inc2 /data/mysql/3306/backup/xbk/full/
# 最后对全备数据进行apply-log，现在的全备数据到周二晚上23点了
innobackupex --apply-log /data/mysql/3306/backup/xbk/full/
# 2. 截取周二23点到周三删库之前的binlog日志，根据position或者GTID都行
# 起点，在周二(前一天)的增量备份中：xtrabackup_binlog_info
[root@cs ~]# cat /data/mysql/3306/backup/xbk/inc2/xtrabackup_binlog_info
mysql-bin.000001 1903 2489f640-b6b2-11eb-8cca-000c29b5bdfc:1-9
# 终点,注意，uuid替换为你自己的uuid
show binlog events in "mysql-bin.000001";
# 截取日志文件
mysqlbinlog --skip-gtids --include-gtids="2489f640-b6b2-11eb-8cca-000c29b5bdfc:10-12" /data/mysql/3306/logs/binlog/mysql-bin.000001 >/tmp/inc2_bin.sql
2. 根据全备恢复数据：恢复到周二晚23点
# 1.清空MySQL的数据目录
[root@cs ~]# pkill mysqld
[root@cs ~]# rm -rf /data/mysql/3306/data/*
[root@cs ~]# ll /data/mysql/3306/data/
# 2.然后进行备份恢复就好了,授权
[root@cs ~]# innobackupex --copy-back /data/mysql/3306/backup/xbk/full/
[root@cs ~]# chown -R mysql:mysql /data/mysql/3306/data/*
[root@cs ~]# ll /data/mysql/3306/data/
# 3.重启服务
[root@cs ~]# systemctl start mysqld
3.根据binlog恢复数据：周二晚23点~删库之前
[root@cs ~]# mysql -uroot -p123
-- 开启关闭binlog日志,恢复数据
set sql_log_bin=0;
source /tmp/inc2_bin.sql;
set sql_log_bin=1;
