MySQL备份与恢复

港南四大炮亡

已于 2023-12-12 15:44:09 修改

阅读量1.5k

点赞数

文章标签：数据库

于 2023-01-25 11:16:31 首次发布

本文链接：https://blog.csdn.net/lpx1249115962/article/details/128759631

版权

15.1 SRE工程师定义
网站稳定性工程师：

网站稳定性:
   1.数据不丢\不泄露
   2.7x24不宕机
   3.访问速度,业务体验更好
   4.效能效率(注重效率,节约成本)

15.2 Linux数据备份？
1)物理备份:
rsync,scp,cp,tar
2)分布式存储,多个副本，本身就是备份,不过一般还是要额外备份。

备份策略:3份副本\不同的机器上\不同的地区

15.3 为什么要做数据备份?
数据丢失\损坏后,可以通过备份恢复数据。

15.4 数据损坏(备份)分类：
物理损坏：磁盘、raid/lvm、数据文件、人为rm误删除。
解决方案：提前cp(tar,rsync,scp)、异机主从复制、备份+binlog日志。

逻辑损坏：使用SQL语句破坏,drop/truncate、delete、update。
解决方案：备份+binlog日志、延时从库。

15.5 备份方式(全备、增量)
全量备份:
整个数据库完整备份(按天100g\按周1T\按月1PB)

增量备份:
保留两个全备周期内的binlog日志,即是增量备份

备份需求=全量备份+增量备份=完整备份

15.6 备份与恢复工具
逻辑备份：用SQL语句备份,工具:mysqldump(MDP)、binlog、主从复制(rsync).....
逻辑恢复：source,mysql,mysqlbinlog

物理备份:拷贝磁盘的IBD文件,工具Percona Xtrabackup(PXB),cp(tar,rsync,scp)
扩展1: 8.0新特性Clone plugin（云原生物理克隆实例）

15.7 DBA备份恢复工作职责
方案：
a. 设计备份策略：备份周期、备份工具、备份方式（全备、增量..）
   1)数据量小(50G内)，每天全备，逻辑备份即可,开启binlog(保留3-7天)。
   2)数据量大（50G+），每天全备，使用物理备份，定期再逻辑备份。,开启binlog(保留7-15天)。

100G以上,10T备份空间的大小,倒推选择备份频率,按天,3个月磁盘满了,3个月以前保留每周的数据留1天.比如每周一

逻辑Or物理?
1.50G以内的小型数据库,选择逻辑备份mysqldump
2.50G以上的数据库,选择物理备份Xtrabackup

30G就可以mysqldump和Xtrabackup同时用.

b. 定期备份并检查备份。(使用zabbix\定期人工)

c. 定期恢复演练。（恢复到测试环境，开发、测试人自己就会测试）

实际解决：
d. 数据恢复。

e. 数据的迁移\升级。

15.8 mysqldump逻辑备份

15.8.1 介绍
mysqldump是逻辑备份工具，以文本形式保存备份，可读性强。
备份思路：将建库建表的语句以及真正数据以SQL语句形式导出到一个sql文件中。

15.8.2 逻辑备份适合场景：
   1)数据量较小场景,50G以内的小型数据库环境。。
   2)跨版本、跨平台迁移。
注意：
一般情况，恢复需要耗时是备份耗时的3-5倍。备份时间超过3个小时，就选择物理备份。
   有主从复制情况,如果物理删除,直接切换从库,只有逻辑删除才会用mysqldump工具.

验证：备份和恢复时间对比，6倍
[root@db01 backup]# time mysqldump -uroot -poldboy123 --set-gtid-purged=OFF -B oldboy world >/server/backup/oldboy_world.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
real   0m3.152s
user   0m1.094s
sys   0m0.368s
[root@db01 backup]#
[root@db01 backup]#
[root@db01 backup]#
[root@db01 backup]# time mysql -uroot -poldboy123 </server/backup/oldboy_world.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
real   0m20.793s
user   0m0.981s
sys   0m0.196s

15.8.3 备份实践:

测试数据:
##oldboy库和t1,t2表
create database oldboy;
use oldboy
CREATE TABLE `t1` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t1 values(11);
insert into t1 values(12);
insert into t1 values(13);

CREATE TABLE `t2` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t2 values(1);
insert into t2 values(1);
insert into t2 values(1);
测试数据:world库city,country,countrylanguage表

1.连接参数
mysqldump -u -p -S -h -P
#注意:MySQL处于开启状态才能用mysqldump

2.备份基础参数
-A 所有全备 *schema等库不会备份,但是会备份mysql库。

mkdir /server/backup/ -p
mysqldump -uroot -poldboy123 -A >/server/backup/full.sql

备份过程有警告，可以不理警告
[root@db01 ~]# mysqldump -uroot -poldboy123 -A >/server/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.
--all-databases(-A) --triggers(触发器) --routines(存储过程) --events(事件)

如果有GTIDs警告,改成如下命令
mysqldump -uroot -poldboy123 --set-gtid-purged=OFF -B oldboy >/server/backup/db.sql

默认备份的所有库结果：
[root@db01 backup]# egrep -wi "Current database" /server/backup/full.sql
-- Current Database: `mysql`
-- Current Database: `oldboy`
-- Current Database: `oldboy3`
-- Current Database: `school`
-- Current Database: `world`

-B 单库或多库备份

2)备份oldboy:
[root@db01 ~]# mysqldump -uroot -poldboy123 -B oldboy >/server/backup/oldboy.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]# egrep -v "\*|^$|#|\-\-" /server/backup/oldboy.sql
USE `oldboy`;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (11),(12),(13);
UNLOCK TABLES;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
LOCK TABLES `t2` WRITE;
INSERT INTO `t2` VALUES (1),(1),(1);
UNLOCK TABLES;

2)备份oldboy和world两个库：
[root@db01 ~]# mysqldump -uroot -poldboy123 -B oldboy world >/server/backup/old_world.sql
[root@db01 ~]# egrep -wi "Current database" /server/backup/old_world.sql
-- Current Database: `oldboy`
-- Current Database: `world`

注意：
-A和-B，备份内容都带有create database xxx和use 库名;语句，直接恢复即可
mysql -uroot -poldboy123 </tmp/oldboy.sql

恢复:
[root@db01 ~]# mysql -uroot -poldboy123 </server/backup/oldboy.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@db01 ~]# mysql -uroot -poldboy123 -e "show databases;show tables from oldboy;select * from oldboy.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| sys |
| world |
+--------------------+
+------------------+
| Tables_in_oldboy |
+------------------+
| t1 |
| t2 |
+------------------+
+------+
| id |
+------+
| 11 |
| 12 |
| 13 |
+------+

如果备份没有带-A和-B,则用下面的方式恢复：
mysql -uroot -poldboy123 oldboy</tmp/oldboy.sql #指定oldboy恢复相当于使用了use oldboy;

假设10个库,如何分库备?
mysqldump -uroot -poldboy123 -B oldboy >/server/backup/oldboy.sql
mysqldump -uroot -poldboy123 -B world >/server/backup/world.sql

分库备份目的:用于恢复单个库或库里的表：

分库备份脚本:
#思路
for dbname in '库列表'
do
mysqldump -uroot -poldboy123 $dbname >/server/backup/$dbname.sql
done

#获取库列表
[root@db01 ~]# mysql -uroot -poldboy123 -e "show databases" 2>/dev/null|sed 1d|egrep -v "_schema"
mysql
oldboy
sys
world

##完整数据演示
dbnames=`mysql -uroot -poldboy123 -e "show databases" 2>/dev/null|sed 1d|egrep -v "_schema"`
for dbname in $dbnames
do
mysqldump -uroot -poldboy123 $dbname >/server/backup/fenku_$dbname.sql
done

库列表:
[root@db01 backup]# mysql -uroot -poldboy123 -e "show databases;" \
2>/dev/null|sed 1d|egrep -v "sys|_schema|mysql"
oldboy
oldboy3
school
world

最终分库备份脚本：
for dbname in `mysql -uroot -poldboy123 -e "show databases;" \
2>/dev/null|sed 1d|egrep -v "sys|_schema|mysql"`
do
mysqldump -uroot -poldboy123 --set-gtid-purged=OFF $dbname >/server/backup/$dbname.sql
done
[root@db01 backup]# ls
full.sql mul_db.sql oldboy3.sql oldboy.sql school.sql world.sql

单表或多表备份(不加-B或-A)

##同时备份world库的city和country表
mysqldump -uroot -poldboy123 world city country >/server/backup/tb.sql

如何备份多张表?
mysqldump -uroot -poldboy123 world city >/server/backup/world_city.sql
mysqldump -uroot -poldboy123 world country >/server/backup/world_country.sql
....

分表备份原理
mysqldump -uroot -poldboy123 world city>/server/backup/world_city.sql
mysqldump -uroot -poldboy123 world country >/server/backup/world_country.sql
...
mysqldump -uroot -poldboy123 oldboy t1>/server/backup/oldboy_t1.sql
mysqldump -uroot -poldboy123 oldboy t2 >/server/backup/oldboy_t2.sql

分库分表批量备份:

#思路
for dbname in '库列表'
do
mysqldump -uroot -poldboy123 $dbname >/server/backup/$dbname.sql
done

#表列表:
[root@db01 ~]# mysql -uroot -poldboy123 -e "show tables from world;" 2>/dev/null|sed 1d
city
country
countrylanguage

#shell:双层for循环.
#脚本原理
for dbname in '库列表'
do
for tablename in `表列表`
   do
       mysqldump -uroot -poldboy123 $dbname $tablename >/server/backup/${dbname}_$tablename.sql
   done
done

##分库分表演示脚本:
mysql_cmd="mysql -uroot -poldboy123"
dbnames=$($mysql_cmd -e "show databases;" 2>/dev/null|sed 1d|egrep -v "_schema|sys|mysql")
for dbname in $dbnames
do
tnames=$($mysql_cmd -e "show tables from $dbname;" 2>/dev/null |sed 1d)
for tname in $tnames
do
mysqldump -uroot -poldboy123 --set-gtid-purged=OFF $dbname $tname >/server/backup/${dbname}_${tname}.sql
done
done

结果：
[root@db01 backup]# ls -l
总用量 48880
-rw-r--r-- 1 root root 49781236 4月 27 19:38 oldboy_t100w.sql
-rw-r--r-- 1 root root 1799 4月 27 19:38 oldboy_t1.sql
-rw-r--r-- 1 root root 2037 4月 27 19:38 school_course.sql
-rw-r--r-- 1 root root 2105 4月 27 19:38 school_sc.sql
-rw-r--r-- 1 root root 2263 4月 27 19:38 school_student.sql
-rw-r--r-- 1 root root 1991 4月 27 19:38 school_teacher.sql
-rw-r--r-- 1 root root 179164 4月 27 19:38 world_city.sql
-rw-r--r-- 1 root root 28325 4月 27 19:38 world_countrylanguage.sql
-rw-r--r-- 1 root root 38939 4月 27 19:38 world_country.sql

基于表恢复:
方法1:
mysql -uroot -poldboy123 oldboy</server/backup/oldboy_t1.sql
方法2:
mysql> use oldboy
mysql> source /server/backup/oldboy_t1.sql

生产经验如何抉择?
全备、分库、分表，备份方案选1种还是3种都选。

1.全备：恢复一个库或者一个表，很累。
如何恢复一个库或者一个表？
方法1：sed,awk筛选某库或表的所有sql语句。
方法2：借用一个临时数据库，把全备恢复进去，然后导出需要库或者表成sql，然后恢复到用的机器。
2.分库分表：
方法1：将来需要完全恢复，可以写脚本批量恢复。
方法2：同时全备加分库分表，要备份两次，浪费磁盘和备份时间。

3.选择：影响的恢复的时间
1）数据量不是特别大，又有磁盘空间，工作可以选择全备加分库分表备，选择从库备份(空间换时间)。
2）选择全备也可以。

3.数据恢复

1)一般恢复方法
库级别恢复方法1:
mysql>source /server/backup/oldboy_world.sql

#表级别恢复方法:需要指定库
mysql>use oldboy;
mysql>source /server/backup/oldboy_world.sql

库级别恢复方法2:
#mysql </server/backup/oldboy_world.sql

#表级别恢复方法:需要指定库
#mysql oldboy </server/backup/oldboy.sql

2)全库备份恢复单库策略方案
多个库同时备份,将来恢复某一个库.其他库会被覆盖,如何解决?
   1.分库分表备.按需恢复.
   2.没有分库分表,但是有全备,用awk,grep抽出要恢复的数据.
   3.把备份恢复到一个测试库,然后把需要的备份出来,在恢复到正式库.

注意:单表或多表备份方式,没有create database和use语句，所以要手工进行建库和use 库，再恢复数据。
只要不加-B或-A就没有create database和use语句
CREATE DATABASE `oldboy`;
USE `oldboy`;

3)单表或多表恢复方法:
恢复方法1:
create database oldboy
use oldboy
source /opt/back.sql
--------------------------
恢复方法2:
create database oldboy
mysql oldboy</opt/back.sql ##在mysql内部执行了use oldboy;source /opt/back.sql

4. mysqldump高级功能参数
问题：生产库有人删除数据库了，如何恢复？
全备+binlog恢复数据时，binlog截取的起点问题？

面试:如何获取全备之后的binlog位置起点?
1)--master-data
#(8.0已经弃用--master-data,使用--source-data替代)

参数一：--master-data
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.

作用：
a. 自动记录备份前临界binlog位置点
b. 自动加GRL锁(FTWRL ,命令：flush tables with read lock)
c. 配合--single-transaction ,减少锁的时间,基于事务的innodb引擎才能这样用。

--master-data参数如何设置:
===========================
--master-data=1
[root@db01 /server/backup]# sed -n 23p oldboy_world1.sql
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000011', MASTER_LOG_POS=42578170;

--master-data=2 ##推荐2
[root@db01 /server/backup]# sed -n 23p oldboy_world2.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000011', MASTER_LOG_POS=42578170;
-- 这两个杠是注释

主从复制导出数据时候:
mysql> flush table with read lock;
mysql> show master status;
mysql> unlock table;

8.0 --source-data=2,
8.0以前--master-data=2

参数二： --single-transaction

举例：查班级人数
1.锁门==数据库加锁
2.拍照片==--single-transaction

参数说明
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.

参数作用：热备:备份的同时,可以读写.
a. 对于InnoDB表，利用MVCC中一致性快照进行备份。
备份数据时,不加锁（8.0前备份元数据依旧会加锁）

b. 注意:备份期间如果出现DDL语句操作，会导致备份数据不一致.

面试问题1：mysqldump是严格意义上的热备吗？
8.0之后master-data和single-transaction,对于InnoDB数据备份时是快照备份的.
备份表结构等数据时,还是FTWRL过程中备份.

--single-transaction 只是针对InnoDB表的数据进行一致性快照备份。

面试问题2： mysqldump备份需要锁表吗？
答:备份表的数据的时候不需要锁表
备份表结构以及在myisam引擎数据备份时,还是FTWRL过程中备份。

#备份例子
mysqldump -uroot -poldboy123 --source-data=2 --single-transaction \
--set-gtid-purged=OFF -B world >/server/backup/world.sql

参数三四五： --triggers(触发器) --routines(存储过程) --events(事件) 备份特殊对象
-R 存储过程、函数
--triggers 触发器
-E 事件

参数六：--max_allowed_packet=64M
最大允许数据包的大小。

参数七：-F, --flush-logs
备份的同时刷新binlog文件从新文件156位置开始

参数八: -d 只备份表结构
-d
###示例:
mysqldump -uroot -poldboy123 --source-data=2 -d oldboy >/server/backup/oldboy_nodata.sql
# egrep -v "\*|\-\-|^$" /server/backup/oldboy_nodata.sql
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

5.2.4 生产标准化备份语句
mysqldump \
-uroot -poldboy123 \
-A --source-data=2 \
--single-transaction \
--set-gtid-purged=OFF \
-R -E --triggers --max_allowed_packet=64M \
>/server/backup/full_`date +%F`.sql

#gzip压缩备份
mysqldump \
-uroot -poldboy123 \
-A --master-data=2 \
--single-transaction \
--set-gtid-purged=OFF \
-R -E --triggers --max_allowed_packet=64M \
|gzip>/server/backup/full_`date +%F`.sql.gz

压缩和没压缩差距4倍左右。
1.5M /server/backup/full_2022-09-28.sql
344K /server/backup/full_2022-09-28.sql.gz

如何解压:
gzip -d /server/backup/full_`date +%F`.sql.gz #会删除压缩包文件

所有参数总结:
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-E, --events Dump events.

-F, --flush-logs Flush logs file in server before starting dump. Note that

-h, --host=name Connect to host.
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
(Defaults to on; use --skip-lock-tables to disable.)

--source-data[=#] This causes the binary log position and filename to be
--master-data[=#] This option is deprecated and will be removed in a future
version. Use source-data instead.
--max-allowed-packet=#

-d, --no-data No row information.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-P, --port=# Port number to use for connection.
-R, --routines Dump stored routines (functions and procedures).
--set-gtid-purged[=name]
--single-transaction
-S, --socket=name The socket file to use for connection.
-T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
--triggers Dump triggers for each dumped table.
(Defaults to on; use --skip-triggers to disable.)
-u, --user=name User for login if not current user.

5.2.5 binlog增量备份

1)本地备份
8.0默认开启状态,5.6/5.7默认没开.
[root@db01 ~]# ls /data/3306/data/bin* -l
-rw-r----- 1 mysql mysql 179 7月 14 16:13 /data/3306/data/binlog.000001
-rw-r----- 1 mysql mysql 179 7月 14 16:14 /data/3306/data/binlog.000002
-rw-r----- 1 mysql mysql 42210207 7月 15 10:03 /data/3306/data/binlog.000003
-rw-r----- 1 mysql mysql 179 7月 15 10:09 /data/3306/data/binlog.000004
-rw-r----- 1 mysql mysql 179 7月 15 10:21 /data/3306/data/binlog.000005
-rw-r----- 1 mysql mysql 179 7月 15 10:40 /data/3306/data/binlog.000006
-rw-r----- 1 mysql mysql 179 7月 15 12:16 /data/3306/data/binlog.000007
-rw-r----- 1 mysql mysql 179 7月 15 15:05 /data/3306/data/binlog.000008

2)异地备份:
   1.rsync定时每分钟增量备份binlog #单机丢数据
   2.sersync+rsync实时备份. #单机
   3.mysqlbinlog远程实时获取binlog.#单机
   4.从库记录binlog(主库从库同时有binlog) ##推荐

单机如何备份binlog：没有需求,至少主从复制
1.rsync定时每分钟
2.rsync+sersync实时，基于文件系统。
3.mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never binlog.000001 --result-file=/opt/

解释如下：
--read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。
--raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。
--user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。
--stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开，mysqlbinlog就会不断的复制远程服务器上的binlog。
mysql-bin.000001：代表从哪个binlog开始复制。

实践：
mysql> create user repl@'10.0.0.%' identified by 'oldboy123';
mysql> grant replication slave on *.* to repl@'10.0.0.%';
mysql> flush privileges;

在B机器上执行；
mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never binlog.000001 --result-file=/opt/ &

A机器上测试写入：
drop database oldboy3;
create database oldboy3;
use oldboy3;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
drop table t1;

B机器上查看结果；
[root@db01 ~]# cd /opt/
[root@db01 opt]# ll
总用量 893380
-rw-r----- 1 root root 4142 4月 22 10:37 binlog.000001
[root@db01 opt]# mysqlbinlog binlog.000001|tail -5
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
如果有主从复制环境，从库记录binlog，这样就是binlog异地备份。

5.2.6 案例：通过mysqldump全备+binlog实现生产数据增量恢复

环境背景：中型业务数据库20G，每天00:00全备，binlog异地备份。
故障场景：周三下午14点，开发人员连接数据库实例错误(连错实例)，导致生产数据被误删除（DROP）
发现过程：用户发现----运营人员---开发人员----运维或DBA----根据报错---检查数据库

生产恢复思路：
       1. 业务网站端挂维护页。

       2. 检查全量备份、日志是否可用。

       3. 如果只是部分损坏，建议找一个应急库进行恢复
           a. 全备恢复
           b. 日志截取并恢复

       4. 恢复后数据校验（业务测试部门验证）

       5. 立即备份（停机冷备cp /data /opt）

       6. 恢复架构系统（例如主从）

       7. 撤维护页，恢复业务访问

模拟环境：周一周二模拟正常写入数据
mysql> create database mdb;
mysql> use mdb
mysql> create table t1 (id int);
mysql> create table t2 (id int);

mysql> insert into t1 values(1),(2),(3);
mysql> insert into t2 values(1),(2),(3);

mysql> select * from t1;
mysql> select * from t2;

周二晚上00点开始备份
date -s "2022/09/28 00:00:00"
mysqldump -uroot -poldboy123 -B mdb --source-data=2 --set-gtid-purged=OFF \
--single-transaction -R -E --triggers --max_allowed_packet=64M >/server/backup/oldboy_`date +%F`.sql

备份后继续写入数据(全备没有,进入最新的binlog)
mysql> create table t3 (id int);
mysql> insert into t3 values(1),(2),(3);
mysql> insert into t2 values(4),(5),(6);
mysql> select * from t3;
mysql> select * from t2;

周三下午14点，误删数据库
date -s "2022/07/18 14:00:00"
mysql> drop database mdb;

恢复过程：按照恢复思路来：
=====
a. 查看备份全备和增量备份完整性
[root@db01 /server/backup]# cd /server/backup/
[root@db01 /server/backup]# ls -l
总用量 4
-rw-r--r-- 1 root root 2780 7月 18 00:00 oldboy_2022-09-28.sql

b. 查看增量备份完整性
[root@db01 /server/backup]# grep -i change oldboy_2022-09-28.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000014', MASTER_LOG_POS=1274;

[root@db01 /server/backup]# ls -l /data/3306/data|grep binlog
-rw-r----- 1 mysql mysql 156 7月 17 18:52 binlog.000011
-rw-r----- 1 mysql mysql 8523 7月 18 12:17 binlog.000012
-rw-r----- 1 mysql mysql 200 7月 18 12:18 binlog.000013
-rw-r----- 1 mysql mysql 2201 7月 18 14:00 binlog.000014 ##binlog.000014 1274
-rw-r----- 1 mysql mysql 224 7月 18 12:18 binlog.index

备份binlog
[root@db01 /server/backup]# mkdir binlog
[root@db01 /server/backup]# cp /data/3306/data/binlog.* binlog/
[root@db01 /server/backup]# ls binlog/
binlog.000001 binlog.000004 binlog.000007 binlog.000010 binlog.000013
binlog.000002 binlog.000005 binlog.000008 binlog.000011 binlog.000014
binlog.000003 binlog.000006 binlog.000009 binlog.000012 binlog.index

b.恢复全备准备(先不要执行)
###mysql -uroot -poldboy123 </server/backup/oldboy_2022-09-28.sql

c.恢复binlog准备

1）获取二进制日志起始位置点
[root@db01 /server/backup]# grep -i change oldboy_2022-09-28.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000014', MASTER_LOG_POS=1274;
从binlog.000014文件的1274位置点向下开始恢复。

[root@db01 /server/backup]# ls binlog/ -l
-rw-r----- 1 root root 8523 7月 18 15:07 binlog.000012
-rw-r----- 1 root root 200 7月 18 15:07 binlog.000013
-rw-r----- 1 root root 2201 7月 18 15:07 binlog.000014
-rw-r----- 1 root root 224 7月 18 15:07 binlog.index

解析生成binlog
思考一个问题： binlog位置点是备份开始时，还是备份结束时的位置点？

恢复思路1：
已知执行了drop语句，此时恢复直接从binlog.000014的1274位置点向下开始恢复
到binlog.000014结束。然后编辑删除里面drop database mdb;即可

恢复思路2：
从binlog.000014的1274位置点向下开始恢复,恢复到drop语句的位置前面，drop后面无语句.

d. 结束位置点:截取binlog
mysql> show binlog events in "binlog.000014";
+---------------+------+-------------+----------------------------------------------------+
| Log_name | Pos | End_log_pos | Info |
+---------------+------+-------------+----------------------------------------------------+
| binlog.000014 | 4 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| binlog.000014 | 125 | 156 | |
| binlog.000014 | 156 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 233 | 338 | create database mdb /* xid=9529 */ |
| binlog.000014 | 338 | 415 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 415 | 525 | use `mdb`; create table t1 (id int) /* xid=9534 */ |
| binlog.000014 | 525 | 602 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 602 | 712 | use `mdb`; create table t2 (id int) /* xid=9535 */ |
| binlog.000014 | 712 | 791 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 791 | 865 | BEGIN |
| binlog.000014 | 865 | 912 | table_id: 772 (mdb.t1) |
| binlog.000014 | 912 | 962 | table_id: 772 flags: STMT_END_F |
| binlog.000014 | 962 | 993 | COMMIT /* xid=9536 */ |
| binlog.000014 | 993 | 1072 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 1072 | 1146 | BEGIN |
| binlog.000014 | 1146 | 1193 | table_id: 773 (mdb.t2) |
| binlog.000014 | 1193 | 1243 | table_id: 773 flags: STMT_END_F |
| binlog.000014 | 1243 | 1274 | COMMIT /* xid=9537 */ |
| binlog.000014 | 1274 | 1351 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 1351 | 1461 | use `mdb`; create table t3 (id int) /* xid=9600 */ |
| binlog.000014 | 1461 | 1540 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 1540 | 1614 | BEGIN |
| binlog.000014 | 1614 | 1661 | table_id: 809 (mdb.t3) |
| binlog.000014 | 1661 | 1711 | table_id: 809 flags: STMT_END_F |
| binlog.000014 | 1711 | 1742 | COMMIT /* xid=9601 */ |
| binlog.000014 | 1742 | 1821 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 1821 | 1895 | BEGIN |
| binlog.000014 | 1895 | 1942 | table_id: 794 (mdb.t2) |
| binlog.000014 | 1942 | 1992 | table_id: 794 flags: STMT_END_F |
| binlog.000014 | 1992 | 2023 | COMMIT /* xid=9602 */ |
| binlog.000014 | 2023 | 2100 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 2100 | 2201 | drop database mdb /* xid=9605 */ |
+---------------+------+-------------+----------------------------------------------------+

解析命令:
cd /server/backup/binlog
mysqlbinlog --skip-gtids --start-position=1274 --stop-position=2100 \
binlog.000014 >/tmp/bin.sql
grep -i drop /tmp/bin.sql
mysql -uroot -poldboy123 mdb </tmp/bin.sql

e:开始恢复
完整恢复方法1:
mysql -uroot -poldboy123 </server/backup/oldboy_2022-09-28.sql
#检查
[root@db01 /server/backup/binlog]# mysql -uroot -poldboy123 -e "show tables from mdb;select * from mdb.t1; select * from mdb.t2;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_mdb |
+---------------+
| t1 |
| t2 |
+---------------+
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
##恢复binlog
mysql -uroot -poldboy123 mdb </tmp/bin.sql
##检查恢复结果.

完整恢复方法2:
mysql> set sql_log_bin=0; ##临时不记录binlog。
mysql> source /server/backup/oldboy_2022-09-28.sql
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;
e.恢复的时候执行的语句再写入binlog

破坏:不是drop database mdb;而是update st1 set name='oldboy';该如何恢复.
答:如果数据很重要,离开停止业务访问(前端负载均衡器立刻挂维护页).

5.2.7 扩展:备份恢复企业面试题 (课后作业)
生产中备份的文件50G数据库，误删除了一张t1表10M大小，有什么思路可以快速恢复？

思路:表的全备+表的增量(截取一张表)

解题思路：
恢复表全备数据思路：
a.备份时分表备份。直接拿到全备文件就是分表的.

b.从全备中，将单表建表语句和insert语句提取出来，进行恢复
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q' /server/backup/oldboy_2022-09-28.sql >/tmp/t1.sql
grep -i 'INSERT INTO `t1`' /server/backup/oldboy_2022-09-28.sql >>/tmp/t1.sql

c.把全备恢复到第三方库，然后在导出需要的表。速度慢

d. binlog默认记录所有库所有表的日志,几乎无可能性。
从binlog中单独截取单表的所有binlog，进行恢复。

b.从全备中将单表建表语句和insert语句提取出来实践：
[root@db01 binlog]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q' /server/backup/oldboy_2022-04-28.sql >/tmp/t1.sql
[root@db01 binlog]# grep -i 'INSERT INTO `t1`' /server/backup/oldboy_2022-04-28.sql >>/tmp/t1.sql
[root@db01 binlog]#
[root@db01 binlog]# cat /tmp/t1.sql
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t1` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES (1),(2),(3);

恢复binlog数据思路：binlog默认记录的所有库所有表的日志

1.用一个测试库,导入正式库所有表结构语句,然后将需要的binlog导入测试库。
然后将需要的表的导出来(需要的表的binlog)。

2.先取库,在取表
1)mysqlbinlog -d oldboy,只能截取单库binlog，没有截取单表参数
2)过滤出需要的表的内容
a.用上面恢复全备中的方案。
b.binlog2sql截取单表binlog恢复。

#########
全备备份+binlog备份说明.
1.最好只应用于逻辑损坏（SQL语句破坏数据）,其他情况恢复不要用,因为很慢.
2.物理损坏只需要使用主从复制从库恢复。
3.除非逻辑损坏导致所有从库损坏，此时需要全备备份+binlog备份恢复。

binlog2sql应用见单独文件

15.9 物理备份工具使用-Percona Xtrabackup（PXB）

15.9.1 Xtrabackup介绍
Xtrabackup物理备份工具，支持全备和增量备份(注意不是binlog增量,而是数据文件中数据页的增量)。
备份原理：
a. 数据库运行期间，拷贝数据表空间对应数据文件(idb等物理文件).
b. 拷贝的同时，会将[备份期间]生成的redo日志进行备份.

恢复原理：
   模拟了InnoDB Crash Recovery(CR)功能，LSN
   1)将备份的数据文件的数据页和备份期间的记录的redo数据页对比，
   2)将备份的数据文件进行处理，执行redo log前滚和undo回滚，生成新的数据文件，
   3)复制到原有数据库的路径下面，重启,才算恢复完成。

5.1 安装PXB
注意：percona-xtrabackup版本说明

第三方软件：
MySQL8.0.20+版本，使用PXB8.0.12+以上版本，
MySQL8.0.11~8.0.19 使用PXB8.0正式版本。
MySQL8.0之前（5.6，5.7）版本：使用PXB2.4
[版本错误，用不了]。

#wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm --no-check-certificate
yum install percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm
yum install percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm

xtrabackup命令参数
--user=USER   用于备份数据的用户
--password=PASSWD   用于备份数据的用户对应的密码
--port=PORT   数据库端口
--host=HOST   备份的主机，主机可以是远程数据库的服务器
--defaults-file   指定MySQL的配置文件备份
--incremental-basedir=DIRECTORY   增量备份使用，上一次（全备）增量备份所在目录
--apply-log   回滚未提交的事务数据，应用redo日志数据。
--apply-log-only 仅应用redo日志数据。
--copy-back   将备份数据复制回原始位置（也可以用mv直接拷贝）
--parallel=N   当数据库大的时候，增加多线程备份，N为数字

5.2 xtrabackup全量备份

1.全量备份
全量备份的目录为： mkdir -p /server/backup/full

远程：
xtrabackup --defaults-file=/etc/my.cnf ---host=10.0.0.51\
--port=3306 --user=root --password=oldboy123 \
--backup --target-dir=/server/backup/full

本地：
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --backup --target-dir=/server/backup/full

-rw-r----- 1 root root 60 12月 17 23:25 xtrabackup_binlog_info
-rw-r----- 1 root root 105 12月 17 23:25 xtrabackup_checkpoints
-rw-r----- 1 root root 581 12月 17 23:25 xtrabackup_info
-rw-r----- 1 root root 4096 12月 17 23:25 xtrabackup_logfile
-rw-r----- 1 root root 39 12月 17 23:25 xtrabackup_tablespaces

binlog_info:
[root@oldboy opt]# cd /server/backup/full
[root@db01 full]# cat xtrabackup_binlog_info
binlog.000011 196 3d5846bb-bec3-11ec-bd1d-000c296ebb8f:1-326

2.数据恢复：
2.1 搞破坏
[root@oldboy ~]# pkill mysqld
[root@oldboy ~]# cd /data/3306/data/;rm -rf *

2.2 准备 --prepare：自动故障CR(crash recover)
xtrabackup --prepare --target-dir=/server/backup/full
说明：模拟CR过程，将redo前滚，undo回滚，让备份数据是一致状态,是备份停止时刻数据的状态

2.2 拷回数据 --copy-back：
##方法1:
cp -r /server/backup/full/* /data/3306/data/
##方法2:
xtrabackup --copy-back --target-dir=/server/backup/full

ls /data/3306/data/ -l

2.3 修改权限并启动数据库
[root@oldboy data]# chown -R mysql.mysql /data/3306/
[root@oldboy data]# /etc/init.d/mysqld start

[root@db01 /data/3306/data]# /etc/init.d/mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/data/3306/data/db01.pid).

报错:Multiple files found for the same tablespace ID
2022-09-28T09:31:42.208062Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.26) starting as process 7909
2022-09-28T09:31:42.215422Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-09-28T09:31:42.220977Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2022-09-28T09:31:42.221060Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 4294967278 = ['undo_002', 'undo_002']
2022-09-28T09:31:42.221113Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 4294967279 = ['undo_001', 'undo_001']
2022-09-28T09:31:42.221179Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Failed, retry may succeed.
2022-09-28T09:31:42.221278Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-09-28T09:31:42.221487Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-09-28T09:31:42.221599Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-09-28T09:31:42.222302Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.26) MySQL Community Server - GPL.
解答:错误原因
前面undologs更改了单独的目录所致
[root@db01 /data/3306/data]# ll /data/3306/undologs/
总用量 32768
-rw-r----- 1 mysql mysql 16777216 7月 18 17:59 undo_001
-rw-r----- 1 mysql mysql 16777216 7月 18 17:59 undo_002
使用xtrabackup备份时,会把undo_00*备份到full目录下,恢复时恢复到/data/3306/data/下,而实际是在/data/3306/undologs/下.

解决:
方法1:
[root@db01 /data/3306/data]# mv *undo* /tmp/
[root@db01 /data/3306/data]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!

方法2:把undo log路径改为/data/3306/data/

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_undo_directory=/data/3306/data
[root@db01 ~]# mv /data/3306/undologs/* /data/3306/data/

mysql> ALTER UNDO TABLESPACE oldboy_undo1 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP UNDO TABLESPACE oldboy_undo1;
Query OK, 0 rows affected (0.01 sec)

[root@db01 /data/3306/data]# ll /data/3306/undologs/
总用量 32768
-rw-r----- 1 mysql mysql 16777216 7月 18 17:59 undo_001
-rw-r----- 1 mysql mysql 16777216 7月 18 17:59 undo_002

5.3 xtrabackup增量备份
xtrabackup增量备份和binlog不一样。xtrabackup增量是基于redo log以及数据文件数据页变化的备份。

5.3.1 介绍

增量备份，是基于上一次备份后LSN变化过的数据页进行备份，在备份同时产生的新变更，会将变更的redo信息备份。

第一次增量是依赖于上一次全备的。
将来的恢复也要增量合并到全备中，再进行统一恢复。

完整的数据:
全备:所有数据文件+备份期间产生的redo log.
增量:增量的数据页和redo log

5.3.2 为什么需要PXB增量备份？
数据量过大,比如几百G以上的时候.
单次备份是500G，一周7天 3.5TB*4=14TB/月
单次备份是500G，每周一全备500G，1-5增量备份每天1G，7天总数据量507G，一个月2TB,大量节省磁盘空间。

5.3.2 增量备份演练
全量备份目录为： mkdir -p /server/backup/full ##周日全量
增量备份目录1为： mkdir -p /server/backup/inc1 ##周一增量
增量备份目录2为： mkdir -p /server/backup/inc2 ##周二增量

按周备份：生产创建6个增量目录
for n in {1..6}
do
mkdir /server/backup/inc$n -p
done
#######工作中用inc_2022-07-19_2做增量目录
[root@db01 /server/backup]# echo $(date +%F_%w)
2022-07-19_2

1.备份操作：PB级别数据库
1.1.全量备份：--target-dir

#周日 00点全备
[root@db01 ~]# cd /server/backup/full/
[root@db01 ~]# rm -fr *

xtrabackup --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --backup --parallel=4 --target-dir=/server/backup/full
#completed OK!

周日00点备份完毕后，后继续写数据:
mysql> create database pxb;
mysql> use pxb
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);

周一00点增量备份（周日00点--周一00点之间的数据）
1.2.增量备份：--incremental-basedir
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --backup --parallel=4 --target-dir=/server/backup/inc1 --incremental-basedir=/server/backup/full

周一00点增量备份完毕后，继续写数据:
mysql> create database pxb1;
mysql> use pxb1
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);

周二00点增量备份
1.3.增量备份：--incremental-basedir
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --backup --parallel=4 --target-dir=/server/backup/inc2 --incremental-basedir=/server/backup/inc1

===得出结论,每次备份都是基于上一次LSN号之后的备份==========
周一全量：
[root@db01 full]# cat /server/backup/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0 ##当前起始LSN号
to_lsn = 353866519 ##结束LSN号
last_lsn = 353866529
flushed_lsn = 0

周二增量：
[root@db01 full]# cat /server/backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 353866519 ##当前起始LSN号,上一个备份结束LSN
to_lsn = 353888298 ##结束LSN号
last_lsn = 353888760
flushed_lsn = 0
=============================
[root@db01 full]# cat /server/backup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 353888298 ##当前起始LSN号,上一个备份结束LSN
to_lsn = 353907257
last_lsn = 353907734
flushed_lsn = 0

#模拟周三白天插入数据
mysql> create database pxb2;
mysql> use pxb2
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);

# 模拟损坏周三白天上午10点坏了
[root@oldboy ~]# pkill mysqld
[root@oldboy ~]# rm -rf /data/3306/data/*
假设破坏是SQL语句delete

2.恢复操作：
2.1 准备全备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/server/backup/full

2.2 准备增量备份的周二的日志：
xtrabackup --prepare --apply-log-only --target-dir=/server/backup/full --incremental-dir=/server/backup/inc1

2.3 准备增量备份的周三的日志：
xtrabackup --prepare --apply-log-only --target-dir=/server/backup/full --incremental-dir=/server/backup/inc2

2.3 全备份准备：
xtrabackup --prepare --target-dir=/server/backup/full
###备份数据周三0点完整全备

2.4 拷回数据：
xtrabackup --copy-back --target-dir=/server/backup/full
ls /data/3306/data

2.5 修改数据目录的权限和属性：
chown -R mysql:mysql /data/

2.6 启动数据库后查看
/etc/init.d/mysqld start

binlog恢复:
1.看周三增量备份里的binlog_info里的起点
[root@db01 inc1]# cat xtrabackup_binlog_info
binlog.000015 236 3d5846bb-bec3-11ec-bd1d-000c296ebb8f:1-326,b804ace6-c6ce-11ec-b7fa-000c296ebb8f:1-6
binlog日志恢复,#才有周三白天的数据

xtrabackup课后面试题思考:
1T数据，每周1全备，其他时间段增量备份，周四下午14点误删除了一个oldboy库 t1表（10M）数据。
如何快速恢复?

解答：
假设pxb,t1表被删了.
模拟1:：
1.把周四0点前的数据redo,undo进行恢复也就是执行到如下位置
xtrabackup --prepare --target-dir=/server/backup/full

此时找到/server/backup/full库下t1.ibd文件.
[root@db01 /server/backup/full]# cd pxb1
[root@db01 /server/backup/full/pxb1]# ls
t1.ibd

2.使用一个测试库,恢复t1表的表结构，删除表空间文件t1.idb.

3.cp oldboy.ibd到新的位置,通过import加载oldboy.ibd文件到表空间.
oldboy表就恢复了。

4.取周四0点增量目录里面binlog_info信息，作为binlog起点
在所有binlog中取单表binlog，在进行恢复。

5.在从测试环境导出t1.表,恢复到正式环境.

思路2：延迟从库(3-6小时)

思路3：1T数据量，300M/S 3600秒，可以直接数据库路径指向/server/backup/full 0秒。
1.把redo,undo进行恢复也就是执行到如下位置
xtrabackup --prepare --target-dir=/server/backup/full
然后把full恢复到一个临时的库里。然后进行binlog增量恢复所有数据。
[root@db01 /server/backup]# cp /server/backup/full/* /data/3307/data/,生成新实例.
启动数据库...恢复binlog到新数据库,完整数据OK.

2.导出oldboy小表，再恢复到生产线。

## 5.4 MySQL8.0.17+Clone-plugin
Clone Plugin（克隆插件）是MySQL 8.0.17引入的一个重大特性，实现该特性主要是为Group Replication服务。在Group Replication中，如果要添加一个新的节点，这个节点差异数据的补齐是通过分布式恢复（Distributed Recovery）来实现的。
https://blog.csdn.net/dreamyuzhou/article/details/117482112

1 . Clone Plugin介绍
MySQL8.0.17推出了一个重量级的功能：clone plugin。允许用户可以将当前实例进行本地或者远程的clone操作。适用于整个实例快速备份和mgr新成员加入。

clone的定义：是把存储在InnoDB中的schema（database）、table（表）、tablespaces（表空间）和data dictionary metadata（数据字典元数据）的数据，进行在线物理快照。

在线数据克隆，为了云原生应用，MGR环境宕机，快速恢复节点。
本地克隆：
启动克隆操作的MySQL服务器实例中的数据，克隆到同服务器或同节点上的一个目录里

远程克隆：
默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并将其替换为捐赠者(donor)的克隆数据。您也可以将数据克隆到接受者的其他目录，以避免删除现有数据。(可选)

2. 原理
# PAGE COPY

这里有两个动作
开启redo archiving功能，从当前点开始存储新增的redo log，这样从当前点开始所有的增量修改都不会丢失。同时上一步在page track的page被发送到目标端。确保当前点之前所做的变更一定发送到目标端。
关于redo archiving，实际上这是官方早就存在的功能，主要用于官方的企业级备份工具，但这里clone利用了该特性来维持增量修改产生的redo。
在开始前会做一次checkpoint，开启一个后台线程log_archiver_thread()来做日志归档。当有新的写入时(notify_about_advanced_write_lsn)也会通知他去archive。当arch_log_sys处于活跃状态时，他会控制日志写入以避免未归档的日志被覆盖(log_writer_wait_on_archiver), 注意如果log_writer等待时间过长的话， archive任务会被中断掉.

# Redo Copy

停止Redo Archiving", 所有归档的日志被发送到目标端，这些日志包含了从page copy阶段开始到现在的所有日志，另外可能还需要记下当前的复制点，例如最后一个事务提交时的binlog位点或者gtid信息，在系统页中可以找到。'

# Done
目标端重启实例，通过crash recovery将redo log应用上去。

3. 限制
官方文档列出的一些限制：
   1.克隆期间不能DDL
   2.版本必须8.0.17+
   3.必须相同版本
   4.必须基于innodb

13个限制条件：

donor：节点处于为新节点准备或传输集群全量数据状态，就是源端
recipient：需要复制的节点，就是目标端

1.clone插件必须在发送方（donor）和接收方（recipient）的MySQL服务器实例上都是活动的
2.权限：
源端Donor登录的用户需要BACKUP_ADMIN特权来访问和传输来自clone服务器的数据，并在操作期间阻塞DDL。
目标端Recipient用户需要clone_ADMIN特权来替换收件人数据，在操作期间阻塞DDL，并自动重新启动服务器。clone_ADMIN特权隐式地包括BACKUP_ADMIN和SHUTDOWN特权。
3.版本一致：donor和recipient必须拥有相同的MySQL服务器版本。MYSQL 8.0.17及更高版本支持clone插件。
4.recipient必须有足够的磁盘空间来存放clone数据。
5.SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES;识别出来的内容才会被clone
6.相同的MySQL服务器字符集和排序规则
7.innodb_page_size和innodb_data_file_path设置必须相同
8.clone加密或页面压缩的数据，则donor和replicat必须具有相同的文件系统块大小。
9.ssl, 秘钥机制必须是一样的
10.recipient里设置clone_valid_donor_list 源库的信息
11.一次只允许一个clone操作
12.clone插件以1MB包和元数据的形式传输数据。因此，对于recipient和donor的MySQL服务器实例，最小需要的max_allowed_packet值是2MB。max_allowed_packet值小于2MB会导致错误。
13.Undo表空间clone期间，不得变更，MySQL 8.0.18开始，如果在clone操作期间遇到重复的undo表空间文件名，就会报告错误。

4. 应用
4.1 本地
4.1.1 加载插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

##启动加载，是可以的
mysqld_safe --defaults-file=/etc/my.cnf --plugin-load-add=mysql_clone.so --user=mysql &

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';
备注：加载介绍，抽空最好看下官网。 https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-installation.html

4.1.2 创建克隆专用用户
CREATE USER clone_user@'%' IDENTIFIED by 'password';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

# BACKUP_ADMIN是MySQL8.0 才有的备份锁的权限

4.1.3 本地克隆
[root@db01 3306]# mkdir -p /data/test/
[root@db01 3306]# chown -R mysql.mysql /data/
mysql -uclone_user -ppassword
CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';

# 观测状态
db01 [(none)]> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;

#日志观测：
set global log_error_verbosity=3;
tail -f db01.err
CLONE LOCAL DATA DIRECTORY = '/data/test/3308';

4.1.4 启动新实例
[root@db01 clonedir]# mysqld_safe --datadir=/data/test/clonedir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql --mysqlx=OFF &

4.2 远程clone
4.2.0 各个节点加载插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

4.2.1 创建远程clone用户
# 捐赠者(source)授权
create user test_s@'%' identified by '123';
grant backup_admin on *.* to test_s@'%';

# 接受者(target)授权
create user test_t@'%' identified by '123';
grant clone_admin on *.* to test_t@'%';

4.2.2 远程clone(目标端)
# 开始克隆
SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';

mysql -utest_t -p123 -h10.0.0.52 -P3306
CLONE INSTANCE FROM test_s@'10.0.0.51':3306 IDENTIFIED BY '123';

```

港南四大炮亡

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL备份与恢复

*
复制链接

扫一扫