mysql备份-恢复模拟演练--通过position定位切割日志

1 故障模拟演练
1.1准备数据

create database backup;
use backup;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*

1.2 周二 23：00全备

-A 全备份，-B 库备份  -R 存储过程和函数  -E 事件 --triggers 触发器 

--master-data=2     *****
(1) 记录备份时刻的binlog信息
(2) 自动锁表
    不加--single-transaction ,温备份
    加了--single-transaction,对于InnoDB表不锁表备份(快照备份)

--single-transaction *****
对于InnoDB的表,进行一致性快照备份,不锁表.

mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

1.3 模拟周二 23：00到周三 10点之间数据变化

use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;

1.4 模拟故障,删除表(只是模拟，不代表生产操作)

drop database backup;

2 恢复过程
2.1准备临时数据库（多实例3307）

systemctl start mysqld3307

2.2 准备备份
（1）准备全备：

cd /backup
gunzip full_2019-11-04.sql

（2）截取二进制日志

vim full_2019-11-04.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=3452;

mysql> show master status;
mysql>show binlog events in 'mysql-bin.000011';

4218

mysqlbinlog --skip-gtids   --start-position=3452 --stop-position=4218  /data/binlog/mysql-bin.000011 >/backup/bin.sql

2.3恢复备份到临时库

set sql_log_bin=0; 不记录binlog

mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2019-11-04.sql
source /backup/bin.sql

2.4 将故障表导出并恢复到生产

mysqldump   -S /data/3307/mysql.sock -B  backup  >/backup/bak.sql
mysql -uroot -p123 
set sql_log_bin=0
source /backup/bak.sql;

扩展参数  ***
在构建主从时,使用AUTO/ON
--set-gtid-purged=AUTO/ON

仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF  

SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';

--max_allowed_packet=128M  控制的是备份时传输数据包的大小.

mysqldump -uroot -p123 -A  -R  --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz