MySQL基于Binlog的數(shù)據(jù)恢復(fù)實(shí)戰(zhàn)
目錄
1、環(huán)境狀態(tài)說明
2、恢復(fù)流程說明
2.1 正向恢復(fù)
2.2 反向恢復(fù)
3、數(shù)據(jù)準(zhǔn)備
3.1 查詢當(dāng)前 binlog 數(shù)據(jù)狀態(tài)
3.2 查找恢復(fù) position 區(qū)間
3.3 position 點(diǎn)確認(rèn)
4、操作恢復(fù)
4.1 正向恢復(fù)
4.2 反向恢復(fù)
作者：AshesCat
1、環(huán)境狀態(tài)說明
操作系統(tǒng)：CentOS 7.7MySQL版本：5.7.30，搭建主從
開啟binlog，binlog_format=row備份情況：每天00:00對(duì)數(shù)據(jù)庫進(jìn)行全量備份
恢復(fù)原因：某日22:00左右，執(zhí)行了批量update語句，需要回滾
2、恢復(fù)流程說明
按照正反兩種方式分別進(jìn)行測試恢復(fù)
2.1 正向恢復(fù)
主要思路
通過全量備份恢復(fù)當(dāng)日 0:00時(shí)間點(diǎn)的數(shù)據(jù)通過 binlog恢復(fù)當(dāng)日0:00-22:00錯(cuò)誤語句之前的全部語句
2.2 反向恢復(fù)
主要思路
使用 binlog2sql從binlog日志中提取錯(cuò)誤語句的反向語句在當(dāng)前已經(jīng)執(zhí)行了錯(cuò)誤語句的數(shù)據(jù)庫執(zhí)行反向語句，將數(shù)據(jù)恢復(fù)至錯(cuò)誤語句執(zhí)行前的狀態(tài)
3、數(shù)據(jù)準(zhǔn)備
3.1 查詢當(dāng)前 binlog 數(shù)據(jù)狀態(tài)
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000027 | 851010973 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
[root@mysql-01 ~]# ll /data/mysql5730/mysql-bin.*
-rw-r----- 1 mysql mysql 1073741880 9月 6 03:02 /data/mysql5730/mysql-bin.000023
-rw-r----- 1 mysql mysql 1073900114 9月 8 16:31 /data/mysql5730/mysql-bin.000024
-rw-r----- 1 mysql mysql 1074333543 9月 9 12:07 /data/mysql5730/mysql-bin.000025
-rw-r----- 1 mysql mysql 1094225409 9月 13 22:01 /data/mysql5730/mysql-bin.000026
-rw-r----- 1 mysql mysql 690570884 9月 14 00:42 /data/mysql5730/mysql-bin.000027
通過以上查詢可以得知，當(dāng)前使用的 binlog 日志文件為mysql-bin.000027，最后更新時(shí)間為9月14日0:42，上一個(gè)binlog文件mysql-bin.000026是9月9日12:07左右創(chuàng)建，最后更新時(shí)間為9月13日22:01。可以確定出錯(cuò)update語句應(yīng)該按照時(shí)間應(yīng)該記錄在26的結(jié)尾和27開始的位置
3.2 查找恢復(fù) position 區(qū)間
正向恢復(fù)開始的position點(diǎn)，會(huì)記錄在每天0點(diǎn)備份的備份文件內(nèi)。
[root@mysql-01 ~]# grep "CHANGE MASTER" /tmp/service_platform_2021-09-13.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=890333336;
結(jié)束的position點(diǎn)，應(yīng)該選取錯(cuò)誤update語句執(zhí)行前的一個(gè)事務(wù)結(jié)束點(diǎn)。按照大概時(shí)間截取2021-09-13 21:55:00-2021-09-13 22:05的26和27兩個(gè)文件的binlog日志
[root@mysql-01 ~]# mysqlbinlog --start-datetime='2021-09-13 21:55:00' --stop-datetime='2021-09-13 22:05' /data/mysql5730/mysql-bin.000026 > /tmp/bin26.sq
[root@mysql-01 ~]# mysqlbinlog --start-datetime='2021-09-13 21:55:00' --stop-datetime='2021-09-13 22:05' /data/mysql5730/mysql-bin.000027 > /tmp/bin27.sql
完整一條binlog事務(wù)記錄
BEGIN # 事務(wù)開始
/*!*/;
# at 918610296 # 事務(wù)開始position
#210913 22:01:05 server id 120 end_log_pos 918610654 CRC32 0x1488200a Table_map: `service_platform`.`student` mapped to number 24404
# at 918610654
#210913 22:01:05 server id 120 end_log_pos 918611652 CRC32 0xf4a9a8c3 Update_rows: table id 24404 flags: STMT_END_F
BINLOG ' # 具體執(zhí)行的sql（base64編碼）
oQ/GYBN4AAAAZgEAAN7iwDYAAFRfAAAAAAEAGWVudHJhbmNlX3NlcnZpY2VfcGxhdGZvcm0AB3N0
dWRlbnQAogMDAwMPDwMPDwMPAwMIAw8DAw8DAwMDAwMPDw8IDwMDDwMPDw8DAw8DDw8PAwMIDwMD
AwMPDwMICAMDAwMDAwQDAwgDAwMDDw8DDwMPDwMDDwMPAwMPAwMPDw8PAwMPAwMDDw8DAwMDAw8P
DwMDAwMDAwMDAw8PDw8PDwMDAwMDDwMPAwMDAwMDAwMPCA8DAwMDAwMDAwMDAwMDDwMDDxEDA2qA
AEAAAAEAAYAAGAAABAAgAARAAIAAgAAABAABgACAAAAEAAFAAEAAAAQABAQABAABgAAAAgAEgAAA
BAAEAAGQAQABAAEoAAABAAEoAAACQAAAAQABgAAAAgABAAEgAEAAQAAAAUAAQAAA/v//////////
//////////////8DCiCIFA==
oQ/GYB94AAAA5gMAAMTmwDYAAFRfAAAAAAEAAgCi////////////////////////////////////
////////////////////BgEBHwBoif//3v3z//////5//jX/3VsIAOqlAQAIMjE5OTI0MjAGNDgw
NDYxAAAAAAkA6YOt5qCp5a2cQR99ABIxMTAxMTYyMDA5MDUxMjE4MjYqWIkAG1yJAIBLCEoAAAAA
2o6JAAMxNTYkrgEAKgDmgIDmn5TljLrov47lrr7ljJfot681NeWPt+alvDLljZXlhYM2MDHlrqQk
rgEAJK4BAKKOAQAkngEAJK4BAAnliJjpgrHlrZBaAS4AQR99ABIxMTAyMjcxOTg3MDUxNjA2MjK4
........................................MzQwNzE2MAAAAAAIMjE5OTI0MjCa3AEAAAAA
ACoA5oCA5p+U5Yy66L+O5a6+5YyX6LevNTXlj7fmpbwy5Y2V5YWDNjAx5a6kFQDvvKcxMTAxMTYy
MDA5MDUxMjE4MjaC4AEABgA5NTgxMzAGADEzMDAwMAAAAAADADE1NgMAMTU2BuWksei0pQIAAAAB
AAAAJK4BAAbkuqznsY0G5Lqs57GNYMYPocOoqfQ=
'/*!*/;
# at 918611652
#210913 22:01:05 server id 120 end_log_pos 918611683 CRC32 0xda6fdc6e Xid = 154913465
COMMIT/*!*/; # 事務(wù)結(jié)束 結(jié)束position 918611683
經(jīng)過確認(rèn)，在22:00:53時(shí)，有一個(gè)大事務(wù)執(zhí)行，基本可以確定是這個(gè)事務(wù)，事務(wù)開始position是918611748，結(jié)束position是1094225331。
# at 918611748
#210913 22:00:53 server id 120 end_log_pos 918611841 CRC32 0xa4f84b66 Query thread_id=419432 exec_time=0 error_code=0
SET TIMESTAMP=1623592853/*!*/;
BEGIN
/*!*/;
# at 918611841
#210913 22:00:53 server id 120 end_log_pos 918612199 CRC32 0xc4e1a859 Table_map: `service_platform`.`student` mapped to number 24404
# at 918612199
#210913 22:00:53 server id 120 end_log_pos 918619761 CRC32 0xdea1bb51 Update_rows: table id 24404
# at 918619761
#210913 22:00:53 server id 120 end_log_pos 918627679 CRC32 0xad1ba65d Update_rows: table id 24404
# at 918627679
#210913 22:00:53 server id 120 end_log_pos 918635509 CRC32 0xf55f5800 Update_rows: table id 24404
# at 918635509
#210913 22:00:53 server id 120 end_log_pos 918643369 CRC32 0x484ffffc Update_rows: table id 24404
# at 918643369
#210913 22:00:53 server id 120 end_log_pos 918651107 CRC32 0xab13fd26 Update_rows: table id 24404
# at 918651107
#210913 22:00:53 server id 120 end_log_pos 918658547 CRC32 0xbc4b489c Update_rows: table id 24404
.....
'/*!*/;
# at 1094225331
#210913 22:00:53 server id 120 end_log_pos 1094225362 CRC32 0x175cc5e4 Xid = 154913333
COMMIT/*!*/;
3.3 position 點(diǎn)確認(rèn)
當(dāng)日當(dāng)天0:00到執(zhí)行錯(cuò)誤語句前的position區(qū)間為890333336 - 918611748，正向恢復(fù)使用
當(dāng)日?qǐng)?zhí)行的錯(cuò)誤語句事務(wù)的position區(qū)間為918611748 - 1094225331，反向恢復(fù)使用
4、操作恢復(fù)
4.1 正向恢復(fù)
4.1.1 恢復(fù)備份數(shù)據(jù)
將2021-09-13 00:00:00的全備數(shù)據(jù)恢復(fù)至數(shù)據(jù)庫。
[root@localhost ~]# mysql -uroot -p < service_platform_2021-09-13.sql
Enter password:
查看當(dāng)前數(shù)據(jù)狀態(tài)，此字段的new_id在當(dāng)日日當(dāng)天被修改。
mysql> use service_platform;
Database changed
mysql> select new_id, town_id from student where id = '15132600';
+-------------+---------+
| new_id | town_id |
+-------------+---------+
| 108 | 105 | # new_id = 108
+-------------+---------+
1 row in set (0.01 sec)
4.1.2 恢復(fù) binlog 日志
截取當(dāng)日當(dāng)天binlog日志
[root@localhost ~]# mysqlbinlog --start-position=890333336 --stop-position=918611748 mysql-bin.000026 > /tmp/a.sql
[root@localhost ~]# ll -h /tmp/a.sql
-rw-r--r-- 1 root root 51M 9 15 15:48 /tmp/a.sql
重寫binlog日志
mysql> source /tmp/a.sql
Query OK, 0 rows affected (0.00 sec)
....
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
4.1.3 檢查數(shù)據(jù)恢復(fù)狀態(tài)
mysql> use service_platform;
Database changed
mysql> select new_id, town_id from student where id = '15132600';
+-------------+---------+
| new_id | town_id |
+-------------+---------+
| 105 | 105 |
+-------------+---------+
1 row in set (0.00 sec)
至此，正向恢復(fù)數(shù)據(jù)完成
4.2 反向恢復(fù)
反向恢復(fù)時(shí)通過binlog2sql，將錯(cuò)誤執(zhí)行的update語句反轉(zhuǎn)，再update回來
4.2.1 安裝 binlog2sql
項(xiàng)目地址：https://github.com/danfengcao/binlog2sql
# 安裝epel源
wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
# 安裝git pip
yum -y install git python-pip
# git下載binlog2sql
[root@localhost ~]# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
Cloning into 'binlog2sql'...
remote: Enumerating objects: 323, done.
remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323
Receiving objects: 100% (323/323), 153.26 KiB | 0 bytes/s, done.
Resolving deltas: 100% (170/170), done.
[root@localhost binlog2sql]# pip install -r requirements.txt
4.2.2 生成反轉(zhuǎn) sql
根據(jù)錯(cuò)誤語句的position區(qū)間918611748 - 1094225331，生成反向語句。注意：此語句必須在原庫執(zhí)行，數(shù)據(jù)庫必須是當(dāng)前錯(cuò)誤狀態(tài)，且數(shù)據(jù)庫執(zhí)行此錯(cuò)誤語句后這些數(shù)據(jù)未發(fā)生其它更改，binlog日志也可正常使用。
[root@localhost mysql]# python /root/binlog2sql/binlog2sql/binlog2sql.py -h 127.0.0.1 -u root -p -d service_platform -t student --start-file='mysql-bin.000026' --start-position=918611748 --stop-position=1094225331 -B >/tmp/b.sql
[root@localhost mysql]# ll -h /tmp/b.sql
-rw-r--r-- 1 root root 1.2G 9 15 16:31 /tmp/b.sql
參數(shù)說明
-h # 主機(jī)ip
-P # 端口號(hào)，這里的P是大寫
-u # 用戶名為root
-p # 密碼
-d # 指定過濾數(shù)據(jù)庫（可忽略）
-t # 指定過濾表（可忽略）
–-start-file # 開始binlog文件
--stop-file # 結(jié)束binlog文件，可忽略，忽略默認(rèn)為start-file
-B # 生成反向sql，不加是生成正向sql
--start-position # 開始position位置點(diǎn)
--stop-position # 結(jié)束position位置點(diǎn)（可忽略，忽略默認(rèn)為binlog文件末尾）
4.2.3 解決 sql 虛擬字段問題（如果有）
binlog內(nèi)存儲(chǔ)的update語句是針對(duì)所有列操作的，如果其中存在虛擬列，則需要先進(jìn)行刪除。可通過vim或者sed對(duì)文件進(jìn)行操作，將虛擬列相關(guān)數(shù)據(jù)變更為空值。相關(guān)報(bào)錯(cuò)如下：
ERROR 3105 (HY000): The value specified for generated column 'student_kind' in table 'student' is not allowed.
具體需替換內(nèi)容聯(lián)系開發(fā)索取
[root@localhost mysql]# sed -i s#`student_kind`='AAA',##g /tmp/b.sql
[root@localhost mysql]# sed -i s#`student_kind`='BBB',##g /tmp/b.sql
[root@localhost mysql]# sed -i s#`student_kind`='CCC',##g /tmp/b.sql
[root@localhost mysql]# sed -i s#`student_kind`='DDD',##g /tmp/b.sql
[root@localhost mysql]# sed -i s#`student_kind`='EEE',##g /tmp/b.sql
[root@localhost mysql]# sed -i s#`student_kind`='FFF',##g /tmp/b.sql
[root@localhost mysql]# sed -i s#\`new_id\`=\d\{6\},##g /tmp/b.sql
4.2.4 寫入反向 sql
mysql> source /tmp/b.sql
4.2.5 檢查數(shù)據(jù)恢復(fù)狀態(tài)
mysql> use service_platform;
Database changed
mysql> select new_id, town_id from student where id = '15132600';
+-------------+---------+
| new_id | town_id |
+-------------+---------+
| 105 | 105 |
+-------------+---------+
1 row in set (0.00 sec)
至此，反向恢復(fù)數(shù)據(jù)完成
See you ~
歡迎進(jìn)群一起進(jìn)行技術(shù)交流
加群方式：公眾號(hào)消息私信“加群”或加我好友再加群均可