1-MySQL - 备份恢复
目录
about
centos7.9 + MySQL5.7.20(tar包安装)
MySQL安装参考：https://www.cnblogs.com/Neeo/articles/13047344.html
运维人员在数据库备份恢复方面的职责
- 设计备份方案，全备？增量？时间？自动？
- 日常备份检查，确认备份是否存在？备份空间是否够用？
- 定期恢复演练，一季度？半年？
- 故障恢复，定期的在测试环境下，人为的模拟出一些故障，通过现有的备份，能够将数据恢复到故障之前的时间节点。
- 迁移，保证停机时间尽量短，准备回退方案。
备份的类型
- 热备：即在线备份，也就是在数据库正常运行时，进行数据备份，并且能够一致性恢复(只能是InnoDB存储引擎)，对业务影响非常小。
- 温备：锁表备份，只能查询不能修改(myisam存储引擎)，影响业务中的写入操作。
- 冷备：关闭数据库，在数据库没有任何变更的情况下，进行数据备份，业务停止。
备份方式及备份工具介绍
- 逻辑备份工具，基于SQL语句进行备份：
- mysqldump+mysqlbinlog：
- 优点：软件自带，无需安装；备份出来的是文本类型的SQL，可读性高，便于备份处理；压缩比高，节省磁盘空间。
- 缺点：依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较消耗资源，数据量大的话，效率比较低。
- 建议：100G以内的数据量级，可以使用mysqldump，超过TB以上，如果使用mysqldump的话，搭配分布式系统也行。
- mysqldump+mysqlbinlog：
- 物理备份工具，基于磁盘数据文件备份：
- xtrabackup(XBK):percona，第三方。
- 优点：类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高。
- 缺点：可读性差；压缩比低，需要更多的磁盘空间。
- 建议：数据量级在TB以上使用。
- MySQL企业版(MySQL Enterprise Backup，MEB)备份工具。
- xtrabackup(XBK):percona，第三方。
备份策略
备份方式：
- 全备，全量备份，备份所有数据。
- 增量，备份变化的数据。
备份周期：根据数据量设计备份周期：
- 数据量较大，例如周日全备，周一到周六增量备份。
- 数据量小的话，每天全备都行。
接下来.....干就完了！！
等等，我的my.cnf文件最终长这样：
[mysqld]
user=mysql
basedir=/opt/software/mysql
datadir=/data/mysql_data
server_id=6
port=3306
socket=/tmp/mysql.sock
secure-file-priv=/tmp
log_bin=/data/mysql_logs/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
[mysql]
socket=/tmp/mysql.sock
# prompt=3306 [\\d]>
[client]
socket=/tmp/mysql.sock
mysqldump
mysqldump是MySQL数据库自带的客户端备份工具，查看mysqldump的使用帮助：
[root@cs ~]# mysqldump --help
mysqldump支持本地备份和远程备份。
在操作前，我们创建一个用于存储备份数据的目录：
[root@cs ~]# mkdir -p /data/mysql_backup
[root@cs ~]# chown -R mysql:mysql /data/mysql_backup/
首先简单了解下备份中常用的参数(不完全)：
| P | Description | 必加参数 | 其他 |
|---|---|---|---|
-A |
备份全部数据库数据 | ||
-B |
备份指定数据库 | ||
--triggers |
备份触发器 | Yes | |
--routines,-R |
备份函数和存储过程 | Yes | |
-events,-E |
备份事件 | Yes | |
-F |
备份时，自动为每个数据库都刷新一个binlog日志文件 | ||
--master-data=2 |
当--master-data=2时，将以注释的方式将position号和binlog的日志文件写入到备份文件中去 |
Yes | |
--single-transaction |
该参数针对于InnoDB存储引擎，实现了快照备份(也可以称之为热备)，特点就是备份时不会锁表 | Yes | |
--set-gtid-purged=OFF |
进行备份时，是否同时备份gtid，默认值是ATUO，等价于ON | ||
--max-allowed-packet |
备份时，设置从mysqld接收和发送包的大小 |
普通参数
-A: 全备参数
将数据全备到本地指定文件中：
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A >/data/mysql_backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cs ~]# du -sh /data/mysql_backup/*
229M /data/mysql_backup/full.sql
-B: 备指定库
备份MySQL数据库中的指定库(一个或多个库，多个库以空格分隔)到本地指定文件中：
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -B sakila world >/data/mysql_backup/sakila_world.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cs ~]# du -sh /data/mysql_backup/*
229M /data/mysql_backup/full.sql
3.5M /data/mysql_backup/sakila_world.sql
备份指定表
分别指定表，也就是只备份指定数据库下的指定表：
# 备份指定库下单个表
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock world city >/data/mysql_backup/world_city.sql
# 备份指定库下多个表
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock world city country >/data/mysql_backup/world_city_country.sql
# 备份指定库下所有表,备份指定库下所有表，针对表的，下面的命令中，只写了库名，省略了表名，意思是备份该库下所有表，没有建库和use语句
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock world >/data/mysql_backup/world_all_table.sql
# 备份指定库下所有表,备份指定库(下所有表)，针对库的
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -B world >/data/mysql_backup/world_all_table.sql
注意，单表备份的备份文件中没有建库和use语句，再恢复时，需要手动建库，这点需要注意。
高级参数
--triggers、--routines、--events
问题来了，备份时，用户针对某些表或者自定义的触发器和函数之类"程序"该怎么办？所以，一般的，在备份时还有三个重要的参数要加，就是下面这三个：
-
--triggers：触发器。 -
--routines，简写-R，存储过程和函数。 -
--events，简写-E，事件(调度器)。
如：
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A --triggers -R -E >/data/mysql_backup/full.sql
在备份时，将触发期、函数、存储过程、事件等等也一起备走，保证恢复后的数据完整性。
-F
备份时，自动为每个数据库都刷新一个binlog日志文件。
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock --triggers -R -E -B world school -F >/data/mysql_backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
3 rows in set (0.00 sec)
--master-data
当--master-data=2时，将以注释的方式将position号和binlog的日志文件写入到备份文件中去：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock --triggers -R -E -B world school --master-data=2 >/data/mysql_backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cs ~]# head -n 30 /data/mysql_backup/full.sql
-- MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: world
-- ------------------------------------------------------
-- Server version 5.7.20-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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
--
-- Current Database: `world`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `world`;
当然，该参数还有其他的值，这里我们不在多表，用到再说。
--single-transaction
该参数针对于InnoDB存储引擎，实现了快照备份(也可以称之为热备)，特点就是备份时不会锁表，备份时加上这个参数就行。
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock --triggers -R -E -A --master-data=2 --single-transaction >/data/mysql_backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
--set-gtid-purged
在开启GTID后，进行备份时，是否同时备份gtid，它有三个值：
- 默认的
--set-gtid-purged=auto，等价于on，主要应用于主从复制环境中。 --set-gtid-purged=OFF，可以在日常的备份中，只回复数据。
当--set-gtid-purged=ON时，备份文件长这样：
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A --set-gtid-purged=ON >/data/mysql_backup/full.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@cs ~]# head -n 30 /data/mysql_backup/full.sql
-- MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.20-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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='';
--
-- Current Database: `binlog`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `binlog` /*!40100 DEFAULT CHARACTER SET latin1 */;
当--set-gtid-purged=OFF时，备份文件长这样：
[root@cs ~]# cls
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A --set-gtid-purged=OFF >/data/mysql_backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cs ~]# head -n 30 /data/mysql_backup/full.sql
-- MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.20-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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `binlog`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `binlog` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `binlog`;
--
-- Current Database: `full_text_test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `full_text_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
一般的，这个参数可以不加，保持默认即可。
--max-allowed-packet
备份时，设置从mysqld接收和发送包的大小。通常应用于大表，在备份时，直接报错，这个时候，我们可以将包的大小限制调大一些。
当然，这个参数也有个系统参数可以：
mysql> select @@max_allowed_packet; -- 4M
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 4194304 |
+----------------------+
1 row in set (0.00 sec)
但我们通常在不去调整这个参数，而是在备份时指定：
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A --max-allowed-packet=256M >/data/mysql_backup/full.sql
案例：从删库到磁盘损坏
环境
创建一个binlog日志的存储目录：
[root@cs ~]# mkdir -p /data/mysql_logs/binlog
[root@cs ~]# chown -R mysql.mysql /data/mysql_logs/binlog
此时，我的数据库配置文件长这样：
[root@cs ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/opt/software/mysql
datadir=/data/mysql_data
server_id=6
port=3306
socket=/tmp/mysql.sock
secure-file-priv=/tmp
# 开启二进制日志
log_bin=/data/mysql_logs/binlog/mysql-bin
binlog_format=row
# 开启GTID
gtid-mode=on
enforce-gtid-consistency=true
[mysql]
socket=/tmp/mysql.sock
# prompt=3306 [\\d]>
背景
- 每天全备。
- binlog日志是完整的。
- 模拟白天的数据变化。
- 在白天的某个时间节，删掉了某个数据，紧随其后，MySQL数据库的数据目录所在的磁盘也坏掉了....
需求
利用全备数据+binlog恢复数据到误删数据库之前。
制造事故现场
首先，模拟出每天的全备，如每晚的23点：
[root@cs ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/mysql_backup/full.sql
然后，模拟白天的数据变化：
create database tt charset utf8;
use tt
create table t1(id int);
insert into t1 values(1),(2),(3);
再然后，就是"失手"删除了数据库：
drop database tt;
没完，删库之后，磁盘也坏掉了：
[root@cs ~]# rm -rf /data/mysql_data/*
[root@cs ~]# pkill mysqld -- 数据都删了，mysqld再跑着也没啥意义了，干掉它吧
现在，磁盘坏掉了，也"失手"删掉了数据，接下来，数据库的恢复工作交给你了.....
基于全备+binlog日志进行数据恢复
数据恢复思路是：
- 首先根据全备数据，将数据库恢复到前一天的23点。
- 23点到"失手"删掉数据库之前，这段时间的数据要根据binlog日志来恢复。
开始吧！
首先检查全备数据和binlog日志还在不在：
[root@cs ~]# ls /data/mysql_logs/binlog/
mysql-bin.000001 mysql-bin.index
[root@cs ~]# ls /data/mysql_backup/ful*
/data/mysql_backup/full.sql
OK，都还在！
开搞吧！等等！！此时，数据库都起不来吧！因为磁盘损坏，导致数据库无法启动，要进行初始化操作。
[root@cs ~]# mysqld --initialize-insecure --user=mysql --basedir=/opt/software/mysql --datadir=/data/mysql_data
2021-05-06T15:29:29.087826Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-06T15:29:29.089101Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2021-05-06T15:29:29.089131Z 0 [ERROR] Aborting
得，有报错，说初始化的时候，数据文件目录非空，那好吧，清空目录后，继续初始化：
[root@cs ~]# ls /data/mysql_data/ # 这是rm -rf 删除后，MySQL还在运行时，创建的缓存文件，就是它搞的鬼，让我们初始化报错
ib_buffer_pool
[root@cs ~]# rm -rf /data/mysql_data/* # 删掉它，继续初始化即可
[root@cs ~]# mysqld --initialize-insecure --user=mysql --basedir=/opt/software/mysql --datadir=/data/mysql_data
2021-05-06T15:31:34.964249Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-06T15:31:35.146179Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-06T15:31:35.176168Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-06T15:31:35.240730Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2443e0bb-ae80-11eb-9330-000c29df6adb.
2021-05-06T15:31:35.241413Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-06T15:31:35.241499Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-06T15:31:35.242620Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
好的，一堆Warning没ERROR，甚好！！
现在，启动数据库，然后，再创建个密码：
[root@cs ~]# systemctl start mysqld.service
[root@cs ~]# mysqladmin -uroot -p password 123 -- 默认密码为空，所以输入密码这里，直接回车即可
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@cs ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
登录进去之后，发现除了自带得数据库，其数据库都没了.......
那接下来，就开始进行数据恢复操作吧。
首先，根据全备数据进行数据恢复，将数据恢复到头一天的23点：
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/mysql_backup/full.sql
......过程太长，省略
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| full_text_test |
| idb |
| my_idb |
| mysql |
| performance_schema |
| sakila |
| school |
| sys |
| temp_db |
| world |
+--------------------+
12 rows in set (0.00 sec)
OK，现在就该考虑如何截取binlog日志了，截取binlog日志要跟position来，那么起点就应该是头天23点备份时的position号；而终点呢？就是删库前的position号，根据这段binlog日志，就可以把数据恢复到删库前。
先来看起点的position号怎么定位？答案是备份时的--master-data=2这个参数记录了起点：
[root@cs ~]# head -n 30 /data/mysql_backup/full.sql
-- MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.20-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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=414;
--
-- Current Database: `binlog`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `binlog` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `binlog`;
从上面的注释中，找到最重要的一行：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=414;
即，binglog当时所使用的binlog日志是'mysql-bin.000004，然后它的position号是414；
好了，起点的position号我们定位好了，来看终点的position号怎么定位吧：
mysql> mysql> show binlog events in 'mysql-bin.000004';
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000004 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'b6a69c9d-ae7c-11eb-b036-000c29df6adb:1' |
| mysql-bin.000004 | 219 | Query | 6 | 414 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| mysql-bin.000004 | 414 | Gtid | 6 | 479 | SET @@SESSION.GTID_NEXT= 'b6a69c9d-ae7c-11eb-b036-000c29df6adb:2' |
| mysql-bin.000004 | 479 | Query | 6 | 580 | create database tt charset utf8 |
| mysql-bin.000004 | 580 | Gtid | 6 | 645 | SET @@SESSION.GTID_NEXT= 'b6a69c9d-ae7c-11eb-b036-000c29df6adb:3' |
| mysql-bin.000004 | 645 | Query | 6 | 738 | use `tt`; create table t1(id int) |
| mysql-bin.000004 | 738 | Gtid | 6 | 803 | SET @@SESSION.GTID_NEXT= 'b6a69c9d-ae7c-11eb-b036-000c29df6adb:4' |
| mysql-bin.000004 | 803 | Query | 6 | 873 | BEGIN |
| mysql-bin.000004 | 873 | Table_map | 6 | 916 | table_id: 436 (tt.t1) |
| mysql-bin.000004 | 916 | Write_rows | 6 | 966 | table_id: 436 flags: STMT_END_F |
| mysql-bin.000004 | 966 | Xid | 6 | 997 | COMMIT /* xid=2836 */ |
| mysql-bin.000004 | 997 | Gtid | 6 | 1062 | SET @@SESSION.GTID_NEXT= 'b6a69c9d-ae7c-11eb-b036-000c29df6adb:5' |
| mysql-bin.000004 | 1062 | Query | 6 | 1148 | drop database tt |
| mysql-bin.000004 | 1148 | Stop | 6 | 1171 | |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
mysqlbinlog --start-position=842 --stop-position=1691 /data/mysql_logs/binlog/mysql-bin.000001 > /tmp/binlog.sql
drop之前的position号是997。现在开始截取binlog日志吧，由于我们此时开启了GTID，所以，这里还要跳过GTID，就是加--skip-gtids参数：
[root@cs ~]# mysqlbinlog --start-position=414 --stop-position=997 --skip-gtids /data/mysql_logs/binlog/mysql-bin.000004 > /tmp/binlog.sql
[root@cs ~]# ls /tmp/bin*
/tmp/binlog.sql
现在，根据截取的binlog日志进行数据恢复即可：
mysql> source /tmp/binlog.sql
......
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| full_text_test |
| idb |
| my_idb |
| mysql |
| performance_schema |
| sakila |
| school |
| sys |
| temp_db |
| tt |
| world |
+--------------------+
13 rows in set (0.00 sec)
mysql> select * from tt.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
OK，我们终于将数据恢复到删库之前了。
xtrabackup
xtrabackup是percona提供的专门用于做备份恢复的工具。
install
percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm的百度云盘链接：https://pan.baidu.com/s/194-Y--JUGZF25e-dIsMLrA 提取码：ko5j
xtrabackup是Perl语言开发，所以还需要配置相关环境和依赖：
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev -y
然后下载安装即可：
# 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
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum install -y percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
安装成功，会有一个innobackupex命令，专门用来做数据的备份恢复，且innobackupex命令依赖/etc/my.cnf文件中的相关参数。
innobackupex命令早期版本仅支持InnoDB存储引擎的数据备份和恢复，但现在版本已经解决了这个问题，同时也支持全备和增量备份。
你可以使用下面的命令检查xtrabackup是否安装成功：
[root@cs ~]# innobackupex --version
xtrabackup: recognized server arguments: --datadir=/data/mysql_data --server-id=6 --log_bin=/data/mysql_logs/binlog/mysql-bin
innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
必要的修改
xtrabackup在执行innobackupex命令时，会自动使用MySQL的socket文件，但默认它去找'/var/lib/mysql/mysql.sock'文件，而我们的socket文件在/tmp下，所以，还需要对MySQL的客户端进行一些参数配置：
[root@cs ~]# vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock
无需重启MySQL服务。
备份方式
物理备份
xtrabackup采取的备份方式类似于cp命令，直接进行物理拷贝数据文件，与此同时，也会拷贝走undo log和redo log。
- 对于非InnoDB表来说，如myisam表，它在备份时会先锁表，然后
cp数据文件，这种形式属于温备的备份方式。 - 对于InnoDB表(支持事务的)来说，不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，算是热备的备份方式。
面试题：xtrabackup在InnoDB表备份的恢复流程
- xtrabackup备份执行的瞬间,立即触发ckpt，将已提交的数据脏页，从内存刷写到磁盘，并记录此时的LSN号
- 备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走，也就是checkpoint LSN之后的日志
- 在恢复之前，模拟Innodb"自动故障恢复"的过程，将redo（前滚）与undo（回滚）进行应用
- 恢复过程是
cp备份到原来数据目录下
全备及恢复
简单的全备示例
全备命令
# 遇事不决，记得help
innobackupex --help
# 备份出的文件目录名称是日期形式的，另外，/data/mysql_backup/xbk 目录不存在会自动创建
[root@cs ~]# innobackupex --user=root --password=123 /data/mysql_backup/xbk
[root@cs ~]# ls /data/mysql_backup/xbk/
2021-05-07_21-26-04
# 自定义目录名,&>/tmp/xbk.log表示将输出结果输出到指定文件，可省略不写
[root@cs ~]# innobackupex --user=root --password=123 --no-timestamp /data/mysql_backup/xbk/full &>/tmp/xbk.log
[root@cs ~]# ls /data/mysql_backup/xbk/
2021-05-07_21-26-04 full
示例：
[root@cs ~]# innobackupex --user=root --password=123 --no-timestamp /data/mysql_backup/xbk/full
....省略输出
[root@cs ~]# ls /data/mysql_backup/xbk
2021-05-07_21-26-04 full
[root@cs ~]# ll /data/mysql_backup/xbk/full/
total 77880
-rw-r-----. 1 root root 487 May 8 19:46 backup-my.cnf
drwxr-x---. 2 root root 20 May 8 19:46 binlog
drwxr-x---. 2 root root 52 May 8 19:46 full_text_test
-rw-r-----. 1 root root 419 May 8 19:46 ib_buffer_pool
-rw-r-----. 1 root root 79691776 May 8 19:46 ibdata1
drwxr-x---. 2 root root 208 May 8 19:46 idb
drwxr-x---. 2 root root 170 May 8 19:46 my_idb
drwxr-x---. 2 root root 4096 May 8 19:46 mysql
drwxr-x---. 2 root root 8192 May 8 19:46 performance_schema
drwxr-x---. 2 root root 4096 May 8 19:46 sakila
drwxr-x---. 2 root root 200 May 8 19:46 school
drwxr-x---. 2 root root 8192 May 8 19:46 sys
drwxr-x---. 2 root root 138 May 8 19:46 temp_db
drwxr-x---. 2 root root 48 May 8 19:46 tt
drwxr-x---. 2 root root 144 May 8 19:46 world
-rw-r-----. 1 root root 104 May 8 19:46 xtrabackup_binlog_info
-rw-r-----. 1 root root 117 May 8 19:46 xtrabackup_checkpoints
-rw-r-----. 1 root root 612 May 8 19:46 xtrabackup_info
-rw-r-----. 1 root root 2560 May 8 19:46 xtrabackup_logfile
一般的，备份产生的目录，我们称之为备份集。
上面备份后的目录中，除了原本的MySQL的相关数据之外，还包含了xtrabackup自己的几个文件，一起来看看。
xtrabackup_binlog_info
[root@cs ~]# cat /data/mysql_backup/xbk/full/xtrabackup_binlog_info
mysql-bin.000006 900 2443e0bb-ae80-11eb-9330-000c29df6adb:1-3,
b6a69c9d-ae7c-11eb-b036-000c29df6adb:1-5
mysql> show binlog events in 'mysql-bin.000006';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000006 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000006 | 123 | Previous_gtids | 6 | 194 | b6a69c9d-ae7c-11eb-b036-000c29df6adb:1-5 |
| mysql-bin.000006 | 194 | Gtid | 6 | 259 | SET @@SESSION.GTID_NEXT= '2443e0bb-ae80-11eb-9330-000c29df6adb:1' |
| mysql-bin.000006 | 259 | Query | 6 | 454 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| mysql-bin.000006 | 454 | Gtid | 6 | 519 | SET @@SESSION.GTID_NEXT= '2443e0bb-ae80-11eb-9330-000c29df6adb:2' |
| mysql-bin.000006 | 519 | Query | 6 | 740 | use `school`; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| mysql-bin.000006 | 740 | Gtid | 6 | 805 | SET @@SESSION.GTID_NEXT= '2443e0bb-ae80-11eb-9330-000c29df6adb:3' |
| mysql-bin.000006 | 805 | Query | 6 | 900 | use `school`; FLUSH PRIVILEGES |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
xtrabackup_binlog_info文件记录的是备份时的二进制日志的position号和GTID号(如果有的话)。
xtrabackup_checkpoints
[root@cs ~]# cat /data/mysql_backup/xbk/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 612558680
last_lsn = 612558689
compact = 0
recover_binlog_info = 0
各字段：
backup_type：备份类型，默认是全备。from_lsn：备份时的lsn号从哪个位置开的:- 全备，从0开始的。
- 如果是增量备份，该值是
last_lsn减9，也就是和to_lsn相等。
to_lsn：当备份命令开始执行时，记录的lsn号。last_lsn：备份结束时的lsn号。如果备份过程中，数据没有变更，那么last_lsn和to_lsn号差9(MySQL5.6这两个是一致的，但5.7中，备份自己使用了9个lsn号，所以二者差9)。compact：略。recover_binlog_info：略。
xtrabackup_info
xtrabackup_info文件是备份时的各种信息的汇总。
[root@cs ~]# cat /data/mysql_backup/xbk/full/xtrabackup_info
uuid = 0aeb8868-aff3-11eb-9ddb-000c29df6adb
name =
tool_name = innobackupex
tool_command = --user=root --password=... --no-timestamp /data/mysql_backup/xbk/full
tool_version = 2.4.12
ibbackup_version = 2.4.12
server_version = 5.7.20-log
start_time = 2021-05-08 19:46:33
end_time = 2021-05-08 19:46:36
lock_time = 0
binlog_pos = filename 'mysql-bin.000006', position '900', GTID of the last change '2443e0bb-ae80-11eb-9330-000c29df6adb:1-3,
b6a69c9d-ae7c-11eb-b036-000c29df6adb:1-5'
innodb_from_lsn = 0
innodb_to_lsn = 612558680
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
xtrabackup_logfile
该文件记录的是备份过程中，产生的redo日志，就不用打开看了。
[root@cs ~]# file /data/mysql_backup/xbk/full/xtrabackup_logfile
/data/mysql_backup/xbk/full/xtrabackup_logfile: data
根据全备恢复数据
xtrabackup默认提供的全备命令，需要数据库的数据目录为空，才能进行数据恢复。而数据目录为空基本上意味着数据库崩了.....
所以，我们模拟一下数据全崩后根据刚才的全备文件进行数据恢复。
模拟数据库全崩然后根据全备进行数据恢复
搞崩数据库：
[root@cs ~]# pkill mysqld
[root@cs ~]# rm -rf /data/mysql_data/*
上面在恢复流程中第三步说过：
- 在恢复之前，模拟Innodb"自动故障恢复"的过程，将redo（前滚）与undo（回滚）进行应用
那这里，xtrabackup也有相应的参数帮我们来做这件事：
[root@cs ~]# innobackupex --apply-log /data/mysql_backup/xbk/full/
然后进行备份恢复就好了：
[root@cs ~]# innobackupex --copy-back /data/mysql_backup/xbk/full/
# 现在，数据就恢复了
[root@cs ~]# ll /data/mysql_data/
total 188460
drwxr-x---. 2 root root 20 May 8 20:33 binlog
drwxr-x---. 2 root root 52 May 8 20:33 full_text_test
-rw-r-----. 1 root root 419 May 8 20:33 ib_buffer_pool
-rw-r-----. 1 root root 79691776 May 8 20:33 ibdata1
-rw-r-----. 1 root root 50331648 May 8 20:33 ib_logfile0
-rw-r-----. 1 root root 50331648 May 8 20:33 ib_logfile1
-rw-r-----. 1 root root 12582912 May 8 20:33 ibtmp1
drwxr-x---. 2 root root 208 May 8 20:33 idb
drwxr-x---. 2 root root 170 May 8 20:33 my_idb
drwxr-x---. 2 root root 4096 May 8 20:33 mysql
drwxr-x---. 2 root root 8192 May 8 20:33 performance_schema
drwxr-x---. 2 root root 4096 May 8 20:33 sakila
drwxr-x---. 2 root root 200 May 8 20:33 school
drwxr-x---. 2 root root 8192 May 8 20:33 sys
drwxr-x---. 2 root root 138 May 8 20:33 temp_db
drwxr-x---. 2 root root 48 May 8 20:33 tt
drwxr-x---. 2 root root 144 May 8 20:33 world
-rw-r-----. 1 root root 612 May 8 20:33 xtrabackup_info
-rw-r-----. 1 root root 1 May 8 20:33 xtrabackup_master_key_id
但有个问题，就是你执行恢复命令的时候，使用的是root用户，所以，再启动MySQL之前，还需要进行授权：
[root@cs ~]# chown -R mysql:mysql /data/mysql_data/*
[root@cs ~]# systemctl restart mysqld.service
[root@cs ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| full_text_test |
| idb |
| my_idb |
| mysql |
| performance_schema |
| sakila |
| school |
| sys |
| temp_db |
| tt |
| world |
+--------------------+
13 rows in set (0.00 sec)
OK了。
增量备份和恢复
增量备份依赖全备。如现在的备份规则是周一到周六是增量备份，周日是全备。
接下来，我们先来演示下，增量备份的一些细节。
准备环境：
# 为了后续方便，删除之前的全备数据
[root@cs ~]# ls /data/mysql_backup/xbk/
2021-05-07_21-26-04 full
[root@cs ~]# rm -rf /data/mysql_backup/xbk/*
[root@cs ~]# ls /data/mysql_backup/xbk/
[root@cs ~]#
# 将binlog日志清空
[root@cs ~]# mysql -uroot -p123
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
增量备份
开始细节增量备份的操作过程吧!
1. 做一些数据库的变更操作
create database t1 charset utf8;
use t1
create table a1(id int);
insert into a1 values(1),(2),(3);
2. 模拟周日的全备
[root@cs ~]# innobackupex --user=root --password=123 --no-timestamp /data/mysql_backup/xbk/full
3. 模拟周一的数据变化
create database t2 charset utf8;
use t2
create table a2(id int);
insert into a2 values(1),(2),(3);
4. 又到了周一的晚上，在夜黑风高的23点，我们要进行增量备份了
[root@cs ~]# innobackupex --user=root --password=123 --no-timestamp --incremental /data/mysql_backup/xbk/inc1 --incremental-basedir=/data/mysql_backup/xbk/full &>/tmp/inc1.log
[root@cs ~]# ll /data/mysql_backup/xbk/inc1/
total 796
-rw-r-----. 1 root root 487 May 8 21:00 backup-my.cnf
drwxr-x---. 2 root root 20 May 8 21:00 binlog
drwxr-x---. 2 root root 79 May 8 21:00 full_text_test
-rw-r-----. 1 root root 419 May 8 21:00 ib_buffer_pool
-rw-r-----. 1 root root 737280 May 8 21:00 ibdata1.delta
-rw-r-----. 1 root root 44 May 8 21:00 ibdata1.meta
drwxr-x---. 2 root root 4096 May 8 21:00 idb
drwxr-x---. 2 root root 4096 May 8 21:00 my_idb
drwxr-x---. 2 root root 4096 May 8 21:00 mysql
drwxr-x---. 2 root root 8192 May 8 21:00 performance_schema
drwxr-x---. 2 root root 4096 May 8 21:00 sakila
drwxr-x---. 2 root root 4096 May 8 21:00 school
drwxr-x---. 2 root root 8192 May 8 21:00 sys
drwxr-x---. 2 root root 73 May 8 21:00 t1
drwxr-x---. 2 root root 73 May 8 21:00 t2
drwxr-x---. 2 root root 230 May 8 21:00 temp_db
drwxr-x---. 2 root root 73 May 8 21:00 tt
drwxr-x---. 2 root root 239 May 8 21:00 world
-rw-r-----. 1 root root 63 May 8 21:00 xtrabackup_binlog_info
-rw-r-----. 1 root root 123 May 8 21:00 xtrabackup_checkpoints
-rw-r-----. 1 root root 643 May 8 21:00 xtrabackup_info
-rw-r-----. 1 root root 2560 May 8 21:00 xtrabackup_logfile
[root@cs ~]# cat /data/mysql_backup/xbk/inc1/
cat: /data/mysql_backup/xbk/inc1/: Is a directory
[root@cs ~]# cat /data/mysql_backup/xbk/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 612569318
to_lsn = 612575669
last_lsn = 612575678
compact = 0
recover_binlog_info = 0
[root@cs ~]# cat /data/mysql_backup/xbk/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 612569318
last_lsn = 612569327
compact = 0
recover_binlog_info = 0
上例：
--incremental表示开启增量备份。/data/mysql_backup/xbk/inc1是增量备份到指定目录。--incremental-basedir=/data/mysql_backup/xbk/full表示，当前增量基于哪个全量日志。
注意，要记得查看确认备份是否成功。
另外，注意观察，在备份时，如果数据没有发生变化，那增量备份xtrabackup_checkpoints文件中的from_lsn号等于全量备份xtrabackup_checkpoints文件中的last_lsn减9。
现在，周一的增量备份已经备份成功了。
5. 一转眼到了周二了，我们模拟周二的数据变化，然后进行增量备份
-- 模拟白天的数据变化
create database t3 charset utf8;
use t3
create table a3(id int);
insert into a3 values(1),(2),(3);
-- 夜黑风高的23点，又该进行增量备份了，该增量备份基于周一的备份
[root@cs ~]# innobackupex --user=root --password=123 --no-timestamp --incremental /data/mysql_backup/xbk/inc2 --incremental-basedir=/data/mysql_backup/xbk/inc1 &>/tmp/inc2.log
[root@cs ~]# cat /data/mysql_backup/xbk/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 612569318
last_lsn = 612569327
compact = 0
recover_binlog_info = 0
[root@cs ~]# cat /data/mysql_backup/xbk/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 612569318
to_lsn = 612575669
last_lsn = 612575678
compact = 0
recover_binlog_info = 0
[root@cs ~]# cat /data/mysql_backup/xbk/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 612575669
to_lsn = 612582977
last_lsn = 612582986
compact = 0
recover_binlog_info = 0
这里也要确认备份是否成功，然后上面有列出了各自的checkpoints文件，注意观察几个lsn号的关系。
6. 到了周三的下午两点，数据库就又坏了....但在坏之前，还有数据变更
-- 模拟白天的数据变化
create database t4 charset utf8;
use t4
create table a4(id int);
insert into a4 values(1),(2),(3);
-- 模拟数据库损坏，这里模拟删除某个数据库
drop database t1;
根据增量备份恢复数据
首先：
- 增量备份不能单独恢复。
- 增量必须按照备份顺序合并到全备中，然后再根据全备恢复。
- 所有备份都需要进行
apply-log过程。 - 需要注意的是，有一部分备份只需要redo不需要undo(--redo-only)，问题来了，这一部分是哪一部分呢？除了最后一个增量备份不需要加
--redo-only，其他备份都需要加。
数据恢复准备工作
- 准备备份。没合并之前的的全备整理，即
apply-log过程，记得加--redo-only：
[root@cs ~]# innobackupex --apply-log --redo-only /data/mysql_backup/xbk/full/
- 合并最后一个增量前的增量到全备，然后同时进行
apply-log过程：
[root@cs ~]# innobackupex --apply-log --redo-only --incremental-dir=/data/mysql_backup/xbk/inc1 /data/mysql_backup/xbk/full/
[root@cs ~]# cat /data/mysql_backup/xbk/full/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 612575669
last_lsn = 612575678
compact = 0
recover_binlog_info = 0
[root@cs ~]# cat /data/mysql_backup/xbk/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 612569318
to_lsn = 612575669
last_lsn = 612575678
compact = 0
recover_binlog_info = 0
此时的全备checkpoints的last_lsn号终于追上了周一的checkpoints的last_lsn号，说明合并成功。
- 合并最后一个增量到全量中，注意不加
--redo-only：
[root@cs ~]# innobackupex --apply-log --incremental-dir=/data/mysql_backup/xbk/inc2 /data/mysql_backup/xbk/full/
[root@cs ~]# cat /data/mysql_backup/xbk/full/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 612582977
last_lsn = 612582986
compact = 0
recover_binlog_info = 0
[root@cs ~]# cat /data/mysql_backup/xbk/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 612575669
to_lsn = 612582977
last_lsn = 612582986
compact = 0
recover_binlog_info = 0
这里的全备的checkpoints的lsn号终于追上了最后一天的增量备份的checkpoints的lsn号，这说明我们的合并过程到此结束。
- 为了避免可能的事务没有回滚完毕，导致数据恢复不完全，我们还需要对全备做最后的整理：
[root@cs ~]# innobackupex --apply-log /data/mysql_backup/xbk/full/
此时此刻，根据全备数据能恢复到周二晚上23点之前的数据，那周三白天的数据到误删数据库之前的数据，就需要根据binlog进行恢复了。
-
binlog截取，这里还是需要判断起点和终点：
- 起点：在周二晚上的增量备份集中的
xtrabackup_binlog_info文件，记录了起点的position号，当然，这里也可以使用GTID，如下，此时全备中已经有了1-9的GTID号，恢复时只需要从10开始即可。
[root@cs ~]# cat /data/mysql_backup/xbk/inc2/xtrabackup_binlog_info mysql-bin.000001 1903 dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:1-9- 终点：从
mysql-bin.000001日志文件中，定位到删库前的那个位置就就是，当然，position和GTID都可以，这里我们使用GTID吧，根据binlog日志中的结果可以确定，删库的GTID是13，那么这里的终点就是12了。
mysql> show binlog events in "mysql-bin.000001"; +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000001 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:1' | | mysql-bin.000001 | 219 | Query | 6 | 320 | create database t1 charset utf8 | | mysql-bin.000001 | 320 | Gtid | 6 | 385 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:2' | | mysql-bin.000001 | 385 | Query | 6 | 478 | use `t1`; create table a1(id int) | | mysql-bin.000001 | 478 | Gtid | 6 | 543 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:3' | | mysql-bin.000001 | 543 | Query | 6 | 613 | BEGIN | | mysql-bin.000001 | 613 | Table_map | 6 | 656 | table_id: 266 (t1.a1) | | mysql-bin.000001 | 656 | Write_rows | 6 | 706 | table_id: 266 flags: STMT_END_F | | mysql-bin.000001 | 706 | Xid | 6 | 737 | COMMIT /* xid=15 */ | | mysql-bin.000001 | 737 | Gtid | 6 | 802 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:4' | | mysql-bin.000001 | 802 | Query | 6 | 903 | create database t2 charset utf8 | | mysql-bin.000001 | 903 | Gtid | 6 | 968 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:5' | | mysql-bin.000001 | 968 | Query | 6 | 1061 | use `t2`; create table a2(id int) | | mysql-bin.000001 | 1061 | Gtid | 6 | 1126 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:6' | | mysql-bin.000001 | 1126 | Query | 6 | 1196 | BEGIN | | mysql-bin.000001 | 1196 | Table_map | 6 | 1239 | table_id: 268 (t2.a2) | | mysql-bin.000001 | 1239 | Write_rows | 6 | 1289 | table_id: 268 flags: STMT_END_F | | mysql-bin.000001 | 1289 | Xid | 6 | 1320 | COMMIT /* xid=41 */ | | mysql-bin.000001 | 1320 | Gtid | 6 | 1385 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:7' | | mysql-bin.000001 | 1385 | Query | 6 | 1486 | create database t3 charset utf8 | | mysql-bin.000001 | 1486 | Gtid | 6 | 1551 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:8' | | mysql-bin.000001 | 1551 | Query | 6 | 1644 | use `t3`; create table a3(id int) | | mysql-bin.000001 | 1644 | Gtid | 6 | 1709 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:9' | | mysql-bin.000001 | 1709 | Query | 6 | 1779 | BEGIN | | mysql-bin.000001 | 1779 | Table_map | 6 | 1822 | table_id: 418 (t3.a3) | | mysql-bin.000001 | 1822 | Write_rows | 6 | 1872 | table_id: 418 flags: STMT_END_F | | mysql-bin.000001 | 1872 | Xid | 6 | 1903 | COMMIT /* xid=67 */ | | mysql-bin.000001 | 1903 | Gtid | 6 | 1968 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:10' | | mysql-bin.000001 | 1968 | Query | 6 | 2069 | create database t4 charset utf8 | | mysql-bin.000001 | 2069 | Gtid | 6 | 2134 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:11' | | mysql-bin.000001 | 2134 | Query | 6 | 2227 | use `t4`; create table a4(id int) | | mysql-bin.000001 | 2227 | Gtid | 6 | 2292 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:12' | | mysql-bin.000001 | 2292 | Query | 6 | 2362 | BEGIN | | mysql-bin.000001 | 2362 | Table_map | 6 | 2405 | table_id: 569 (t4.a4) | | mysql-bin.000001 | 2405 | Write_rows | 6 | 2455 | table_id: 569 flags: STMT_END_F | | mysql-bin.000001 | 2455 | Xid | 6 | 2486 | COMMIT /* xid=93 */ | | mysql-bin.000001 | 2486 | Gtid | 6 | 2551 | SET @@SESSION.GTID_NEXT= 'dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:13' | | mysql-bin.000001 | 2551 | Query | 6 | 2637 | drop database t1 | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ 40 rows in set (0.00 sec)最后的GTID的范围是 10~12。
- 根据起点和重点进行binlog截取：
[root@cs ~]# mysqlbinlog --skip-gtids --include-gtids="dd5b6d9c-aff9-11eb-a0b8-000c29df6adb:10-12" /data/mysql_logs/binlog/mysql-bin.000001 >/tmp/inc2_bin.sql - 起点：在周二晚上的增量备份集中的
到此为止，所有的备份和binlog日志截取都完成了，接下来就可以进行数据恢复了。
数据恢复
这个时候，其实可以搞个测试库进行数据恢复演戏一下，比较保险，但我这里为了省事儿(生产中不能偷懒啊!!!)，就直接按照上面的全备数据的思路进行恢复。
- 清空数据库的数据目录：
[root@cs ~]# pkill mysqld
[root@cs ~]# rm -rf /data/mysql_data/*
[root@cs ~]# ll /data/mysql_data/ # 注意检查是否清空了
total 4
-rw-r-----. 1 mysql mysql 461 May 8 22:31 ib_buffer_pool
[root@cs ~]# rm -rf /data/mysql_data/*
[root@cs ~]# ll /data/mysql_data/
total 0
- 将全备数据拷贝到MySQL的数据目录，这一步做完，数据库恢复到周二晚上23点了：
[root@cs ~]# cp -a /data/mysql_backup/xbk/full/* /data/mysql_data/
[root@cs ~]# ll /data/mysql_data/
total 196668
-rw-r-----. 1 root root 487 May 8 20:53 backup-my.cnf
drwxr-x---. 2 root root 20 May 8 21:49 binlog
drwxr-x---. 2 root root 52 May 8 21:49 full_text_test
-rw-r-----. 1 root root 419 May 8 20:53 ib_buffer_pool
-rw-r-----. 1 root root 79691776 May 8 21:53 ibdata1
-rw-r-----. 1 root root 50331648 May 8 21:53 ib_logfile0
-rw-r-----. 1 root root 50331648 May 8 21:49 ib_logfile1
-rw-r-----. 1 root root 12582912 May 8 21:53 ibtmp1
drwxr-x---. 2 root root 208 May 8 21:49 idb
drwxr-x---. 2 root root 170 May 8 21:49 my_idb
drwxr-x---. 2 root root 4096 May 8 21:49 mysql
drwxr-x---. 2 root root 8192 May 8 21:49 performance_schema
drwxr-x---. 2 root root 4096 May 8 21:49 sakila
drwxr-x---. 2 root root 200 May 8 21:49 school
drwxr-x---. 2 root root 8192 May 8 21:49 sys
drwxr-x---. 2 root root 48 May 8 21:49 t1
drwxr-x---. 2 root root 48 May 8 21:49 t2
drwxr-x---. 2 root root 48 May 8 21:49 t3
drwxr-x---. 2 root root 138 May 8 21:49 temp_db
drwxr-x---. 2 root root 48 May 8 21:49 tt
drwxr-x---. 2 root root 144 May 8 21:49 world
-rw-r-----. 1 root root 63 May 8 21:49 xtrabackup_binlog_info
-rw-r--r--. 1 root root 22 May 8 21:53 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root 117 May 8 21:53 xtrabackup_checkpoints
-rw-r-----. 1 root root 643 May 8 21:49 xtrabackup_info
-rw-r-----. 1 root root 8388608 May 8 21:39 xtrabackup_logfile
-rw-r--r--. 1 root root 1 May 8 21:53 xtrabackup_master_key_id
- 按道理来说，周三白天的数据可以使用binlog进行恢复了，但是binlog恢复需要MySQL服务运行才行，但现在是非运行的状态，所以，启动MySQL服务：
[root@cs ~]# chown -R mysql:mysql /data/mysql_data/*
[root@cs ~]# systemctl start mysqld.service
[root@cs ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| full_text_test |
| idb |
| my_idb |
| mysql |
| performance_schema |
| sakila |
| school |
| sys |
| t1 |
| t2 |
| t3 |
| temp_db |
| tt |
| world |
+--------------------+
16 rows in set (0.00 sec)
此时的数据库中，有t1、t2、t3三个数据库，问题来了：
- 数据库
t1不是在周三的白天被删掉了么？是的，但是t1是在全备之前创建的，所以，被恢复了。 - 数据库
t4怎么没有？因为t4是周三白天创建的，但此时数据才恢复到周二晚上23点，所以，t4还没有。
- 现在，是时候将周三白天的数据恢复到删库前了：
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/inc2_bin.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| full_text_test |
| idb |
| my_idb |
| mysql |
| performance_schema |
| sakila |
| school |
| sys |
| t1 |
| t2 |
| t3 |
| t4 |
| temp_db |
| tt |
| world |
+--------------------+
17 rows in set (0.00 sec)
mysql> select * from t4.a4;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
此时，数据全部恢复完毕。
一番操作下来，是不是觉得增量备份和恢复相对麻烦？是的，所以，如果数据量不大的话，可以选择每周日23点做全备，周一到周六都用binlog就好了。出了问题，全备加binlog也很方便。但如果数据量很大， 导致binlog文件很大的话，恢复起来也很麻烦。所以，最总还是要根据实际的环境，选择合理的备份和恢复的模式。
常见报错
ERROR 1273 (HY000) at line 22: Unknown error 1273
centos7+mysql5.7
ubuntu20.04+mysql8.0
报这个错，问题还是出现在版本不一致上。
报错过程，我从Ubuntu20.04的MySQL8.0的数据库中，使用备份命令把某个数据库备份出来：
然后想在另一个centos7+mysql5.7中恢复数据，但恢复时出现了问题：
[root@localhost luffycity1]# mysqld --version
mysqld Ver 5.7.20 for linux-glibc2.12 on x86_64 (MySQL Community Server (GPL))
[root@localhost luffycity1]# mysql -uroot -p123 --default-character-set=utf8 <db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1273 (HY000) at line 22: Unknown error 1273
解决方案就是解决编码问题，找到从Ubuntu中备份出来的sql文件。
然后替换：
查找：utf8mb4_unicode_ci，如果没搜到utf8mb4_unicode_ci 就搜 utf8mb4_0900_ai_ci 然后全部替换为：utf8_general_ci
查找：utf8mb4 全部替换为 utf8
完事再重新执行恢复命令就OK了。
参考：
- https://blog.csdn.net/lsy_666/article/details/104635038
- https://blog.csdn.net/weixin_43703246/article/details/116406615
that's all, see also:
