MySQL 基於 Binlog 的數據恢復實戰

目錄

• 1、環境狀態說明

• 2、恢復流程說明

• 2.1 正向恢復

• 2.2 反向恢復

• 3、數據準備

• 3.1 查詢當前 binlog 數據狀態

• 3.2 查找恢復 position 區間

• 3.3 position 點確認

• 4、操作恢復

• 4.1 正向恢復

• 4.2 反向恢復

作者：AshesCat

1、環境狀態說明

操作系統：CentOS 7.7``MySQL版本：5.7.30，搭建主從 開啓binlog，binlog_format=row備份情況：每天00:00對數據庫進行全量備份 恢復原因：某日22:00左右，執行了批量update語句，需要回滾

2、恢復流程說明

按照正反兩種方式分別進行測試恢復

2.1 正向恢復

主要思路

• 通過全量備份恢復當日0:00時間點的數據

• 通過binlog恢復當日0:00-22:00錯誤語句之前的全部語句

2.2 反向恢復

主要思路

• 使用binlog2sql從binlog日誌中提取錯誤語句的反向語句

• 在當前已經執行了錯誤語句的數據庫執行反向語句，將數據恢復至錯誤語句執行前的狀態

3、數據準備

3.1 查詢當前 binlog 數據狀態

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

通過以上查詢可以得知，當前使用的 binlog 日誌文件爲mysql-bin.000027，最後更新時間爲9月14日0:42，上一個binlog文件mysql-bin.000026是9月9日12:07左右創建，最後更新時間爲9月13日22:01。可以確定出錯update語句應該按照時間應該記錄在26的結尾和27開始的位置

3.2 查找恢復 position 區間

正向恢復開始的position點，會記錄在每天0點備份的備份文件內。

[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;

結束的position點，應該選取錯誤update語句執行前的一個事務結束點。按照大概時間截取2021-09-13 21:55:00-2021-09-13 22:05的26和27兩個文件的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事務記錄

BEGIN                 # 事務開始
/*!*/;
# at 918610296        # 事務開始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 '              # 具體執行的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/*!*/;          # 事務結束 結束position 918611683

經過確認，在22:00:53時，有一個大事務執行，基本可以確定是這個事務，事務開始position是918611748，結束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 點確認

當日當天0:00到執行錯誤語句前的position區間爲890333336 - 918611748，正向恢復使用 當日執行的錯誤語句事務的position區間爲918611748 - 1094225331，反向恢復使用

4、操作恢復

4.1 正向恢復

4.1.1 恢復備份數據

將2021-09-13 00:00:00的全備數據恢復至數據庫。

[root@localhost ~]# mysql -uroot -p < service_platform_2021-09-13.sql
Enter password:

查看當前數據狀態，此字段的new_id在當日日當天被修改。

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 恢復 binlog 日誌

截取當日當天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 檢查數據恢復狀態

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)

至此，正向恢復數據完成

4.2 反向恢復

反向恢復時通過binlog2sql，將錯誤執行的update語句反轉，再update回來

4.2.1 安裝 binlog2sql

項目地址：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 生成反轉 sql

根據錯誤語句的position區間918611748 - 1094225331，生成反向語句。注意：此語句必須在原庫執行，數據庫必須是當前錯誤狀態，且數據庫執行此錯誤語句後這些數據未發生其它更改，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

參數說明

-h                      # 主機ip
-P                      # 端口號，這裏的P是大寫
-u                      # 用戶名爲root
-p                      # 密碼
-d                      # 指定過濾數據庫（可忽略）
-t                      # 指定過濾表（可忽略）
–-start-file            # 開始binlog文件
--stop-file             # 結束binlog文件，可忽略，忽略默認爲start-file
-B                      # 生成反向sql，不加是生成正向sql
--start-position        # 開始position位置點
--stop-position         # 結束position位置點（可忽略，忽略默認爲binlog文件末尾）

4.2.3 解決 sql 虛擬字段問題（如果有）

binlog內存儲的update語句是針對所有列操作的，如果其中存在虛擬列，則需要先進行刪除。可通過vim或者sed對文件進行操作，將虛擬列相關數據變更爲空值。相關報錯如下：

ERROR 3105 (HY000): The value specified for generated column 'student_kind' in table 'student' is not allowed.

具體需替換內容聯繫開發索取

[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 檢查數據恢復狀態

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)

至此，反向恢復數據完成

See you ~

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/WoZP4KXTja1jKHSx7dbvBQ