第一章:备份恢复

作用
处理数据库损坏。
损坏？
物理：磁盘、文件系统、数据文件。处理方案：主从、高可用、备份+日志。
逻辑：drop truncate delete update 。处理方案：备份+日志、延时从。
备份工具

逻辑备份： mysqldump (MDP)、binlog 、主从 .....
物理备份 : Percona Xtrabackup (PXB\XBK\Xbakcup)
扩展: 8.0 Clone plugin

作为DBA在备份恢复工作职责

a. 设计备份策略：备份周期、备份工具、备份方式（全备、增量..）

b. 定期备份检查。

c. 定期恢复演练。

d. 数据恢复。

e. 数据的迁移升级。

mysqldump 应用

介绍:逻辑备份工具。文本形式保存备份，可读性较强。
备份逻辑：将建库、建表、数据插入语句导出，包存至一个sql文件中。
比较适合于：数据量较小的场景，单表数据行千万级别以内。百G以内的小型数据库.跨版本、跨平台迁移。
可以本地、可以远程备份。
注意：一般情况下，恢复需要耗费的时间是备份耗费时间的3-5倍。

备份基础参数

-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，再恢复数据。

高级功能的参数

全备份+binlog恢复数据,binlog的起点问题

参数一: -- master-data=2

作用说明:

a. 自动记录binlog位置点
b. 自动加GRL锁(FTWRL ,flush tables  with read lock)
c. 配合--single-transaction ,减少锁的时间

参数二： --single-transaction

a. 对于InnoDB表，利用MVCC中一致性快照进行备份。备份数据时,不加锁
b. 备份期间如果出现DDL操作，导致备份数据不一致

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

8.0 之后 master-data和single-transaction,对于InnoDB数据备份时是快照备份的. 备份表结构等数据时,还是FTWRL过程备份.

--single-transaction 只是针对InnoDB表数据进行一致性快照备份。
问题： mysqldump备份需要锁表吗？
是有的。global read lock

参数三： -R -E --triggers 备份特殊对象
存储过程函数触发器事件

参数四： --max_allowed_packet=64M

最大允许数据包的大小。

标准化备份:

[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:

4.2.5 案例：通过mysqldump全备+binlog实现PIT数据恢复

环境背景：小型的业务数据库，50G，每天23:00全备，定期binlog异地备份。
故障场景：周三下午2点，开发Navicat连接数据库实例错误，导致生产数据被误删除（DROP）

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

模拟环境

mysql> create database mdb;
Query OK, 1 row affected (0.01 sec)

mysql> use mdb
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


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

mysql> create table t3 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database mdb;

进行标椎备份:

[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

方法一:利用binlog日志进行恢复

第一步:进入配置文件进行查看binlog日志的起点:

[root@db01 backup]# vim full_2020-11-17.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=1421;

第二步:进行查看binlog日志

mysql> show binlog events in 'binlog.000005';


| binlog.000005 | 1498 | Query          |         1 |        1599 | drop database mdb /* xid=740 */                                                                                                                          |
+---------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------

第三步:进行截取恢复

[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=2717  --stop-position=2686 /data/3306/data/binlog.000005 >/tmp/bin.sql

第四步:在数据库中进行导入备份数据库

source /tmp/bin.sql

方法二:进行mysqldump的恢复

    source /data/backup/full_2020-11-17.sql

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

5.0 介绍

物理备份工具，支持全备和增量备份。
备份逻辑：
a. 数据库运行期间，拷贝数据表空间.
b. 拷贝的同时，会将备份期间的redo进行备份

恢复逻辑：
模拟了InnoDB Crash Recovery 功能，需要要将备份进行处理（前滚和回滚）后才能恢复

做全量的备份

xtrabackup --defaults-file=/etc/my.cnf  --user=root --password=123  --backup --parallel=4 --target-dir=/data/backup/full &>/tmp/bak.log

2.数据恢复：

2.0 搞破坏

[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*
[root@db01 ~]# rm -rf /data/3306/logs/*
[root@db01 ~]# rm -rf /data/3306/binlog/*

2.1 准备：(CR)

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

2.2 拷回数据：

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

修改权限并启动数据库

[root@db01 data]# chown -R mysql.mysql /data/*
[root@db01 data]# /etc/init.d/mysqld start

做增量的备份

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

第一步:删除第一次全备份的

[root@db01 backup]# rm -rf *

第二步:进行全量的备份

xtrabackup --defaults-file=/etc/my.cnf  --user=root --password=123  --backup --parallel=4 --target-dir=/data/backup/full &>/tmp/bak.log

第三步:在全部备份结束后模拟数据库的继续增加数据

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



参数的含义:
--target-dir=/data/backup/inc  ##增量备份的存放位置
basedir=/data/backup/full     ###参照第一次的全量备份

第五步:进行全量的日志的准备

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 --prepare --target-dir=/data/backup/full

第八步:进行数据的拷贝

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

第九步: 修改数据目录的权限和属性：

chown -R mysql:mysql /data/*

接下来进行数据库的启动数据库的连接检查

Clone Plugin介绍

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

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

克隆的前提准备

第一步:在配置文件中进行添加或者在命令行进行直接修改(在命令行修改在数据库进行重启就失效)

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';

第二步;创建克隆的专用用户

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';

进行观测状态

 观测状态
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                       |
+-----------+-------------+----------------------------+

启动新实例

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

远程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';

数据库的备份恢复