問題描述：建立測試庫和測試表，先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,'ls'),(3,'ww'),(4,'zl'),(5,'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 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;
4.刪除所有資料
use back_gtid; delete from tmp; commit;
5.再插入資料新的資料
use back_gtid; insert into tmp values(9,"dd"),(10,"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-02-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 'mysql-bin.000021'; +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | 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 | 8 | 154 | | | mysql-bin.000021 | 154 | Gtid | 8 | 219 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:1' | | mysql-bin.000021 | 219 | Query | 8 | 325 | create database test3308 | | mysql-bin.000021 | 325 | Gtid | 8 | 390 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:2' | | mysql-bin.000021 | 390 | Query | 8 | 497 | drop database back_gtid | | mysql-bin.000021 | 497 | Gtid | 8 | 562 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:3' | | mysql-bin.000021 | 562 | Query | 8 | 687 | create database back_gtid charset utf8mb4 | | mysql-bin.000021 | 687 | Gtid | 8 | 752 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:4' | | mysql-bin.000021 | 752 | Query | 8 | 878 | use `back_gtid`; create table tmp(id int, name varchar(20)) | | mysql-bin.000021 | 878 | Gtid | 8 | 943 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:5' | | mysql-bin.000021 | 943 | Query | 8 | 1020 | BEGIN | | mysql-bin.000021 | 1020 | Table_map | 8 | 1074 | table_id: 109 (back_gtid.tmp) | | mysql-bin.000021 | 1074 | Write_rows | 8 | 1149 | table_id: 109 flags: STMT_END_F | | mysql-bin.000021 | 1149 | Xid | 8 | 1180 | COMMIT /* xid=21 */ | | mysql-bin.000021 | 1180 | Gtid | 8 | 1245 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:6' | | mysql-bin.000021 | 1245 | Query | 8 | 1322 | BEGIN | | mysql-bin.000021 | 1322 | Table_map | 8 | 1376 | table_id: 111 (back_gtid.tmp) | | mysql-bin.000021 | 1376 | Update_rows | 8 | 1428 | table_id: 111 flags: STMT_END_F | | mysql-bin.000021 | 1428 | Xid | 8 | 1459 | COMMIT /* xid=650 */ | | mysql-bin.000021 | 1459 | Gtid | 8 | 1524 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:7' | | mysql-bin.000021 | 1524 | Query | 8 | 1601 | BEGIN | | mysql-bin.000021 | 1601 | Table_map | 8 | 1655 | table_id: 111 (back_gtid.tmp) | | mysql-bin.000021 | 1655 | Update_rows | 8 | 1707 | table_id: 111 flags: STMT_END_F | | mysql-bin.000021 | 1707 | Xid | 8 | 1738 | COMMIT /* xid=652 */ | | mysql-bin.000021 | 1738 | Gtid | 8 | 1803 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:8' | | mysql-bin.000021 | 1803 | Query | 8 | 1880 | BEGIN | | mysql-bin.000021 | 1880 | Table_map | 8 | 1934 | table_id: 111 (back_gtid.tmp) | | mysql-bin.000021 | 1934 | Write_rows | 8 | 1993 | table_id: 111 flags: STMT_END_F | | mysql-bin.000021 | 1993 | Xid | 8 | 2024 | COMMIT /* xid=654 */ | | mysql-bin.000021 | 2024 | Gtid | 8 | 2089 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:9' | | mysql-bin.000021 | 2089 | Query | 8 | 2166 | BEGIN | | mysql-bin.000021 | 2166 | Table_map | 8 | 2220 | table_id: 111 (back_gtid.tmp) | | mysql-bin.000021 | 2220 | Delete_rows | 8 | 2263 | table_id: 111 flags: STMT_END_F | | mysql-bin.000021 | 2263 | Xid | 8 | 2294 | COMMIT /* xid=656 */ | | mysql-bin.000021 | 2294 | Gtid | 8 | 2359 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:10' | | mysql-bin.000021 | 2359 | Query | 8 | 2436 | BEGIN | | mysql-bin.000021 | 2436 | Table_map | 8 | 2490 | table_id: 111 (back_gtid.tmp) | | mysql-bin.000021 | 2490 | Delete_rows | 8 | 2581 | table_id: 111 flags: STMT_END_F | | mysql-bin.000021 | 2581 | Xid | 8 | 2612 | COMMIT /* xid=661 */ | | mysql-bin.000021 | 2612 | Gtid | 8 | 2677 | SET @@SESSION.GTID_NEXT= 'b7ccf235-5f7b-11eb-a983-000c29a61c0a:11' | | mysql-bin.000021 | 2677 | Query | 8 | 2754 | BEGIN | | mysql-bin.000021 | 2754 | Table_map | 8 | 2808 | table_id: 111 (back_gtid.tmp) | | mysql-bin.000021 | 2808 | Write_rows | 8 | 2859 | table_id: 111 flags: STMT_END_F | | mysql-bin.000021 | 2859 | Xid | 8 | 2890 | COMMIT /* xid=666 */ | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ 45 rows in set (0.00 sec)
11.將二進位制檔案匯出為sql檔案，從6-11也就是現在位置，但是需要排除恢復誤刪除的區間內的10
mysqlbinlog --skip-gtids --include-gtids="b7ccf235-5f7b-11eb-a983-000c29a61c0a:6-11" --exclude-gtids="b7ccf235-5f7b-11eb-a983-000c29a61c0a:10" /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.sock set sql_log_bin=0; use back_gtid; source /tmp/gtid-tmp.sql;
15.查詢驗證3308是否恢復
use back_gtid; select * from tmp; set sql_log_bin=1;