MySQL备份管理
一、MySQL备份管理
1.1.1 MySQL备份管理介绍
- 为什么要做备份
有效积极的备份策略，可以提高企业的数据容灾可靠性，防止因意外导致的数据损坏而无法恢复，给企业造成损失。有效的备份策略：全备 增量 时间 自动 ，记住备份是容灾的基础。
- 备份检查测试
日常备份检查:备份存在性,备份空间够用否.
定期恢复演练(测试库):一季度或者半年.
故障恢复:通过现有备份,能够将数据库恢复到故障之前的时间点
迁移（非技术):停机时间,回退方案 (主从切换)
- 备份的类型
1、热备
在数据库正常业务时,备份数据,并且能够一致性恢复（只能是innodb）
对业务影响非常小
2、温备
锁表备份,只能查询不能修改（myisam）
影响到写入操作
3、冷备
关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止
- 备份工具
1、逻辑备份工具
基于SQL语句进行备份
mysqldump（MDP） *****
mysqlbinlog
2、物理备份工具
基于磁盘数据文件备份
xtrabackup(XBK) ：Percona 第三方 *****
MySQL Enterprise Backup（MEB）
- 逻辑备份和物理备份的比较
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增量
其他：通过主从复制备份
- 逻辑备份工具-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到库中。
- 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的备份恢复
- mysql 压缩备份
mysql 压缩备份 压缩还原 命令
1、mysqldump 备份并压缩sql文件
mysql>mysqldump -h主机ip -u用户名 -p密码（也可不输入） 数据库名 | gzip > 压缩后文件位置
2、mysql直接用压缩文件恢复
mysql>gunzip < backupfile.sql.gz | mysql -u用户名 -p密码（也可不输入） 数据库名
- 企业备份恢复案列（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. 评估此次故障的处理的合理性和实用性
- 模拟数据恢复
进行数据全备
[root@db01 ~]# mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction > |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‘ > /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)
- 从全备中导出单表备份
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软件的物理备份
- 安装依赖环境
[root@db01 /data/backup]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
- 下载安装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版本
- 备份命令介绍:
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进行前滚，对未提交的事务进行回滚
最后得到一个一致性备份
- 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
- 数据全备恢复(首先你要有备份数据)
停止和删除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)
数据恢复完成
- 增量备份
备份方式：基于上次的备份的增量
增量备份不能单独恢复，必须合并到全备中，一起恢复
第一步全备：
[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符合增量备份的数据就是正确的，否则错误
- 增量备份恢复
备份恢复准备：将所有增量合并到全备，每个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)
数据恢复成功