[MySQL]-08MySQL备份恢复

第1章备份恢复

1.备份恢复的职责

1.备份、恢复策略的设计。
  备份周期、备份工具、备份方式、恢复方式全部流程化
2.日常备份检查
  日志、备份内容	 
3.定期的恢复演练
4.数据故障时，利用现有的资源，快速恢复
5.数据迁移、升级。

第2章备份工具介绍

1.逻辑备份

mysqldump / source   *****
mysqlbinlog / source 
mydumper / myloader
select into outfile / load data infile 
binlog2sql
myflashback

2.物理备份

Percona Xtrabackup (PXB,XBK) *****

3.选型

100G 以内： 逻辑
100G 以上： 物理

第3章 mysqldump工具使用

1.介绍

linux6数据逻辑备份工具。（Create database   create  table  insert）
MySQL 自带的客户端命令。可以实现远程和本地备份。

2.参数

2.1 连接参数

-u 
-p 
-S 
-h 
-P

2.2 备份参数

# -A  全备
mkdir /data/backup
mysqldump -uroot -p123 -A >/data/backup/full.sql

# -B  单库或多库
mysqldump -uroot -p123 -B world gtdb test >/data/backup/db.sql

# 备份单表或多表 
mysqldump -uroot -p123 world t1 country >/data/backup/tab.sql

# --master-data=2 
1.自动记录备份时的binlog信息（注释）
2.自动锁定所有表，自动解锁（global read lock）。最好配合--single-transaction 参数，减少锁表时间。
mysqldump -uroot -p123 -A --master-data=2 >/data/backup/full.sql

# --single-transaction
对于InnoDB表，开启独立事务，通过快照备份表数据，不锁表备份，可以理解为热备。
mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  >/data/backup/full.sql

# --max_allowed_packet=64M 最大允许的数据包大小
mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  >/data/backup/full.sql

# -R  -E  --triggers 备份特殊对象使用
mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E --triggers >/data/backup/full.sql

# 按日期备份定义文件名
mysqldump -uroot -p  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full_`date +%F`.sql

3.故障恢复演练(mysqldump+binlog)

3.1 模拟环境

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

3.2 模拟周一23:00 全备

备份命令：

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

查看GTID相关信息，GTID截取起点

SET @@GLOBAL.GTID_PURGED='9b52b744-eb82-11ea-986c-000c294983f8:1-6';

查看pos号，备份开始时binlog位置点信息

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1028;

3.3 模拟周二白天数据变化

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

3.4 模拟周二下午2点，误删除了mdb核心库

mysql> drop database linux6;

3.5 恢复数据

a.恢复全备到周一晚23:00

# 检查全备： 
vim /data/backup/full_2021-06-28.sql

# 查看 GTID相关信息 ：GTID截取起点。
SET @@GLOBAL.GTID_PURGED='3a92e706-d806-11eb-b187-000c294983f8:1-4';

# 查看pos号，备份开始时binlog位置点信息。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1028;

b.截取日志

# 起点： 
mysql-bin.000001 3a92e706-d806-11eb-b187-000c294983f8:2 或者 mysql-bin.000001 pos=479

# 终点： 找到drop事件
[root@db-51 backup]# mysql -uroot -p123456 -e "show binlog events in 'mysql-bin.000001'"|grep -B 1 "drop database linux6"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-bin.000001        1462    Gtid    51      1527    SET @@SESSION.GTID_NEXT= '3a92e706-d806-11eb-b187-000c294983f8:7'
mysql-bin.000001        1527    Query   51      1625    drop database linux6

# 截取日志 
[root@db-51 ~]# mysqlbinlog --skip-gtids --include-gtids='3a92e706-d806-11eb-b187-000c294983f8:2-6' /data/mysql_3306/logs/mysql-bin.000001 > /data/backup/bin.sql

c.恢复

set sql_log_bin=0;
source /data/backup/full_2021-06-28.sql
source /data/backup/bin.sql
set sql_log_bin=1;

d.检查数据

use linux6
show tables;
select * from t1;
select * from t2;

4.mysqldump多种备份策略和恢复策略介绍

4.1 场景

100G 全库数据 全库备份 30分钟-40分钟，恢复整库需要5倍时间2.5-3小时之间
一张表 1G 被误删除了

4.2 备份策略

a. linux6 full + binlog 增量备份思路

1.提取full全备中的故障表数据 ，恢复数据
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q'  full_2021-06-28.sql > create_table.sql
sed -n '/CREATE TABLE `t1` /,/;/p' /data/backup/full.sql 

grep -i 'INSERT INTO `t1`' full_2021-06-28.sql > data.sql 

2.binlog中截取全备到误删除t1之间对于这张表的修改

b.单库单表备份+binlog 增量思路

1.恢复单表的备份
2.binlog中截取备份到误删除t1之间对于这张表的修改

4.3 模拟故障

实验环境

库 oldboy
表 t1 t2

a.模拟原始数据

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

b.周一晚上全库备份

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

c.模拟周二白天的数据变化

use oldboy ;
insert into t1 values(11),(22),(33);
commit;

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

insert into t1 values(111),(222),(333);
commit;

d.模拟周二下午2点，误删除数据库

drop table t1;

4.4 模拟恢复

a.处理全备

[root@db-51 ~]# sed -n '/CREATE TABLE `t1` /,/;/p' /data/backup/full.sql >/data/backup/create.sql
[root@db-51 ~]# grep -i 'INSERT INTO `t1`'  /data/backup/full.sql >/data/backup/insert.sql

b.binlog的截取

获取当前binlog文件

[root@db-51 ~]# mysql -uroot -p123456 -e "show master status"|grep mysql-bin
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-bin.000001        1920                    079a2db2-d80b-11eb-80e9-000c294983f8:1-8

获取起点：079a2db2-d80b-11eb-80e9-000c294983f8:1-3

[root@db-51 ~]# vim /data/backup/full.sql
SET @@GLOBAL.GTID_PURGED='079a2db2-d80b-11eb-80e9-000c294983f8:1-3';

终点：079a2db2-d80b-11eb-80e9-000c294983f8:8

[root@db-51 ~]# mysql -uroot -p123456 -e "show binlog events in 'mysql-bin.000001'" |grep -B 1 'DROP TABLE `t1`' 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-bin.000001        1736    Gtid    51      1801    SET @@SESSION.GTID_NEXT= '079a2db2-d80b-11eb-80e9-000c294983f8:8'
mysql-bin.000001        1801    Query   51      1920    use `oldboy`; DROP TABLE `t1 /* generated by server */

gtid范围：

[root@db-51 ~]# mysqlbinlog --include-gtids='079a2db2-d80b-11eb-80e9-000c294983f8:4-7' /data/mysql_3306/logs/mysql-bin.000001 |grep -B 16 't1'|grep "GTID_NEXT"
SET @@SESSION.GTID_NEXT= '079a2db2-d80b-11eb-80e9-000c294983f8:4'/*!*/;
SET @@SESSION.GTID_NEXT= '079a2db2-d80b-11eb-80e9-000c294983f8:7'/*!*/;

截取方法1：

mysqlbinlog --skip-gtids --include-gtids='079a2db2-d80b-11eb-80e9-000c294983f8:4-7' --exclude-gtids='079a2db2-d80b-11eb-80e9-000c294983f8:5-6' /data/mysql_3306/logs/mysql-bin.000001 >/data/backup/bin.sql

截取方法2:

mysqlbinlog --skip-gtids --include-gtids='079a2db2-d80b-11eb-80e9-000c294983f8:4','079a2db2-d80b-11eb-80e9-000c294983f8:7' /data/mysql_3306/logs/mysql-bin.000001 >/data/backup/bin2.sql

c.恢复数据

use oldboy;
set sql_log_bin=0;
source /data/backup/create.sql
source /data/backup/insert.sql
commit;
source /data/backup/bin.sql
set sql_log_bin=1;

5.mysqldump实现单库单表备份

设置安全导出文件：

[root@db-51 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv=/tmp

[root@db-51 ~]# systemctl restart mysqld

构造备份语句脚本：

[root@db-51 ~]# mkdir -p /data/backup/single_bak
[root@db-51 ~]# mysql -uroot -p123
mysql> select concat("mysqldump -uroot -p123 -A --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers ",table_schema," ",table_name," >/data/backup/single_bak/",table_schema,"_",table_name,".sql") 
from information_schema.tables 
where table_schema not in ('sys','information_schema','performance_schema')
into outfile '/tmp/single_bak.sh';  
[root@db-51 ~]# sh /tmp/single_bak.sh & > /tmp/bak.log

6.免交互输密码实现访问数据库

写入配置:

cat > /opt/mypass.cnf << 'EOF' 
[client]
user=root
password=123456
EOF

调用方法：

echo "show databases;"|mysql --defaults-file=/opt/mypass.cnf -S /tmp/mysql_3306.sock -N

编写脚本:

#!/bin/bash

sql=$(echo "show databases;"|mysql --defaults-file=/opt/mypass.cnf -S /tmp/mysql_3306.sock -N)

for ku in ${sql}
do
  for biao in $(echo "show tables from ${ku};"|mysql --defaults-file=/opt/mypass.cnf -S /tmp/mysql_3306.sock -N)
    do
      mkdir -p /backup/${ku}
      mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --set-gtid-purged=OFF --master-data=2  --single-transaction  --max_allowed_packet=64M -R -E --triggers ${ku} ${biao} > /backup/${ku}/${biao}.sql 
    done
done

第4章 Xtrabackup工具使用

1.介绍

percona公司研发 
xtrabackup  --> C  C++
innobackupex  --> perl语言
8.0之前，2.4.x 
8.0之后，8.0
物理备份工具，类似于cp文件。支持：全备和增量备份

2.安装

2.1 安装依赖包

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

2.2 下载软件并安装

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

3.全备

3.1 介绍

拷贝/data/mysql_3306/data/下的数据文件。
InnoDB  : 热备。拷贝ibdataN,UNDO00N ,ibtmpN ,ibd 。通过截取变化redo。 
非InnoDB: FTWRL，全局锁。拷贝非INNODB的文件frmmyimyd...
只能本地备份。

3.2 实现全备

修改配置文件并重启

[root@db-51 ~]# vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock
[root@db-51 ~]# systemctl restart mysqld

全备命令：

innobackupex --user=root --password=123456 /data/backup/

查看备份完成的目录：

[root@db-51 ~]# ll /data/backup/test/2020-09-14_22-06-11/
总用量 12348
-rw-r----- 1 root root      487 9月  14 22:06 backup-my.cnf
drwxr-x--- 2 root root       48 9月  14 22:06 gtdb
-rw-r----- 1 root root    10056 9月  14 22:06 ib_buffer_pool
-rw-r----- 1 root root 12582912 9月  14 22:06 ibdata1
drwxr-x--- 2 root root       52 9月  14 22:06 ku
drwxr-x--- 2 root root       52 9月  14 22:06 linux5
drwxr-x--- 2 root root       76 9月  14 22:06 linux6
drwxr-x--- 2 root root     4096 9月  14 22:06 mysql
drwxr-x--- 2 root root       90 9月  14 22:06 oldboy
drwxr-x--- 2 root root      134 9月  14 22:06 oldya
drwxr-x--- 2 root root     8192 9月  14 22:06 performance_schema
drwxr-x--- 2 root root      160 9月  14 22:06 school
drwxr-x--- 2 root root     8192 9月  14 22:06 sys
drwxr-x--- 2 root root       54 9月  14 22:06 test
drwxr-x--- 2 root root      144 9月  14 22:06 world
-rw-r----- 1 root root       63 9月  14 22:06 xtrabackup_binlog_info
-rw-r----- 1 root root      117 9月  14 22:06 xtrabackup_checkpoints
-rw-r----- 1 root root      546 9月  14 22:06 xtrabackup_info
-rw-r----- 1 root root     2560 9月  14 22:06 xtrabackup_logfile

目录文件介绍:

1.xtrabackup_binlog_info
记录binlog位置点， 截取binlog起点位置

2.xtrabackup_checkpoints
from_lsn = 0         # 一般增量备份会关注，一般上次备份的to_lsn的位置
to_lsn = 180881595   # CKPT-LSN 最近的内存数据落地到磁盘上的LSN号
last_lsn = 180881604 # xtrabackup_logfile LSN

3.xtrabackup_info         
备份总览信息

4.xtrabackup_logfile      
备份期间产生的redo变化

自定义备份目录

innobackupex --user=root --password=123 --no-timestamp /data/backup/xbk/full_`date +%F`

3.3 全备恢复应用

模拟删除

pkill mysqld 
rm -rf /data/mysql_3306/*

使用全备恢复数据

a.prepare 准备备份阶段

innobackupex --apply-log /data/backup/xbk/full

b.copy-back 恢复

方法1:

cp -a /data/backup/test/2020-09-14_22-06-11/* /data/mysql_3306/
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql.mysql /data/*

方法2:

innobackupex --copy-back /data/backup/test/2020-09-14_22-06-11/
innobackupex --move-back /data/backup/test/2020-09-14_22-06-11/

方法3:

直接修改配置文件把数据目录指向备份目录

c.天坑

1.备份恢复的时候不会把日志目录一起备份，比如错误日志和Binlog日志，恢复完成后需要手动创建
2.恢复后数据目录的用户权限都是root，需要手动更改权限

d.恢复后操作步骤：

mkdir /data/mysql_3306/{logs,binlog} -p
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306/

4.增量备份功能

4.1 介绍

自带的功能。
每次增量一般是将最近一次备份作为参照物。
自动读取参照物cat xtrabackup_checkpoints中to_lsn值，与当前CKPT的LSN对比，备份变化过page。
备份期间新的数据变化，通过redo自动备份。
恢复数据时，需要把所有需要的增量合并到FULL中。无法通过增量单独恢复数据，依赖与全备。

4.2 增量备份演练（FULL(周日)+inc1(周一)+inc2(周二)+inc3(周三)）

1.备份前数据准备：

create database xbk charset utf8mb4;
use xbk
create table full (id int);
insert into full values(1),(2),(3);
commit;

2.模拟周日 23:00 全备

innobackupex --user=root --password=123 --no-timestamp /data/backup/full_`date +%F`

3.模拟周一白天数据变化

use xbk
create table inc1 (id int);
insert into  inc1 values(1),(2),(3);
commit;

4.模拟周一23:00增量备份

innobackupex --user=root --password=123  --no-timestamp  --incremental --incremental-basedir=/data/backup/full_2020-09-15  /data/backup/inc1_`date +%F`

5.模拟周二白天数据变化

use xbk
create table inc2 (id int);
insert into  inc2 values(1),(2),(3);
commit;

6.模拟周二23:00增量备份

innobackupex --user=root --password=123  --no-timestamp   --incremental --incremental-basedir=/data/backup/inc1_2020-09-15  /data/backup/inc2_`date +%F`

7.模拟周三白天数据变化

use xbk
create table inc3(id int);
insert into  inc3 values(1),(2),(3);
commit;

8.模拟周三23:00增量备份

innobackupex --user=root --password=123  --no-timestamp   --incremental --incremental-basedir=/data/backup/inc2_2020-09-15  /data/backup/inc3_`date +%F`

9.模拟周四白天的数据变化。

use xbk
create table inc4(id int);
insert into  inc4 values(1),(2),(3);
commit;

10.周四下午出现数据损坏。如何恢复到误删除之前。

pkill mysqld
rm -rf /data/mysql_3306/*

11.恢复思路

1.我们有什么？
备份：  
full+inc1+inc2+inc3 
binlog:
full以来全量的binlog

2.处理备份
需要将inc1inc2inc3按顺序依次合并到全备，并进行prepare.
从官方角度：基础全备和合并所有增量（排除最后一个）都需要此参数
原理角度： 使所有备份合并时，LSN必须是连续的

12.处理base_full

innobackupex --apply-log --redo-only  /data/backup/full_2020-09-15/

13.inc1合并到full中，并且prepare

cd /data/backup/
innobackupex --apply-log --redo-only  --incremental-dir=inc1_2020-09-15 full_2020-09-15

检验合并结果

cat full_2020-09-15/xtrabackup_checkpoints |grep "to_lsn"
cat inc1_2020-09-15/xtrabackup_checkpoints |grep "to_lsn"

14.inc2合并到full中，并且prepare

cd /data/backup/
innobackupex --apply-log --redo-only  --incremental-dir=inc2_2020-09-15 full_2020-09-15

检验合并结果

cat full_2020-09-15/xtrabackup_checkpoints |grep "to_lsn"
cat inc2_2020-09-15/xtrabackup_checkpoints |grep "to_lsn"

15.inc3合并到full中，并且prepare

cd /data/backup/
innobackupex --apply-log --redo-only  --incremental-dir=inc3_2020-09-15 full_2020-09-15

检验合并结果

cat full_2020-09-15/xtrabackup_checkpoints |grep "to_lsn"
cat inc3_2020-09-15/xtrabackup_checkpoints |grep "to_lsn"

16.将合并后全备再次prepare

innobackupex --apply-log  /data/backup/full_2020-09-15

17.恢复并启动

innobackupex --copy-back /data/backup/full_2020-09-15
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql.mysql /data/*
systemctl restart mysqld

18.截取周三增量备份后到故障前所有的binlog日志并进行恢复

前提条件：binlog没有被删掉，如果binlog和数据目录放在一起，刚才的操作就把binlog也一起删掉了。

起点：2029

[root@db-51 ~]# cat /data/backup/inc3_2020-09-15/xtrabackup_binlog_info 
mysql-bin.000001        2029    9b52b744-eb82-11ea-986c-000c294983f8:1-17,
cb0fd847-f6e3-11ea-af80-000c294983f8:1-9

19.截取命令

mysqlbinlog --skip-gtids --start-position=2029  /binlog/mysql-bin.000001 >/data/backup/bin.sql

20.恢复binlog

mysql -uroot -p123
mysql> set sql_log_bin=0;
mysql> source /data/backup/bin.sql
mysql> set sql_log_bin=1;

21.xbk恢复完成后，清空所有日志

mysql> reset master;

21.立即再做个全备

innobackupex --user=root --password=123456 --no-timestamp /data/backup/full_bak_`date +%F`

相关阅读:
如何简单使用tensorboard展示(二)
如何简单使用tensorboard展示(一)
Cypress 系列之----03 常用API
Cypress 系列之----02 自定义命令Custom Commands
Windows下启动Jmeter出现Not able to find Java executable or version问题解决方案
 linux命令行下文件名中包含特殊符号如何的处理方法
 jenkins高级篇 pipeline系列之-—01简介
 Jenkins部署报错问题解决----git低版本引发的问题
 存储过程--使用变量循环调用
 jenkins高级篇 pipeline 系列之-—06 实现自动打增量包
原文地址：https://www.cnblogs.com/alaska/p/14961708.html

[MySQL]-08MySQL备份恢复

第1章 备份恢复

1.备份恢复的职责

第2章 备份工具介绍

1.逻辑备份

2.物理备份

3.选型

第3章 mysqldump工具使用

1.介绍

2.参数

2.1 连接参数

2.2 备份参数

3.故障恢复演练(mysqldump+binlog)

3.1 模拟环境

3.2 模拟周一23:00 全备

3.3 模拟周二白天数据变化

3.4 模拟周二下午2点，误删除了mdb核心库

3.5 恢复数据

4.mysqldump多种备份策略和恢复策略介绍

4.1 场景

4.2 备份策略

4.3 模拟故障

4.4 模拟恢复

5.mysqldump实现单库单表备份

6.免交互输密码实现访问数据库

第4章 Xtrabackup工具使用

1.介绍

2.安装

2.1 安装依赖包

2.2 下载软件并安装

3.全备

3.1 介绍

3.2 实现全备

3.3 全备恢复应用

4.增量备份功能

4.1 介绍

4.2 增量备份演练（FULL(周日)+inc1(周一)+inc2(周二)+inc3(周三)）

第1章备份恢复

第2章备份工具介绍