模拟原始数据
create database oldboy charset utf8mb4;
use oldboy;
create table oldguo (id int);
insert into oldguo values(1),(2),(3);
commit;
周一晚上全库备份
mysqldump -uroot -p -A --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers >/data/backup/full.sql
模拟周二白天的数据变化
use oldboy ;
insert into oldguo values(11),(22),(33);
commit;
create table oldli(id int);
insert into oldli values(1),(2),(3);
commit;
insert into oldguo values(111),(222),(333);
commit;
模拟周二下午2点,误删除oldguo表
drop table oldguo;
3.4.4 恢复过程
1. 处理全备
[root@db01 ~]# sed -n '/CREATE TABLE `oldguo` /,/\;/p' /data/backup/full.sql >/data/backup/create.sql
[root@db01 ~]# grep -i 'INSERT INTO `oldguo`' /data/backup/full.sql >/data/backup/insert.sql
2. binlog 的截取
范围:
起点: 通过备份。
vim /data/backup/full.sql
SET @@GLOBAL.GTID_PURGED=‘f4112c3c-6bdf-11ea-bceb-000c29f18f14:1-47’;
终点: 通过
[root@db01 ~]# mysql -uroot -p123456 -e "show binlog events in 'mysql-bin.000003'" |grep -B 1 'DROP TABLE\ `oldguo`'
mysqlbinlog --include-gtids='f4112c3c-6bdf-11ea-bceb-000c29f18f14:48-51' /data/mysql/log/binlog/mysql-bin.000003|grep -B 18 '`oldboy`.`oldguo`'|grep 'GTID_NEXT'
注意:-B 后面有的是18,有的是8,需要自己进去数一下
截取:
方法1:
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='f4112c3c-6bdf-11ea-bceb-000c29f18f14:48-51 --exclude-gtids='f4112c3c-6bdf-11ea-bceb-000c29f18f14:49-50' /data/mysql/log/binlog/mysql-bin.000003 >/data/backup/bin1.sql
方法2:
mysqlbinlog --skip-gtids --include-gtids='f4112c3c-6bdf-11ea-bceb-000c29f18f14:48','f4112c3c-6bdf-11ea-bceb-000c29f18f14:51'/data/mysql/log/binlog/mysql-bin.000003 >/data/backup/bin1.sql
3. 恢复数据
use oldboy;
set sql_log_bin=0;
source /data/backup/create.sql
source /data/backup/insert.sql
commit;
source /data/backup/bin1.sql
set sql_log_bin=1;