MySQL使用mysqldump+binlog完整恢復被刪除的數據庫的辦法

2024-07-24 12:36:02

字體：大中小

來源：轉載

供稿：網友

　　這篇文章主要講解了MySQL使用mysqldump+binlog完整恢復被刪除的數據庫的方法，內容清晰明了，對此有興趣的小伙伴可以學習一下，相信大家閱讀完之后會有幫助。

　　（一）概述

　　在日常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 -h292.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
　　 [root@masterdb binlog]# mysql -uroot -p123456 lijiamandb < /mysql/backup/lijiamandb.sql
　　 mysql: [Warning] Using a password on the command line interface can be insecure.
　　在執行全量備份恢復之后，發現只有753238筆數據：

　　[root@masterdb 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備份到的最終位置

　　[root@masterdb 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里面確認。

　　[root@masterdb 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號日志文件
　　[root@masterdb binlog]# mysqlbinlog -v master-bin.000056 | grep -i "drop database lijiamandb"
　　 [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb"
　　 [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb"
　　 [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 | grep -i "drop database lijiamandb"
　　drop database lijiamandb

　　# 保存到文本，便于搜索
　　[root@masterdb 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:恢復結束，確認全部數據已經還原

　　[root@masterdb 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數據庫服務CPU高問題剖析與優化

下一篇：MySQL大表刪除導致服務器變慢的解析