数据 备份恢复

1、最终版:（非常重要）

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

wget http://mirrors.aliyun.com/repo/epel-7.repo

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MYSQL libev

innobackupex --user=root --password=123456 --no-timestamp /Xbk/data/xbk/full_$(date +%F)

/usr/bin/mysqldump -uroot -p'D(!l+PdH-,$HaH@a' --all-databases --triggers --routines --events --master-data=2 --single-transaction --flush-logs > /data02/fullbackup/fulldb_`date +%F-%H-%M-%S`.sql

----------------------------------------------------------

1.MYSQL数据损坏类型

1.1 物理损坏

磁盘损坏：硬件，磁盘坏，dd，格式化

文件损坏：数据文件损坏，redo损坏。

1.2 逻辑损坏

drop

delete

truncate

update

2.DBA运维人员在备份，恢复的职责

2.1 设计备份、容灾策略

2.1.1 备份策略：

备份工具选择

备份周期设计

备份监控方法

2.1.2 容灾策略

备份：用什么备份。

架构：高可用，演示从库，灾备库

2.2 定期的备份、容灾检查

备份软件 -----> 带库

每周 北京 ---》天津 货运

2.3 定期的故障恢复演练

2.4 数据损坏时的快速准确恢复

2.5 数据迁移工作。

3.MYSQL 常用备份工具

3.1 逻辑备份方式

mysqldump（MDP） ******

replication

mydumper (自行拓展)

load data in file （自行拓展）

3.2 物理备份方式

MYSQL Enterprise Backup （企业版）

Percona Xtrabackup （PBK,XBK） *****

4.mysqldump (MDP) 应用

4.1 介绍

逻辑备份工具。备份的是SQL语句。

4.2 备份方式

4.2.1 INNODB

可以采取快照备份的方式。

开启一个独立的事务，获取当前最新的一致性快照。

将快照数据，放在临时表中，转换成SQL（Create database，create table，insert），保存到SQL文件中。

4.2.2 非INNODB

需要锁表备份。触发FTWRL，全局锁表，转换成SQL（CREATE DATABASE,CREATE TABLE,INSERT），保存到SQL文件中。

4.3 mysqldump的核心参数

4.3.1 连接参数

-u

-p

-h

-P

-S

4.3.2 备份参数

-A 全备

mkdir -p /data/backup

chown -R mysql.mysql /data/*

mysqldump -uroot -p123456 -S /tmp/mysql.sock -A >/data/backup/full.sql

vim /data/backup/full.sql

-B 备份1或者多个库

mysqldump -uroot -p123456 -B school xwu >/data/backup/db01.sql

备份单表或多表

mysqldump -uroot -p123456 -S /tmp/mysql.sock school t1 >/data/backup/tab01.sql

验证一下:以上两个命令的备份结果区别?

mysqldump -uroot -p123456 -B school xwu >/data/backup/db01.sql

mysqldump -uroot -p123456 school >/data/backup/db02.sql

应用时，world库不存在，需要手工创建，并且use到world库下再恢复。

4.3.3 备份高级参数

--master-data=2

场景：

每周日23:00全备，周1-6binlog备份。所有备份是完整的。

周三时，有一个核心运维人员进行了删库操作。

恢复全备 + 所有需要binlog恢复

痛点：binlog的截取

起点查找比较困难：

方法一：备份开始时，切割日志。-F

方法二：备份开始时，自动记录日志文件信息 --master-data=2

终点：drop之前的位置点。

--master-data=2

--master-data=[#]

功能：

1.备份时自动记录binlog信息

2.自动锁表和解锁

3.配合single transaction 可以减少锁表时间。

对于InnoDB引擎表备份时，开启一个独立的事务，获取一致性快照，进行备份。

自己的理解：

需求：需要统计班上的人数，还得让不限制每个人的活动。

方法：

1.原来的思想:把每个人统一叫到教室，逐个的数，弊端是限制了每个人活动。

2.新的思想:先把学生教到办公室，火速给他们照张相，从相片里数人数，这样既不限制个人自由，又能数清人数，并且高效。

锁的思想：

行级锁：

理解:上厕所，每个厕所有个大门，还有每个坑位有个门，行级锁的思想就是，不要锁厕所大门，锁每个坑位的门，再厕所里面，每个坑位互不影响，这也就是说这样锁之间不相互影响，可以达到高并发的状态。

--R -E --triggers

--max_allowed_packet=64M

最终版:（非常重要）

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

5.基于mysqldump + binlog 故障恢复案例

案例场景:

基础环境：Centos 7.6 + mysql5.7.28,LNMT 网站业务，数据量100G，每天5-10M数据增长。

备份策略：mysqldump每天全备，binlog定时备份。

恢复思路：

1.挂维护页。

2.找测试库。

3.恢复周二全备。

4.截取周二全备---》周三上午10点误删除之前得binlog，并恢复。

5.测试业务功能正常。

6.恢复业务：

方案1：故障库导回原生产。

方案2：直接用测试库承当生产，先跑着。

模拟数据损坏及恢复：

1.模拟原始数据

create database mdp charset=utf8mb4;

use mdp

create table mdp_test(id int,name,varchar(10))charset=utf8mb4 engine=innodb;

begin;

insert into mdp_test values(1,"a"),(2,"b"),(3,"c");

commit;

begin;

insert into mdp_test values(4,"d"),(5,"e"),(6,"f");

commit;

begin;

insert into mdp_test values(7,"g"),(8,"i"),(9,"l");

commit;

周二全备:

1.mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=64M >/data/backup/mdpfull_$(date +%Y%m%d).sql

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

周三发生操作

create table mdp_test1(id int)charset=utf8mb4 engine=innodb;

begin;

insert into mdp_test1 values(1),(2),(3);

commit;

总有刁民想还朕:

drop database mdp;

完了完了，影响生产了。

想啊想使用全备恢复生产

set sql_log_bin=0;临时二进制日志关闭。

source /data/backup/mdpfull_$(date +%Y%m%d).sql;用全备恢复周二前的数据库。

set sql_log_bin=1;

解决周二到周三这个阶段的数据恢复。

截取日志:vi source /data/backup/mdpfull_$(date +%Y%m%d).sql;

方法1:位置截取

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1407;

mysqlbinlog --start-position=1407 --stop-position=1876 mysql-bin.000004 >/tmp/1.sql

set sql_log_bin=0;临时二进制日志关闭。

source /tmp/1.sql;恢复周二到周三的数据库。

set sql_log_bin=1;

方法2:gtid截取

mysqlbinlog --skip-gtids --in

clude-gtids='f5b82fef-e3c6-11ea-8ed6-000c29c407ed:40-41' mysql-bin.000004>/tmp/gtmdb.sql;

set sql_log_bin=0;临时二进制日志关闭。

source /tmp/gtmdb.sql;恢复周二到周三的数据库。

set sql_log_bin=1;

数据恢复正常。

6.mysqldump的小结

参数:

-u -p -S -h -P

选择场景:

优点:可读性比较强，压缩比，节省空间，不需要下载安装。

缺点:备份时间相对较长。恢复时间长。

数据量较少:建议mysqldump。100G内

分布式架构，数据量较大的时候，可以采用分布式备份。也可以选择mysqldump。

拓展:

从mysqldump 全备中获取 库和表的备份。

1.获得表结构

sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE 'student'/!d;q' mdpfull_20201128.sql>createtable.sql

2.获得insert into 语句，用于数据恢复

grep -i 'insert into 'student'' mdpfull_20201128.sql >data.sql &

3.获取单库的备份

sed -n '/^-- Current Database: 'school'/,/^-- Current Database: '/p' all.sql >world.sql

7.Percona Xtrabackup

7.1 安装

7.1.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

7.1.2 下载软件并安装

https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/

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

yum -y install percona-xtrabackup-*.rpm

7.2 介绍

物理备份工具.拷贝数据文件.原生态支持全备

和增量

InnoDB表:

热备份：业务正常发生的时候，影响较小的备份方式。

1.checkpoint，将已提交的数据页刷新到磁盘。记录一个LSN。

2.拷贝InnoDB表相关的文件（Ibdata1,frm ibd...）

3.备份期间产生新的数据变化redo也会备份走。

非InnoDB表：

温备份: 锁表备份。

1.FTWRL，触发全局锁。

2.拷贝非InnoDB表的数据

3.解锁

再次统计LSN，写入到专用文件。

所有二进制日志位置记录下来。

所有备份文件统一存放在一个目录下。

7.3 XBK应用

7.3.1 前提

（1）数据库启动

（2）能连上数据库

[client]

socket=/tmp/mysql.sock

(3)默认会读取[mysqld] ====>datadir=xxx

(4)服务器端工具

7.3.2 全备

innobackupex --user=root --password=123456 --no-timestamp /Xbk/data/xbk/full_$(date +%F)

7.3.4 备份结果

xtrabackup_binlog_info :记录备份后binlog位置点信息，binlog的截取起点。

xtrabackup_checkpoints :备份过程中LSN记录，方面做增量备份。

7.3.5 全备的恢复演练

破坏：

pkill mysqld

rm -rf /Xbk/data/3306/*

备份处理：prepare

redo 前滚，undo回滚，模仿CSR过程。

innobackupex --apply-log /Xbk/data/xbk/full_$(date +%F)/

数据恢复：

cp -a /Xbk/data/xbk/full_$(date +%F)/* /Xbk/data/3306

chown -R mysql.mysql /data/*

/etc/init.d/mysqld start

增量备份:

1.xbk的增量备份

1.1

说明：

备份时：

增量必须依赖于全备。

每次增量都是参照上次备份的LSN号码（xtrabackup checkpoints）,在此基础上变化的数据页，备份走并且会将备份过程中产生新的变化的redo一并备份走。

恢复时：

将所有需要inc备份，按顺序合并到全备中。

并且需要将每个备份进行prepare。

1.2 增量备份实践

1.2.1 基础环境模拟

mysql> create table xbk_inc_t1(id int)charset=utf8mb4 engine=innodb;

mysql> begin;

mysql> insert into xbk_inc_t1 values (88);

mysql> commit;

mysql> select * from xbk_inc_t1;

1.2.2 模拟周日全备

rm -rf /Xbk/data/backup/*

innobackup --user=root --password=123456 --no-timestamp /Xbk/data/backup/full_$(date +%F)

1.2.3 模拟周一数据变化

mysql> create table xbk_inc_t2(id int)charset=utf8mb4 engine=innodb;

mysql> begin;

mysql> insert into xbk_inc_t2 values (4),(5),(6);

mysql> commit;

mysql> select * from xbk_inc_t2;

1.2.4 周一增量备份inc1

innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/Xbk/data/backup/full /Xbk/data/backup/inc1

说明:

--incremental 增量备份开关

--incremental-basedir 增量备份基目录。

1.2.5 模拟周二数据变化

mysql> create table xbk_inc_t3(id int)charset=utf8mb4 engine=innodb;

mysql> begin;

mysql> insert into xbk_inc_t3 values (4),(5),(6);

mysql> commit;

mysql> select * from xbk_inc_t3;

1.2.6 模拟周二晚上增量备份inc2

innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/Xbk/data/backup/full /Xbk/data/backup/inc2

1.2.7 模拟周三的数据变化

use xbk_inc;

create table t4(id int);

insert into t4 values (7),(8),(9);

commit;

1.2.8 搞破坏

pkill mysqld

rm -rf /data/3306/*

2.xbk full + inc + binlog 备份恢复手段。

2.0 思路

1.合并，prepare 所有inc备份到全备。

2.恢复数据，启动数据库。

3.截取binlog日志

4.恢复日志

2.1 过程

1.合并，prepare所有inc备份到全备。

# 基础全备整理

innobackupex --apply-log --redo-only /Xbk/data/backup/full

--redo-only This option should be used when preparing the base full

backup and when merging all incrementals except the last

one. This forces xtrabackup to skip the "rollback" phase

and do a "redo" only. This is necessary if the backup

will have incremental changes applied to it later. See

the xtrabackup documentation for details.

2.1 恢复过程

# 合并，prepare inc1到 full

innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full

# 合并，prepare inc2到full

innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc2 /data/backup/full

# 整体整合一遍

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

# 修复数据库

chown -R mysql.mysql /data/backup/full/

vim /etc/my.cnf

修改一下内容

datadir=/data/backup/full

#启动数据库

/etc/init.d/mysqld start

3.日志截取

起点:

终点:文章末尾

mysqlbinlog --skip-gtids --start-position /data/binlog/mysql-bin.0000020 >/tmp/bin.sql

mysql>set sql_log_bin=0;

mysql>source /tmp/bin.sql;

mysql>set sql_log_bin=1;

彩蛋:

问题思考:总数据量3TB，共10个业务，10个库500张表，周三上午10点，误删除了drop taobao.t1 核心业务表20G，导致淘宝库业务无法正常运行。tab

备份策略：周日 full，周一到周五inc，binlog完整。

怎么恢复，还不影响业务?

重点：1.不能影响业务。

2.要快速恢复。

思路：借助全备+incl+inc2+binlog

过程：1.drop table t1;

2.借助全备数据库查看此表的建库语句,建立表。

3.删除目标库上的ibd文件:

alter table tmp.test1 discard tablespace;

4.从源库中把目标库表的ibd文件拷到目标库

cp data/mysql/data/3306/testdb/test1.ibd data/mysql/data/3306/tmp/

5.alter table tmp.test1 import tablespace；

拓展:

1.书写mysqldump，XBK备份脚本。

2.自行学习mysqldumper

3.自行研究一下 into outfile /load data in file