1. 企业的备份恢复案例（mysqldump+binlog），年终故障恢复演练。（项目案例）
案例背景： 某中小型互联网公司，业务为提供网页服务。MySQL 5.7.26 ，Centos 7.6 ，数据量级80G，每日数据增量5-6M
备份策略： 每天mysqldump全备+binlog备份，每天23:00进行。
故障描述： 周三下午2点，数据由于某原因数据损坏。
处理思路：
1. 挂出维护页
2. 评估一下数据损坏状态
2.1 全部丢失-->推荐直接使用备份恢复到生产库
2.2 部分丢失
(1) 从备份中导出单表数据
(2)测试库进行全备恢复
3. 恢复全备，将数据追溯到周二晚上23:00状态
4. 截取并恢复从备份时刻，到下午两点误删除之前binlog。
5. 校验数据一致性
6. 撤维护页，恢复生产。
处理结果：
1. 经过30-40分钟处理，业务恢复，生成故障恢复报告
2. 评估此次故障的处理的合理性和实用性
案例模拟及恢复：
1. 进行周二全备
（1）全库备份
[root@db01 /data/mysql/data/mysql]# mysqldump -uroot -p123 -A -R --triggers -E --master-data=2 --single-transaction > /data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
（2）检查备份情况
[root@db01 /data/backup]# ll -h
total 97M
-rw-r--r-- 1 root root 49M Aug 27 08:41 bak.sql
-rw-r--r-- 1 root root 49M Aug 27 11:03 full.sql
[root@db01 ~]# vim /data/backup/full.sql
省略部分内容……
SET @@GLOBAL.GTID_PURGED='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1-12';（此次备份中包含的GTID号码，如果要用GTID恢复，就从13开始，因为这里已经有1-12了）
省略部分内容……
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000004', MASTER_LOG_POS=194;（这一条是由master-data=2生成的，这是恢复数据的起点）
省略部分内容……
2. 模拟全备之后到下午两点前的业务操作
3306 [(none)]>create database mdp charset utf8mb4;
3306 [(none)]>use mdp
3306 [mdp]>create table t1(id int);
3306 [mdp]>insert into t1 values(1),(2),(3);
3306 [mdp]>commit;
3306 [mdp]>insert into t1 values(11),(12),(13);
3306 [mdp]>commit;
3306 [mdp]>update t1 set id=20 where id>10;
3306 [mdp]>commit;
3306 [mdp]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 20 |
| 20 |
| 20 |
+------+
3. 模拟损坏
[root@db01 ~]# rm -rf /data/mysql/data/*
[root@db01 ~]# pkill mysqld
[root@db01 ~]# ss -lntup| grep 3306
[root@db01 ~]# ps -ef |grep mysqld
root 10237 8761 0 11:42 pts/1 00:00:00 grep --color=auto mysqld
[root@db01 ~]# ls /data/mysql/data/
ib_buffer_pool pkill后自动生成的文件
[root@db01 ~]# !rm
rm -rf /data/mysql/data/*
[root@db01 ~]# !ls
ls /data/mysql/data/
4. 初始化数据
[root@db01 /data/mysql/data]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
[root@db01 ~]# ll /data/mysql/data/
total 110636
-rw-r----- 1 mysql mysql 56 Aug 27 11:47 auto.cnf
-rw-r----- 1 mysql mysql 163 Aug 27 11:47 db01-slow.log
-rw-r----- 1 mysql mysql 419 Aug 27 11:47 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Aug 27 11:47 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 27 11:47 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 27 11:47 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Aug 27 11:47 mysql
-rw-r----- 1 mysql mysql 975 Aug 27 11:47 mysql_error.log
drwxr-x--- 2 mysql mysql 8192 Aug 27 11:47 performance_schema
drwxr-x--- 2 mysql mysql 8192 Aug 27 11:47 sys
[root@db01 /data/mysql/data]# /etc/init.d/mysqld start
5. 进行全备恢复
[root@db01 ~]# mysql
3306 [test]> set sql_log_bin=0;
3306 [test]> source /data/backup/full.sql
3306 [test]> flush privileges;
3306 [test]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| gg |
| mysql |
| oldboy |
| performance_schema |
| sys |
| t100w |
| test |
+--------------------+
6. 找日志起点和终点
[root@db01 ~]# vim /data/backup/full.sql
省略部分内容…………
SET @@GLOBAL.GTID_PURGED='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1-12';
省略部分内容…………
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000004', MASTER_LOG_POS=194;
省略部分内容…………
3306 [(none)]> show binlog events in 'mysql-binlog.000004';
+---------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-binlog.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-binlog.000004 | 123 | Previous_gtids | 6 | 194 | 3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1-12 |
| mysql-binlog.000004 | 194 | Gtid | 6 | 259 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:13' |
| mysql-binlog.000004 | 259 | Query | 6 | 366 | create database mdp charset utf8mb4 |
| mysql-binlog.000004 | 366 | Gtid | 6 | 431 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:14' |
| mysql-binlog.000004 | 431 | Query | 6 | 526 | use `mdp`; create table t1(id int) |
| mysql-binlog.000004 | 526 | Gtid | 6 | 591 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:15' |
| mysql-binlog.000004 | 591 | Query | 6 | 662 | BEGIN |
| mysql-binlog.000004 | 662 | Table_map | 6 | 706 | table_id: 143 (mdp.t1) |
| mysql-binlog.000004 | 706 | Write_rows | 6 | 756 | table_id: 143 flags: STMT_END_F |
| mysql-binlog.000004 | 756 | Xid | 6 | 787 | COMMIT /* xid=557 */ |
| mysql-binlog.000004 | 787 | Gtid | 6 | 852 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:16' |
| mysql-binlog.000004 | 852 | Query | 6 | 923 | BEGIN |
| mysql-binlog.000004 | 923 | Table_map | 6 | 967 | table_id: 143 (mdp.t1) |
| mysql-binlog.000004 | 967 | Write_rows | 6 | 1017 | table_id: 143 flags: STMT_END_F |
| mysql-binlog.000004 | 1017 | Xid | 6 | 1048 | COMMIT /* xid=560 */ |
| mysql-binlog.000004 | 1048 | Gtid | 6 | 1113 | SET @@SESSION.GTID_NEXT= '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:17' |
| mysql-binlog.000004 | 1113 | Query | 6 | 1184 | BEGIN |
| mysql-binlog.000004 | 1184 | Table_map | 6 | 1228 | table_id: 143 (mdp.t1) |
| mysql-binlog.000004 | 1228 | Update_rows | 6 | 1294 | table_id: 143 flags: STMT_END_F |
| mysql-binlog.000004 | 1294 | Xid | 6 | 1325 | COMMIT /* xid=562 */ |
| mysql-binlog.000004 | 1325 | Stop | 6 | 1348 | |
+---------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
22 rows in set (0.00 sec)
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='3276d3f1-bd6d-11e9-9553-000c29f1dbe8:13-17' /data/binlog/mysql-binlog.000004 > /data/backup/bin.sql
恢复数据
3306 [(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>source /data/backup/bin.sql
Query OK, 0 rows affected (0.00 sec)
……省略部分输出
3306 [mdp]>show tables;
+---------------+
| Tables_in_mdp |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
3306 [mdp]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 20 |
| 20 |
| 20 |
+------+
6 rows in set (0.00 sec) 恢复成功
或者：
[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=194 /data/binlog/mysql-bin.000045 >/tmp/aa.sql
- 恢复日志
3306 [(none)]> set sql_log_bin=0;
3306 [(none)]> source /data/backup/bin.sql
扩展：从全备中恢复单表
（1）模拟删除
3306 [(none)]>use binlog;
Database changed
3306 [binlog]>show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
3306 [binlog]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3306 [binlog]>drop table t1;
Query OK, 0 rows affected (0.01 sec)
（2）从全备中获得表结构（建表语句）
[root@db01 ~]# vim /data/backup/full.sql
省略部分内容…………
CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
省略部分内容…………
[root@db01 ~]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q' /data/backup/full.sql>/data/backup/createtable.sql
（3）获得INSERT INTO 语句，用于数据的恢复
[root@db01 ~]# vim /data/backup/full.sql
省略部分内容…………
INSERT INTO `t1` VALUES (1),(2),(3);
省略部分内容…………
[root@db01 ~]# grep -i 'INSERT INTO `t1`' /data/backup/full.sql >/data/backup/data.sql
（4）登录数据库进行单表恢复
3306 [(none)]>use binlog;
Database changed
3306 [binlog]>show tables;
Empty set (0.00 sec)
3306 [binlog]>source /data/backup/createtable.sql 恢复表
省略输出…………
3306 [binlog]>source /data/backup/data.sql 恢复数据
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
（5）检测数据恢复状态
3306 [binlog]>show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
3306 [binlog]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
3306 [binlog]>commit；
扩展：从全备中恢复单库
（1）模拟删除binlog库
3306 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| gg |
| mdp |
| mysql |
| oldboy |
| performance_schema |
| sys |
| t100w |
| test |
+--------------------+
10 rows in set (0.00 sec)
3306 [(none)]>drop database binlog;
Query OK, 1 row affected (0.00 sec)
3306 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gg |
| mdp |
| mysql |
| oldboy |
| performance_schema |
| sys |
| t100w |
| test |
+--------------------+
9 rows in set (0.00 sec)
（2）从全备中获取建库、建表、数据插入语句
[root@db01 /data/backup]# sed -n '/^-- Current Database: `binlog`/,/^-- Current Database: `/p' /data/backup/full.sql >/data/backup/binlog.sql
（3）数据恢复
3306 [(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>source /data/backup/binlog.sql
省略部分输出…………
3306 [binlog]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog 恢复成功
3306 [binlog]>use binlog;
Database changed
3306 [binlog]>show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
3306 [binlog]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
3306 [binlog]>commit;
Query OK, 0 rows affected (0.00 sec)
作业： 模仿以下备份命令，书写备份脚本
mysqldump -uroot -p123 -A -R --triggers --master-data=2 max_allowed_packet=128M --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
=======================================================================
2. XBK的应用
2.1安装
2.1.1 安装依赖包
（1）安装epel源
[root@db01 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
（2）安装所需软件包
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
2.1.2 下载软件并安装
注意：该版本只支持8.0以下的数据库，如需支持8.0版本数据库，则需下载安装最新版
（1）CentOS6版
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
（2）CentOS7版，也是本次使用的版本
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
（3）安装
[root@db01 /server/tools]# ll -h
total 623M
-rw-r--r-- 1 root root 615M Aug 13 09:25 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 root root 7.5M Jun 26 11:43 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@db01 /server/tools]# yum -y localinstall percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
2.2 备份命令介绍（xbk软件的功能）
（1）xtrabackup 8.0版本后就只会有这个xtrabackup了
（2）innobackupex ******
2.3 备份方式——物理备份（面试题）
对于不同的表有不同的备份方式
（1）对于非Innodb表（比如 myisam）是，锁表cp数据文件（短暂的），属于一种温备份。
（2）对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，并把一部分redo和undo一并备走，属于热备方式。
面试题：请问你们公司用的什么备份工具？请描述一下该工具的备份原理（备份恢复的流程）。
答：
（1）xtrabakcup。
（2）如果是全备，xtrabackup会先检测所有表并对所有表的类型进行分类，自动检查当前所有表的存储引擎类型进行分类，一类是InnoDB，一类是非InnoDB的。
（3）首先，xtrabackup会对非InnoDB的表进行锁表（可以理解为整个数据库短暂的锁定，8.0后就不存在了），备完这些表后，会立即释放这些锁。
（4）然后开始InnoDB表的备份，进行InnoDB表备份时，xbk备份执行的瞬间，会立即触发ckpt，将已提交的数据脏页，从内存刷写到磁盘，并记录此时的LSN号码。
（5）记录下LSN号码后，xtrabackup会触发备份InnoDB表的数据，同时还会将备份过程中产生的redo和undo一起拷贝走，也就是checkpoint LSN之后的日志。
（6）在恢复之前，会模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用。
（7）恢复过程是cp 备份到原来数据目录下
2.4 innobackupex使用
注意：备份工具是依赖于/etc/my.cnf
它会读取 [mysqld] 下的一部分配置信息
还会读取 [client] 下的配置信息
另外也可以自己定义 [innobackupex] 标签
如果说my.cnf配置文件没有在/etc ，可以如下操作
[root@db01 backup]# innobackupex --defaults-file=xxxxx --user=root --password=123 /data/bak
2.4.1 全备（默认就是全备）
[root@db01 ~]# mkdir /data/xbk_bakcup
[root@db01 backup]# innobackupex --user=root --password=123 /data/bak
如果这里报错“Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).
”
把my.cnf中客户端的标签改成[client]即可。
[root@db01 /data/xbk_bakcup]# tail -3 /etc/my.cnf
[client]
socket=/tmp/mysql.sock
prompt=3306 [\\d]>
[root@db01 /data/xbk_bakcup]# innobackupex --user=root --password=123 /data/xbk_bakcup/
省略部分内容……
190827 19:16:23 completed OK!
不改配置文件也可以这样：
[root@db01 /data/xbk_bakcup]# innobackupex -S /tmp/mysql.sock --user=root --password=123 /data/xbk_bakcup/
备份后的查看
[root@db01 /data/xbk_bakcup]# ll -h
total 0
drwxr-x--- 11 root root 297 Aug 27 19:21 2019-08-27_19-21-06
[root@db01 /data/xbk_bakcup]# cd 2019-08-27_19-21-06/
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# ll
total 77876
-rw-r----- 1 root root 487 Aug 27 19:21 backup-my.cnf
drwxr-x--- 2 root root 48 Aug 27 19:21 binlog
drwxr-x--- 2 root root 20 Aug 27 19:21 gg
-rw-r----- 1 root root 419 Aug 27 19:21 ib_buffer_pool
-rw-r----- 1 root root 79691776 Aug 27 19:21 ibdata1
drwxr-x--- 2 root root 20 Aug 27 19:21 mdp
drwxr-x--- 2 root root 4096 Aug 27 19:21 mysql
drwxr-x--- 2 root root 48 Aug 27 19:21 oldboy
drwxr-x--- 2 root root 8192 Aug 27 19:21 performance_schema
drwxr-x--- 2 root root 8192 Aug 27 19:21 sys
drwxr-x--- 2 root root 54 Aug 27 19:21 t100w
drwxr-x--- 2 root root 20 Aug 27 19:21 test
-rw-r----- 1 root root 108 Aug 27 19:21 xtrabackup_binlog_info 备份软件自动生成文件
-rw-r----- 1 root root 117 Aug 27 19:21 xtrabackup_checkpoints 备份软件自动生成文件
-rw-r----- 1 root root 591 Aug 27 19:21 xtrabackup_info 备份软件自动生成文件
-rw-r----- 1 root root 2560 Aug 27 19:21 xtrabackup_logfile 备份软件自动生成文件
xtrabackup_binlog_info：记录二进制日志文件信息
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# cat xtrabackup_binlog_info
mysql-binlog.000007 701 3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1-17,
72b6b704-c87d-11e9-9ebf-000c29f1dbe8:1-3
xtrabackup_checkpoints：存放LSN号
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# cat xtrabackup_checkpoints
backup_type = full-backuped 备份类型
from_lsn = 0 上次所到达的LSN号（对于全备就是从0开始，对于增量有别的显示方法）
to_lsn = 163868556 备份开始时间（ckpt）点数据页的LSN
last_lsn = 163868565 备份结束后，redo日志最终的LSN
compact = 0
recover_binlog_info = 0
xtrabackup_info：备份的总体信息
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# cat xtrabackup_info
uuid = c4480ea8-c8bc-11e9-80d9-000c29f1dbe8
name =
tool_name = innobackupex
tool_command = --user=root --password=... /data/xbk_bakcup/
tool_version = 2.4.12
ibbackup_version = 2.4.12
server_version = 5.7.26-log
start_time = 2019-08-27 19:21:06
end_time = 2019-08-27 19:21:08
lock_time = 0
binlog_pos = filename 'mysql-binlog.000007', position '701', GTID of the last change '3276d3f1-bd6d-11e9-9553-000c29f1dbe8:1-17,
72b6b704-c87d-11e9-9ebf-000c29f1dbe8:1-3'
innodb_from_lsn = 0
innodb_to_lsn = 163868556
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
xtrabackup_logfile：redo存放文件，无法查看
（1）备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT).开始备份数据，数据文件的LSN会停留在to_lsn位置。
（2）备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化了。
（3）在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。
从to_lsn ----》last_lsn 就是，备份过程中产生的数据变化.
自主定制备份路径名（推荐使用）
[root@db01 /data/xbk_bakcup]# innobackupex --user=root --password=123 --no-timestamp /data/xbk_bakcup/full_$(date +%F)
[root@db01 /data/xbk_bakcup]# ls
full_2019-08-27
2.4.2模拟误删除，然后用XBK全备恢复
（1）误操作数据库
[root@db01 ~]# pkill mysqld
[root@db01 ~]# \rm -rf /data/mysql/data/*
（2）恢复前的准备工作（Prepared）
将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程，由xbk触发
[root@db01 ~]# innobackupex --apply-log /data/xbk_bakcup/2019-08-27_19-21-06/ 把redo前滚，undo回滚
省略部分输出………
190827 20:21:12 completed OK!
（3）开始恢复
恢复备份前提：
1、被恢复的目录是空
2、被恢复的数据库的实例是关闭
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# ls
backup-my.cnf ib_logfile0 oldboy xtrabackup_binlog_info
binlog ib_logfile1 performance_schema xtrabackup_checkpoints
gg ibtmp1 sys xtrabackup_info
ib_buffer_pool mdp t100w xtrabackup_logfile
ibdata1 mysql test xtrabackup_master_key_id
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# cp -a * /data/mysql/data/
（4）授权
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# chown -R mysql. /data/mysql/data/*
（5）启动数据库
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# /etc/init.d/mysql start
Starting MySQL.Logging to '/data/mysql/data/mysql_error.log'.
SUCCESS!
（6）查看数据恢复情况
[root@db01 /data/xbk_bakcup/2019-08-27_19-21-06]# cd /data/mysql/data/
[root@db01 /data/mysql/data]# ll
total 196676
-rw-r----- 1 mysql mysql 56 Aug 27 20:27 auto.cnf
-rw-r----- 1 mysql mysql 487 Aug 27 19:21 backup-my.cnf
drwxr-x--- 2 mysql mysql 48 Aug 27 19:21 binlog
-rw-r----- 1 mysql mysql 6 Aug 27 20:27 db01.pid
-rw-r----- 1 mysql mysql 186 Aug 27 20:27 db01-slow.log
drwxr-x--- 2 mysql mysql 20 Aug 27 19:21 gg
-rw-r----- 1 mysql mysql 419 Aug 27 19:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Aug 27 20:27 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 27 20:27 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 27 20:21 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Aug 27 20:27 ibtmp1
drwxr-x--- 2 mysql mysql 20 Aug 27 19:21 mdp
drwxr-x--- 2 mysql mysql 4096 Aug 27 19:21 mysql
-rw-r----- 1 mysql mysql 3776 Aug 27 20:27 mysql_error.log
drwxr-x--- 2 mysql mysql 48 Aug 27 19:21 oldboy
drwxr-x--- 2 mysql mysql 8192 Aug 27 19:21 performance_schema
drwxr-x--- 2 mysql mysql 8192 Aug 27 19:21 sys
drwxr-x--- 2 mysql mysql 54 Aug 27 19:21 t100w
drwxr-x--- 2 mysql mysql 20 Aug 27 19:21 test
-rw-r----- 1 mysql mysql 108 Aug 27 19:21 xtrabackup_binlog_info
-rw-r----- 1 mysql mysql 117 Aug 27 20:21 xtrabackup_checkpoints
-rw-r----- 1 mysql mysql 591 Aug 27 19:21 xtrabackup_info
-rw-r----- 1 mysql mysql 8388608 Aug 27 20:21 xtrabackup_logfile
-rw-r--r-- 1 mysql mysql 1 Aug 27 20:21 xtrabackup_master_key_id
[root@db01 /data/mysql/data]# mysql -uroot -p
Enter password:
3306 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| gg |
| mdp |
| mysql |
| oldboy |
| performance_schema |
| sys |
| t100w |
| test |
+--------------------+
3. XBK增量备份（--incremental）
备份方式：基于上次的备份的增量
在XBK中，增量备份不能单独恢复，必须合并到全备中，一起恢复
3.1 增量备份模拟演示
（1）清空XBK数据目录下的所有目录
[root@db01 /data/xbk_bakcup]# ls
full_2019-08-27
[root@db01 /data/xbk_bakcup]# rm -rf *
（2）开始模拟周日全备
innobackupex --user=root --password=123 --no-timestamp /data/bak/full_$(date +%F)
（3）模拟周一数据变化
3306 [(none)]>create database xbk charset utf8mb4;
Query OK, 1 row affected (0.08 sec)
3306 [(none)]>use xbk
Database changed
3306 [xbk]>create table t1(id int);
Query OK, 0 rows affected (0.08 sec)
3306 [xbk]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
3306 [xbk]>commit;
Query OK, 0 rows affected (0.00 sec)
（4）模拟周一晚上增量备份
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/xbk_bakcup/full_2019-08-28 /data/xbk_bakcup/inc_$(date +%F)
[root@db01 /data/xbk_bakcup]# ll -h
total 0
drwxr-x--- 11 root root 297 Aug 28 09:06 full_2019-08-28
drwxr-x--- 12 root root 334 Aug 28 21:04 inc_2019-08-28
（5）模拟周二白天的数据变化
3306 [(none)]>use xbk
Database changed
3306 [xbk]>create table t2(id int);
Query OK, 0 rows affected (0.03 sec)
3306 [xbk]>insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
3306 [xbk]>commit;
Query OK, 0 rows affected (0.00 sec)
3306 [xbk]>show tables;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows in set (0.00 sec)
（6）模拟周二晚上的增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/xbk_bakcup/inc_2019-08-28 /data/xbk_bakcup/inc2_$(date +%F)
[root@db01 /data/xbk_bakcup]# ll
total 0
drwxr-x--- 11 root root 297 Aug 28 09:06 full_2019-08-28
drwxr-x--- 12 root root 334 Aug 28 21:04 inc_2019-08-28
drwxr-x--- 12 root root 334 Aug 29 16:25 inc2_2019-08-29
3.2 判断增量备份是否成功
image.png
3.3 XBK全备到增量的恢复演示
注意：
（1）合并所有增量到全备
（2）每个XBK备份都需要恢复准备(prepare)
（3）准备的参数 --apply-log（该回滚的回滚，该前滚的前滚） --redo-only（全备时使用，及合并所有增量时使用）
（1）prepare，整理全备
[root@db01 /data/xbk_bakcup]# innobackupex --apply-log --redo-only /data/xbk_bakcup/full_2019-08-28/
省略部分输出……
190830 09:42:50 completed OK!
--redo-only：防止回滚，防止LSN号断节
（2）整理并合并周一增量到全备
[root@db01 /data/xbk_bakcup]# innobackupex --apply-log --redo-only --incremental-dir=/data/xbk_bakcup/inc_2019-08-28 /data/xbk_bakcup/full_2019-08-28/
省略部分输出……
190831 19:15:18 completed OK!
--incremental-dir：合并
（3）合并后的检查
image.png
（4）整理并合并周二的增量到全备
注意：最后一次合并增量到全备不用加 --redo-only
[root@db01 /data/xbk_bakcup]# innobackupex --apply-log --incremental-dir=/data/xbk_bakcup/inc2_2019-08-29/ /data/xbk_bakcup/full_2019-08-28/
省略部分输出……
190831 19:43:03 completed OK!
（5）合并后的检查
image.png
（6） 再次整理全备（一致性整理）
[root@db01 /data/xbk_bakcup]# innobackupex --apply-log /data/xbk_bakcup/full_2019-08-28/
省略部分输出……
xtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!
xtrabackup: Log applied to lsn 163871793
xtrabackup: The intended lsn is 163884682
上面报了两错误，先看看能不能恢复数据
（7）破坏数据库，恢复数据
杀死mysqld进程，并删除全部数据
[root@db01 /data/bak]# pkill mysqld
[root@db01 /data/bak]# \rm -rf /data/mysql/data/*
恢复全备
[root@db01 /data/bak]# innobackupex --copy-back /data/xbk_bakcup/full_2019-08-28/
目录授权
[root@db01 /data/mysql/data]# chown -R mysql.mysql /data/*
启动数据库，如果启动失败，表示恢复失败
[root@db01 /data/xbk_bakcup]# /etc/init.d/mysql start
Starting MySQL.Logging to '/data/mysql/data/mysql_error.log'.
SUCCESS!
登录数据库查看
[root@db01 /data/xbk_bakcup]# mysql -uroot -p
Enter password:
3306 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| gg |
| mdp |
| mysql |
| oldboy |
| performance_schema |
| sys |
| t100w |
| test |
| xbk |
+--------------------+
11 rows in set (0.02 sec)
4. XBK企业备份恢复案例（full+inc+binlog）
案例背景： 某中型互联网公司。MySQL 5.7.26 ，Centos 7.6 ，数据量级600G，每日数据增量15-50M
备份策略： 周日XBK全备+周一到周六inc增量+binlog备份，每天23:00进行。
故障描述： 周三下午2点，数据由于某原因数据损坏。
处理思路：
1. 挂出维护页
2. 评估一下数据损坏状态
2.1 全部丢失-->推荐直接生产恢复
2.2 部分丢失
3. 整理合并所有备份：full+inc1+inc2
4. 截取 周二晚上到周三下午午故障点的binlog日志
5. 恢复全备，恢复binlog
6. 检查数据完整性
7. 恢复业务
处理结果：
1. 经过70-80分钟处理，业务恢复
2. 评估此次故障的处理的合理性和实用性
4.1 数据备份与删除
（1）模拟周日的全备
[root@db01 /data/xbk_bakcup]# innobackupex --user=root --password=123 --no-timestamp /data/xbk_bakcup/full_$(date +%F)
省略部分输出……
190901 15:37:19 completed OK!
[root@db01 /data/xbk_bakcup]# ls
full_2019-09-01
[root@db01 /data/xbk_bakcup]# cat full_2019-09-01/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 163875197
last_lsn = 163875206
compact = 0
recover_binlog_info = 0
（2） 模拟周一的数据变化
create database hisoss charset utf8mb4;
use hisoss
create table his_order(id int);
insert into his_order values(1),(2),(3);
commit;
（3） 模拟周一的增量备份
[root@db01 /data/xbk_bakcup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/xbk_bakcup/full_2019-09-01 /data/xbk_bakcup/inc1
省略部分输出……
190901 15:37:59 completed OK!
备份后检查
[root@db01 /data/xbk_bakcup]# cat full_2019-09-01/xtrabackup_checkpoints inc1/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 163875197
last_lsn = 163875206 206-9=197，也就是第一次增量的lsn号
compact = 0
recover_binlog_info = 0
===============================================
backup_type = incremental
from_lsn = 163875197 197+9=206
to_lsn = 163881551
last_lsn = 163881560
compact = 0
recover_binlog_info = 0
（4）模拟周二的数据变化
use hisoss;
insert into his_order values(11),(22),(33);
commit;
（5）模拟周二的增量备份
[root@db01 /data/xbk_bakcup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/xbk_bakcup/inc1 /data/xbk_bakcup/inc2
省略部分输出…………
190901 15:42:49 completed OK!
检查备份
[root@db01 /data/xbk_bakcup]# cat full_2019-09-01/xtrabackup_checkpoints inc1/xtrabackup_checkpoints inc2/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 163875197
last_lsn = 163875206
compact = 0
recover_binlog_info = 0
=================================================
backup_type = incremental
from_lsn = 163875197
to_lsn = 163881551
last_lsn = 163881560 560-9=551
compact = 0
recover_binlog_info = 0
===================================================
backup_type = incremental
from_lsn = 163881551 551+9=560
to_lsn = 163884455
last_lsn = 163884464
compact = 0
recover_binlog_info = 0
（6）模拟周三的数据变化
use hisoss;
insert into his_order values(111),(222),(333);
commit;
（7）周三下午，有一个傻子，把数据库data目录给rm掉了
[root@db01 /data/xbk_bakcup]# pkill mysqld
[root@db01 /data/xbk_bakcup]# \rm -rf /data/mysql/data/*
[root@db01 /data/xbk_bakcup]# ls /data/mysql/data/
[root@db01 /data/xbk_bakcup]#
4.2 数据整理
整理 合并备份
备份有：全备、周1-周2的增量、binlog日志
（1） 整理全备
[root@db01 /data/xbk_bakcup]# innobackupex --apply-log --redo-only /data/xbk_bakcup/full_2019-09-01/
省略部分输出…………
190831 23:18:13 completed OK!
（2）增量inc1 合并并整理到全备full中
[root@db01 /data/xbk_bakcup]# innobackupex --apply-log --redo-only --incremental-dir=/data/xbk_bakcup/inc1 /data/xbk_bakcup/full_2019-09-01/
省略部分输出…………
190901 15:43:58 completed OK!
（3）增量inc2 合并并整理到全备full中
[root@db01 /data/xbk_bakcup]# innobackupex --apply-log --incremental-dir=/data/xbk_bakcup/inc2 /data/xbk_bakcup/full_2019-09-01/
省略部分输出…………
190901 15:44:20 completed OK!
（4） 整体的整理
[root@db01 /data/xbk_bakcup]# innobackupex --apply-log /data/xbk_bakcup/full_2019-09-01/
省略部分输出…………
190901 15:44:33 completed OK!
4.3 恢复备份数据
[root@db01 /data/xbk_bakcup]# cp -a /data/xbk_bakcup/full_2019-09-01/* /data/mysql/data/
[root@db01 /data/xbk_bakcup]# ls /data/mysql/data/
backup-my.cnf ib_logfile0 performance_schema xtrabackup_binlog_pos_innodb
binlog ib_logfile1 sys xtrabackup_checkpoints
gg ibtmp1 t100w xtrabackup_info
hisoss mdp test xtrabackup_logfile
ib_buffer_pool mysql xbk xtrabackup_master_key_id
ibdata1 oldboy xtrabackup_binlog_info
[root@db01 /data/xbk_bakcup]# chown -R mysql.mysql /data
到这里 数据已经恢复到周二晚上的状态了
[root@db01 /data/xbk_bakcup]# /etc/init.d/mysql start
Starting MySQL.Logging to '/data/mysql/data/mysql_error.log'.
... SUCCESS!
4.4 截取二进制日志恢复数据
恢复周二晚上备份后到周三故障点的日志
[root@db01 /data/xbk_bakcup]# cat inc2/xtrabackup_binlog_info
mysql-binlog.000017（在数据库中查它） 1376 091092c2-c8c6-11e9-8d89-000c29f1dbe8:1-5,
1222b472-cc6f-11e9-9e5e-000c29f1dbe8:1-6,
174c093e-cc86-11e9-b75b-000c29f1dbe8:1-4,
省略部分输出……
登录数据库
mysql> show binlog events in 'mysql-binlog.000017';
省略部分输出……
| mysql-binlog.000017 | 1376 | Gtid | 6 | 1441 | SET @@SESSION.GTID_NEXT= '174c093e-cc86-11e9-b75b-000c29f1dbe8:5' 复制这个GTID
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='174c093e-cc86-11e9-b75b-000c29f1dbe8:5' /data/binlog/mysql-binlog.000017 > /data/xbk_bakcup/bin.sql
恢复数据：
mysql> set sql_log_bin=0;
mysql> source /data/xbk_bakcup/bin.sql
mysql> source /data/xbk_bakcup/bin.sql
检查恢复情况·
mysql> select * from hisoss.his_order;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
| 111 |
| 222 |
| 333 |
+------+
9 rows in set (0.00 sec)
扩展： 少量数据恢复（单表）
假如，只是少量数据被损坏，以上方法有哪些不妥的地方？
全备恢复的话，时间大概需要一个多小时，特别浪费时间，
单独恢复被删除的小表，可能只需要10S。
alter table t1 discard tablespace
alter table t1 import tablespace 必须要有t1表的ibd文件
生产：推送本地备份到备份服务器上
innobackupex --user=root --password=123 --defaults-file=/etc/my.cnf --no-timestamp --stream=tar --use-memory=256M --parallel=8 /data/mysql_backup | gzip | ssh root@10.0.0.52 " cat - > /data/mysql_backup.tgz"
--stream=tar 启用流的压缩方式备份，就是备份完后自动tar包，可以稍微提高一点点性能
--use-memory=256M 在备份中单独使用一部分内存，不指定默认使用的是文件系统缓存
--parallel=8 开启并发备份，这个值为cpu核心数的一半
5. MySQL数据迁移
5.0迁移前要考虑的问题
技术方面
选择什么工具或方法，MDP XBK
非技术
停机时间，要尽可能缩短停机时间
回退方案
5.1 换主机
5.1.1 数据量小
思路：
1. 在线 MDP，XBK备份出来，scp到目标主机
2. 追加所有备份后的日志
3. 申请停机
4. 剩余部分的binlog继续恢复（可以使用搭建主从的方式来替代）
5. 校验数据
6. 进行业务割接
5.1.2 数据量大
XBK备份出来，scp到目标主机
搭建主从的方式
申请停机15分钟
校验数据
进行业务割接
5.2 换版本升级
例如：
5.6升级到5.7
步骤如下：
假设源机器5.6要升级到5.7，先关闭5.6，
然后把5.7安装好，把原来5.6的my.cnf配置文件修改一下，
指向5.7，5.6的环境变量PATH等也要改成5.7的，
然后用mysql_safe启动5.7，再用mysql_upgrade重建数据字典，
最后关闭数据库，正常启动即可。
（1）方法一：
建议使用 mysqldump逻辑备份方式，按业务库进行分别备份，就是一个库一个备份，排除掉 information_schema,performance_schema,sys
恢复完成后，升级数据字典
（2）方法二：
进行过滤复制，排除掉 information_schema,performance_schema,sys
5.3 异构迁移-系统不一样
只能用逻辑备份，也就是mysqldump，因为物理备份实现不了
网友评论