前言
“刪庫跑路”是程序員經常談起的話題,今天,我就要教大家如何刪!庫!跑!路!
開個玩笑,今天文章的主題是如何使用Mysql內置的Binlog日誌對誤刪的數據進行恢復,讀完本文,你能夠了解到:
- MySQL的binlog日誌是什麼?通常是用來幹什麼的?
- 模擬一次誤刪數據的操作,並且使用binlog日誌恢復誤刪的數據。
寫這篇文章的初衷,是有一次我真的險些把測試數據庫的一張表給刪除了,當時嚇出一身冷汗。原因是由於Spring JPA的配置中,有一個spring.jpa.properties.hibernate.hbm2ddl.auto=create-drop,其用途是每次加載hibernate時根據model類生成表,但是sessionFactory一關閉,表就自動刪除。,這個可不能隨便配置上去,直接就把你原來存在的表給drop了!
好了,迴歸正題,這篇文章就是想讓大家放心,MySQL就算進行了誤刪操作,也基本都能夠搶救回來。尤其是大公司內,數據可不是你想刪就能刪掉的,有無數權限/備份阻攔著你。
正文
Binlog介紹
binlog是記錄所有數據庫表結構變更(例如CREATE、ALTER TABLE…)以及表數據修改(INSERT、UPDATE、DELETE…)的二進制日誌。
binlog不會記錄SELECT和SHOW這類操作,因為這類操作對數據本身並沒有修改,但你可以通過查詢通用日誌來查看MySQL執行過的所有語句。
看了上面binlog的定義,大家也應該能大致推理出binlog的三大用途:
- 恢復數據:今天要說的重點
- 數據庫複製:主從數據庫是通過將binlog傳給從庫,從庫有兩個線程,一個I/O線程,一個SQL線程,I/O線程讀取主庫傳過來的binlog內容並寫入到relay log,SQL線程從relay log裡面讀取內容,寫入從庫的數據庫。
- 審計:用戶可以通過二進制日誌中的信息來進行審計,判斷是否有對數據庫進行注入攻擊。
所以說,想要能夠恢復數據,首先,你得打開Mysql的binlog,在平常你自己安裝的單機Mysql中,默認情況下不會開啟。下面就一步步地實踐下如何開啟你服務器上的Binlog日誌。
在MySQL中開啟Binlog
首先進入數據庫控制檯,運行指令:
<code>mysql> show variables like'log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+-------+ 5 rows in set (0.00 sec) /<code>
可以看到我們的binlog是關閉的,都是OFF。接下來我們需要修改Mysql配置文件,執行命令:
<code>sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf /<code>
在文件末尾添加:
<code>log-bin=/var/lib/mysql/mysql-bin /<code>
保存文件,重啟mysql服務:
<code>sudo service mysql restart /<code>
重啟完成後,查看下mysql的狀態:
<code>systemctl status mysql.service /<code>
這時,如果你的mysql版本在5.7或更高版本,就會報錯:
<code>Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.190791Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.190839Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.359713Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (se Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.361395Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.28-0ubuntu0.16.04.2-log) starting as process 5930 ... Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.363017Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.363747Z 0 [ERROR] Aborting Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.363922Z 0 [Note] Binlog end Jan 06 15:49:58 VM-0-11-ubuntu mysqld[5930]: 2020-01-06T07:49:58.364108Z 0 [Note] /usr/sbin/mysqld: Shutdown complete Jan 06 15:49:58 VM-0-11-ubuntu systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE /<code>
You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server
之前我們的配置,對於5.7以下版本應該是可以的。但對於高版本,我們需要指定server-id。
如果你不是分佈式的部署Mysql,這個server-id隨機給個數字就可以。
<code>server-id=123454 /<code>
模擬刪除數據並恢復
- 首先新建數據庫mytest,新建一張表table1,結構見下方SQL代碼
<code>CREATE DATABASE `test` ; USE `test`; DROP TABLE IF EXISTS `table1`; CREATE TABLE `table2` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /<code>
- 插入兩條數據,分別是 (1,'A'),(2,'B')
<code>INSERT INTO `table1` VALUES (1,'A'),(2,'B'); /<code>
- 我們看一下binlog日誌的狀態,使用show master status
<code>mysql> show master status -> ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 690 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set /<code>
binlog日誌特徵:每當我們重啟MySQL一次,會自動生成一個binlog文件,當然,我們也可以手動的來刷新binlog文件,通過 flush logs,同樣會新創建一個binlog文件。實際上當服務器在重啟時,也會調用flush logs操作。
上圖代碼中可以看到,現在我們正在使用 mysql-bin.0000001 ,並且這個文件現在正在記錄到690行。
- 然後,使用flush logs來主動刷新一次binlog
<code>mysql> flush logs; Query OK, 0 rows affected mysql> show master status -> ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set /<code>
可以看到,現在日誌文件在 mysql-bin.000002 文件中,位置為154。也就是我們主動刷新了一次binlog,生成了新的000002,而000001則已經歸檔了,不會再寫入新的日誌進去了。
- 接下來我們在插入兩條數據
<code>insert into table1 values (3,'C'); insert into table1 values (4,'D'); /<code>
<code>mysql> select * from table1; +----+----+ | id |name| +----+----+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | +----+----+ /<code>
- 這時候我們已經有了四條數據,我們再次flush logs,把mysql-bin.000002日誌存檔,開啟新的mysql-bin.000003日誌,這樣,每次我們插入的數據彼此獨立。實際情況下,binlog會比較複雜,這裡也是做了簡化,為了理解更方便。
<code>mysql> flush logs; Query OK, 0 rows affected mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set /<code>
- 然後我們刪除id為4的數據(4,D),並且再次刷新binlog,如此一來,binlog.000003裡面只有一條刪除操作。
<code>mysql> delete from table1 where id = 4; Query OK, 1 row affected mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 423 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set mysql> flush logs; Query OK, 0 rows affected mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set /<code>
- 讓我們來好好觀察下mysql-bin.00002和mysql-bin00003兩個binlog,使用命令:show binlog events in 'mysql-bin.000003'
<code>mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 123456 | 123 | Server ver: 5.7.28-0ubuntu0.16.04.2-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 123456 | 154 | | | mysql-bin.000003 | 154 | Anonymous_Gtid | 123456 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 219 | Query | 123456 | 293 | BEGIN | | mysql-bin.000003 | 293 | Table_map | 123456 | 343 | table_id: 108 (test.table1) | | mysql-bin.000003 | 343 | Delete_rows | 123456 | 392 | table_id: 108 flags: STMT_END_F | | mysql-bin.000003 | 392 | Xid | 123456 | 423 | COMMIT /* xid=39 */ | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+ 7 rows in set mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 123456 | 123 | Server ver: 5.7.28-0ubuntu0.16.04.2-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 123456 | 154 | | | mysql-bin.000002 | 154 | Anonymous_Gtid | 123456 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 123456 | 293 | BEGIN | | mysql-bin.000002 | 293 | Table_map | 123456 | 343 | table_id: 108 (test.table1) | | mysql-bin.000002 | 343 | Write_rows | 123456 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 390 | Xid | 123456 | 421 | COMMIT /* xid=34 */ | | mysql-bin.000002 | 421 | Anonymous_Gtid | 123456 | 486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 486 | Query | 123456 | 560 | BEGIN | | mysql-bin.000002 | 560 | Table_map | 123456 | 610 | table_id: 108 (test.table1) | | mysql-bin.000002 | 610 | Write_rows | 123456 | 659 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 659 | Xid | 123456 | 690 | COMMIT /* xid=35 */ | | mysql-bin.000002 | 690 | Rotate | 123456 | 737 | mysql-bin.000003;pos=4 | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+ 13 rows in set /<code>
雖然有很多看似複雜的指令,但是還是不難看出,在02裡,有兩條寫操作,03裡有一條刪除操作。
一條插入操作的完整日誌是這樣:
<code>| mysql-bin.000002 | 154 | Anonymous_Gtid | 123456 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 123456 | 293 | BEGIN | | mysql-bin.000002 | 293 | Table_map | 123456 | 343 | table_id: 108 (test.table1) | | mysql-bin.000002 | 343 | Write_rows | 123456 | 390 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 390 | Xid | 123456 | 421 | COMMIT /* xid=34 */ | /<code>
- 我們的目的是恢復誤刪的數據,其實就是將binlog.000002日誌的兩條插入記錄重演一遍,而不需要取理會binlog.000003的操作(因為刪除是一個誤操作)
所以現在能理解為什麼我們頻繁刷新binlog了吧,當然,在實際的線上環境中,我們肯定需要將binlog導出後,仔細篩選出誤操作,並將其排除,之後再運行binlog。
在本文中,我們只做一個恢復兩條插入語句的操作,執行語句:
<code>sudo mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position 154 --stop-position 690 | mysql -uroot -p mytest /<code>
注意:這裡填寫的路徑/var/lib/mysql/mysql-bin.000002需要具體到你的binlog目錄,網上大部分文章只寫到mysql-bin.000002,如果你不在目錄裡,mysqlbinlog命令並不會自動定位binlog所在路徑。
參數描述:
<code>--start-datetime:從二進制日誌中讀取指定等於時間戳或者晚於本地計算機的時間 --stop-datetime:從二進制日誌中讀取指定小於時間戳或者等於本地計算機的時間 取值和上述一樣 --start-position:從二進制日誌中讀取指定position 事件位置作為開始。 --stop-position:從二進制日誌中讀取指定position 事件位置作為事件截至 /<code>
執行成功後,再次查看錶table1,可以看到兩條新的id=3和4的數據被插入了進來。恢復成功了。
<code>mysql> select * from table1; +----+----+ | id |name| +----+----+ | 1 | A | | 2 | B | | 3 | C | | 3 | C | | 4 | D | +----+----+ 6 rows in set /<code>
延伸思考
Binlog在什麼情況下無法恢復數據?
結語
刪庫跑路不用怕,其他開發運維都等著恢復你的數據呢,多好的練手機會是不是。
當然,看完binlog日誌恢復數據的原理,希望大家以後在定期備份數據庫的腳本里,也能夠加上刷新binlog日誌的命令,這樣一旦某天丟失數據,可以將當天binlog數據單獨拿出來還原,做到清晰可辨,也加快恢復效率。
參考
https://www.cnblogs.com/rjzheng/p/9721765.html
https://blog.csdn.net/king_kgh/article/details/74890381
https://www.jianshu.com/p/564fcc2b5e31
https://blog.csdn.net/king_kgh/article/details/74833539
關注我
我是一名後端開發工程師。
主要關注後端開發,數據安全,爬蟲,物聯網,邊緣計算等方向,歡迎交流。
各大平臺都可以找到我
- 微信公眾號:後端技術漫談
- Github:@qqxx6661
- CSDN:@後端技術漫談
- 知乎:@後端技術漫談
- 簡書:@後端技術漫談
- 掘金:@後端技術漫談
原創博客主要內容
- 後端開發相關技術文章
- Java面試知識點複習全手冊
- 設計模式/數據結構
- Leetcode/劍指offer 算法題解析
- SpringBoot/SpringCloud 入門實戰系列
- 爬蟲相關技術文章
- 逸聞趣事/好書分享/個人興趣
個人公眾號:後端技術漫談
公眾號:後端技術漫談.jpg
如果文章對你有幫助,不妨收藏,投幣,轉發,在看起來~