大家好,我是anyux。本文介绍MySQL基于GTID的数据恢复。
文末总结基于GTID的数据恢复,并有导图
创建一个数据库 gtid,在gtid库下创建表tmp,插入5行数据
create database back_gtid charset utf8mb4; use back_gtid; create table tmp(id int, name varchar(20)); insert into tmp values(1,'zs'),(2,'ls'),(3,'ww'),(4,'zl'),(5,'qb'); commit;
全备
mysqldump -uroot -proot -A -R -E --triggers --master-data=2 --single-transaction | gzip > /tmp/full_$(date +%F).sql.gz
修改数据
use back_gtid; update tmp set name = 'ZS' where id=1; commit; update tmp set name = 'LS' where id =2; commit; insert into tmp values(6,'aa'),(7,'bb'),(8,'cc'); commit; delete from tmp where id = 5; commit;
删除所有数据(误操作)
use back_gtid; delete from tmp; commit;
再插入数据(正常操作)
use back_gtid; insert into tmp values(9,"dd"),(10,"ee"); commit;
恢复过程
准备临时数据库(多实例)
systemctl restart mysqld3307
准备恢复全备
cd /tmp gunzip full_2020-03-15.sql.gz
截取二进制日志
找到全备结束时GTID号为141
过滤 GTID_PURGED,找到全备结束时的GTID号,查找删除库前的position号
grep -A 10 "GTID_PURGED" full_2020-03-15.sql
上面的1-141表示,全备的数据已经从1到141的gtid都包含了,使用二进制恢复备份要从142开始,跳过误删除部分,加上新插入的数据行
找到当前二进制备份文件
show master status;
从上图中Executed_Gtid_Set看到结尾的GTID号是147
对齐开头的142,对齐结尾的147
show binlog events in 'log-bin.000001';
每一次事务的提交,都对应一个GTID号,从模拟执行过程看,需要146就是删除表数据的元凶,恢复时要排除它
将二进制文件导出为sql文件
mysqlbinlog --skip-gtids --include-gtids="87031e16-4413-11ea-bb11-000c2922081d:142-147" --exclude-gtids="87031e16-4413-11ea-bb11-000c2922081d:146" /data/mysql/log-bin.000001 > /tmp/gtid-bin.sql;
对比文件,发现position号2123到2441都被跳过了,与show binlog events的对比,跳过的正是gtid为146的内容
恢复备份到临时库
mysql -uroot -proot -S /data/3307/mysql.sock
仅当前会话关闭二进制日志
set sql_log_bin=0; source /tmp/full_2020-03-15.sql; source /tmp/gtid-bin.sql;
查询验证
use back_gtid; select * from tmp;
将故障表导出并恢复到生产
mysqldump -uroot -proot -S /data/3307/mysql.sock back_gtid tmp > /tmp/gtid-tmp.sql mysql -uroot -proot set sql_log_bin=0; use back_gtid; use back_gtid; source /tmp/gtid-tmp.sql;
查询验证
use back_gtid; select * from tmp;
select * from tmp;欢迎在评论区一起讨论,质疑。文章都是手打原创,每天最浅显的介绍运维、数据库相关的技术,喜欢我的文章就关注一波吧,可以看到最新更新和之前的文章。