第一章:备份恢复
作用
处理数据库损坏。
损坏？
物理 ：磁盘、文件系统、数据文件。处理方案：主从、高可用、备份+日志。
逻辑 ：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';
