备份与恢复
备份
作用；处理数据的损坏
类型：
1> 物理损坏 ： 磁盘的损坏/核心数据的丢失/删除;
2> 逻辑损坏 ： drop/delete/truncate/update 使用命令的将数据误删除的
职责：
1.设计备份和恢复的策略
2.备份要定期检查
3.定期的恢复演练
4.数据损坏的恢复（效率/一致性）
5.迁移/升级，构建主从复制
工具：
1.逻辑备份
mysqldump(MDP)
mysqlbinlog
mydumper
2.物理备份
percona Xtrabackup(PXB / XBK / Xbackup)
3.8.0之后的克隆（也算逻辑备份，8017之后才有）
clone plugin
mysqldump 逻辑备份
mysqldump:逻辑备份工具
备份结果：
SQL语句 create database/table insert into（我只需要将这些做了备份，就可以了）
备份逻辑：
RR级别，获取一致性快照，为了就是保证数据的一致性
1.元数据备份
FTWRL （
flush tables with read lock;手动加锁
MDL锁： 是获取的，算是表上，对象上的属性。
例子：当我们备份大数据的时候，就会出现延迟，那么就会获取不到锁，获取不到，就没有办法对表进行操作，就不会备份成功。
show create database 建库
show create table 建表
2.备份数据行
select -->
INNODB 可以进行快照备份
拼接成insert
非innodb表，锁表备份
4.1.3 适合的场景
单节点数据量,100G以内,单表2000w以内.可以使用MDP
恢复时间一般是备份时间的3-5倍.
4.2 参数应用
4.2.1 连接参数
-u
-p
-h
-P
-S
4.2.2 基础备份参数
-A : 全库备份
[root@db01 ~]
-B : 单库或多库备份
[root@db01 ~]
[root@db01 ~]
[root@db01 ~]
说明: 以上方法,在恢复时先建立数据库,use到库中,再source
4.2.3 特殊备份参数
a. --master-data=2
1. 自动记录binlog信息
2. 自动进行GRL锁(FTWRL),加了 --single-transaction 会有不一样的效果.
mysqldump -uroot -p123 -S /tmp/mysql.sock -A --master-data=2 >/tmp/full.sql
b. --single-transaction
解:
1. 对于InnoDB表进行一致性快照备份
2. 备份期间如果有DDL会备份失败.
我想要数一下班级的同学准确数字。
1.锁门：我将大门锁上，所有人不能进出，然后统计之后，在解锁。
2.快照：就是我用照相机，先给全班同学拍照，但是这个时候，其中有一个同学不是我们班的，那么就相当于，改变了我们班级的人数。也就相当于改变了表的结构。
c. --max_allowed_packet=128M
如果你是想要备份，那么就是从服务端，向客户端推送数据，就需要添加在客户端
如果你需要insert into ，就相当于从客户端像服务端，插入数据，就要添加在服务端
mysqldump -uroot -p123 -S /tmp/mysql.sock -A --master-data=2 --single-transaction --max_allowed_packet=128M >/tmp/full.sql
d. -R -E --triggers
备份时,同时将 存储过程\函数\事件\触发器 等高级对象备份走.
mysqldump -uroot -p123 -S /tmp/mysql.sock -A --master-data=2 --single-transaction --max_allowed_packet=128M -R -E --triggers >/tmp/full.sql
4.2.4 标准化备份/备份
[root@db01 tmp]
案例：
环境背景： 小型的业务数据库，50G，每天23:00全备，定期binlog异地备份。
故障场景： 周三下午2点，开发Navicat连接数据库实例错误，导致生产数据被误删除（DROP）
恢复思路：
1. 挂维护页。
2. 检查备份、日志可用。
3. 如果只是部分损坏，建议找一个应急库进行恢复
a. 全备恢复
b. 日志截取并恢复
4. 恢复后数据校验 （业务测试部门验证）
5. 立即备份（停机冷备）
6. 恢复架构系统
7. 撤维护页，恢复业务
模拟故障:
1. 模拟测试数据
mysql> create database pit;
mysql> use pit
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
2. 全备
[root@db01 tmp]
3. 模拟周三白天的操作
mysql> use pit
mysql> insert into t1 values(11),(22),(33);
mysql> commit;
4. 模拟周三下午2:00,删库操作
mysql> drop database pit;
恢复过程 :
1. 恢复全备
source /tmp/full_2300.sql
2. 截取二进制日志
起点: 21105555
[root@db01 tmp]
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=21105555;
终点:21105836
a. show master status ;
b.
mysql> pager grep -i "drop database pit" -B 10
mysql> show binlog events in 'mysql_bin.000009';
| mysql-bin.000009 | 21105805 | Xid | 51 | 21105836 | COMMIT /* xid=6232 */
| mysql-bin.000009 | 21105836 | Gtid | 51 | 21105913 | SET @@SESSION.GTID_NEXT= '95972e36-43fe-11eb-a366-000c2905f029:70'
| mysql-bin.000009 | 21105913 | Query | 51 | 21106014 | drop database pit /* xid=6234 */
[root@db01 tmp]
/bin.sql
3. 恢复日志
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;
Percona Xrabackup 物理备份应用
5.1 介绍
物理备份工具. 备份恢复更快.
支持全备和增量备份
5.2 全备备份逻辑
a. 8.0 之前
预备: 初始化进程和线程,分配专用内存.
备份:
0. 连接目标数据库,获取数据库信息.
a. 当前LSN号码记录 ---> show engine InnoDB stutus ;
主要: Last checkpoint at NUM
`在这里会区分不同种类的备份
b. non-InnoDB 数据 :frm , csv , MYI ,MYI ----> FTWRL（`会加这把锁，用来备份非innodb的表）但是在8.0之后，这些备份的数据都没有了
c. unlock tables;
d. Copy Innodb 数据: ibd ibdata1 undo tmp ,可以允许DML
e. 备份期间的redo截取和备份,并且记录LSN号.（`因为innodb在备份期间，并没有加锁，所以可以做插入的一些操作，那么就会刷新redo日志,但是他们只能存在于内存中，并不可能落盘到磁盘）
结束备份:
记录binlog的当前位置点,将所有备份信息记录至专用日志文件中.
b. 8.0 之后
预备: 初始化进程和线程,分配专用内存.
备份:
0. 连接目标数据库,获取数据库信息.
a. 当前LSN号码记录 ---> show engine InnoDB stutus ; 主要: Last checkpoint at NO.
b. LOCK INSTANCE FOR BACKUP（轻量级的备份锁，让我们的备份更加流畅）;
UNLOCK INSTANCE;
c. Copy Innodb 数据: ibd ibdata1 undo tmp ,可以允许DML
d. 备份期间的redo截取和备份,并且记录LSN号.
结束:
记录binlog的当前位置点,将所有备份信息记录至专用日志文件中.
5.3 增量备份逻辑（根据的是LSN的号码，进行的增量备份）
a. 第一次增量备份,使用全备作为"参照物",把变化的数据页和备份过程中产生的redo保存.
b. 往后所有的增量,都基于上一次备份作为参照物.把变化的数据页和备份过程中产生的redo保存.
c. 增量备份只针对InnoDB表有效,所以在8.0之前,针对非InnoDB表,都是全备.
5.4 恢复过程
全备 + 增量 + binlog
例如:备份策略为,full + inc1 + inc2 .......
a. prepare(准备备份) 全备 (CR)
应用redo前滚
应用undo回滚(省略)
b. 合并所有增量到全备并且prepare
应用redo前滚
应用undo回滚(除了最后一次增量,这步省略)
c. 合并后的全备prepare
d. 恢复备份
5.5 PXB的版本兼容性
MySQL 5.6 ,5.7 : PXB 2.4版本
MySQL 8.0.11 ~ 8.0.19 : PXB 8.0 稳定版.
MySQL 8.0.20 : PXB 8.0.12+
5.6 全量备份
5.6.0 安装
5.6.1 全量备份
mkdir -p /data/backup
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123 --backup --target-dir=/data/backup/full
配置文件中需要加：
[client]
socket=/tmp/mysql.sock 加上客户端的socket.因为你是通过本地进行连接的。
5.6.2 数据恢复：
a 搞破坏
[root@db01 ~]
[root@db01 ~]
[root@db01 ~]
[root@db01 ~]
b 准备：(CR)
xtrabackup --prepare --target-dir=/data/backup/full
说明： 模拟CR过程，将redo前滚，undo回滚，让备份数据是一致状态
c 拷回数据：
xtrabackup --copy-back --target-dir=/data/backup/full
d 修改权限并启动数据库
[root@db01 data]
[root@db01 data]
场景1: 80G,mysqldump全备+binlog备份,误删除了10M表,进行模拟和恢复
1.mysql> create database mdp;
2.mysql> use mdp;
3.mysql> create table app(id int);
4.mysql> insert into app values(1),(2),(3);
1.mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /tmp/full.sql （全备放入文件）
1.mysql> use mdp;
2.mysql> insert into app values(11),(22),(33);
1.mysql> drop table app;
----------------------------------------------------------------------
----------------------------------------------------------------------
1. 从全备中抽取app 表的备份
vim /tmp/full.sql
CREATE TABLE `app` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
`找到app表的插入语句，如果数据过多，可以打入到一个文件中
[root@db01 ~]# grep -i 'insert into `app` ' /tmp/full.sql （也有bug）
INSERT INTO `app` VALUES (1),(2),(3);
[root@db01 ~]# grep -i 'insert into `app` ' /tmp/full.sql >/tmp/insert.sql
2.从binlog中抽取app表的日志
[root@db01 binlog2sql-master]# mysqlbinlog --base64-output=decode-rows -vvv \--start-position=2050 /data/3306/binlog/mysql_bin.000001 |grep '^###'>/tmp/bin.sql
'--start-position=2050 从grep -i "\-- change master to " /tmp/full.sql 过滤`
'/data/3306/binlog/mysql_bin.000001 从 show master status 查看'
vim /tmp/bin.sql
3.恢复
1.先恢复建表语句
set sql_log_bin=0; 都不产生新的日志
mysql> use mdp;
mysql> CREATE TABLE `app` ( `id` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
mysql> INSERT INTO `app` VALUES (1),(2),(3);
2.在恢复表内容
source /tmp/bin.sql;
场景2: 500G数据库,PXB 周日全备+周一-周五binlog,周三时候完全损坏,进行模拟和恢复
create database txp;
use txp;
create table t1(id int);
insert into t1 values(1),(2),(3);
mkdir -p /data/backup/full
xtrabackup --user=root --password=123 --backup --target-dir=/data/backup/full
mysql> use pxb;
mysql> insert into t1 values(11),(22),(33);
[root@db01 ~]
[root@db01 /data/3306/data]
------------------------------------------------------------------
-----------------------------------------------------------------
1.[root@db01 /data/3306/binlog]
2.[root@db01 /data/backup/full]
mysql_bin.000002 196 5b0f15fd-48db-11eb-a874-000c29033daa:1-18
3.[root@db01 /data/backup/full]
[root@db01 /data/3306/binlog]
4.[root@db01 ~]
[root@db01 ~]
5. [root@db01 ~]
[root@db01 ~]
mysql> source /tmp/bin.sql;
扩展题: 500G数据库,PXB 周日全备+周一-周五binlog,周三时候误删除50M的小表,怎么快速恢复?
1.不扩展
就直接把周三的那张小表进行恢复即可,使用迁移表空间即可。
2.扩展
如果扩展，就是直接全部干掉，然后先用pxb，恢复全表备份，在用binlog进行，就行增量恢复，最后在恢复表。
扩展：迁移表空间（独立表空间）
准备：必须ibd文件存在
1.在目标数据库创建一个空表，与源端库属性一模一样
` create database test;
2.目标端删除表空间文件，保留表结构
` alter table t100w discard tablespace;
3.源端锁定需要迁移的表
` flush table t100w with read lock
4.拷贝ibd文件，到目标端的目录下。
` cp -a 源目录下的ibd文件 目标目录下的ibd文件路径
5.目标端导入表空间文件
` alter table t100w import tablespace;
## 当数据拷贝过程之后，并不被数据库识别，所以需要重新加载系统的那些表空间文件，然后让其与内容进行相对应，然后使得系统对于识别。
6.释放源端表锁操作
` unlock tables;
备注：
其实先删除表空间文件，和后加载表空间。
可以理解成，我搬家，我需要先找到一个家。然后新家里的自带的东西我先清理出去，我将自己的东西，先放进去。然后在将自带的东西放进房间，和我的东西进行匹配，合成一个完整的。
表迁移案例
学生案例1:
环境: centos 7.x IBM 开发用的 jira confluence 业务 mysql 5.6.35 rpm.
现象:
断电导致: / read-only fsck , 开发执行了fsck
OS 正常启动,数据库起不来.
检查 /data/3306/data 数据,jira 在 ,confluence 没了
求助:
1. confluence有没有办法恢复? ----> 还钱磁盘数据修复
2. 能不能单独恢复jira库数据下先用着
自己尝试,cp jira 到虚拟环境,发现读取不了.
我提供方案:表空间迁移
恢复过程:
1. 找到所有jira库下的建表语句.
使用的是2年前的备份中导出建表语句 ,并恢复
2. 批量的将空表的表空进行discard
alter table test.t100w discard tablespacce;
select concat("alter table ",table_schema,".",table_name," discard tablespce;") from information_schema.tables
where table_schema='jira' into outfile '/tmp/discard.sql'
3. 拷贝 ibd 到指定目录下
cp
4. 批量的进行import
select concat("alter table ",table_schema,".",table_name,"import tablespce;") from information_schema.tables
where table_schema='jira' into outfile '/tmp/import.sql'
学生案例2:
现场:
mysql-5.7.18,网上找参数,调整生产库.误删除了ibdata1 文件.数据库再也起不来了.
备份也不好使.
扩展1: 8.0之前,如何通过frm文件获取建表语句
[root@db01 ~]
[root@db01 ~]
[root@db01 ~]
[root@db01 ~]
[root@db01 ~]
[root@db01 ~]
[root@db01 ~]
[root@db01 test]
扩展2 : 8.0之后,只有ibd文件怎么获取建表语句?
[root@db01 test]
增量备份
1 介绍
增量备份，是基于上一次备份LSN变化过的数据页进行备份，在备份同时产生的新变更，会将redo备份。
第一次增量是依赖于全备的。将来的恢复也要合并到全备中，再进行统一恢复。
2 增量备份演练
全量备份的目录为： mkdir -p /data/backup/full
增量备份的目录为： mkdir -p /data/backup/inc
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123 --backup --parallel=4 --target-dir=/data/backup/full
mysql> create database pxb;
mysql> use pxb
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123 --backup --parallel=4 --target-dir=/data/backup/inc --incremental-basedir=/data/backup/full
[root@db01 ~]
[root@db01 ~]
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full --incremental-dir=/data/backup/inc
xtrabackup --copy-back --target-dir=/data/backup/full
chown -R mysql:mysql /data/*
` 增量两天的数据
1.如果要进行增量备份，肯定是在第一天增量的备份基础上进行第二天的增量备份
[root@db02 /data/backup/inc1]
inc 是第一天的增量备份，inc_1是第二天的增量备份。inc_1需要单独创建成一个目录并且授权。
2.在进行恢复的时候，只需要从inc_1直接就行恢复就可以了，不用在经过inc这个增量备份文件了
[root@db02 ~]
'然后在进行全部的备份准备，然后在进行全部的备份，在重启数据库即可'
克隆 Clone-plugin
1 . Clone Plugin介绍
本地克隆(local)：
启动克隆操作的MySQL服务器实例中的数据，克隆到同服务器或同节点上的一个目录里
远程克隆(remote)：
默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并将其替换为捐赠者(donor)的克隆数据。您也可以将数据克隆到接受者的其他目录，以避免删除现有数据。
2 应用
2.1 本地
2.1.1 加载插件
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';
2.1.2 创建克隆专用用户
CREATE USER clone_user@'%' IDENTIFIED by 'password';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user';
2.1.3 本地克隆
[root@db01 3306]
[root@db01 3306]
mysql -uclone_user -ppassword
CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';
db01 [(none)]> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-------------+----------------------------+
| STAGE | STATE | END_TIME |
+-----------+-------------+----------------------------+
| DROP DATA | Completed | 2020-04-20 21:13:19.264003 |
| FILE COPY | Completed | 2020-04-20 21:13:20.025444 |
| PAGE COPY | Completed | 2020-04-20 21:13:20.028552 |
| REDO COPY | Completed | 2020-04-20 21:13:20.030042 |
| FILE SYNC | Completed | 2020-04-20 21:13:20.439444 |
| RESTART | Not Started | NULL |
| RECOVERY | Not Started | NULL |
+-----------+-------------+----------------------------+
7 rows in set (0.00 sec)
set global log_error_verbosity=3;
tail -f db01.err
CLONE LOCAL DATA DIRECTORY = '/data/test/3308';
2.1.4 启动新实例
[root@db01 clonedir]
2.2 远程clone
2.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';
2.2.1 创建克隆用户
官方建议:
源端: backup admin
目标端: clone admin
mysql> create user clone@'10.0.0.%' identified with mysql_native_password by '123';
mysql> grant all on *.* to clone@'10.0.0.%';
mysql> create user clone_1@'10.0.0.%' identified with mysql_native_password by '123';
mysql> grant all on *.* to clone_1@'10.0.0.%';
2.2.2 远程clone(目标端)
SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';
mysql -uclone_1 -p123 -h10.0.0.52 -P3306
CLONE INSTANCE FROM clone@'10.0.0.51':3306 IDENTIFIED BY '123';
474
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
