歡迎您光臨本站 註冊首頁

MySQL使用mysqldump+binlog完整恢復被刪除的數據庫原理解析

←手機掃碼閱讀     火星人 @ 2020-04-27 , reply:0

(一)概述

在日常MySQL數據庫運維過程中,可能會遇到用戶誤刪除數據,常見的誤刪除數據操作有:

用戶執行delete,因為條件不對,刪除了不應該刪除的數據(DML操作);

用戶執行update,因為條件不對,更新數據出錯(DML操作);

用戶誤刪除表drop table(DDL操作);

用戶誤清空表truncate(DDL操作);

用戶刪除數據庫drop database,跑路(DDL操作)

…等

這些情況雖然不會經常遇到,但是遇到了,我們需要有能力將其恢復,下面講述如何恢復。

(二)恢復原理

如果要將數據庫恢復到故障點之前,那麼需要有數據庫全備和全備之後產生的所有二進制日誌。

全備作用 :使用全備將數據庫恢復到上一次完整備份的位置;

二進制日誌作用:利用全備的備份集將數據庫恢復到上一次完整備份的位置之後,需要對上一次全備之後數據庫產生的所有動作進行重做,而重做的過程就是解析二進制日誌文件為SQL語句,然後放到數據庫裡面再次執行。

舉個例子:小明在4月1日晚上8:00使用了mysqldump對數據庫進行了備份,在4月2日早上12:00的時候,小華不小心刪除了數據庫,那麼,在執行數據庫恢復的時候,需要使用4月1日晚上的完整備份將數據庫恢復到“4月1日晚上8:00”,那4月1日晚上8:00以後到4月2日早上12:00之前的數據如何恢復呢?就得通過解析二進制日誌來對這段時間執行過的SQL進行重做。

(三)刪庫恢復測試

(3.1)實驗目的

在本次實驗中,我直接測試刪庫,執行drop database lijiamandb,確認是否可以恢復。

(3.2)測試過程

在測試數據庫lijiamandb中創建測試表test01和test02,然後執行mysqldump對數據庫進行全備,之後執行drop database,確認database是否可以恢復。

STEP1:創建測試數據,為了模擬日常繁忙的生產環境,頻繁的操作數據庫產生大量二進制日誌,我特地使用存儲過程和EVENT產生大量數據。

創建測試表:

use lijiamandb;create table test01 ( id1 int not null auto_increment, name varchar(30), primary key(id1) ); create table test02 ( id2 int not null auto_increment, name varchar(30), primary key(id2) );

創建存儲過程,往測試表裡面插入數據,每次執行該存儲過程,往test01和test02各自插入10000條數據:

CREATE DEFINER=`root`@`%` PROCEDURE `p_insert`() BEGIN #Routine body goes here... DECLARE str1 varchar(30); DECLARE str2 varchar(30); DECLARE i int; set i = 0; while i < 10000 do set str1 = substring(md5(rand()),1,25); insert into test01(name) values(str1); set str2 = substring(md5(rand()),1,25); insert into test02(name) values(str1); set i = i + 1; end while; END

制定事件,每隔10秒鐘,執行上面的存儲過程:

use lijiamandb; create event if not exists e_insert on schedule every 10 second on completion preserve do call p_insert();

啟動EVENT,每個10s自動向test01和test02各自插入10000條數據

mysql> show variables like '%event_scheduler%'; +----------------------------------------------------------+-------+ | Variable_name | Value | +----------------------------------------------------------+-------+ | event_scheduler | OFF | +----------------------------------------------------------+-------+ mysql> set global event_scheduler = on; Query OK, 0 rows affected (0.08 sec)

--過3分鐘。。。

STEP2:第一步生成大量測試數據後,使用mysqldump對lijiamandb數據庫執行完全備份

mysqldump -h192.168.10.11 -uroot -p123456 -P3306 --single-transaction --master-data=2 --events --routines --databases lijiamandb > /mysql/backup/lijiamandb.sql

注意:必須要添加--master-data=2,這樣才會備份集裡面mysqldump備份的終點位置。

--過3分鐘。。。

STEP3:為了便於數據庫刪除前與刪除後數據一致性校驗,先停止表的數據插入,此時test01和test02都有930000行數據,我們後續恢復也要保證有930000行數據。

mysql> set global event_scheduler = off; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from test01; +----------+ | count(*) | +----------+ | 930000 | +----------+ row in set (0.14 sec) mysql> select count(*) from test02; +----------+ | count(*) | +----------+ | 930000 | +----------+ row in set (0.13 sec)

STEP4:刪除數據庫

mysql> drop database lijiamandb; Query OK, 2 rows affected (0.07 sec)

STEP5:使用mysqldump的全備導入

mysql> create database lijiamandb; Query OK, 1 row affected (0.01 sec) mysql> exit Bye [[email protected] binlog]# mysql -uroot -p123456 lijiamandb < /mysql/backup/lijiamandb.sql mysql: [Warning] Using a password on the command line interface can be insecure.

在執行全量備份恢復之後,發現只有753238筆數據:

[[email protected] binlog]# mysql -uroot -p123456 lijiamandb mysql> select count(*) from test01; +----------+ | count(*) | +----------+ | 753238 | +----------+ row in set (0.12 sec) mysql> select count(*) from test02; +----------+ | count(*) | +----------+ | 753238 | +----------+ row in set (0.11 sec)

很明顯,全量導入之後,數據不完整,接下來使用mysqlbinlog對二進制日誌執行增量恢復。

使用mysqlbinlog進行增量日誌恢復最重要的就是確定待恢復的起始位置(start-position)和終止位置(stop-position),起始位置(start-position)是我們執行全被之後的位置,而終止位置則是故障發生之前的位置。

STEP6:確認mysqldump備份到的最終位置

[[email protected] backup]# cat lijiamandb.sql |grep "CHANGE MASTER" -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000044', MASTER_LOG_POS=8526828

備份到了44號日誌的8526828位置,那麼恢復的起點可以設置為:44號日誌的8526828。

--接下來確認要恢復的終點位置,即執行"DROP DATABASE LIJIAMAN"之前的位置,需要到binlog裡面確認。

[[email protected] binlog]# ls master-bin.000001 master-bin.000010 master-bin.000019 master-bin.000028 master-bin.000037 master-bin.000046 master-bin.000055 master-bin.000002 master-bin.000011 master-bin.000020 master-bin.000029 master-bin.000038 master-bin.000047 master-bin.000056 master-bin.000003 master-bin.000012 master-bin.000021 master-bin.000030 master-bin.000039 master-bin.000048 master-bin.000057 master-bin.000004 master-bin.000013 master-bin.000022 master-bin.000031 master-bin.000040 master-bin.000049 master-bin.000058 master-bin.000005 master-bin.000014 master-bin.000023 master-bin.000032 master-bin.000041 master-bin.000050 master-bin.000059 master-bin.000006 master-bin.000015 master-bin.000024 master-bin.000033 master-bin.000042 master-bin.000051 master-bin.index master-bin.000007 master-bin.000016 master-bin.000025 master-bin.000034 master-bin.000043 master-bin.000052 master-bin.000008 master-bin.000017 master-bin.000026 master-bin.000035 master-bin.000044 master-bin.000053 master-bin.000009 master-bin.000018 master-bin.000027 master-bin.000036 master-bin.000045 master-bin.000054 # 多次查找,發現drop database在54號日誌文件 [[email protected] binlog]# mysqlbinlog -v master-bin.000056 | grep -i "drop database lijiamandb" [[email protected] binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb" [[email protected] binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb" [[email protected] binlog]# mysqlbinlog -v master-bin.000054 | grep -i "drop database lijiamandb" drop database lijiamandb # 保存到文本,便於搜索 [[email protected] binlog]# mysqlbinlog -v master-bin.000054 > master-bin.txt # 確認drop database之前的位置為:54號文件的9019487 # at 9019422 #200423 16:07:46 server id 11 end_log_pos 9019487 CRC32 0x86f13148 Anonymous_GTID last_committed=30266 sequence_number=30267 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 9019487 #200423 16:07:46 server id 11 end_log_pos 9019597 CRC32 0xbd6ea5dd Query thread_id=100 exec_time=0 error_code=0 SET TIMESTAMP=1587629266/*!*/; SET @@session.sql_auto_is_null=0/*!*/; /*!C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; drop database lijiamandb /*!*/; # at 9019597 #200423 16:09:25 server id 11 end_log_pos 9019662 CRC32 0x8f7b11dc Anonymous_GTID last_committed=30267 sequence_number=30268 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 9019662 #200423 16:09:25 server id 11 end_log_pos 9019774 CRC32 0x9b42423d Query thread_id=100 exec_time=0 error_code=0 SET TIMESTAMP=1587629365/*!*/; create database lijiamandb

STEP7:確定了開始結束點,執行增量恢復

開始:44號日誌的8526828

結束:54號文件的9019487

這裡分為3條命令執行,起始日誌文件涉及到參數start-position參數,單獨執行;中止文件涉及到stop-position參數,單獨執行;中間的日誌文件不涉及到特殊參數,全部一起執行。

# 起始日誌文件

# 起始日誌文件 mysqlbinlog --start-position=8526828 /mysql/binlog/master-bin.000044 | mysql -uroot -p123456 # 中間日誌文件 mysqlbinlog /mysql/binlog/master-bin.000045 /mysql/binlog/master-bin.000046 /mysql/binlog/master-bin.000047 /mysql/binlog/master-bin.000048 /mysql/binlog/master-bin.000049 /mysql/binlog/master-bin.000050 /mysql/binlog/master-bin.000051 /mysql/binlog/master-bin.000052 /mysql/binlog/master-bin.000053 | mysql -uroot -p123456 # 終止日誌文件 mysqlbinlog --stop-position=9019487 /mysql/binlog/master-bin.000054 | mysql -uroot -p123456

STEP8:恢復結束,確認全部數據已經還原

[[email protected] binlog]# mysql -uroot -p123456 lijiamandb mysql> select count(*) from test01; +----------+ | count(*) | +----------+ | 930000 | +----------+ row in set (0.15 sec) mysql> select count(*) from test02; +----------+ | count(*) | +----------+ | 930000 | +----------+ row in set (0.13 sec)

(四)總結

1.對於DML操作,binlog記錄了所有的DML數據變化:

--對於insert,binlog記錄了insert的行數據

--對於update,binlog記錄了改變前的行數據和改變後的行數據

--對於delete,binlog記錄了刪除前的數據

假如用戶不小心誤執行了DML操作,可以使用mysqlbinlog將數據庫恢復到故障點之前。



2.對於DDL操作,binlog只記錄用戶行為,而不記錄行變化,但是並不影響我們將數據庫恢復到故障點之前。



總之,使用mysqldump全備加binlog日誌,可以將數據恢復到故障前的任意時刻。



[火星人 ] MySQL使用mysqldump+binlog完整恢復被刪除的數據庫原理解析已經有205次圍觀

http://coctec.com/docs/mysql/show-post-231769.html