第七章-MySQL-备份恢复

weixin_46837396

于 2021-06-21 22:49:56 发布

阅读量95

点赞数

分类专栏： MySQL

本文为博主原创文章，未经博主允许不得转载

本文链接：https://blog.csdn.net/weixin_46837396/article/details/118096125

版权

MySQL 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

1. 运维在数据库备份恢复方面的职责

1.1 设计备份策略

全备、增量、时间、自动

1.2 日常备份检查

备份存在性
备份空间够用否

1.3 定期恢复演练(测试库)

一季度或者半年

1.4 故障恢复

通过现有备份,能够将数据库恢复到故障之前的时间点.

1.5 迁移

停机时间
回退方案

2. 备份类型

2.1 热备

在数据库正常业务时,备份数据,并且能够一致性恢复（只能是innodb）
对业务影响非常小

2.2 温备

锁表备份,只能查询不能修改（myisam）
影响到写入操作

2.3 冷备

关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止

3. 备份方式及工具介绍

3.1 逻辑备份工具

基于SQL语句进行备份
mysqldump *****
mysqlbinlog *****

3.2 物理备份工具

基于磁盘数据文件备份
xtrabackup(XBK) ：percona 第三方 *****
MySQL Enterprise Backup（MEB）

4. 逻辑备份和物理备份的比较

4.1 mysqldump (MDP)

16进制----内存—转换为SQL语句

优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高,便于备份处理
3.压缩比较高，节省备份的磁盘空间
4.备份出来是create insert语句
缺点：
5.依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB =1024 PB =1000000 TB

4.2 xtrabackup(XBK)

优点：
1.类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
缺点：
2.可读性差
3.压缩比低，需要更多磁盘空间
建议：
100G<TB

5.备份策略

备份方式：
全备:全库备份，备份所有数据
增量:备份变化的数据
逻辑备份=mysqldump+mysqlbinlog
物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog
备份周期:
根据数据量设计备份周期
比如：周日全备，周1-周6增量
注意：不管是mysqldump还是xbp都需要配合binlog备份

6.备份工具使用-mysqldump

6.1 mysqldump (逻辑备份的客户端工具)

6.1.1 客户端通用参数

-u  -p   -S   -h  -P    
本地备份:
mysqldump -uroot -p  -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p  -h 10.0.0.51 -P3306

6.1.2 备份专用基本参数

-A 全备参数

例子1:
[root@db01 ~]# mkdir -p /data/backup
mysqldump -uroot -p -A >/data/backup/full.sql
Enter password: 

mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

# 补充:
# 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告
# [root@db01 ~]# mysqldump -uroot -p123 -A  --set-gtid-purged=OFF  >/backup/full.sql
# 2.构建主从时,做的备份,不需要加这个参数
# [root@db01 ~]# mysqldump -uroot -p123 -A    --set-gtid-purged=ON >/backup/full.sql

执行成功的显示图
在这里插入图片描述

注意：
mysqldump备份的数据只有create database table insert 。。。。。
50G的数据备份范围时间15-30分钟
打开备份文件开头必须是版本信息
如果删除某个表需要恢复，需要拿备份中的数据在测试库中恢复，只需要把这个表的数据导入到生产中
如果直接把/data目录删除，就可以直接拿整个数据到生产

补充：

从mysqldump 全备中获取库和表的备份

1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

-B db1 db2 db3 备份多个单库

说明：生产中需要备份，生产相关的库和MySQL库
例子2 :
mysqldump -B mysql gtid --set-gtid-purged=OFF >/data/backup/b.sql

备份单个或多个表

例子3 world数据库下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql
以上备份恢复时:必须库事先存在,并且ues才能source恢复

6.1.3 高级参数应用

特殊参数1使用（生产中必须要加）

-R            备份存储过程及函数
--triggers  备份触发器
-E             备份事件

例子4:
[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql
(5) 特殊参数2使用

-F 在备份开始时,刷新一个新binlog日志

例子5:
mysqldump -uroot -p  -A  -R --triggers -F >/bak/full.sql

–master-data=2（非常重要）

以注释的形式,保存备份开始时间点的binlog的状态信息

mysqldump -uroot -p  -A  -R --triggers --master-data=2   >/back/world.sql
[root@db01 ~]# grep 'CHANGE' /backup/world.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;

功能：
（1）在备份时，会自动记录，二进制日志文件名和位置号
0 默认值
1  以change master to命令形式，可以用作主从复制
2  以注释的形式记录，备份时刻的文件名+postion号
（2） 自动锁表
（3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是实现快照备份。

查看

MASTER_LOG_POS=194;字眼

–single-transaction

innodb 存储引擎开启热备(快照备份)功能       
master-data可以自动加锁
（1）在不加--single-transaction ，启动所有表的温备份，所有表都锁定
（1）加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能
例子6: 备份必加参数
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

–set-gtid-purged=auto

auto , on   --默认
off 
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

分析理解一下：

#默认全备
[root@db01 backup]# mysqldump -uroot -p123 -A /data/backup/full2.sql;
[root@db01 backup]# vim full2.sql 
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='31dc93c5-cac3-11eb-8d46-000c295250b5:1-21';
如果加--set-gtid-purged=OFF 就不会有以上三行参数，会重新开始计。
如果加上--set-gtid-purged=ON，也会有三行数据。

–max-allowed-packet=256M

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

 --max-allowed-packet=# 
The maximum packet length to send to or receive from server.

6.2 小练习：

6.2.1. 实现所有表的单独备份

提示：
information_schema.tables
mysqldump -uroot -p123 world city >/backup/world_city.sql

select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2 --single-transaction --set-gtid-purged=0  -R -E --triggers>/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema');

6.2.1.1 实现所有表的单独备份（除过sys,information_schema,performance_schema）

目前比较流行的一种形式

#第一步
select concat("mysqldump -uroot -p123 -S /tmp/mysql.sock ",table_schema," ",table_name,">/data/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in('sys','information_schema','performance_schema') into outfile '/tmp/mysqldump.sh';
#第二步执行脚本
[root@db01 ~]# vim /tmp/mysqldump.sh 
[root@db01 ~]# sh /tmp/mysqldump.sh 
#查看
[root@db01 ~]# cd /data/backup/
[root@db01 backup]# ll
总用量 2316
-rw-r--r-- 1 root root   2032 6月  14 16:22 aaa_t1.sql
-rw-r--r-- 1 root root 114396 6月  14 15:45 bak.sql
-rw-r--r-- 1 root root   2061 6月  14 16:22 bbb_t1.sql
-rw-r--r-- 1 root root   2039 6月  14 16:22 binlog_t1.sql
-rw-r--r-- 1 root root   2249 6月  14 16:22 ddd_t_100w.sql
-rw-r--r-- 1 root root 901828 6月  14 14:51 full.sql
-rw-r--r-- 1 root root   2632 6月  14 16:22 mysql_columns_priv.sql
-rw-r--r-- 1 root root   4000 6月  14 16:22 mysql_db.sql
-rw-r--r-- 1 root root   2558 6月  14 16:22 mysql_engine_cost.sql
-rw-r--r-- 1 root root   4078 6月  14 16:22 mysql_event.sql
-rw-r--r-- 1 root root   2281 6月  14 16:22 mysql_func.sql
-rw-r--r-- 1 root root    996 6月  14 16:22 mysql_general_log.sql
-rw-r--r-- 1 root root   2443 6月  14 16:22 mysql_gtid_executed.sql
-rw-r--r-- 1 root root   3555 6月  14 16:22 mysql_help_category.sql
-rw-r--r-- 1 root root  14635 6月  14 16:22 mysql_help_keyword.sql
-rw-r--r-- 1 root root  15832 6月  14 16:22 mysql_help_relation.sql
-rw-r--r-- 1 root root 713945 6月  14 16:22 mysql_help_topic.sql
-rw-r--r-- 1 root root   7873 6月  14 16:22 mysql_innodb_index_stats.sql
-rw-r--r-- 1 root root   3230 6月  14 16:22 mysql_innodb_table_stats.sql
-rw-r--r-- 1 root root   2613 6月  14 16:22 mysql_ndb_binlog_index.sql
-rw-r--r-- 1 root root   2152 6月  14 16:22 mysql_plugin.sql
-rw-r--r-- 1 root root   2718 6月  14 16:22 mysql_procs_priv.sql
-rw-r--r-- 1 root root 320727 6月  14 16:22 mysql_proc.sql
-rw-r--r-- 1 root root   2727 6月  14 16:22 mysql_proxies_priv.sql
-rw-r--r-- 1 root root   2677 6月  14 16:22 mysql_server_cost.sql
-rw-r--r-- 1 root root   2466 6月  14 16:22 mysql_servers.sql
-rw-r--r-- 1 root root   4703 6月  14 16:22 mysql_slave_master_info.sql
-rw-r--r-- 1 root root   2959 6月  14 16:22 mysql_slave_relay_log_info.sql
-rw-r--r-- 1 root root   2997 6月  14 16:22 mysql_slave_worker_info.sql
-rw-r--r-- 1 root root    996 6月  14 16:22 mysql_slow_log.sql
-rw-r--r-- 1 root root   3052 6月  14 16:22 mysql_tables_priv.sql
-rw-r--r-- 1 root root   2285 6月  14 16:22 mysql_time_zone_leap_second.sql
-rw-r--r-- 1 root root   2199 6月  14 16:22 mysql_time_zone_name.sql
-rw-r--r-- 1 root root   2211 6月  14 16:22 mysql_time_zone.sql
-rw-r--r-- 1 root root   2336 6月  14 16:22 mysql_time_zone_transition.sql
-rw-r--r-- 1 root root   2478 6月  14 16:22 mysql_time_zone_transition_type.sql
-rw-r--r-- 1 root root   6446 6月  14 16:22 mysql_user.sql
-rw-r--r-- 1 root root   2266 6月  14 16:22 school_course.sql
-rw-r--r-- 1 root root   2355 6月  14 16:22 school_sc.sql
-rw-r--r-- 1 root root   2495 6月  14 16:22 school_student.sql
-rw-r--r-- 1 root root   2210 6月  14 16:22 school_teacher.sql
-rw-r--r-- 1 root root  45896 6月  14 16:22 world_city.sql
-rw-r--r-- 1 root root  28559 6月  14 16:22 world_countrylanguage.sql
-rw-r--r-- 1 root root  38968 6月  14 16:22 world_country.sql
-rw-r--r-- 1 root root   2005 6月  14 16:22 world_tt.sql

6.2.2.模拟故障案例并恢复

（1）每天全备
（2）binlog日志是完整
（3）模拟白天的数据变化
（4）模拟下午两点误删除数据库

需求： 利用全备+binlog回复数据库误删除之前。
故障模拟及恢复：
1. 模拟周一23:00的全备
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. 模拟白天的数据变化
Master [(none)]>create database day1 charset utf8;
Master [(none)]>use day1
Master [day1]>create table t1(id int);
Master [day1]>insert into t1 values(1),(2),(3);
Master [day1]>commit;
Master [world]>update city set countrycode='CHN';
Master [world]>commit;
模拟磁盘损坏：
[root@db01 data]# \rm -rf /data/mysql/data/*
3. 恢复故障
[root@db01 data]# pkill mysqld
[root@db01 data]# \rm -rf /data/mysql/data/*
4. 恢复思路
1.检查备份可用性
2.从备份中获取二进制日志位置
3.根据日志位置截取需要的二进制日志
4.初始化数据库,并启动
5.恢复全备
6.恢复二进制日志

解决方法：

#第一步首先查看定时任务的脚本
[root@db01 ~]# crontab -l
#第二步进行全备
mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
#模拟数据变化
3306 [(none)]>create database day1 charset utf8;
3306 [(none)]>use day1
3306 [day1]>create table t1(id int);
3306 [day1]>insert into t1 values(1),(2),(3);
3306 [day1]>commit;
3306 [world]>use world
3306 [world]>update city set countrycode='CHN';
3306 [world]>commit;
3306 [world]>select * from city;
#模拟磁盘损坏：
[root@db01 data]# \rm -rf /data/mysql/data/*
#恢复故障
[root@db01 data]# pkill mysqld
#查看backup的全备情况可以看见MASTER_LOG_POS=194;
[root@db01 backup]# vim full.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=194;
#截取binlog日志
[root@db01 binlog]# mysqlbinlog --start-position=194 --skip-gtids /data/binlog/mysql-bin.000030 >/data/backup/bin.sql

#查看
[root@db01 backup]# vim bin.sql 

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
......
# at 194
#210614 19:45:46 server id 6  end_log_pos 259 CRC32 0x795e4b96  GTID    last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= '31dc93c5-cac3-11eb-8d46-000c295250b5:22'/*!*/;
......
#初始数据库
[root@db01 backup]# mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql
#报错有文件存在
2021-06-14T20:09:30.531976+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-06-14T20:09:30.542128+08:00 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2021-06-14T20:09:30.542188+08:00 0 [ERROR] Aborting

#删除数据库存在的文件
[root@db01 data]# cd /data/mysql/
[root@db01 mysql]# ll
总用量 4
-rw-r----- 1 mysql mysql 646 6月  14 20:08 ib_buffer_pool
[root@db01 mysql]# rm -rf ib_buffer_pool
#重新初始化数据库
[root@db01 mysql]# mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql
#重启数据库
[root@db01 mysql]# /etc/init.d/mysqld start
#进入数据库source全备恢复
3306 [(none)]>source /data/backup/full.sql;
#进行二进制日志的恢复
3306 [day1]>set sql_log_bin=0;
3306 [day1]>source /data/backup/bin.sql;
#查看回复的表
3306 [day1]>show tables;
+----------------+
| Tables_in_day1 |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

3306 [day1]>select  * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
6 rows in set (0.00 sec)

6.3. 压缩备份并添加时间戳

例子：
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz

mysqldump备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）
set sql_log_bin=0;
source /backup/full_2018-06-28.sql

注意：
1、mysqldump在备份和恢复时都需要mysql实例启动为前提。
2、一般数据量级100G以内，大约15-45分钟可以恢复，数据量级很大很大的时候（PB、EB）
3、mysqldump是覆盖形式恢复的方法。

一般我们认为，在同数据量级，物理备份要比逻辑备份速度快.
逻辑备份的优势:
1、可读性强
2、压缩比很高

7、企业故障恢复案例

7.1 背景环境：

正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。

7.2 备份策略：

每天23:00点，计划任务调用mysqldump执行全备脚本

7.3 故障时间点：

年底故障演练:模拟周三上午10点误删除数据库，并进行恢复.

7.4 思路：

1、停业务，避免数据的二次伤害
2、找一个临时库，恢复周三23：00全备
3、截取周二23：00 — 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、
5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
5.2 方法二：将误删除的表导出，导入到原生产库
6、开启业务
处理结果：经过20分钟的处理，最终业务恢复正常

7.5 故障模拟演练

7.5.1 准备数据

create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /data/backup/*

7.5.2 周二 23：00全备

mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
或者
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

7.5.3 模拟周二 23：00到周三 10点之间数据变化

use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;

7.5.4 模拟故障,删除表(只是模拟，不代表生产操作)

drop database backup;

7.6 恢复过程

7.6.1 准备临时数据库（多实例3307）


systemctl start mysqld3307
#连接数据库
[root@db01 ~]# mysql -S /data/3307/mysql.sock
3306 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
是新库

7.6.2 准备备份

（1）准备全备：
cd /backup
gunzip full_2018-10-17.sql.gz 
（2）截取二进制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=793;
mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:6-7' /data/binlog/mysql-bin.000036 >/backup/bin.sql
或者查看
mysql -uroot -p123 -e 'show binlog events in "mysql-bin.000036"' |tail -100 |grep -i 'drop'
截取
 mysqlbinlog --skip-gtids --start-position=833 --stop-position=1599 /data/binlog/mysql-bin.000036 >/tmp/tt.sql

7.6.3 恢复备份到临时库

mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2018-10-17.sql
source /backup/bin.sql

7.6.4 将故障表导出并恢复到生产

mysqldump   -S /data/3307/mysql.sock backup t1 >/backup/t1.sql
mysql -uroot -p123 
set sql_log_bin=0
use backup 
source /backup/t1.sql;

8. 课下作业：

练习：
1、创建一个数据库 oldboy
2、在oldboy下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据
写备份脚本和策略

1.可以借助于binlog2sql工具处理只支持DML语句（insert update delete），进行反转。
2.也可以使用mariadb flashbackup闪回功能

binlog2sql工具处理

9. 备份时优化参数:

(1) max_allowed_packet   最大的数据包大小

mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2 max_allowed_packet=128M  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

(2) 增加key_buffer_size    (临时表有关)
(3) 分库分表并发备份       (作业)
(4) 架构分离,分别备份      (架构拆分,分布式备份)

10. MySQL物理备份工具-xtrabackup(XBK、Xbackup)

10.1安装

10.1.1 安装依赖包：

#依赖epel源安装
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
#相关依赖包
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

10.1.2 下载软件并安装

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

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

下载连接
XtraBackup-centos7-2.4.4下载
 XtraBackup-centos6-2.4.4下载

10.2、备份命令介绍:

xtrabackup
innobackupex    ******   依赖于/etc/my.cnf 会自动去读这个文件

[root@db01 ~]# innobackupex -version
xtrabackup: recognized server arguments: --datadir=/data/mysql --server-id=6 --log_bin=/data/binl
innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)

10.3 备份方式——物理备份

（1）对于非Innodb表（比如 myisam）是，锁表cp数据文件，属于一种温备份。
（2）对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。

面试题： xbk 在innodb表备份恢复的流程

0、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
1、备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志
2、在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用
3、恢复过程是cp 备份到原来数据目录下

10.4、innobackupex使用

10.4.1 全备

[root@db01 backup]# innobackupex --user=root --password=123  /data/backup

[root@db01 backup]# innobackupex --user=root --password=123 /data/backup/ &>/tmp/xbk.log

如果报错找不到文件/var/mysql/log的话
需要修改配置文件
[client] ----------- 包含所有客户端的参数
socket=/tmp/mysql.sock
意思是从所有的客户端去读这个文件

自主定制备份路径名

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full

或者

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/$(date +%F)_full &>/tmp/xbk.binlog

备份集中多出来的文件：

-rw-r----- 1 root root       24 Jun 29 09:59 xtrabackup_binlog_info
-rw-r----- 1 root root      119 Jun 29 09:59 xtrabackup_checkpoints
-rw-r----- 1 root root      489 Jun 29 09:59 xtrabackup_info
-rw-r----- 1 root root     2560 Jun 29 09:59 xtrabackup_logfile

xtrabackup_binlog_info ：（备份时刻的binlog位置）
[root@db01 full]# cat xtrabackup_binlog_info 
mysql-bin.000003    536749
79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7
记录的是备份时刻，binlog的文件名字和当时的结束的position，可以用来作为截取binlog时的起点。

xtrabackup_checkpoints ：
backup_type = full-backuped
from_lsn = 0            上次所到达的LSN号(对于全备就是从0开始,对于增量有别的显示方法)
to_lsn = 160683027      备份开始时间(ckpt)点数据页的LSN    
last_lsn = 160683036    备份结束后，redo日志最终的LSN
compact = 0
recover_binlog_info = 0
（1）备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT).开始备份数据，数据文件的LSN会停留在to_lsn位置。
（2）备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化了。
（3）在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。
从to_lsn  ----》last_lsn 就是，备份过程中产生的数据变化.
（4）如果在全备的基础上要进行增量的备份，就相当于在last_lsn上减去9即可。
（5）to_lsn和last_lsn默认就是相差9

在这里插入图片描述

10.4.2 全备的恢复

准备备份（Prepared）

将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程
[root@db01 ~]# innobackupex --apply-log  /backup/full

恢复备份

前提：
1、被恢复的目录是空
2、被恢复的数据库的实例是关闭
systemctl stop mysqld

创建新目录

[root@db01 backup]# mkdir /data/mysql1

数据授权

chown -R mysql.mysql /data/mysql1

恢复备份

[root@db01 full]# cp -a /backup/full/* /data/mysql1/

启动数据库

vim /etc/my.cnf
datadir=/data/mysql1
[root@db01 mysql1]# chown -R mysql.mysql /data/mysql1
systemctl start mysqld

总结步骤：四步走

1应用日志
[root@db01 mysql]# innobackupex --apply-log /data/backup/full
2.进行copy恢复
[root@db01 mysql]# innobackupex --copy-back /data/backup/full
3. 授权
[root@db01 mysql]# chown -R mysql.mysql /data/*
4.重启
[root@db01 mysql]# /etc/init.d/mysqld restart

注意：xbk恢复不需要数据库的启动

10.4.3 innobackupex 增量备份(incremental)

（1）增量备份的方式，是基于上一次备份进行增量。
（2）增量备份无法单独恢复。必须基于全备进行恢复。
（3）所有增量必须要按顺序合并到全备中。
（4）所有增量必须要--apply-log进行整理备份
（5）部分备份只需要做redo不做undo （--read-only）
（6）最后一次合并不需要添加redo，其余都需要添加redo

增量备份命令

（1）删掉原来备份
略.
（2）全备（周日）
[root@db01 backup]# innobackupex --user=root --password --no-timestamp /backup/full >&/tmp/xbk_full.log
（3）模拟周一数据变化
db01 [(none)]>create database cs charset utf8;
db01 [(none)]>use cs
db01 [cs]>create table t1 (id int);
db01 [cs]>insert into t1 values(1),(2),(3);
db01 [cs]>commit;

（4）第一次增量备份（周一）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full  /backup/inc1 &>/tmp/inc1.log
（5）模拟周二数据
db01 [cs]>create table t2 (id int);
db01 [cs]>insert into t2 values(1),(2),(3);
db01 [cs]>commit;
（6）周二增量
 innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1  /backup/inc2  &>/tmp/inc2.log
（7）模拟周三数据变化
db01 [cs]>create table t3 (id int);
db01 [cs]>insert into t3 values(1),(2),(3);
db01 [cs]>commit;
db01 [cs]>drop database cs;

全备与Inc1 Inc2的关系变化图：
在这里插入图片描述

恢复到周三误drop之前的数据状态

恢复思路：
1.  挂出维护页，停止当天的自动备份脚本
2.  检查备份：周日full+周一inc1+周二inc2，周三的完整二进制日志
3. 进行备份整理（细节），截取关键的二进制日志（从备份——误删除之前）
4. 测试库进行备份恢复及日志恢复
5. 应用进行测试无误，开启业务
6. 此次工作的总结

恢复过程

1. 检查备份
1afe8136-601d-11e9-9022-000c2928f5dd:7-9
2. 备份整理（apply-log）+合并备份（full+inc1+inc2）
(1) 全备的整理
[root@db01 one]# innobackupex --apply-log --redo-only /data/backup/full
(2) 合并inc1到full中
[root@db01 one]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full
(3) 合并inc2到full中
[root@db01 one]# innobackupex --apply-log  --incremental-dir=/data/backup/inc2 /data/backup/full
(4) 最后一次整理全备
[root@db01 backup]#  innobackupex --apply-log  /data/backup/full
3. 截取周二 23:00 到drop 之前的 binlog 
3.1判断起点（Inc2）
[root@db01 2021-06-15_02-19-30]# cat xtrabackup_binlog_info 
mysql-bin.000001	1178	e0fdd2b5-cd38-11eb-8958-000c295250b5:1-7
3.2判断终点
3306 [(none)]>show binlog events in 'mysql-bin.000001';
截取二进制日志
[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='1afe8136-601d-11e9-9022-000c2928f5dd:7-9' /data/binlog/mysql-bin.000009 >/data/backup/binlog.sql
5. 进行恢复
[root@db01 backup]# mkdir /data/mysql/data2 -p
[root@db01 full]# cp -a * /data/mysql/data2
[root@db01 backup]# chown -R mysql.  /data/*
[root@db01 backup]# systemctl stop mysqld
vim /etc/my.cnf
datadir=/data/mysql/data2
systemctl start mysqld
Master [(none)]>set sql_log_bin=0;
Master [(none)]>source /data/backup/binlog.sql

生产建议方法—找测试库

以上第五步5另一种方法进行恢复
或者准备3301多实例的环境
准备新环境
[root@db01 data]# cd /data/3307/data/
[root@db01 data]# rm -rf *
恢复数据
[root@db01 data]# cp -a /data/backup/full/* /data/3307/data/
授权
[root@db01 data]# chown -R mysql.mysql /data*
启动
[root@db01 data]# systemctl start mysqld3307
测试连接
[root@db01 data]# mysql -uroot -p -S /data/3307/mysql.sock
恢复二进制日志
Master [(none)]>set sql_log_bin=0;
Master [(none)]>source /data/backup/binlog.sql

补图：
（1）与（2）的比较—全备与第一次增量加到全备中去
在这里插入图片描述
（1）与（3）的比较—全备与第二次增量加到全备中去

xtrabackup脚本

备份：

#!/bin/bash 
#测试全备命令
#innobackupex --defaults-file=/data2/linksus/apps/linksus_beta/my.cnf  --no-timestamp --host=10.10.0.172 --user=backup --password=backup --port=7001 --socket=/data2/linksus/apps/linksus_beta/tmp/mysql.7001.sock  /test/data

BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"`  #begining time 
format_time=`date +"%Y-%m-%d_%H:%M:%S"` 
#oldDate=$(date -d "-7 days" "+%Y-%m-%d_%H:%M:%S")
oldDate=$(date -d "-7 days" "+%Y-%m-%d_%H")
week=`date +%w`  #week number
backdir=/data/Xtrabackup  #backup folder
file_cnf=/data/linksus/mysql_7001/my.cnf
user_name=linksus_dba 
password=linksus@123.dba
out_log=$backdir/xtrabackup_log_$format_time 
time_cost=$backdir/xtrabackup_time.txt
if [ -d "$backdir/incr6" ];then 
    rm -rf $backdir-${oldDate}*
    mv $backdir $backdir-$format_time
    mkdir $backdir 
else
    mkdir -p  $backdir
fi





###### Start backup now, Sunday full backup other days is incremental backup ###### 

if [ ! -d "$backdir/full" ] && [ $week -eq 0 ];then
		echo "#####start sunday full backup at  $BEGINTIME to directory full" >>$time_cost
  		innobackupex --defaults-file=$file_cnf --no-timestamp --host=10.10.3.91 --user=$user_name --password=$password --port=7001 --socket=/data/linksus/mysql_7001/tmp/mysql.7001.sock $backdir/full 1> $out_log 2>&1
		exit 1
fi
if [ ! -d "$backdir/incr1" ] && [ $week -eq 1 ];then 
        echo "#####start incremental 1 backup at $BEGINTIME to directory incr1" >>$time_cost 
        innobackupex --defaults-file=$file_cnf --no-timestamp --host=10.10.3.91 --user=$user_name --password=$password --port=7001 --socket=/data/linksus/mysql_7001/tmp/mysql.7001.sock --incremental-basedir=$backdir/full --incremental $backdir/incr1 1> $out_log 2>&1
		exit 1
fi
if [ ! -d "$backdir/incr2" ] && [ $week -eq 2 ];then
        echo "#####start incremental 2 backup at $BEGINTIME to directory incr2" >>$time_cost
		innobackupex --defaults-file=$file_cnf --no-timestamp --host=10.10.3.91 --user=$user_name --password=$password --port=7001 --socket=/data/linksus/mysql_7001/tmp/mysql.7001.sock --incremental-basedir=$backdir/incr1 --incremental $backdir/incr2 1> $out_log 2>&1
         exit 1
fi
if [ ! -d "$backdir/incr3" ] && [ $week -eq 3 ];then
        echo "#####start incremental 3 backup at $BEGINTIME to directory incr3" >>$time_cost
		innobackupex --defaults-file=$file_cnf --no-timestamp --host=10.10.3.91 --user=$user_name --password=$password --port=7001 --socket=/data/linksus/mysql_7001/tmp/mysql.7001.sock --incremental-basedir=$backdir/incr2 --incremental $backdir/incr3 1> $out_log 2>&1
         exit 1
fi
if [ ! -d "$backdir/incr4" ] && [ $week -eq 4 ];then
        echo "#####start incremental 4 backup at $BEGINTIME to directory incr4" >>$time_cost
		innobackupex --defaults-file=$file_cnf --no-timestamp --host=10.10.3.91 --user=$user_name --password=$password --port=7001 --socket=/data/linksus/mysql_7001/tmp/mysql.7001.sock --incremental-basedir=$backdir/incr3 --incremental $backdir/incr4  1> $out_log 2>&1
         exit 1  
fi
if [ ! -d "$backdir/incr5" ] && [ $week -eq 5 ];then
        echo "#####start incremental 5 backup at $BEGINTIME to directory incr5" >>$time_cost
		innobackupex --defaults-file=$file_cnf --no-timestamp --host=10.10.3.91 --user=$user_name --password=$password --port=7001 --socket=/data/linksus/mysql_7001/tmp/mysql.7001.sock --incremental-basedir=$backdir/incr4 --incremental $backdir/incr5  1> $out_log 2>&1
         exit 1
fi
if [ ! -d "$backdir/incr6" ] && [ $week -eq 6 ];then
        echo "#####start incremental 6 backup at $BEGINTIME to directory incr6" >>$time_cost
         innobackupex --defaults-file=$file_cnf --no-timestamp --host=10.10.3.91 --user=$user_name --password=$password --port=7001 --socket=/data/linksus/mysql_7001/tmp/mysql.7001.sock --incremental-basedir=$backdir/incr5 --incremental $backdir/incr6  1> $out_log 2>&1
		 exit 1
fi
### END weekly backup ###


#恢复：

#!/bin/bash

#backdir=/databackup/db_backup/Xtrabackup
#backdir=/data/Xtrabackup

#innobackupex --apply-log --redo-only --use-memory=12G $backdir/full/ 1>$backdir/huifu.log 2>&1
#innobackupex --apply-log --redo-only --use-memory=12G $backdir/full/ --incremental-dir=$backdir/incr1/ 1>$backdir/huifu.log1 2>&1
#innobackupex --apply-log --redo-only --use-memory=12G $backdir/full/ --incremental-dir=$backdir/incr2/ 1>$backdir/huifu.log2 2>&1
#innobackupex --apply-log --redo-only --use-memory=12G $backdir/full/ --incremental-dir=$backdir/incr3/ 1>$backdir/huifu.log3 2>&1
#innobackupex --apply-log --redo-only --use-memory=12G $backdir/full/ --incremental-dir=$backdir/incr4/ 1>$backdir/huifu.log4 2>&1
#innobackupex --apply-log --redo-only --use-memory=2G $backdir/full/ --incremental-dir=$backdir/incr5/ 1>$backdir/huifu.log 2>&1
#innobackupex --apply-log --redo-only --use-memory=2G $backdir/full/ --incremental-dir=$backdir/incr6/ 1>$backdir/huifu.log 2>&1
#innobackupex --apply-log --use-memory=2G $backdir/full/ 1> $backdir/fuifu.log 2>&1
#innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back $backdir/full/

mysqldump脚本

#!/bin/bash

user='mha'
password='mha'
host='10.0.0.51'
port=3306
backup_dir='/backup'
date=`date +%F`
binglog_dir='/var/lib/mysql'

/usr/bin/mysqldump  -h ${host} -P ${port} -u ${user} -p${password} -A -R -E
--triggers --single-transcation --master-sata=2 > ${backup_dir}/full_$date.s
ql

if [ $? == 0 ];then
        echo"${date} mysqldump backup successfull" >> ${backup_dir}/backup.l
og
else
        echo"${date} mysqldump backup failed" >> ${backup_dir}/error.log
fi
scp root@${host}:${binlog_dir}/mysql-bin.00000* ${backup}/
find /backup -mtine +30 -name "*.sql" | xargs rm -f

crontab -l

作业1

 Xtrabackup企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量20M-30M
备份策略：
xtrabackup，每周日23：00进行全备，周一到周六23:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
如何恢复？
全备加增量
1.一直全备加上binlog日志
2.全备加增量

作业2

练习：mysqldump备份恢复例子
1、创建一个数据库 oldboy
2、在oldboy下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据

作业3

分别写备份脚本和策略

作业4：备份集中单独恢复表

思考:在之前的项目案例中,如果误删除的表只有10M,而备份有500G,该如何快速恢复误删除表?
提示：
drop table city;
create table city like city_bak;
alter table city discard tablespace;
cp /backup/full/world/city.ibd  /application/mysql/data/world/
chown -R mysql.mysql  /application/mysql/data/world/city.ibd 
alter table city import  tablespace;

作业5：从mysqldump 全备中获取库和表的备份

1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql