（一）概述
在日常MySQL數(shù)據(jù)庫(kù)運(yùn)維過(guò)程中，可能會(huì)遇到用戶誤刪除數(shù)據(jù)，常見(jiàn)的誤刪除數(shù)據(jù)操作有：
這些情況雖然不會(huì)經(jīng)常遇到，但是遇到了，我們需要有能力將其恢復(fù)，下面講述如何恢復(fù)。
（二）恢復(fù)原理
如果要將數(shù)據(jù)庫(kù)恢復(fù)到故障點(diǎn)之前，那么需要有數(shù)據(jù)庫(kù)全備和全備之后產(chǎn)生的所有二進(jìn)制日志。
全備作用 ：使用全備將數(shù)據(jù)庫(kù)恢復(fù)到上一次完整備份的位置；
二進(jìn)制日志作用：利用全備的備份集將數(shù)據(jù)庫(kù)恢復(fù)到上一次完整備份的位置之后，需要對(duì)上一次全備之后數(shù)據(jù)庫(kù)產(chǎn)生的所有動(dòng)作進(jìn)行重做，而重做的過(guò)程就是解析二進(jìn)制日志文件為SQL語(yǔ)句，然后放到數(shù)據(jù)庫(kù)里面再次執(zhí)行。
舉個(gè)例子：小明在4月1日晚上8：00使用了mysqldump對(duì)數(shù)據(jù)庫(kù)進(jìn)行了備份，在4月2日早上12：00的時(shí)候，小華不小心刪除了數(shù)據(jù)庫(kù)，那么，在執(zhí)行數(shù)據(jù)庫(kù)恢復(fù)的時(shí)候，需要使用4月1日晚上的完整備份將數(shù)據(jù)庫(kù)恢復(fù)到“4月1日晚上8：00”，那4月1日晚上8：00以后到4月2日早上12：00之前的數(shù)據(jù)如何恢復(fù)呢？就得通過(guò)解析二進(jìn)制日志來(lái)對(duì)這段時(shí)間執(zhí)行過(guò)的SQL進(jìn)行重做。
（三）刪庫(kù)恢復(fù)測(cè)試
（3.1）實(shí)驗(yàn)?zāi)康?/p>
在本次實(shí)驗(yàn)中，我直接測(cè)試刪庫(kù)，執(zhí)行drop database lijiamandb,確認(rèn)是否可以恢復(fù)。
（3.2）測(cè)試過(guò)程
在測(cè)試數(shù)據(jù)庫(kù)lijiamandb中創(chuàng)建測(cè)試表test01和test02,然后執(zhí)行mysqldump對(duì)數(shù)據(jù)庫(kù)進(jìn)行全備，之后執(zhí)行drop database,確認(rèn)database是否可以恢復(fù)。
STEP1:創(chuàng)建測(cè)試數(shù)據(jù)，為了模擬日常繁忙的生產(chǎn)環(huán)境，頻繁的操作數(shù)據(jù)庫(kù)產(chǎn)生大量二進(jìn)制日志，我特地使用存儲(chǔ)過(guò)程和EVENT產(chǎn)生大量數(shù)據(jù)。
創(chuàng)建測(cè)試表：
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) );
創(chuàng)建存儲(chǔ)過(guò)程，往測(cè)試表里面插入數(shù)據(jù)，每次執(zhí)行該存儲(chǔ)過(guò)程，往test01和test02各自插入10000條數(shù)據(jù)：
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秒鐘，執(zhí)行上面的存儲(chǔ)過(guò)程：
use lijiamandb; create event if not exists e_insert on schedule every 10 second on completion preserve do call p_insert();
啟動(dòng)EVENT，每個(gè)10s自動(dòng)向test01和test02各自插入10000條數(shù)據(jù)
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)
--過(guò)3分鐘。。。
STEP2:第一步生成大量測(cè)試數(shù)據(jù)后，使用mysqldump對(duì)lijiamandb數(shù)據(jù)庫(kù)執(zhí)行完全備份
mysqldump -h192.168.10.11 -uroot -p123456 -P3306 --single-transaction --master-data=2 --events --routines --databases lijiamandb > /mysql/backup/lijiamandb.sql
注意：必須要添加--master-data=2，這樣才會(huì)備份集里面mysqldump備份的終點(diǎn)位置。
--過(guò)3分鐘。。。
STEP3:為了便于數(shù)據(jù)庫(kù)刪除前與刪除后數(shù)據(jù)一致性校驗(yàn)，先停止表的數(shù)據(jù)插入，此時(shí)test01和test02都有930000行數(shù)據(jù)，我們后續(xù)恢復(fù)也要保證有930000行數(shù)據(jù)。
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:刪除數(shù)據(jù)庫(kù)
mysql> drop database lijiamandb; Query OK, 2 rows affected (0.07 sec)
STEP5:使用mysqldump的全備導(dǎo)入
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.
在執(zhí)行全量備份恢復(fù)之后，發(fā)現(xiàn)只有753238筆數(shù)據(jù)：
[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)
很明顯，全量導(dǎo)入之后，數(shù)據(jù)不完整，接下來(lái)使用mysqlbinlog對(duì)二進(jìn)制日志執(zhí)行增量恢復(fù)。
使用mysqlbinlog進(jìn)行增量日志恢復(fù)最重要的就是確定待恢復(fù)的起始位置(start-position)和終止位置(stop-position)，起始位置(start-position)是我們執(zhí)行全被之后的位置，而終止位置則是故障發(fā)生之前的位置。
STEP6:確認(rèn)mysqldump備份到的最終位置
[root@masterdb backup]# cat lijiamandb.sql |grep "CHANGE MASTER" -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000044', MASTER_LOG_POS=8526828
備份到了44號(hào)日志的8526828位置，那么恢復(fù)的起點(diǎn)可以設(shè)置為：44號(hào)日志的8526828。
--接下來(lái)確認(rèn)要恢復(fù)的終點(diǎn)位置，即執(zhí)行"DROP DATABASE LIJIAMAN"之前的位置，需要到binlog里面確認(rèn)。
[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 # 多次查找，發(fā)現(xiàn)drop database在54號(hào)日志文件 [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 # 確認(rèn)drop database之前的位置為：54號(hào)文件的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:確定了開(kāi)始結(jié)束點(diǎn)，執(zhí)行增量恢復(fù)
開(kāi)始：44號(hào)日志的8526828
結(jié)束：54號(hào)文件的9019487
這里分為3條命令執(zhí)行，起始日志文件涉及到參數(shù)start-position參數(shù)，單獨(dú)執(zhí)行；中止文件涉及到stop-position參數(shù)，單獨(dú)執(zhí)行；中間的日志文件不涉及到特殊參數(shù)，全部一起執(zhí)行。
# 起始日志文件
# 起始日志文件 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:恢復(fù)結(jié)束，確認(rèn)全部數(shù)據(jù)已經(jīng)還原
[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)
（四）總結(jié)
1.對(duì)于DML操作，binlog記錄了所有的DML數(shù)據(jù)變化：
--對(duì)于insert，binlog記錄了insert的行數(shù)據(jù)
--對(duì)于update，binlog記錄了改變前的行數(shù)據(jù)和改變后的行數(shù)據(jù)
--對(duì)于delete，binlog記錄了刪除前的數(shù)據(jù)
假如用戶不小心誤執(zhí)行了DML操作，可以使用mysqlbinlog將數(shù)據(jù)庫(kù)恢復(fù)到故障點(diǎn)之前。
2.對(duì)于DDL操作，binlog只記錄用戶行為，而不記錄行變化，但是并不影響我們將數(shù)據(jù)庫(kù)恢復(fù)到故障點(diǎn)之前。
總之，使用mysqldump全備加binlog日志，可以將數(shù)據(jù)恢復(fù)到故障前的任意時(shí)刻。
到此這篇關(guān)于MySQL使用mysqldump+binlog完整恢復(fù)被刪除的數(shù)據(jù)庫(kù)的文章就介紹到這了,更多相關(guān)MySQL恢復(fù)被刪除的數(shù)據(jù)庫(kù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
標(biāo)簽：定西 秦皇島 白銀 澳門 杭州 益陽(yáng) 恩施 寧夏
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL使用mysqldump+binlog完整恢復(fù)被刪除的數(shù)據(jù)庫(kù)原理解析》，本文關(guān)鍵詞 MySQL,使用,mysqldump+binlog,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
