备份恢复
mysql数据损坏类型
1.物理损坏
磁盘损坏：硬件，磁道坏，dd，格式化
文件损坏：数据文件损坏，redo损坏
2.逻辑损坏
drop，delete，truncate，update
运维人员在备份，恢复的指责
1.设计备份，容灾策略
备份策略：
备份工具的选择；
备份周期设计；
备份监控方法
容灾策略：
备份：什么备份；
架构：高可用，演示从库，灾备库
2.定期备份，容灾检查
备份软件 ----> 带库
两地三中心：
每周 北京 ==》天津 数据库相互切换
3.定期的故障恢复演练
使用从库和测试库进行演练
4.数据损坏时的快速准确恢复
5.数据迁移工作
Mysql 常用的备份工具
逻辑备份方式
mysqldump (MDP) *******
mydumper（自行扩展）
load data in file(自行扩展)
物理备份方式
企业版 Mysql Enterprise Backup(企业版)
Percona Xtrabackup (PBK,XBK) *****
mysqldump （MDP） 应用
介绍
逻辑备份工具。备份的是SQL语句。
InnoDB表：
利用多版本特性，采取快照备份的方式。开启一个独立的事务。
获取当前最新的一致性快照，将快照数据，放在临时表中转换为sql语句，保存到SQL文件中。
非InnoDB表：
需要锁表备份。触发FTWRL,全局锁表。转换为sql语句，保存到SQL文件中。
mysqldump的核心参数
连接参数：
-u 用户名
-p 密码
-h ip地址
-P 端口
-S socket
备份参数 :
-A 全备
mkdir -p /data/backup
chown mysql.mysql /data/*
mysqldump -uroot -pluobiao123 -S /tmp/mysql.sock -A > /data/backup/full.sql
-B 单库或者多库
mysqldump -uroot -pluobiao123 -S /tmp/mysql.sock -B world > /data/backup/world.sql
备份单表或者多表
mysqldump -uroot -pluobiao123 -S /tmp/mysql.sock world t9 t1 > /data/backup/world.sql
备份所有的表：
mysqldump -uroot -pluobiao123 -S /tmp/mysql.sock world > /data/backup/world.sql
恢复的时候，需要先建库，在use 库，在恢复.
备份高级参数:
每周23:00全备，周1-6binlog备份，所有备份是完整的。
周三时，又一个核心运维人员进行删库操作。
恢复全备 + 所有需要的binlog恢复。
问题？binlog的截取
起点查找比较困难：
方法一：备份开始时，切割日志。-F
方法二：备份开始时，自动记录日志文件信息
终点 :drop 之前的位置
--master-data=2
--master-data=[=#]
功能：
备份时自动记录当前的最新binlog信息，得到准确的position号，
用来恢复数据值，方便进行全备之后的binlog日志恢复。
自动锁表和解表
配合single transaction 可以减少锁表时间
--single-transaction
作用：对于innodb表备份时，开启一个事务，获取一致性快照进行备份,减少锁表。
测试:mysqldump -uroot -pluobiao123 --single-transaction --master-data=1 -S /tmp/mysql.sock -A > /data/backup/full2.sql
-R 备份存储过程,函数 -E 事件 --triggers 触发器
测试:mysqldump -uroot -pluobiao123 -R -E --triggers --single-transaction --master-data=1 -S /tmp/mysql.sock -A > /data/backup/full3.sql
--max_allowed_packet=64M 客户端 《=====》服务端 双向发送数据的大小设置
作用：
测试：测试:mysqldump -uroot -pluobiao123 --max-allowed-packet=64M -R -E --triggers --single-transaction --master-data=1 -S /tmp/mysql.sock -A > /data/backup/full4.sql
上面的语句就是mysql的最终的逻辑备份的语句的完整版，参数缺一不可。
5.基于mysqldump + binlog 故障恢复案例
案例环境：centos7.6 + mysql5.7.28 LNMT网站业务，数据量100G，
每天5-10M增长
备份策略：mysqldump每天全备，binlog定时备份。
故障模拟:周三上午10点数据故障，例如：核心业务库被删除。
恢复思路：
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
3.模拟周三数据变化
mysql> create table t2 (id int );
Query OK, 0 rows affected (0.12 sec)
mysql> insert into t2 values(1),(2),(3);
4.发生误操作
mysql> drop database mdp;
5.开始恢复
5.1恢复全备
检查全备：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=787;
5.2 恢复全备
source /data/backup/full_2020-03-28.sql
5.3 截取binlog
起点：
[root@later03 backup]# grep "\-- CHANGE MASTER*" /data/backup/full_2020-03-28.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=787;
终点：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000017 | 1364 | | | 7acb1b1a-6660-11ea-b89b-00163e0477d5:1-17 |
+------------------+----------+--------------+------------------+-------------------------------------------+
mysql> show binlog events in 'mysql-bin.000017';
| mysql-bin.000017 | 1275 | Query | 6 | 1364 | drop database mdp |
所以起点position号：787 终点:1275
开始截取 :
mysqlbinlog --start-position=787 --stop-position=1275 --skip-gtids /data/binlog/mysql-bin.000017 > /tmp/bbq.sql
5.4开始恢复:
不再计入binlog
set sql_log_bin = 0;
source /tmp/bbq.sql
开启binlog
set sql_log_bin = 0;
6.使用gtid恢复
6.1.使用全备恢复
6.2 寻找起点
[root@later03 backup]# grep "\-- CHANGE MASTER*" /data/backup/full_2020-03-28.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=787;
找到该position号对应的事务ID；
show binlog events in 'mysql-bin.000017';
| mysql-bin.000017 | 787 | Gtid | 6 | 852 | SET @@SESSION.GTID_NEXT= '7acb1b1a-6660-11ea-b89b-00163e0477d5:15' |
6.3寻找终点：
| mysql-bin.000017 | 949 | Gtid | 6 | 1014 | SET @@SESSION.GTID_NEXT= '7acb1b1a-6660-11ea-b89b-00163e0477d5:16' |
| mysql-bin.000017 | 1014 | Query | 6 | 1085 | BEGIN |
| mysql-bin.000017 | 1085 | Table_map | 6 | 1129 | table_id: 406 (mdp.t2) |
| mysql-bin.000017 | 1129 | Write_rows | 6 | 1179 | table_id: 406 flags: STMT_END_F |
| mysql-bin.000017 | 1179 | Xid | 6 | 1210 | COMMIT /* xid=5122 */ |
| mysql-bin.000017 | 1210 | Gtid | 6 | 1275 | SET @@SESSION.GTID_NEXT= '7acb1b1a-6660-11ea-b89b-00163e0477d5:17' |
| mysql-bin.000017 | 1275 | Query | 6 | 1364 | drop database mdp
6.3 截取日志
mysqlbinlog --include-gtids='7acb1b1a-6660-11ea-b89b-00163e0477d5:15-16' --skip-gtids /data/binlog/mysql-bin.000017 >/tmp/bbq3.sql
6.4进行恢复
set sql_log_bin=0;
source /tmp/bbq3.sql
set sql_log_bin=1;
总结：
选择场景:
数据量较少，建议mysqldump。大约在100G -200G左右。大概1个小时-2个小时。
恢复是备份时间的2～3倍左右。
优点：可读性比较强，压缩比高,不需要下载安装，自带工具。
缺点：备份时间相对较长,恢复时间比备份时间长。
分布式架构，数据量较大的时候，可以才用分布式备份，也可以 选择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
