MySQL中閃回功能的方案討論及實現(xiàn)
Oracle有一個閃回(flashback)功能,能夠用戶恢復誤操作的數(shù)據(jù)。本文討論MySQL中支持閃回的方案。
1、 閃回的目標
即使為了數(shù)據(jù)安全,我們搭建了主從。但實時主從備份只能防止硬件問題,比如主庫的硬盤損壞。但對于誤操作,則無能為力。比如在主庫誤刪一張表,或者一個update語句沒有指定where條件,導致全表被更新。當操作被同步到從庫上后,則主從都“回天無力”。
線上或者測試環(huán)境經(jīng)常出現(xiàn)的誤操作總是讓DBA同學那么鬧心。
閃回的目的是要讓數(shù)據(jù)庫在commit之后,還能恢復到之前的某個狀態(tài),整庫或指定的表。
這里我們討論用binlog來實現(xiàn)閃回的方案。
2、 無米無炊一
恢復到之前的某個狀態(tài),是需要數(shù)據(jù)的。這數(shù)據(jù)可以是 a) 回滾步驟 或者 b) 操作之前的數(shù)據(jù)狀態(tài)原文。
但我們知道,若使用statement,并沒有上述需要的數(shù)據(jù)。試想binlog中記錄了一句update t set f1=3 where id=3。怎么恢復呢?
因此,我們的第一個“米”,就是binlog必須是row based的。在row base下,binlog同時記錄了更新前后的整行記錄。
a)單個語句的閃回
了row base的binlog后,我們來分析一下怎么實現(xiàn)閃回。平時的DML無非三種操作,增刪改,先說三種操作的日志格式。
一個語句分成兩個event (實際上不止,其他可以忽略), 一個table_map event 和 一個Rows_log_event。Table_map event是一樣的,主要看Rows_log_event。
每個Rows_log_event中包含event_type, 可選值為WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT。從宏名字就能看出用途。
對于insert和delete,event中包含了插入/刪除的記錄的所有字段的值(太爽了。。)
對于update操作,event中依次記錄舊行, 新行的值。
因此我們看到,這些信息足夠讓我們對單個操作實現(xiàn)“逆操作”。
i. 對于insert操作,只需要把event_type改成DELETE_ROWS_EVENT;對于delete操作,改成WRITE_ROWS_EVENT
ii. 對于update操作,只需要把event中的舊行和新行值對調即可。
b)binlog的閃回
我們只需要把binlog文件反向執(zhí)行,每個操作都執(zhí)行逆操作即可。當然也不是所有的event都反轉。Table_map event必須還是在Rows_log_event每個操作之前。目前的方案是用mysqlbinlog工具,增加一個flashback參數(shù),輸出結果為一個新的binlog文件――姑且叫做flashbacklog,這個flashbacklog順序執(zhí)行,可制定某張表和執(zhí)行到哪個pos,來實現(xiàn)數(shù)據(jù)庫的閃回。
3、 無米無炊二
上面我們說了DML的閃回方案。但對于DDL卻無能為力,對于大多數(shù)的ddl,即使是row base格式,binlog中仍只記錄語句本身。對于刪表操作,只記錄一個語句drop table t。僅憑這句話,無法還原表的數(shù)據(jù)。
雖然可以將一個drop table語句轉換成先delete再刪表,性能卻會降低很多。這里我們用上面說道的另外一種可用數(shù)據(jù):“操作前數(shù)據(jù)備份”。
按順序我們先討論怎么保存數(shù)據(jù),再討論怎么閃回。
保存數(shù)據(jù)
先說DDL的分類。有一類DDL,是不需要重建表的,比如加非聚簇索引。這類操作其實不會丟數(shù)據(jù),也是在原表上直接操作,對于我們“以恢復數(shù)據(jù)為目的”的閃回,是可以先忽略的。 另外一類,則是會影響到表數(shù)據(jù)的操作。比如
a) Drop/truncate table
這兩個操作直接把表數(shù)據(jù)清空。
b) Alter table add、drop、change column
這類操作的執(zhí)行過程是,1) 按照新的表定義建立一個臨時表tmpa,2) 將原表數(shù)據(jù)拷貝到臨時表,3)將原始表改名tmpb,4)將tmpa改名為原表名,5)將tmpb刪除。
我們分析上面的兩個操作,都有一個“刪除數(shù)據(jù)”的過程。
因此我們的保存數(shù)據(jù)方法就是:在刪除的動作開始之前,把表數(shù)據(jù)備份起來,然后留一個空表,在空表上執(zhí)行“刪除”操作。
用一個庫 #bak_database存放這些歷史數(shù)據(jù)。
閃回
有了數(shù)據(jù)以后,我們就要想一個比較統(tǒng)一的方法來閃回。上面我們說了對于DML操作,可以通過反向執(zhí)行所有逆操作來實現(xiàn),對于語句里面的DDL,只能直接跳過。原因是一個DDL不一定有直接的逆操作。
因此我們的方案中要構造這種逆操作。Event_type增加一種FLASHBACK_EVENT。這類操作形式與Query_Event相同,都是簡單的SQL語句,只是包含了將數(shù)據(jù)恢復的操作。
舉例:
a) 對于altert table t add column 操作。
我們在操作的過程中將臨時表tmpb,不刪除,而是保存到 #bak_database.#bak_table_xxxxx (后綴用于去重),在我的patch中用當前的時刻(微秒)。
對于這個語句,我們生成兩條FLASHBACK_EVENT,分別是 alter table `#bak_database`.`#bak_table_xxxxx` rename to `my_db`.`my_tbl`; 和 drop table `my_db`.`my_tbl`;
這里的my_db和my_tbl分別為原來的庫名和表名。
所以mysqlbinlog工具怎么處理FLASHBACK_EVENT這類event呢,直接執(zhí)行就行了。(注意這兩個event也是反向執(zhí)行的,所以在恢復的時候是先刪除`my_db`.`my_tbl`,再從#bak_database恢復回來)
b)對于drop table操作
由于實際操作會把這個表刪,只需要一個alter table `#bak_database`.`#bak_table_xxxxx` rename to `my_db`.`my_tbl`; 即可。
4、 演示
初始狀態(tài)我們庫中只有一個表 test.tb,兩行
CREATE TABLE `tb` ( `c` int(11) NOT NULL DEFAULT '0′, `d` int(11) DEFAULT NULL, PRIMARY KEY (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> select * from tb; +——+——+ | c | d | +——+——+ | 1 | 10 | | 2 | 20 | +——+——+
模擬一個DML和一個DDL
insert into tb values(3,30); alter table tb drop column d;
這兩個操作后在binlog文件中生成的結果如下
說明:
將mysqlbinloig中的其他信息去掉,其中紅色框中為insert語句產(chǎn)生的binlog,在flashback工具中會被轉化為delete操作。
兩個藍色框中的即為我們生成的FLASHBACK_EVENT。除了event_type與普通的query_event不同,還將庫名+表名放在語句的前面,目的是為了flashback工具執(zhí)行按表閃回的時候可以直接識別表名,不需要解析binlog語句。
紫色框中就是那個真正的DDL操作,在flashback工具中被忽略。
所以上面的binlog被flashback工具解析后的結果是兩個DDL語句和一個delete操作,能夠恢復到表的初始狀態(tài)。
5、小結
這里討論了MySQL閃回的一種方案.
增加一種新的event_type, 不會影響原來mysqlbinlog工具的使用;
備份即將刪除的表,沒有增加額外的操作,不會對正常操作性能造成影響(當然需要更多的存儲空間)。
實際上我們上面還留了一個“空擋”沒有討論,在方案完成后補充。
涉及到MySQL server本身和mysqlbinlog這個工具兩部分的修改,MySQL工具部分由@plinux同學實現(xiàn)。(其實如果不恢復DDL,可以只使用這個工具). server部分的改動可以只部署slave,這樣slave同時擔負備份和閃回準備的功能。
到此這篇關于MySQL中閃回功能的方案討論及實現(xiàn)的文章就介紹到這了,更多相關MySQL閃回內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
CentOS 6.5 i386 安裝MySQL 5.7.18詳細教程
這篇文章主要介紹了CentOS 6.5 i386 安裝MySQL 5.7.18詳細教程,需要的朋友可以參考下2017-04-04修改MySQL數(shù)據(jù)庫中表和表中字段的編碼方式的方法
這篇文章主要介紹了如何修改MySQL數(shù)據(jù)庫中表和表中字段的編碼方式,需要的朋友可以參考下2014-05-05MySQL中的alter table命令的基本使用方法及提速優(yōu)化
這篇文章主要介紹了MySQL中的alter table命令的基本使用方法及提速優(yōu)化的方法,包括ALTER COLUMN的使用等等,需要的朋友可以參考下2015-11-11MySQL敏感數(shù)據(jù)加密的實現(xiàn)方案
這篇文章主要介紹了MySQL敏感數(shù)據(jù)加密的實現(xiàn)方案,本文通過實例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值 ,需要的朋友可以參考下2024-02-02MySQL錯誤代碼3140:無效的JSON文本編碼問題解決辦法
下面這篇文章主要給大家介紹了關于MySQL錯誤代碼3140:無效的JSON文本編碼問題的解決辦法,文中通過代碼介紹的非常詳細,對大家學習或者使用mysql具有一定的參考借鑒價值,需要的朋友可以參考下2024-03-03