MySQL备份管理

目录

• MySQL备份管理
  • * 一、MySQL备份管理
    • 1.1.1 MySQL备份管理介绍
    • 1.1.2 基于mysqldump的备份恢复
    • 1.1.3 基于xtrabackup软件的物理备份

一、MySQL备份管理

1.1.1 MySQL备份管理介绍

1. 为什么要做备份

有效积极的备份策略，可以提高企业的数据容灾可靠性，防止因意外导致的数据损坏而无法恢复，给企业造成损失。有效的备份策略：全备 增量 时间 自动 ，记住备份是容灾的基础。

1. 备份检查测试

   日常备份检查:备份存在性,备份空间够用否.
   定期恢复演练(测试库):一季度或者半年.
   故障恢复:通过现有备份,能够将数据库恢复到故障之前的时间点
   迁移（非技术):停机时间,回退方案 (主从切换)

2. 备份的类型

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

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

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

3. 备份工具

   1、逻辑备份工具
   基于SQL语句进行备份
   mysqldump（MDP） *
   mysqlbinlog

   2、物理备份工具
   基于磁盘数据文件备份
   xtrabackup(XBK) ：Percona 第三方 *
   MySQL Enterprise Backup（MEB）

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

   mysqldump (MDP)
   优点：
   1.不需要下载安装
   2.备份出来的是SQL，文本格式，可读性高,便于备份处理
   3.压缩比较高，节省备份的磁盘空间
   缺点：
   4.依赖于数据库引擎，需要从磁盘把数据读出
   然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低

   建议：
   100G以内的数据量级，可以使用mysqldump
   超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
   1EB =1024 PB = 1000000 TB

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

   100G<TB

   备份方式：
   全备:全库备份，备份所有数据
   增量:备份变化的数据
   逻辑备份=mysqldump+mysqlbinlog
   物理备份=xtrabackup_full+xtrabackup_incr+binlog 或者 xtrabackup_full+binlog

   5.2 备份周期:

   根据数据量设计备份周期
   比如：周日全备，周1-周6增量
   其他：通过主从复制备份

5. 逻辑备份工具-mysqldump使用

   本地备份连接方式：
   mysqldump -uroot -pxxx -S /tmp/mysql.sock
   远程备份的连接方式：
   mysqldump -uroot -pxxx -h xxx -P xxx

   基本参数：
   -A 全库备份
   [root@db01 ~]# mkdir -p /data/backup
   [root@db01 ~]# mysqldump -uroot -p123456 -A >/data/backup/full.$(date +%F).sql
   mysqldump: [Warning] Using a password on the command line interface can be insecure.
   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. 警告先不用管
   [root@db01 ~]# ll /data/backup/
   total 4
   -rw-r--r-- 1 root root 203 Jun 24 07:20 full.2020-06-24.sql

   -B 备份单个或多个库
   [root@db01 ~]# mysqldump -uroot -p123456 -B world test >/data/backup/world.$(date +%F).sql
   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.
   [root@db01 ~]# ll /data/backup/world.2020-06-24.sql
   -rw-r--r-- 1 root root 50025135 Jun 24 12:41 /data/backup/world.2020-06-24.sql

   库名+表名：备份一个库下的一张或多张表
   [root@db01 ~]# mysqldump -uroot -p123456 world city country >/data/backup/db.$(date +%F).sql
   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.
   [root@db01 ~]# ll /data/backup/db.2020-06-24.sql
   -rw-r--r-- 1 root root 217282 Jun 24 12:54 /data/backup/db.2020-06-24.sql

   注意： 此种方法，只会备份建表+插入语句。所以，恢复前需要把库建好，而且要use到库中。

6. mysqldump 备份时的常用参数

   -R 在备份时，同时备份存储过程和函数，如果没有会自动忽略
   -E 在备份时，同时备份EVENT，如果没有会自动忽略
   --triggers 在备份时，同时备份触发器，如果没有会自动忽略
   --master-data=2 记录备份开始时 position号 ，可以作为将来做日志截取的起点

   • 记录备份时的position

   • 自动锁表

   • 配合--single-transaction，减少锁的(innodb引擎)
     --single-transaction 减少锁的(innodb引擎)
     -F 刷新binlog日志，但是有多少个库(除系统库)就刷新多少个binlog（有点鸡肋，看生产环境使用）
     --set-gtid-purged= 有三种情况（auto自动 on开启 off关闭）

     • 使用场景
       mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

     auto , on:在构建主从复制环境时需要的参数配置
     mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

     --max-allowed-packet=128M 备份传输受限（这个参数即是server端参数也是客户端参数，可以在my.cnf配置，非必要参数）

     默认打下4M
     mysql> show variables like 'max_allowed_packet';
     +--------------------+---------+
     | Variable_name | Value |
     +--------------------+---------+
     | max_allowed_packet | 4194304 |
     +--------------------+---------+
     1 row in set (0.01 sec)

   mysql> select sum(4194304/1024/1024);
   +------------------------+
   | sum(4194304/1024/1024) |
   +------------------------+
   | 4.00000000 |
   +------------------------+
   1 row in set (0.00 sec)

   综合例子：
   [root@db01 ~]# mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full2.sql
   mysqldump: [Warning] Using a password on the command line interface can be insecure.
   [root@db01 ~]# ll /data/backup/full2.sql
   -rw-r--r-- 1 root root 50913798 Jun 24 14:17 /data/backup/full2.sql

1.1.2 基于mysqldump的备份恢复

1. mysql 压缩备份

   mysql 压缩备份 压缩还原 命令

   1、mysqldump 备份并压缩sql文件
   mysql>mysqldump -h主机ip -u用户名 -p密码（也可不输入） 数据库名 | gzip > 压缩后文件位置

   2、mysql直接用压缩文件恢复
   mysql>gunzip < backupfile.sql.gz | mysql -u用户名 -p密码（也可不输入） 数据库名

2. 企业备份恢复案列（mysqldump+binlog）

   系统：[root@db01 ~]# cat /etc/redhat-release
   CentOS Linux release 7.7.1908 (Core)

   数据库版本：mysql> select version();
   +------------+
   | version() |
   +------------+
   | 5.7.26-log |
   +------------+
   1 row in set (0.03 sec)

   数据量：数据量级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. 评估此次故障的处理的合理性和实用性

3. 模拟数据恢复

   进行数据全备
   [root@db01 ~]# mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction </p>

   |gzip >/data/backup/db.$(date +%F).sql.zip
   mysqldump: [Warning] Using a password on the command line interface can be insecure.
   [root@db01 ~]# ll /data/backup/db.2020-06-24.sql.zip
   -rw-r--r-- 1 root root 13110601 Jun 24 19:28 /data/backup/db.2020-06-24.sql.zip

   解压验证数据的正确性
   [root@db01 /data/backup]# gunzip -c db.2020-06-24.sql.zip >db.sql
   [root@db01 /data/backup]# ll
   total 62528
   -rw-r--r-- 1 root root 13110601 Jun 24 19:28 db.2020-06-24.sql.zip
   -rw-r--r-- 1 root root 50913798 Jun 24 19:46 db.sql

   [root@db01 /data/backup]# cat db.sql|head -10

   -- MySQL dump 10.13 Distrib 5.7.26, for linux-glibc2.12 (x86_64)

   -- Host: localhost Database:

   ---

   -- Server version 5.7.26-log

   /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
   /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
   /*!40101 SET NAMES utf8 */;
   注意：gunzip 解压时它会将文件解压缩为文件 db.sql，原来的文件则没有了，为了保留原有的文件，我们可以加上 -c 选项并利用 linux 的重定向

   获取GTID号和position其实位置
   [root@db01 /data/backup]# vim db.sql

   -- GTID state at the beginning of the backup

   SET @@GLOBAL.GTID_PURGED='5cabcecd-95d4-11ea-928e-000c290e8d03:1-6';

   -- Position to start replication or point-in-time recovery from

   -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=194;

   模拟全备之后到下午两点前的业务操作
   mysql> create database mdp charset utf8mb4;
   mysql> use mdp
   mysql> create table t1(id int);
   mysql> insert into t1 values(1),(2),(3);
   mysql> commit;
   mysql> insert into t1 values(11),(12),(13);
   mysql> commit;
   mysql> update t1 set id=20 where id>10;
   mysql> commit;

   mysql> show databases like 'mdp';
   +----------------+
   | Database (mdp) |
   +----------------+
   | mdp |
   +----------------+
   1 row in set (0.00 sec)

   损坏数据：删除mysql数据目录data的所有数据（不代表生产操作，此命令只做测试，如果生产中做了，赶紧买票。）
   [root@db01 /data/backup]# rm -rf /application/mysql/data/*
   [root@db01 /data/backup]# ll /application/mysql/data/
   total 0
   [root@db01 /data/backup]# pkill mysqld （无法正常关闭的情况下）

   开始恢复：
   第一步：因为连表空间都没有了，所以先初始化数据库
   [root@db01 /data/backup]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
   [root@db01 ~]# ll /application/mysql/data/
   total 798760
   -rw-r----- 1 mysql mysql 56 Jun 24 20:50 auto.cnf
   -rw-r----- 1 mysql mysql 419 Jun 24 20:50 ib_buffer_pool
   -rw-r----- 1 mysql mysql 12582912 Jun 24 20:50 ibdata1
   -rw-r----- 1 mysql mysql 268435456 Jun 24 20:50 ib_logfile0
   -rw-r----- 1 mysql mysql 268435456 Jun 24 20:50 ib_logfile1
   -rw-r----- 1 mysql mysql 268435456 Jun 24 20:50 ib_logfile2
   drwxr-x--- 2 mysql mysql 4096 Jun 24 20:50 mysql
   -rw-r----- 1 mysql mysql 1002 Jun 24 20:50 mysql.err
   drwxr-x--- 2 mysql mysql 8192 Jun 24 20:50 performance_schema
   drwxr-x--- 2 mysql mysql 8192 Jun 24 20:50 sys
   启动数据库
   [root@db01 ~]# systemctl status mysqld

   全备数据恢复
   mysql> set sql_log_bin=0; （不记录二进制文件）
   Query OK, 0 rows affected (0.00 sec)

   mysql> show databases;
   +--------------------+
   | Database |
   +--------------------+
   | information_schema |
   | binlog |
   | chenhj |
   | ll |
   | mysql |
   | performance_schema |
   | school |
   | sys |
   | test |
   | test2 |
   | wordpress |
   | world |
   +--------------------+
   12 rows in set (0.00 sec)

   mysql> flush privileges;
   Query OK, 0 rows affected (0.00 sec)

   增量备份恢复（binlog）在备份文件中找到二进制文件和初始值
   [root@db01 /data/backup]# vim db.sql

   SET @@GLOBAL.GTID_PURGED='5cabcecd-95d4-11ea-928e-000c290e8d03:1-6';

   -- Position to start replication or point-in-time recovery from

   -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=194;

   mysql> show binlog events in 'mysql-bin.000028';
   +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
   | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
   +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
   | mysql-bin.000028 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
   | mysql-bin.000028 | 123 | Previous_gtids | 1 | 194 | 5cabcecd-95d4-11ea-928e-000c290e8d03:1-6 |
   | mysql-bin.000028 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:7' |
   | mysql-bin.000028 | 259 | Query | 1 | 366 | create database mdp charset utf8mb4 |
   | mysql-bin.000028 | 366 | Gtid | 1 | 431 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:8' |
   | mysql-bin.000028 | 431 | Query | 1 | 526 | use mdp; create table t1(id int) |
   | mysql-bin.000028 | 526 | Gtid | 1 | 591 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:9' |
   | mysql-bin.000028 | 591 | Query | 1 | 662 | BEGIN |
   | mysql-bin.000028 | 662 | Table_map | 1 | 706 | table_id: 398 (mdp.t1) |
   | mysql-bin.000028 | 706 | Write_rows | 1 | 756 | table_id: 398 flags: STMT_END_F |
   | mysql-bin.000028 | 756 | Xid | 1 | 787 | COMMIT /* xid=4524 / | | mysql-bin.000028 | 787 | Gtid | 1 | 852 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:10' | | mysql-bin.000028 | 852 | Query | 1 | 923 | BEGIN | | mysql-bin.000028 | 923 | Table_map | 1 | 967 | table_id: 398 (mdp.t1) | | mysql-bin.000028 | 967 | Write_rows | 1 | 1017 | table_id: 398 flags: STMT_END_F | | mysql-bin.000028 | 1017 | Xid | 1 | 1048 | COMMIT / xid=4526 / | | mysql-bin.000028 | 1048 | Gtid | 1 | 1113 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:11' | | mysql-bin.000028 | 1113 | Query | 1 | 1184 | BEGIN | | mysql-bin.000028 | 1184 | Table_map | 1 | 1228 | table_id: 398 (mdp.t1) | | mysql-bin.000028 | 1228 | Update_rows | 1 | 1294 | table_id: 398 flags: STMT_END_F | | mysql-bin.000028 | 1294 | Xid | 1 | 1325 | COMMIT / xid=4528 */ |
   | mysql-bin.000028 | 1325 | Stop | 1 | 1348 | |
   +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
   22 rows in set (0.00 sec)

   截取binlog日志：
   第一种GTID方式：因为1-6已经存在了，所以从7截取到11
   [root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='5cabcecd-95d4-11ea-928e-000c290e8d03:7-11' </p>

   /application/mysql/log_bin/mysql-bin.000028 >/data/backup/bin.sql
   [root@db01 ~]# ll /data/backup/bin.sql
   -rw-r--r-- 1 root root 3614 Jun 24 22:37 /data/backup/bin.sql

   第二种position方式
   [root@db01 ~]# mysqlbinlog --skip-gtids --start-position=194 /application/mysql/log_bin/mysql-bin.000028 >/data/backup/binback.sql
   [root@db01 ~]# ll /data/backup/binback.sql
   -rw-r--r-- 1 root root 3605 Jun 24 22:40 /data/backup/binback.sql

   恢复增量数据（binlog日志）
   mysql> set sql_log_bin=0;
   Query OK, 0 rows affected (0.00 sec)
   mysql> source /data/backup/bin.sql

   mysql> show databases like 'mdp';
   +----------------+
   | Database (mdp) |
   +----------------+
   | mdp |
   +----------------+
   1 row in set (0.00 sec)

   mysql> select * from mdp.t1;
   +------+
   | id |
   +------+
   | 1 |
   | 2 |
   | 3 |
   | 20 |
   | 20 |
   | 20 |
   +------+
   6 rows in set (0.00 sec)

4. 从全备中导出单表备份

   mysql> use world
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A

   Database changed
   mysql> show tables;
   +-----------------+
   | Tables_in_world |
   +-----------------+
   | city |
   | country |
   | countrylanguage |
   | t1 |
   +-----------------+
   4 rows in set (0.00 sec)

   删除city表做测试
   mysql> drop table city;
   Query OK, 0 rows affected (0.01 sec)

   从全备中截取city表
   [root@db01 /data/backup]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE city/!d;q' db.sql>createtable.sql
   [root@db01 /data/backup]# cat createtable.sql

   DROP TABLE IF EXISTS city;
   /*!40101 SET @saved_cs_client = @@character_set_client */;
   /*!40101 SET character_set_client = utf8 */;
   CREATE TABLE city (
   ID int(11) NOT NULL AUTO_INCREMENT,
   Name char(35) NOT NULL DEFAULT '',
   CountryCode char(3) NOT NULL DEFAULT '',
   District char(20) NOT NULL DEFAULT '',
   Population int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (ID),
   KEY CountryCode (CountryCode),
   KEY idx_name (Name(5)),
   KEY idx_co_po (CountryCode,Population),
   CONSTRAINT city_ibfk_1 FOREIGN KEY (CountryCode) REFERENCES country (Code)
   ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
   /*!40101 SET character_set_client = @saved_cs_client */;

   从全备中截取city表的insert数据
   [root@db01 /data/backup]# grep -i 'INSERT INTO city' db.sql >data.sql
   #数据量大这里就不展开了

   恢复city数据
   mysql> source /data/backup/createtable.sql
   Query OK, 0 rows affected, 1 warning (0.00 sec)

   Query OK, 0 rows affected (0.00 sec)

   Query OK, 0 rows affected (0.00 sec)

   Query OK, 0 rows affected (0.01 sec)

   Query OK, 0 rows affected (0.00 sec)

   mysql> source /data/backup/data.sql
   Query OK, 4079 rows affected (0.10 sec)
   Records: 4079 Duplicates: 0 Warnings: 0

   检查是否成功
   mysql> select * from city limit 10;
   +----+----------------+-------------+---------------+------------+
   | ID | Name | CountryCode | District | Population |
   +----+----------------+-------------+---------------+------------+
   | 1 | Kabul | AFG | Kabol | 1780000 |
   | 2 | Qandahar | AFG | Qandahar | 237500 |
   | 3 | Herat | AFG | Herat | 186800 |
   | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
   | 5 | Amsterdam | NLD | Noord-Holland | 731200 |
   | 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
   | 7 | Haag | NLD | Zuid-Holland | 440900 |
   | 8 | Utrecht | NLD | Utrecht | 234323 |
   | 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
   | 10 | Tilburg | NLD | Noord-Brabant | 193238 |
   +----+----------------+-------------+---------------+------------+
   10 rows in set (0.00 sec)

1.1.3 基于xtrabackup软件的物理备份

1. 安装依赖环境

   [root@db01 /data/backup]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

2. 下载安装xtrabackup软件

   [root@db01 /server/tools]# 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
   [root@db01 /server/tools]# ll percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
   -rw-r--r-- 1 root root 7836036 Jun 14 2018 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
   [root@db01 /server/tools]# yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm -y

   [root@db01 /server/tools]# innobackupex --version
   xtrabackup: recognized server arguments: --datadir=/application/mysql/data/ --server-id=1 --log_bin=/application/mysql/log_bin/mysql-bin --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DIRECT --innodb_log_buffer_size=128M --innodb_log_file_size=256M --innodb_log_files_in_group=3
   innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)

   注意8.0以上版本的mysql需要8.0以上Xtrabackup版本

3. 备份命令介绍:

   xtrabackup
   innobackupex
   备份方式——物理备份
   （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 备份到原来数据目录下

   备份过程：

   1. ckpt ，记录ckpt后LSN ,to lsn

   2. 拷贝数据页 ，保存为数据文件

   3. 自动将备份过程redo，会一并备份走，提取最后的last LSN

      恢复：
      其实就是模拟了CSR过程
      对比LAST LSN ,to lsn
      使用redo进行前滚，对未提交的事务进行回滚
      最后得到一个一致性备份

4. innobackupex备份命令使用

   [root@db01 ~]# mkdir /data/xbkup
   [root@db01 /data/xbkup]# innobackupex --usre=root --password=123456 /data/xbkup
   报错：
   Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','',…) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1314.
   200625 14:04:37 Connecting to MySQL server host: localhost, user: not set, password: set, port: not set, socket: not set
   Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).
   原因是客户端找不到socket文件
   在etc/my.cnf添加如下配置
   [client]
   socket = /tmp/mysql.sock

   [root@db01 /data/xbkup]# innobackupex --usre=root --password=123456 /data/xbkup
   [root@db01 /data/xbkup]# ls /data/xbkup/2020-06-25_14-09-54/
   backup-my.cnf ib_buffer_pool mdp school test2 xtrabackup_binlog_info xtrabackup_logfile
   binlog ibdata1 mysql sys wordpress xtrabackup_checkpoints
   chenhj ll performance_schema test world xtrabackup_info
   #注意如果my.cnf没在/etc/下 需要--defaults-file=指定配置文件

   二进制日志信息（备份时刻的binlog位置）
   [root@db01 /data/xbkup]# cat 2020-06-25_14-09-54/xtrabackup_binlog_info
   mysql-bin.000031 194 5cabcecd-95d4-11ea-928e-000c290e8d03:1-11

   记录LSN号信息
   [root@db01 /data/xbkup]# cat 2020-06-25_14-09-54/xtrabackup_checkpoints
   backup_type = full-backuped
   from_lsn = 0 上次所到达的LSN号(对于全备就是从0开始,对于增量有别的显示方法)
   to_lsn = 136841830 备份开始时间(ckpt)点数据页的LSN
   last_lsn = 136841839 备份结束后，redo日志最终的LSN
   compact = 0
   recover_binlog_info = 0

   自主定制备份路径名
   [root@db01 /data/xbkup]# innobackupex --user=root --password=123456 --no-timestamp /data/xbkup/$(hostname)_$(date +%F)
   [root@db01 /data/xbkup]# ll
   total 4
   drwxr-x--- 14 root root 4096 Jun 25 14:45 db01_2020-06-25

5. 数据全备恢复(首先你要有备份数据)

   停止和删除mysql data数据目录
   [root@db01 /data/xbkup]# systemctl stop mysqld
   [root@db01 /data/xbkup]# systemctl status mysqld
   [root@db01 /data/xbkup]# rm -rf /application/mysql/data/*
   [root@db01 /data/xbkup]# ll /application/mysql/data/
   total 0

   准备备份（Prepared，这一步一定要做）
   将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程
   [root@db01 /data/xbkup]# innobackupex --apply-log /data/xbkup/db01_2020-06-25/
   最后出现ok才行
   200625 14:57:50 completed OK!

   将备份数据拷贝到mysql data数据目录下
   [root@db01 /data/xbkup]# cp -a db01_2020-06-25/* /application/mysql/data/
   [root@db01 /data/xbkup]# ls /application/mysql/data/
   backup-my.cnf ib_buffer_pool ib_logfile1 ll performance_schema test world xtrabackup_info
   binlog ibdata1 ib_logfile2 mdp school test2 xtrabackup_binlog_info xtrabackup_logfile
   chenhj ib_logfile0 ibtmp1 mysql sys wordpress xtrabackup_checkpoints xtrabackup_master_key_id

   授予mysql权限
   [root@db01 /data/xbkup]# chown -R mysql.mysql /application/mysql/data/

   启动mysql
   [root@db01 /data/xbkup]# systemctl start mysqld
   [root@db01 /data/xbkup]# systemctl status mysqld
   ● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2020-06-25 15:06:05 CST; 5s ago
   Docs: man:mysqld(8)
   http://dev.mysql.com/doc/refman/en/using-systemd.html
   Main PID: 8170 (mysqld)
   CGroup: /system.slice/mysqld.service
   └─8170 /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf

   Jun 25 15:06:05 db01 systemd[1]: Started MySQL Server.

   mysql> show databases like 'world';
   +------------------+
   | Database (world) |
   +------------------+
   | world |
   +------------------+
   1 row in set (0.00 sec)
   数据恢复完成

6. 增量备份

   备份方式：基于上次的备份的增量
   增量备份不能单独恢复，必须合并到全备中，一起恢复

   第一步全备：
   [root@db01 /data/xbkup]# innobackupex -uroot -p123456 --no-timestamp /data/xbkup/$(hostname)_$(date +%F)
   [root@db01 /data/xbkup]# ll
   total 4
   drwxr-x--- 14 root root 4096 Jun 25 15:38 db01_2020-06-25

   模拟周一的增量数据
   mysql> create database xbk charset utf8mb4;
   Query OK, 1 row affected (0.01 sec)

   mysql> use xbk
   Database changed
   mysql> create table t1(id int);
   Query OK, 0 rows affected (0.02 sec)

   mysql> insert into t1 values(1),(2),(3);
   Query OK, 3 rows affected (0.03 sec)
   Records: 3 Duplicates: 0 Warnings: 0

   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)

   备份周一的增量数据（只做数据模拟，周几不必介意）
   [root@db01 /data/xbkup]# innobackupex -uroot -p123456 --no-timestamp --incremental --incremental-basedir=/data/xbkup/db01_2020-06-25 /data/xbkup/inc_$(date +%A)

   [root@db01 /data/xbkup]# ll
   total 8
   drwxr-x--- 14 root root 4096 Jun 25 15:38 db01_2020-06-25
   drwxr-x--- 15 root root 4096 Jun 25 16:02 inc_Thursday

   模拟周二白天的数据变化
   mysql> use xbk
   Database changed
   mysql> create table t2(id int);
   Query OK, 0 rows affected (0.04 sec)

   mysql> insert into t2 values(1),(2),(3);
   Query OK, 3 rows affected (0.00 sec)
   Records: 3 Duplicates: 0 Warnings: 0

   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)

   [root@db01 /data/xbkup]# innobackupex -uroot -p123456 --no-timestamp --incremental --incremental-basedir=/data/xbkup/inc_Thursday /data/xbkup/inc2_$(date +%A)

   周二的增量备份是基于周一的增量备份（依次类推），而不是全备，第一次增量备份采用全备

   检查备份是否完好
   [root@db01 /data/xbkup]# cat db01_2020-06-25/xtrabackup_checkpoints inc_Thursday/xtrabackup_checkpoints inc2_Thursday/xtrabackup_checkpoints
   backup_type = full-backuped
   from_lsn = 0
   to_lsn = 136842299
   last_lsn = 136842308
   compact = 0
   recover_binlog_info = 0

   backup_type = incremental
   from_lsn = 136842299
   to_lsn = 136848080
   last_lsn = 136848089
   compact = 0
   recover_binlog_info = 0

   backup_type = incremental
   from_lsn = 136848080
   to_lsn = 136853667
   last_lsn = 136853676
   compact = 0
   recover_binlog_info = 0

   lsn 有9个字节是自己占用的所以 from_lsn=last_lsn-9 (这里的from_lsn是下一个增量的起始值，全备的起始值为0) 如果得到的数字和from_lsn符合增量备份的数据就是正确的，否则错误

7. 增量备份恢复

   备份恢复准备：将所有增量合并到全备，每个XBK备份都需要恢复准备(prepare)
   --apply-log 将增量备份合并到全备还需要--redo-only（只前滚，不回滚）

   第一步：整理基础全备
   [root@db01 /data/xbkup]# innobackupex --apply-log --redo-only /data/xbkup/db01_2020-06-25/

   第二步：周一增量数据合并基础全备
   [root@db01 /data/xbkup]# innobackupex --apply-log --redo-only --incremental-dir=/data/xbkup/inc_Thursday /data/xbkup/db01_2020-06-25/
   出现报错：xtrabackup: error: applying incremental backup needs target prepared with --apply-log-only.
   解决方法：重新执行一遍整理基础全备

   检查周一的数据是否合并到全备中
   [root@db01 /data/xbkup]# cat db01_2020-06-25/xtrabackup_checkpoints inc_Thursday/xtrabackup_checkpoints inc2_Thursday/xtrabackup_checkpoints
   backup_type = log-applied
   from_lsn = 0
   to_lsn = 136848080 #与周一的to_lsn相同
   last_lsn = 136848089 #与周一的last_lsn相同
   compact = 0
   recover_binlog_info = 0

   backup_type = incremental
   from_lsn = 136842299
   to_lsn = 136848080 #相同
   last_lsn = 136848089 #相同
   compact = 0
   recover_binlog_info = 0

   backup_type = incremental
   from_lsn = 136848080
   to_lsn = 136853667
   last_lsn = 136853676
   compact = 0
   recover_binlog_info = 0

   #与周一的to_lsn和last_lsn相同 ，证明周一的增量数据合并成功。

   第三步：周二增量数据合并基础全备
   [root@db01 /data/xbkup]# innobackupex --apply-log --incremental-dir=/data/xbkup/inc2_Thursday /data/xbkup/db01_2020-06-25/
   #注意合并增量数据到基础全备，最后一次增量合并不用加--redo-only，如果不是最后一次就要加上（比如需要合并3天的增量，前两天需要加--redo-only，第三天的不需要）

   检查周一的数据是否合并到全备中
   [root@db01 /data/xbkup]# cat db01_2020-06-25/xtrabackup_checkpoints inc_Thursday/xtrabackup_checkpoints inc2_Thursday/xtrabackup_checkpoints
   backup_type = full-prepared
   from_lsn = 0
   to_lsn = 136853667 #与周二的to_lsn相同
   last_lsn = 136853676 #与周二的last_lsn相同
   compact = 0
   recover_binlog_info = 0

   backup_type = incremental
   from_lsn = 136842299
   to_lsn = 136848080
   last_lsn = 136848089
   compact = 0
   recover_binlog_info = 0

   backup_type = incremental
   from_lsn = 136848080
   to_lsn = 136853667
   last_lsn = 136853676
   compact = 0
   recover_binlog_info = 0
   #与周二的to_lsn和last_lsn相同 ，证明周一的增量数据合并成功。

   第四步再一次整理全备（这一次不要加--redo-only）
   [root@db01 /data/xbkup]# innobackupex --apply-log /data/xbkup/db01_2020-06-25

   删除data数据目录下的数据然后做测试恢复
   [root@db01 /data/xbkup]# systemctl stop mysqld
   [root@db01 /data/xbkup]# rm -rf /application/mysql/data/*
   [root@db01 /data/xbkup]# ll /application/mysql/data/

   拷贝全备数据到data数据目录下
   [root@db01 /data/xbkup]# cp -a db01_2020-06-25/* /application/mysql/data/
   [root@db01 /data/xbkup]# innobackupex --copy-back /data/xbkup/db01_2020-06-25
   #上面两条命令功能是等同的 第二条是xtrabackup内置参数命令
   [root@db01 /data/xbkup]# ls /application/mysql/data/
   backup-my.cnf ibdata1 ibtmp1 performance_schema test2 xtrabackup_binlog_info xtrabackup_logfile
   binlog ib_logfile0 ll school wordpress xtrabackup_binlog_pos_innodb xtrabackup_master_key_id
   chenhj ib_logfile1 mdp sys world xtrabackup_checkpoints
   ib_buffer_pool ib_logfile2 mysql test xbk xtrabackup_info

   授予data目录mysql管理权限并启动登录数据库
   [root@db01 /data/xbkup]# chown -R mysql.mysql /application/mysql/data/
   [root@db01 /data/xbkup]# systemctl start mysqld
   select * from t2' at line 1
   mysql> select * from t2 ;
   +------+
   | id |
   +------+
   | 1 |
   | 2 |
   | 3 |
   +------+
   3 rows in set (0.01 sec)

   mysql> select * from t1 ;
   +------+
   | id |
   +------+
   | 1 |
   | 2 |
   | 3 |
   +------+
   3 rows in set (0.00 sec)
   数据恢复成功