问题描述：创建测试库和测试表，先update数据，在delete数据，在update数据，通过gtID查找两次update的值。
参考文档：https://baijiahao.baidu.com/s?id=1661214737415657389&wfr=spider&for=pc
1.创建测试数据
create database back_gtID charset utf8mb4;use back_gtID; create table tmp(ID int,name varchar(20));insert into tmp values(1,'zs'),(2,1)">ls3,1)">ww4,1)">zl5,1)">qb');commit;
2.全库导出
MysqLdump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction -S /data/3308/MysqL.sock | gzip > /tmp/full_$(date +%F).sql.gz
3.修改数据
use back_gtID; update tmp set name = ZS' where ID=1; commit; update tmp LSwhere ID =2; commit; insert into tmp values(6,1)">aa7,1)">bb8,1)">cc); commit; delete from tmp where ID = 5; commit;
4.删除所有数据
use back_gtID; delete from tmp; commit;
5.再插入数据新的数据
use back_gtID; insert into tmp values(9,1)">"dd"),1)">10,1)">ee"); commit;
6.准备多实例测试库3309，做中转库做数据测试
systemctl start MysqLd3309.service
7.全库恢复
cd /tmp gunzip full_2021-02-01.sql.gz
8.从备份中找到创建库时的GTID，跳过误删除部分，加上新插入的数据行,查找删除库前的position号，从6开始
[root@MysqL-test /tmp 20:00:05]# grep -A 10 GTID_PURGED" full_2021-01.sql SET @@GLOBAL.GTID_PURGED=b7ccf235-5f7b-11eb-a983-000c29a61c0a:1-5;---- position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_file=MysqL-bin.000021',MASTER_LOG_POS=1180;---- Current Database: `back_gtID`--
9.查看当前的gtID值，现在的位置号是11，说明从备份数据到现在为止的 *** 作是6-11，现在要在6-11的区间内排除误删数据的 *** 作
MysqL> show master status;+------------------+----------+--------------+------------------+-------------------------------------------+| file | position | binlog_Do_DB | binlog_Ignore_DB | Executed_GtID_Set |+------------------+----------+--------------+------------------+-------------------------------------------+| MysqL-bin.000021 | 2890 | | | b7ccf235-5f7b-11eb-a983-000c29a61c0a:1-11 |+------------------+----------+--------------+------------------+-------------------------------------------+1 row in set (0.00 sec)
10.每一次事务的提交，都对应一个GTID号，从模拟执行过程看，需要10就是删除表数据的元凶，恢复时要排除它
从下面的events中可以看到，delete *** 作的位置号是10，
MysqL> show binlog events in ;+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_ID | End_log_pos | Info |+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+| MysqL-bin.000021 | 4 | Format_desc | 8 | 123 | Server ver: 5.7.32-log,binlog ver: 4 || MysqL-bin.000021 | 123 | PrevIoUs_gtIDs | 154 | || MysqL-bin.154 | GtID | 219 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:1' || MysqL-bin.219 | query | 325 | create database test3308 || MysqL-bin.325 | GtID | 390 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:2390 | query | 497 | drop database back_gtID || MysqL-bin.497 | GtID | 562 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:3562 | query | 687 | create database back_gtID charset utf8mb4 || MysqL-bin.687 | GtID | 752 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:4752 | query | 878 | use `back_gtID`; create table tmp(ID 20)) || MysqL-bin.878 | GtID | 943 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:5943 | query | 8 | 1020 | BEGIN || MysqL-bin.000021 | 1020 | table_map | 1074 | table_ID: 109 (back_gtID.tmp) || MysqL-bin.1074 | Write_rows | 1149 | table_ID: 109 flags: STMT_END_F || MysqL-bin.1149 | XID | 1180 | COMMIT /* xID=21 */ || MysqL-bin.1180 | GtID | 1245 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:61245 | query | 1322 | BEGIN || MysqL-bin.1322 | table_map | 1376 | table_ID: 111 (back_gtID.tmp) || MysqL-bin.1376 | Update_rows | 1428 | table_ID: 111 flags: STMT_END_F || MysqL-bin.1428 | XID | 1459 | COMMIT xID=650 */ || MysqL-bin.1459 | GtID | 1524 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:71524 | query | 1601 | BEGIN || MysqL-bin.1601 | table_map | 1655 | table_ID: 1655 | Update_rows | 1707 | table_ID: 1707 | XID | 1738 | COMMIT xID=652 1738 | GtID | 1803 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:81803 | query | 1880 | BEGIN || MysqL-bin.1880 | table_map | 1934 | table_ID: 1934 | Write_rows | 1993 | table_ID: 1993 | XID | 2024 | COMMIT xID=654 2024 | GtID | 2089 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:92089 | query | 2166 | BEGIN || MysqL-bin.2166 | table_map | 2220 | table_ID: 2220 | Delete_rows | 2263 | table_ID: 2263 | XID | 2294 | COMMIT xID=656 2294 | GtID | 2359 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:10' || MysqL-bin.2359 | query | 2436 | BEGIN || MysqL-bin.2436 | table_map | 2490 | table_ID: 2490 | 2581 | table_ID: 2581 | XID | 2612 | COMMIT xID=661 2612 | GtID | 2677 | SET @@SESSION.GTID_NEXT= b7ccf235-5f7b-11eb-a983-000c29a61c0a:112677 | query | 2754 | BEGIN || MysqL-bin.2754 | table_map | 2808 | table_ID: 2808 | Write_rows | 2859 | table_ID: 2859 | XID | 2890 | COMMIT xID=666 */ |+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+45 rows 0.00 sec)
11.将二进制文件导出为sql文件，从6-11也就是现在位置，但是需要排除恢复误删除的区间内的10
MysqLbinlog --skip-gtIDs --include-gtIDs=b7ccf235-5f7b-11eb-a983-000c29a61c0a:6-11" --exclude-gtIDs=" /data/3308/MysqL-bin.000021 > /tmp/gtID-bin.sql;
12.将备份恢复到临时库，先恢复之前备份的数据，在恢复提取出来的数据是否正确
MysqL -uroot -p -S /data/3309/MysqL.sock set sql_log_bin=0;
source /tmp/full_2021-02-01.sql;
source /tmp/gtID-bin.sql;
13.查询验证
use back_gtID; select * from tmp;
14.没问题可以恢复到生产
MysqLdump -uroot -p --set-gtID-purged=OFF -S /data/3309/MysqL.sock back_gtID tmp > /tmp/gtID-tmp.sql
MysqL -uroot -p -S /data/3308/MysqL.sockset sql_log_bin=0; use back_gtID; source /tmp/gtID-tmp.sql;
15.查询验证3308是否恢复
use back_gtID; tmp;1;
总结
以上是内存溢出为你收集整理的基于GTID恢复误篡改数据全部内容，希望文章能够帮你解决基于GTID恢复误篡改数据所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）