MySQL之六:备份恢复

备份

（1）为什么要备份

 灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据

（2）丢失场景

 备份注意要点
 能容忍最多丢失多少数据
 恢复数据需要在多长时间内完成
 需要恢复哪些数据

（3）还原要点

 做还原测试，用于测试备份的可用性
 还原演练

备份类型：

完全备份，部分备份

 完全备份：整个数据集
 部分备份：只备份数据子集，如部分库或表

完全备份、增量备份、差异备份

 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
 差异备份：仅备份最近一次完全备份以来变化的数据，
 备份较慢，还原简单

注意：二进制日志文件不应该与数据文件放在同一磁盘

冷、温、热备份

 冷备：读写操作均不可进行
 温备：读操作可执行；但写操作不可执行
 热备：读写操作均可执行

MyISAM：温备，不支持热备 InnoDB：都支持 物理和逻辑备份

 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
 逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

冷备份完全备份操作过程：特点，备份速度快，但是需要关闭服务器才能操作。

作用：

处理数据库损坏。

 物理:磁盘，文件系统,数据文件,处理方案；主从，高可用，备份+日志。
 逻辑:drop  truncate  delete   update,   处理方案： 备份+日志，延时从。

备份工具：

 逻辑备份：  mysqldump(MDP)，binlog,主从....... mydumper, phpMyAdmin
 物理备份： Percona,Xtrabackup (PXB\XBK\Xbackup)
 扩展：8.0  Clone  plugin

备份方式：逻辑:

全备：mysqldump
增量：mysqlbinlog (flush logs ,cp)

物理:

全备：XBK

增量：XBK

 xtrabackup_full+xtrabackup_incr+binlog
 或者
 xtrabackup_full+binlog

定期备份检查 crontab -l 备份脚本备份路径备份日志备份文件(大小,内容,文件数量,...) 备份存在性备份空间够用否
定期恢复演练(测试库) 一季度或者半年
数据恢复通过现有备份,能够将数据库恢复到故障之前的时间点(快速)
数据迁移（停机时间+回退方案）
- 不同操作系统的迁移
- mysql -> mysql
- 其他 -> mysql
- mysql -> 其他

mysqldump应用

介绍

mysqldump（MDP）是逻辑备份工具。

适用：单实例数据量亿级以内，单表数据行千万级以内，数据文件大小百G以内，跨版本、跨平台的迁移。

优点：

不需要下载安装
压缩比较高，节省备份的磁盘空间。
SQL文本形式保存备份，可读性较强。
支持本地、远程备份。

缺点：

依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
一般情况下，恢复需要耗费的时间是备份耗费时间的3-5倍。

备份逻辑：将建库、建表、数据插入语句导出，保存至一个sql文件中。

注意：

mysqldump在备份和恢复时都需要mysql实例启动为前提。

mysqldump是覆盖形式恢复的方法。

注意：一般情况下，恢复需要耗费的时间是备份耗费时间的3-5倍。

参数

mysqldump

 本地备份:
 mysqldump -uroot -p1 -S /tmp/mysql.sock
 远程备份:
 mysqldump -uroot -p1 -h 10.0.0.51 -P3306
 -u 用户
 -p 密码
 -S 套接字文件
 -h IP
 P  端口

mysqldump 常见参数

 -A, --all-databases 备份所有数据库，含create database
 系统相关的库不会备份
 information_schema
 performance_schema
 不用恢复系统库，直接重新初始化系统相关的库就回恢复。
 -B, --databases db_name… 指定备份的数据库，包括create database语句 单库或多库备份   
 -E, --events：备份相关的所有event scheduler
 -R, --routines：备份所有存储过程和自定义函数
 --triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
 --default-character-set=utf8 指定字符集
 --master-data[=#]： 此选项须启用二进制日志
 -F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次日志
 --compact 去掉注释，适合调试，生产不使用
 -d, --no-data 只备份表结构
 -t, --no-create-info 只备份数据,不备份create table
 -n,--no-create-db 不备份create database，可被-A或-B覆盖
 --flush-privileges 备份mysql或相关时需要使用
 -f, --force 忽略SQL错误，继续执行
 --hex-blob 使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
 -q, --quick 不缓存查询，直接输出，加快备份速度

 mysqldump [OPTIONS] database [tables]
 mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
 mysqldump [OPTIONS] –A [OPTIONS]

mysqldump参考： https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

备份实战：

`-A 全备`

 [root@db01 ~]# mysqldump -uroot -p123 -A >/data/backup/full.sql

`-B 单库或多库备份`

 [root@db01 ~]# mysqldump -uroot -p123 -B test world >/data/backup/db.sql

`单表或多表备份`

 [root@db01 ~]# mysqldump -uroot -p123 world city country >/data/backup/tb.sql

注意：-A 和 -B 都带有了 create database 和use 语句，直接恢复即可;

单表或多表备份方式, 没有 create database 和use 语句，所以要`手工进行建库和use，再恢复数据。

`-R 备份存储过程及函数`

`--triggers 备份触发器`

`-E 备份事件`

[root@db01 backup]# mysqldump -uroot -p123 -A -R -E --triggers >/data/backup/full2.sql

`-F 在备份开始时,刷新一个新binlog日志`

将新旧的二进制日志文件分别存放，做到了分离，如果不区分二进制日志文件，二进制日志文件会很大，不方便遇到紧急事情还原。

 mysqldump -uroot -p123  -A  -R --triggers -F >/data/backup/full3.sql

`--master-data=1|2用法`

（单纯备份用2，主从复制时用1）备份时，建议使用此选项，恢复时也可以快速定位恢复：

a. 记录备份时刻的binlog信息 b. 自动加GRL锁(FTWRL ,flush tables with read lock) 不加--single-transaction ,温备份加了--single-transaction,对于InnoDB表不锁表备份(快照备份)，但表结构等元数据还是FTWRL备份（8.0之后）

 [root@db01 backup]# mysqldump -uroot -p123   --master-data=2 -A >/data/backup/bak.sql备份二进制日志

  会记录什么节点上备注的二进制日志

`--single-transaction`

 --single-transaction 
 通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB支持）；对于其他存储引擎，转储不能保证是一致的。当一个--single transaction dump正在进行时，为了确保有效的转储文件（正确的表内容和二进制日志位置），其他任何连接都不应使用以下语句：ALTER table、DROP table、RENAME table、TRUNCATE table，因为一致快照没有与它们隔离。选项自动关闭 --lock-tables       
 a. 对于InnoDB表，利用MVCC中一致性快照进行备份。备份数据时,不加锁
 b. 备份期间如果出现DDL操作，导致备份数据不一致

问题： mysqldump是严格意义上的热备吗？

 8.0 之后 master-data和single-transaction,对于InnoDB数据备份时是快照备份的. 备份表结构等数据时,还是FTWRL过程备份.
 --single-transaction 只是针对InnoDB表数据进行一致性快照备份。
 问题： mysqldump备份需要锁表吗？
 是有的。global read lock

`--set-gtid-purged=auto`

 auto , on
 off 
 使用场景:
 1. --set-gtid-purged=OFF,可以使用在日常备份参数中.

 mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
 2. 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=64M`

 --max-allowed-packet=#
 发送到服务器或从服务器接收的最大数据包长度。

 mysqldump -uroot -p -A -R -E --triggers --master-data=2
 --single-transaction  --max-allowed-packet=64M >/data/backup/full.sql

`标准化备份`

[root@db01 backup]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full_`date +%F`.sql

 Enter password:#MySQL登陆密码password

添加时间戳并压缩备份

 mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M |gzip > /data/backup/full_$(date +%F-%T).sql.gz

所有表单独备份

 提示：

information_schema.tables
 mysqldump -uroot -p1 world city >/backup/world_city.sql
 
 select concat("mysqldump -uroot -p1 ",table_schema," ",table_name," --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /data/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema');

`案例1`：全库故障恢复

通过`mysqldump`全备+`binlog`实现`PIT`数据恢复

环境背景： `小型业务数据库`，50G，`每天23:00全备`，定期`binlog异地备份`。

故障场景：周三下午2点，开发Navicat连接数据库实例错误，导致生产数据被误删除（DROP）

恢复思路：

 1.挂维护页。
 2.检查备份、日志可用。
 3.如果只是部分损坏，建议找一个应急库进行恢复
   a. 全备恢复 
   b. 日志截取并恢复 
 4.恢复后数据校验（业务测试部门验证）
 5.立即备份（停机冷备） 
 6.恢复架构系统
 7.撤维护页，恢复业务

`故障模拟环境 :`

故障模拟

准备数据

 create database mdb;
 use mdb
 create table t1 (id int);
 create table t2 (id int);
 insert into t1 values(1),(2),(3);
 commit;
 insert into t2 values(1),(2),(3);
 commit;

周二 23：00全备

mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /tmp/full_`date +%F`.sql

周二 23：00到周三 10点之间数据变化

 create table t3 (id int);
 insert into t3 values(1),(2),(3);
 commit;

模拟故障：删除库

 drop database mdb;

`故障恢复过程：`

a. 查看备份获取二进制日志位置点

 mysql> show master status ;
 mysql> show binlog events in 'binlog.000047';
 [root@db01 backup]# vim full_2020-11-17.sql
 SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '4326cb30-23e8-11eb-92ad-000c29f8c9ad:1-5';
 -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000047', MASTER_LOG_POS=1274;

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

b. 恢复全备

 mysql> source /data/backup/full_2020-09-18.sql

c. binlog 截取并恢复

 mysql> show binlog events in 'binlog.000047';
 | binlog.000001 | 1711 | Xid      |     1 |    1742 | COMMIT /* xid=2278 */  
 [root@db01 backup]# mysqlbinlog --skip-gtids --start-position=1274 
--stop-position=1742 /data/3306/data/binlog.000047 >/tmp/bin.sql
 mysql> set sql_log_bin=0;
 mysql> source /tmp/full_2020-11-17.sql
 mysql> source /tmp/bin.sql

`练习：`

100G mysqldump全备恢复时间很长，误删除的表10M大小，有什么思路可以快速恢复？

思路： a. 从全备中，将单表建表语句和insert语句提取出来，进行恢复

 1、获得表结构

 # sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql
 2、获得INSERT INTO 语句，用于数据的恢复
 # grep -i 'INSERT INTO `city`'  full.sqll >data.sql &
 3.获取单库的备份
 # sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

 单表：
 1.

[root@db01 backup]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  
/data/backup/full_2020-11-17.sql  >/data/backup/createtable.sql
 2.获得INSERT INTO 语句，用于数据的恢复
 [root@db01 backup]# grep -i 'INSERT INTO `city`'   /data/backup/full_2020-11-17.sql >/data/backup/data.sql
 ###假如说误删除了city
 mysql -uroot -p123
 mysql> use world;
 mysql> drop table city;
 mysql> source createtable.sql    #数据恢复
 mysql> source data.sql           #数据恢复
 mysql> select * from city;       #通过查看看出数据已经恢复 
 数据恢复
 3.获取单库的备份
 # sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql

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

适用：单实例数据文件大小百G至TB，跨版本、跨平台的迁移。

优点：

类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
支持全备和增量备份。

缺点：

可读性差
压缩比低，需要更多磁盘空间

备份逻辑：

非InnoDB，进行锁表备份，copy所有的非innoDB表文件

InnoDB，数据库运行期间，备份触发Checkpoint，并记录此时LSN号
拷贝所有InnoDB表相关的文件(ibdata1,ibd,frm)，并且备份过程中产生的redo和undo，也就是checkpoint LSN之后的日志一起拷走。

恢复逻辑：

在恢复之前，模拟InnoDB Crash Recovery功能，对备份进行处理（先前滚后回滚），将数据和redo的LSN追平，然后进行一致性恢复。
恢复过程是cp备份到原来数据目录下

安装

 wget https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.14
/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
 yum -y install percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm

版本：

MySQL 8.0.20需要使用PXB 8.0.12+

MySQL 8.0.11~19需要使用PXB 8.0

MySQL 8.0之前（5.5/5.6/5.7）需要使用PXB 2.4

https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm

Percona XtraBackup 2.4 用户手册

Percona XtraBackup 8.0 用户手册

xtrabackup 全量备份

 xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123  --backup --target-dir=/data/3306/backup/full

备份文件

 [root@db01 ~]# cat /data/backup/full/xtrabackup_binlog_info 
 binlog.000050   196 4326cb30-23e8-11eb-92ad-000c29f8c9ad:1-8
 # File       Position   Executed_Gtid_Set
 [root@db01 ~]# cat /data/backup/full/xtrabackup_checkpoints 
 backup_type = full-backuped
 from_lsn = 0
 to_lsn = 190891589
 last_lsn = 190891599
 flushed_lsn = 0

数据恢复流程：

 mkdir -p /data/3306/backup

全量备份

 xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123 --backup --target-dir=/data/3306/backup/full

破坏数据

 pkill mysqld
 rm -rf /data/3306/data/*

准备全备数据：(CR)

 xtrabackup --prepare --target-dir=/data/3306/backup/full

说明：模拟CR过程，先前滚再回滚，让备份数据是一致状态

拷回数据

 xtrabackup --copy-back --target-dir=/data/3306/backup/full

修改权限并启动数据库

 chown -R mysql.mysql /data/*
 systemctl start mysql

xtrabackup 增量备份

增量备份，是基于上一次备份LSN变化过的数据页进行备份，在备份同时产生的新变更，会将redo备份。第一次增量是依赖于全备的。将来的恢复也要合并到全备中，再进行统一恢复。

 xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123 
--backup --parallel=4 --target-dir=/data/3306/backup/inc --incremental-basedir=/data/3306/backup/full
 -u, --user=name             用户
 -H, --host=name             主机
 -P, --port=#                端口
 -p, --password[=name]       密码
 -S, --socket=name           套接字文件
 --defaults-file=#           仅从给定文件#（MySQL配置文件）读取默认选项
 --target-dir=name           目标目录
 --backup                    备份到目标目录
 --incremental-basedir=name (for --backup): 仅复制比指定目录的备份更新的.ibd页
 --incremental-dir=name     (for --prepare): 在指定的目录中应用.delta文件和日志文件
 --parallel=#                用于并行数据文件传输的线程数（default 1）
 --prepare                   准备备份，以便在备份上启动 mysql server
 --apply-log-only            准备时，仅应用日志，停止恢复进程且不递增LSN
 --copy-back                 将以前备份的所有文件从备份目录复制到其原始位置

数据恢复流程：

全量备份

 xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123 --backup --target-dir=/data/3306/backup/full

增加数据

 create database pxb;
 use pxb
 create table t1 (id int);
 insert into t1 values(1),(2),(3);
 commit;

增量备份

 xtrabackup --defaults-file=/etc/my.cnf --user
=root --password=123 --backup --parallel=4 --target-dir
=/data/3306/backup/inc --incremental-basedir=/data/3306/backup/full

破坏数据

 pkill mysqld
 rm -rf /data/3306/data/*

准备全备份的日志：

 xtrabackup --prepare --apply-log-only --target-dir=/data/3306/backup/full

准备增量备份的日志：

 xtrabackup --prepare --apply-log-only --target-dir=/data/3306/backup/full --incremental-dir=/data/3306/backup/inc

准备全备数据：(CR)

 xtrabackup --prepare --target-dir=/data/3306/backup/full

拷回数据

 xtrabackup --copy-back --target-dir=/data/3306/backup/full

修改权限并启动数据库

 chown -R mysql.mysql /data/*
 systemctl start mysqld

备份策略：

每周全备+每天增量+每天binlog
每周全备+每天binlog

增量恢复单表

50G数据，每周日全备，其他时间段增量，周三下午误删了一个表（10M），快速恢复。

解决方案：独立表空间迁移配合增量恢复截取二进制日志恢复

故障复现：

 drop table city;
 create table city like city_bak;
 alter table city discard tablespace;

独立表空间迁移全备单表

 cp /backup/full/world/city.ibd /application/mysql/data/world/
 chown -R mysql.mysql /application/mysql/data/world/city.ibd 
 alter table city import tablespace;

binlog2sql 截取取增量单表binlog

Cline Plugin

Mysql8.0.17+ 新增插件Cline Plugin。

允许在本地或从远程MySQL服务器实例克隆数据。克隆数据是其中存储的数据的物理快照，InnoDB其中包括模式，表，表空间和数据字典元数据。克隆的数据包含一个功能齐全的数据目录，允许克隆插件配置MySQL服务器。

本地克隆操作

本地克隆操作：将数据，从启动克隆操作的MySQL服务器，克隆到同服务器或同节点上的一个目录里

远程克隆操作

远程克隆操作涉及启动克隆操作的本地MySQL服务器（接收端（recipient））和源数据所在的远程MySQL服务器（给予端（donor））。

在接受端上启动远程克隆操作时，克隆的数据会通过网络从给予端传输到接受端。默认情况下，远程克隆操作会删除接受端数据目录中的数据，并将其替换为克隆的数据。（可选）您可以将数据克隆到接受端的其他目录中，以避免删除现有数据。

克隆插件还支持：

克隆插件限制

克隆操作期间不允许使用 DDL（包括TRUNCATE TABLE）。一种解决方法是使用专用的给予端，可以在克隆数据时阻止DDL操作，允许并发DML操作。
仅MySQL 8.0.17及更高版本支持clone插件。给予端（donor）和接受端（recipient）版本必须完全相同，不支持跨版本。
一次只能克隆一个MySQL实例。
不支持由mysqlx_port指定的X协议端口。
不支持接收方MySQL服务器保留其配置，包括持久的系统变量设置。
不支持二进制日志的克隆。
仅支持克隆存储在InnoDB存储引擎中的数据。其他存储引擎数据未克隆。MyISAM和 CSV存储在任何表（包括sys）的数据被克隆为空表。
不支持通过MySQL路由器连接的给予端MySQL服务器。
本地克隆操作不支持克隆使用绝对路径创建的常规表空间。这将导致与源表空间文件具有相同路径的克隆表空间文件的冲突。

安装克隆插件

运行时加载插件：

 mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

INSTALL PLUGIN加载插件，并将其注册到mysql.plugins 系统表中，以使插件在每次后续的正常服务器启动时都加载，而无需 --plugin-load-add。

启动时加载插件并防止在运行时将其删除：

 [mysqld]
 plugin-load-add=mysql_clone.so  
 clone=FORCE_PLUS_PERMANENT

FORCE_PLUS_PERMANENT强制服务器启动失败（如果插件未成功初始化）

验证插件安装：

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME = 'clone';

 +------------------------+---------------+
 | PLUGIN_NAME            | PLUGIN_STATUS |
 +------------------------+---------------+
 | clone                  | ACTIVE        |
 +------------------------+---------------+

 mysql> SHOW PLUGINS;

克隆本地数据

将数据从本地MySQL数据目录克隆到运行MySQL服务器实例的同一服务器或节点上的另一个目录：

 CLONE LOCAL DATA DIRECTORY [=] 'clone_dir';

需要用户有BACKUP_ADMIN权限才能执行上述语句。

 CREATE USER clone_user@'%' IDENTIFIED by 'password'; 
 GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

创建克隆专用用户

 CREATE USER clone_user@'%' IDENTIFIED by 'password'; 
 GRANT BACKUP_ADMIN ON *.* TO 'clone_user'; 
 [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';

其中/path/to/clonedir必需是将数据克隆到的本地目录的绝对路径，指定的路径必须是存在的路径，但是指定的目录 clonedir一定不存在。MySQL服务器必须具有创建目录所需的写权限。

注意：

本地克隆操作不支持克隆位于数据目录外部的用户创建的表或表空间。尝试克隆此类表或表空间会导致以下错误：
 ERROR 1086 (HY000): File '/path/to/tablespace_name.ibd' already exists. 
克隆与源表空间路径相同的表空间会导致冲突，因此被禁止。

所有其他用户将克隆到指定目录。

监视克隆操作

使用错误日志监视

设置错误日志级别

 set global log_error_verbosity=3;

监视错误日志

 tail -f db01.err

执行克隆操作

 CLONE LOCAL DATA DIRECTORY = '/data/test/3308';

使用性能模式克隆表监视克隆操作

只能监视接受端MySQL服务器实例的克隆操作。

查看clone_status和 clone_progress表监视克隆操作的状态和进度。

clone_status表提供了当前或上次执行的克隆操作的状态。

克隆操作有四种可能的状态： Not Started，In Progress，Completed，和 Failed。

 mysql> SELECT STATE FROM performance_schema.clone_status;
 +-----------+
 | STATE     |
 +-----------+
 | Completed |
 +-----------+

如果在克隆操作期间发生故障，获取错误信息：

 mysql> SELECT STATE, ERROR_NO, ERROR_MESSAGE FROM performance_schema.clone_status;
 +-----------+----------+---------------+
 | STATE     | ERROR_NO | ERROR_MESSAGE |
 +-----------+----------+---------------+
 | Failed    |      xxx | "xxxxxxxxxxx" |
 +-----------+----------+---------------+

clone_progress表按阶段提供了当前或上次执行的克隆操作的进度信息。

克隆操作的各个阶段包括DROP DATA， FILE COPY， PAGE_COPY， REDO_COPY， FILE_SYNC，RESTART，和RECOVERY。

 mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
 +-----------+-----------+----------------------------+
 | stage     | state     | end_time                   |
 +-----------+-----------+----------------------------+
 | DROP DATA | Completed | 2019-01-27 22:45:43.141261 |
 | FILE COPY | Completed | 2019-01-27 22:45:44.457572 |
 | PAGE COPY | Completed | 2019-01-27 22:45:44.577330 |
 | REDO COPY | Completed | 2019-01-27 22:45:44.679570 |
 | FILE SYNC | Completed | 2019-01-27 22:45:44.918547 |
 | RESTART   | Completed | 2019-01-27 22:45:48.583565 |
 | RECOVERY  | Completed | 2019-01-27 22:45:49.626595 |
 +-----------+-----------+----------------------------+

使用性能架构阶段事件监视克隆操作

可以监视给予端或接受端MySQL服务器实例的克隆操作。

按照发生的顺序，克隆操作阶段事件包括：

stage/innodb/clone (file copy)：指示克隆操作的文件复制阶段的进度。 WORK_ESTIMATED和 WORK_COMPLETED单位是文件块。在文件复制阶段开始时就知道要传输的文件数，并且基于文件数来估计块数。 WORK_ESTIMATED设置为估计的文件块数。WORK_COMPLETED 发送每个块后更新。
stage/innodb/clone (page copy)：指示克隆操作的页面复制阶段的进度。WORK_ESTIMATED和 WORK_COMPLETED单位是页面。一旦完成文件复制阶段，就知道要传输的页数，并将 WORK_ESTIMATED其设置为该值。 WORK_COMPLETED发送每页后更新。
stage/innodb/clone (redo copy)：指示克隆操作的重做复制阶段的进度。WORK_ESTIMATED和 WORK_COMPLETED单位是重做块。一旦页面复制阶段完成，就知道要传输的重做块的数量，并将 WORK_ESTIMATED其设置为该值。 WORK_COMPLETED发送每个块后更新。

启用 stage/innodb/clone%事件工具和相关的使用者表来监视克隆操作。

启用stage/innodb/clone% 工具：

 mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
 WHERE NAME LIKE 'stage/innodb/clone%';

启用舞台活动消费表，其中包括 events_stages_current， events_stages_history，和 events_stages_history_long。
```
 mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
 WHERE NAME LIKE '%stages%';
```
运行克隆操作。例如：本地数据目录被克隆到名为的目录中 cloned_dir。
```
mysql> CLONE LOCAL DATA DIRECTORY = '/path/to/cloned_dir';
```

查询events_stages_current表来检查克隆操作的进度： WORK_COMPLETED列显示已完成的工作。WORK_ESTIMATED列显示总共需要的工作。

 mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current
        WHERE EVENT_NAME LIKE 'stage/innodb/clone%';
 +--------------------------------+----------------+----------------+
 | EVENT_NAME                     | WORK_COMPLETED | WORK_ESTIMATED |
 +--------------------------------+----------------+----------------+
 | stage/innodb/clone (redo copy) |              1 |              1 |
 +--------------------------------+----------------+----------------+

如果克隆操作已完成，检查 events_stages_history表将返回一个空集：

 mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history
        WHERE EVENT_NAME LIKE 'stage/innodb/clone%';
 +--------------------------------+----------------+----------------+
 | EVENT_NAME                     | WORK_COMPLETED | WORK_ESTIMATED |
 +--------------------------------+----------------+----------------+
 | stage/innodb/clone (file copy) |            301 |            301 |
 | stage/innodb/clone (page copy) |              0 |              0 |
 | stage/innodb/clone (redo copy) |              1 |              1 |
 +--------------------------------+----------------+----------------+

使用`Performance Schema Clone Instrumentation`监视克隆操作

 mysql> SELECT * FROM performance_schema.setup_instruments
        WHERE NAME LIKE WHERE NAME LIKE '%clone%';
 +----------------------------------------------+---------+
 | NAME                                         | ENABLED |
 +----------------------------------------------+---------+
 | wait/synch/mutex/innodb/clone_snapshot_mutex | NO      |
 | wait/synch/mutex/innodb/clone_sys_mutex      | NO      |
 | wait/synch/mutex/innodb/clone_task_mutex     | NO      |
 | wait/io/file/innodb/innodb_clone_file        | YES     |
 | stage/innodb/clone (file copy)               | YES     |
 | stage/innodb/clone (redo copy)               | YES     |
 | stage/innodb/clone (page copy)               | YES     |
 | statement/abstract/clone                     | YES     |
 | statement/clone/local                        | YES     |
 | statement/clone/client                       | YES     |
 | statement/clone/server                       | YES     |
 | memory/innodb/clone                          | YES     |
 | memory/clone/data                            | YES     |
 +----------------------------------------------+---------+

等待仪器（Wait Instruments）

性能架构等待工具可跟踪耗时的事件。克隆等待事件工具包括：

wait/synch/mutex/innodb/clone_snapshot_mutex：跟踪克隆快照互斥锁的等待事件，该事件在多个克隆线程之间同步对动态快照对象（在给予端和接受端上）的访问。
wait/synch/mutex/innodb/clone_sys_mutex：跟踪克隆sys互斥锁的等待事件。MySQL服务器实例中有一个克隆系统对象。此互斥锁同步对供体和接受端上的克隆系统对象的访问。它是由克隆线程以及其他前台和后台线程获取的。
wait/synch/mutex/innodb/clone_task_mutex：跟踪克隆任务互斥锁的等待事件，该事件用于克隆任务管理。该 clone_task_mutex被克隆的线程收购。
wait/io/file/innodb/innodb_clone_file：跟踪克隆所操作的文件的所有I / O等待操作。

舞台乐器（Stage Instruments）

Performance Schema阶段事件跟踪在语句执行过程中发生的步骤。克隆阶段事件工具包括：

stage/innodb/clone (file copy)：指示克隆操作的文件复制阶段的进度。
stage/innodb/clone (redo copy)：指示克隆操作的重做复制阶段的进度。
stage/innodb/clone (page copy)：指示克隆操作的页面复制阶段的进度。

声明工具（Statement Instruments）

性能架构语句事件跟踪语句执行。启动克隆操作时，可以并行执行克隆语句工具跟踪的不同语句类型。您可以在Performance Schema语句事件表中观察这些语句事件。执行的语句数取决于 clone_max_concurrency和 clone_autotune_concurrency 设置。

克隆语句事件工具包括：

statement/abstract/clone：在将任何克隆操作归类为本地，客户端或服务器操作类型之前，跟踪所有克隆操作的语句事件。
statement/clone/local：跟踪克隆语句事件以进行本地克隆操作；执行CLONE LOCAL语句时生成。
statement/clone/client：跟踪在接受端MySQL服务器实例上发生的远程克隆语句事件；CLONE LOCAL在接受端上执行语句时生成。
statement/clone/server：跟踪在给予端MySQL服务器实例上发生的远程克隆语句事件；CLONE LOCAL在接受端上执行语句时生成。

记忆仪器（Memory Instruments）

性能架构内存工具跟踪内存使用情况。克隆内存使用工具包括：

memory/innodb/clone：跟踪InnoDB为动态快照分配的内存。
memory/clone/data：跟踪克隆操作期间克隆插件分配的内存。

克隆远程数据

从远程MySQL服务器实例（给予端）克隆数据，并将其传输到发起克隆操作的MySQL实例（接受端）。

 CLONE INSTANCE FROM 'user'@'host':port
 IDENTIFIED BY 'password'
 [DATA DIRECTORY [=] 'clone_dir']
 [REQUIRE [NO] SSL];

user是给予端MySQL服务器上的克隆用户。
password是 user 密码。
host是给予端MySQL服务器的地址。
port是给予端MySQL服务器的端口号。（不支持X协议端口和通过MySQL路由器连接到给予端MySQL服务器实例。）
DATA DIRECTORY [=] 'clone_dir'是一个可选子句，用于在接受端上为要克隆的数据指定目录。

如果您不想删除接受端数据目录中的现有数据，请使用此选项。绝对路径是必需的，目录必须不存在。MySQL服务器必须具有创建目录所需的写权限。

不使用该子句时，克隆操作将删除接受端数据目录中的现有数据，将其替换为克隆的数据，然后自动重新启动服务器。
[REQUIRE [NO] SSL]明确指定在通过网络传输克隆数据时是否使用加密连接。如果无法满足显式规范，则返回错误。如果未指定SSL子句，则克隆会默认尝试建立加密连接，如果安全连接尝试失败，则会回退到不安全的连接。无论是否指定此子句，克隆加密数据时都需要安全连接。

注意：

如果用户创建的InnoDB表和表空间，位于给予端上数据目录的外部，将被克隆到接受端上的相同路径。如果表或表空间已经存在，则会报告错误。

远程克隆先决条件

要执行克隆操作，克隆插件必须在给予端和接受端均处于活动状态。

执行克隆操作需要给予者和接受者上的MySQL用户（克隆用户）。

在给予端上，克隆用户需要 BACKUP_ADMIN特权（8.0版本新增）才能访问和传输来自给予端的数据，并在克隆操作期间阻止DDL。
在接受端上，克隆用户需要具有以下 CLONE_ADMIN特权：替换接受端数据，在克隆操作期间阻止DDL以及自动重新启动服务器。 CLONE_ADMIN权限隐含包括BACKUP_ADMIN和 SHUTDOWN权限。

执行CLONE INSTANCE语句时，将检查以下先决条件：

给予端和接受端必须具有相同的MySQL服务器版本。MYSQL 8.0.17及更高版本支持clone插件。

 mysql> SHOW VARIABLES LIKE 'version';
  +---------------+--------+
 | Variable_name | Value  |
 +---------------+--------+
 | version       | 8.0.17 |
 +---------------+--------+

给予端和接受端必须在相同的操作系统和平台上运行。
给予端和接受端必须具有相同的MySQL服务器字符集和排序规则。
给予端和接受端上的参数innodb_page_size 和innodb_data_file_path 设置必须相同。
给予端和接受端上的参数max_allowed_packet最低设置为2MB。因为克隆插件以1MB数据包以及元数据的形式传输数据。
接受端必须具有足够的磁盘空间来存储克隆的数据。
InnoDB允许在数据目录之外创建一些表空间类型。如果给予端MySQL服务器实例的表空间位于数据目录之外，则克隆操作必须能够访问这些表空间。

查看在数据目录之外的表空间：
```
 mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES;
```
在给予端上活动的插件（包括密钥插件）也必须在接收端上活动。您可以通过SHOW PLUGINS或查询 INFORMATION_SCHEMA.PLUGINS表来查看活动插件。
如果克隆加密或页面压缩的数据，则给予端和接受端必须具有相同的文件系统块大小。对于页面压缩的数据，接受端文件系统必须支持稀疏文件和打孔，以便在接受端上进行打孔。
如果要克隆加密的数据，则需要安全连接。
接受端设置clone_valid_donor_list 必须包括给予端的主机地址。检查设置：
```
 mysql> SHOW VARIABLES LIKE 'clone_valid_donor_list';
```
一次只能进行一次克隆操作。要确定克隆操作是否正在运行，请查询 clone_status表。
给予端上的撤消表空间文件名必须唯一。如果在克隆操作期间遇到重复的撤消表空间文件名，在MySQL 8.0.18之前会覆盖，之后会报告错误。

查看撤消表空间文件名：
```
 mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
        WHERE FILE_TYPE LIKE 'UNDO LOG';
```

注意：

克隆数据后，默认接受端MySQL服务器实例将自动重新启动。为了进行自动重启，接受端上必须有一个监视过程来检测服务器关闭。否则，在克隆数据并关闭接收方MySQL服务器实例后，克隆操作会因以下错误而暂停：
 ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).
解决方案：手动启动MySQL，并检查Performance Schema克隆表以验证克隆操作是否成功完成

如果使用DATA DIRECTORY 子句克隆到指定目录，不会执行自动重启。

克隆远程数据

默认情况下，远程克隆操作会删除接受端数据目录中的数据，将其替换为克隆的数据，然后重新启动MySQL服务器。

假定满足远程克隆先决条件。

使用管理用户帐户登录到给予端MySQL服务器实例。

创建具有BACKUP_ADMIN特权的克隆用户。

 mysql> CREATE USER 'donor_clone_user'@'example.donor.host.com' IDENTIFIED BY 'password';
 mysql> GRANT BACKUP_ADMIN on *.* to 'donor_clone_user'@'example.donor.host.com';

安装克隆插件：

 mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

使用管理用户帐户登录到接受端MySQL服务器实例。

创建具有CLONE_ADMIN特权的克隆用户。

 mysql> CREATE USER 'recipient_clone_user'@'example.recipient.host.com' IDENTIFIED BY 'password';
 mysql> GRANT CLONE_ADMIN on *.* to 'recipient_clone_user'@'example.recipient.host.com';

安装克隆插件：

 mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

将给予端MySQL服务器实例的主机地址添加到 clone_valid_donor_list 变量中。
```
 mysql> SET GLOBAL clone_valid_donor_list = 'example.donor.host.com:3306';
```

使用克隆用户登录到接受端MySQL服务器实例，并执行该 CLONE INSTANCE语句。

 mysql> CLONE INSTANCE FROM 'donor_clone_user'@'example.donor.host.com':3306
        IDENTIFIED BY 'password';

克隆数据完成后，接受端上的MySQL服务器实例将自动重新启动。

克隆远程数据到命名目录

默认情况下，远程克隆操作会删除接受端数据目录中的数据，并将其替换为克隆的数据。

通过克隆到命名目录，可以避免从接受端数据目录中删除现有数据。

使用CLONE INSTANCE语句包含DATA DIRECTORY子句：

 mysql> CLONE INSTANCE FROM 'user'@'example.donor.host.com':3306
        IDENTIFIED BY 'password'
        DATA DIRECTORY = '/path/to/clone_dir';

绝对路径是必需的，目录必须不存在。MySQL服务器必须具有创建目录所需的写权限。

注意：克隆数据完成后，接受端MySQL服务器实例不会自动重新启动。

克隆插件实例

本地克隆

加载插件

 [mysqld]
 plugin-load-add=mysql_clone.so
 clone=FORCE_PLUS_PERMANENT

重启MySQL

 systemctl restart mysql

验证插件是否加载

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

创建克隆专用用户

 CREATE USER clone_user@'%' IDENTIFIED by 'password'; 
 GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

执行本地克隆

 mysql -uclone_user -ppassword

 CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';

观测克隆状态

 mysql -uroot -p123重新连接root用户
 mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;

启动新实例

 mysqld_safe --datadir=/data/clonedir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql --mysqlx=OFF &

远程克隆

连接原数据库

 mysql -uroot -p123 -h10.0.0.51

创建给予者(donor)用户并授权，并加载插件

 CREATE USER 'donor_clone_user'@'%' IDENTIFIED BY '123';
 GRANT BACKUP_ADMIN on *.* to 'donor_clone_user'@'%';
 INSTALL PLUGIN clone SONAME 'mysql_clone.so';

连接新数据库

 mysql -uroot -p123 -h10.0.0.52

 各个节点加载插件
 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';

创建接受者(recipient)用户并授权，并加载插件

 # 捐赠者(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@'%';

设置原数据库IP:端口到给予主机列表

 mysql> SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';##db02上操作

执行远程克隆

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

克隆完成，新数据库自动重启

posted @ 2021-03-03 21:11 上善若水~小辉阅读(373) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

www.cnblogs.com

www.cnblogs.com/strugger-0316/

MySQL之六:备份恢复

备份

备份类型：

冷备份完全备份操作过程：特点，备份速度快，但是需要关闭服务器才能操作。

介绍

参数

mysqldump

mysqldump 常见参数

备份实战：

-A 全备

-B 单库或多库备份

单表或多表备份

-R 备份存储过程及函数

--triggers 备份触发器

-E 备份事件

-F 在备份开始时,刷新一个新binlog日志

--master-data=1|2用法

（单纯备份用2，主从复制时用1）备份时，建议使用此选项，恢复时也可以快速定位恢复：

--single-transaction

问题： mysqldump是严格意义上的热备吗？

--set-gtid-purged=auto

--max_allowed_packet=64M

标准化备份

添加时间戳并压缩备份

所有表单独备份

案例1：全库故障恢复

通过mysqldump全备+binlog实现PIT数据恢复

环境背景： 小型业务数据库，50G，每天23:00全备，定期binlog异地备份。

故障模拟环境 :

故障恢复过程：

练习：

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

安装

xtrabackup 全量备份

xtrabackup 增量备份

备份策略：

增量恢复单表

Cline Plugin

克隆插件限制

安装克隆插件

克隆本地数据

监视克隆操作

使用错误日志监视

使用性能模式克隆表监视克隆操作

使用性能架构阶段事件监视克隆操作

使用Performance Schema Clone Instrumentation监视克隆操作

等待仪器（Wait Instruments）

舞台乐器（Stage Instruments）

声明工具（Statement Instruments）

记忆仪器（Memory Instruments）

克隆远程数据

远程克隆先决条件

克隆远程数据

克隆远程数据到命名目录

克隆插件实例

本地克隆

远程克隆

公告

`-A 全备`

`-B 单库或多库备份`

`单表或多表备份`

`-R 备份存储过程及函数`

`--triggers 备份触发器`

`-E 备份事件`

`-F 在备份开始时,刷新一个新binlog日志`

`--master-data=1|2用法`

`--single-transaction`

`--set-gtid-purged=auto`

`--max_allowed_packet=64M`

`标准化备份`

`案例1`：全库故障恢复

通过`mysqldump`全备+`binlog`实现`PIT`数据恢复

环境背景： `小型业务数据库`，50G，`每天23:00全备`，定期`binlog异地备份`。

`故障模拟环境 :`

`故障恢复过程：`

`练习：`

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

使用`Performance Schema Clone Instrumentation`监视克隆操作