MySQL 备份恢复

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）*******

3.3 架构备份方式

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 全备
[root@db01 ~]$ mkdir -p /data/backup
[root@db01 ~]$ chown -R mysql.mysql /data/*
[root@db01 ~]$ mysqldump -uroot -pmysql -S /tmp/mysql.sock -A >/data/backup/full.sql
[root@db01 ~]$ vim /data/backup/full.sql

-B 备份单或多个库
[root@db01 ~]$ mysqldump -uroot -pmysql -B world test >/data/backup/db.sql

 

# 隐藏登录密码
[root@db01 ~]$ vim .my.cnf
[mysql]
user=root
password=mysql
[mysqldump]
user=root
passworl=mysql
[root@db01 ~]$ mysql
[root@db01 ~]$ mysqldump -A >/data/backup/full.sql;
[root@db01 ~]$ mysql --help --verbose |grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
[root@db01 ~]$ rm -rf .my.cnf

备份单表或多表
[root@db01 ~]$ mysqldump -uroot -pmysql -S /tmp/mysql.sock world city country >/data/backup/tdb.sql

面试题：

验证一下：以下两个命令的备份结果区别？

mysqldump -uroot -pmysql -B world >/data/backup/db1.sql
多了以下：
create database world;
use world;
mysqldump -uroot -pmysql world >/data/backup/db2.sql
应用时，world库不存在，需要手工创建，并且use到world库下再恢复

[root@db01 ~]$ mysqldump -uroot -pmysql -B world >/data/backup/db1.sql
[root@db01 ~]$ mysqldump -uroot -pmysql world >/data/backup/db2.sql
[root@db01 ~]$ vimdiff /data/backup/db1.sql /data/backup/db2.sql

4.3.3 备份高级参数

场景：
每周日 23:00 全备，周1-6 binlog备份。所有备份是完整的
周三时，有一个核心运维人员进行了删库操作。

先恢复全备 + 所有需要binlog恢复
痛点：binlog的截取
起点： 查找比较困难：
方法一：备份开始时，切割日志。 —F
方法二：备份开始时，自动记录日志文件信息 --master-data=2

终点： drop之前的位置点。

--master-data=2

[root@db01 ~]$ mysqldump --help
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.

--master-data[=#]
功能：
1. 备份是自动记录binlog信息
2. 自动锁表和解锁
3. 配合single transaction 可以减少锁表时间

[root@db01 ~]$ mysqldump -uroot -pmysql -A --master-data=2 >/data/backup/full1.sql
[root@db01 ~]$ vim /data/backup/full1.sql

[root@db01 ~]$ mysqldump -uroot -pmysql -A --master-data=1 >/data/backup/full2.sql
[root@db01 ~]$ vim /data/backup/full2.sql

[root@db01 ~]$ vimdiff /data/backup/full1.sql /data/backup/full2.sql


[root@db01 ~]$ ls -l /data/binlog
[root@db01 ~]$ vimmysqldump -uroot -pmysql -A -F >/data/backup/full3.sql
[root@db01 ~]$ ls -l /data/binlog
[root@db01 ~]$ mysql -uroot -pmysql
mysql> show databases;
说明：-F参数 切割出现，有多少个库就会切割出多少个binlog日志

--single-transaction

[root@db01 ~]$ mysqldump --help
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.

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

[root@db01 ~]$ mysmysqldump -uroot -pmysql -A --master-data=2 --single-transaction >/data/backup/full5.sql

-R -E --triggers

[root@db01 ~]$ mysqldump -uroot -pmysql -A --master-data=2 --single-transaction -R -E --triggers >/data/backup/full5.sql

--max_allowed_packet=64M

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

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

基础环境：CentOS7.6 + MySQL 5.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 t1 (id int);
begin;
insert into t1 values(1),(2),(3);
commit;
2. 模拟周二晚上全备
mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full_`date +%F`.sql
[root@db01 ~]$ ls -l /data/backup/
-rw-r--r-- 1 root root 50894112 7月 15 22:20 full_2020-07-15.sql
3. 模拟周三白天的数据变化
use mdp;
create table t2(id int);
insert into t2 values(1),(2),(3);
commit;
4. 搞破坏
drop database mdp;
5. 开始恢复
5.1 检查全备
[root@db01 ~]$ cd /data/backup
[root@db01 backup]$ vim full_2020-07-15.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=788;
5.2 恢复全备
mysql> set sql_log_bin=0;
mysql> source /data/backup/full_2020-07-15.sql;

mysql> use mdp;
mysql> show tables;

5.3 截取binlog
起点：
[root@db01 backup]$ grep "\-- CHANGE MASTER TO" /data/backup/full_2020-07-15.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=788;
终点：
mysql> show master status;
mysql> show binlog events in 'mysql-bin.000017';
| mysql-bin.000017 | 1274 | Query | 6 | 1363 | drop database mdp |
position截取：
mysqlbinlog --skip-gtids --start-position=788 --stop-position=1274 /data/binlog/mysql-bin.000017 >/tmp/binlog.sql;
[root@db01 ~]$ mysqlbinlog --skip-gtids --start-position=788 --stop-position=1274 /data/binlog/mysql-bin.000017 >/tmp/binlog.sql;

GTID截取：
[root@db01 ~]$ vim /data/backup/full_2020-07-15.sql
SET @@GLOBAL.GTID_PURGED='066d65da-b0a8-11ea-affd-000c29682dd4:1-15';
起点：
066d65da-b0a8-11ea-affd-000c29682dd4:16'
终点：
066d65da-b0a8-11ea-affd-000c29682dd4:17'
| mysql-bin.000017 | 1209 | Gtid | 6 | 1274 | SET @@SESSION.GTID_NEXT= '066d65da-b0a8-11ea-affd-000c29682dd4:18' |
| mysql-bin.000017 | 1274 | Query | 6 | 1363 | drop database mdp |
mysqlbinlog --skip-gtids --include-gtids='xxxxxxxxxxx:16-17' /data/binlog/mysql-bin.000017 >/tmp/gtdb.sql

5.4 恢复binlog
mysql> set sql_log_bin=0;
mysql> source /tmp/binlog.sql;
mysql> set sql_log_bin=1;

练习：

使用rm -rf /data/3306/* 故障模拟恢复

6. mysqldump的小结

参数：
-u -p -S -h -P
-A -B --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64
选择场景：
优点：可读性比较强，压缩比相较高，节省空间，不需要下载安装。
缺点：备份时间相对较长。恢复时间长。
数据量较少，建议mysqldump。100G以内控制在1小时之内
数据量巨大：分布式架构，数据量较大时候，可以采用分布式备份，也可以选择mysqldump

扩展：

从mysqldump 全备中获取 库和表的备份
1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql>createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
# grep -i 'INSERT INTO `city`' full.sqll >data.sql &
3.获取单库的备份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql