內(nèi)部群炸鍋了，某程序員離職當天刪庫跑路！
大家好，我是二哥呀~我們都知道，數(shù)據(jù)非常重要。
網(wǎng)上也經(jīng)?？吹揭恍┒巫?，某公司程序員對工作不滿，刪庫跑路，老板損失慘重，欲哭無淚。這不前幾天又爆出一例，某程序員離職當天刪庫跑路！
那么有沒有什么解決方案？即使數(shù)據(jù)庫真的被刪了，也有備份數(shù)據(jù)，能快速恢復。甚至可以做到實時熱備，即使內(nèi)部炸掉外部用戶也感知不到，一片風平浪靜。
MySQL 作為當下流行數(shù)據(jù)庫，在數(shù)據(jù)備份、高可用方面非常有競爭力，今天，我們就重點聊一聊數(shù)據(jù)備份的殺手锏 binlog。
一、MySQL 主備是什么？
情況一：
客戶端的業(yè)務操作，讀、寫訪問的是主庫 主庫通過某種機制，將數(shù)據(jù)實時同步給備庫 主庫由于有些原因，無法正常響應客戶端的請求
情況二：
完成主備切換 客戶端讀寫，訪問的是備庫（此時備庫升級為新主庫）
那么，這里面最核心的數(shù)據(jù)同步是如何實現(xiàn)的呢？
二、主從同步
1、在備庫執(zhí)行 change master 命令 ，綁定主庫的信息
mysql>?CHANGE?MASTER?TO?MASTER_HOST?=?'192.168.1.1',?MASTER_USER?=?'repl',?MASTER_PASSWORD?=?'replpassword',?MASTER_PORT?=?3306,?MASTER_AUTO_POSITION?=?1,?MASTER_RETRY_COUNT?=?0,?MASTER_HEARTBEAT_PERIOD?=?10000;?
簡單解釋下：
MASTER_HOST ：master主機名（或IP地址） MASTER_PORT ：mysql實例端口號 MASTER_USER：用戶名 MASTER_PASSWORD：密碼 MASTER_AUTO_POSITION：如果進行change master to時使用MASTER_AUTO_POSITION = 1，slave連接master將使用基于GTID的復制協(xié)議 MASTER_RETRY_COUNT：重連次數(shù) MASTER_HEARTBEAT_PERIOD：復制心跳的周期
2、備庫執(zhí)行 start slave 命令，備庫啟動兩個線程：I/O thread 和 SQL thread
3、master主庫，有數(shù)據(jù)更新，將此次更新的事件類型寫入到主庫的 binlog 文件中
4、主庫會創(chuàng)建log dump 線程，通知slave有數(shù)據(jù)更新
5、slave，向master節(jié)點的 log dump線程請求一份指定binlog文件位置的副本，并將請求回來的binlog存到本地的Relay log 中繼日志中
6、slave 再開啟一個SQL 線程讀取Relay log日志，解析出日志里的命令，并執(zhí)行，從而保證主備庫數(shù)據(jù)同步
三、開啟 binlog
從以上步驟可以看得出，binlog 起到了非常關鍵的作用，那就讓我們近距離看下 binlog 日志。
binlog 格式有三種：
row statement mixed
接下來，我們來做一個實驗：
先創(chuàng)建一個表：
CREATE?TABLE?`person`?(
??`id`?bigint(20)?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
??`income`?bigint(20)?NOT?NULL?COMMENT?'收入',
??`expend`?bigint(20)?NOT?NULL?COMMENT?'支出',
??PRIMARY?KEY?(`id`),
??KEY?`idx_income`?(`income`)
)?ENGINE=InnoDB?AUTO_INCREMENT=1?DEFAULT?CHARSET=utf8?COMMENT='個人收支表';
插入4條記錄：
insert?into?person?values(50,500,500);
insert?into?person?values(60,600,600);
insert?into?person?values(70,700,700);
insert?into?person?values(80,800,800);
查看binlog模式：
查看當前正在寫入的binlog文件：
查看 binlog 中的內(nèi)容，我們先來看下 row 模式
show?binlog?events?in?'mysql-bin.000001';
說明：
SET @@SESSION.GTID_NEXT='ANONYMOUS’ BEGIN ?開始一個事務 Table_map ?記錄更新了哪個庫、哪張表 Write_rows 記錄做了什么操作，詳細看binlog需要借助mysqlbinlog工具。 COMMIT /* xid=157 */ ?結(jié)束一個事務
查找 binlog 文件的物理位置：
root@167bfa3785f1:/#?find?/?-name?mysql-bin.000001
/var/lib/mysql/mysql-bin.000001
借助 mysqlbinlog 命令，查看具體內(nèi)容：
mysqlbinlog?-vv?mysql-bin.000001?--start-position=2986;
紅框中的內(nèi)容表示執(zhí)行了插入命令，insert into person values(80,800,800);
其中，@1、@2、@3 表示表 person 的第幾個字段，不用原始名稱，是為了節(jié)省空間。
修改 binlog 格式，設置為 STATEMENT ，查看日志格式：
set?global?binlog_format='STATEMENT';
設置之后，需要退出mysql重新連接，才能看到生效
show?binlog?events?in?'mysql-bin.000001';
從上圖中我們可以看出，當 binlog_format=statement 時，binlog 里面記錄的就是 SQL 語句的原文。
其中，use tomge ?表示要先切到對應的數(shù)據(jù)庫
如果想從指定位置查看binlog，可以增加 from 可選參數(shù)，如下：
show?binlog?events?in?'mysql-bin.000001'??from?5168;
statement 與 row 對比：
statement 格式的binlog記錄的是sql語句；row 格式的binlog記錄的是event（Table_map，Write_rows，Delete_rows）
當 binlog 在 statement 格式下，記錄的是sql語句，在主庫執(zhí)行時可能使用的是索引 A；但是同步給備庫執(zhí)行時，可能用了 索引B。
索引不同，同一條sql語句，運行結(jié)果可能也不一樣。
針對這個場景，我們建議采用 row 格式的 binlog。
即使我們使用了帶where 條件（如：income>720）的delete語句，但 binlog 記錄的是要刪除的主鍵id（id =80 ），所以不會出現(xiàn)差錯。
mixed 格式 的binlog 是個啥？
由于 statement 格式的binlog 可能會導致主庫、備庫間的數(shù)據(jù)同步不一致，因此我們會采用 row 格式。
但是，row 格式占用的空間很大，寫 binlog 也要占用大量的 IO 資源。
所以，官方提出一種mixed混合模式，集成了兩者的優(yōu)點。
內(nèi)容如下：
mysql會自動判斷statement格式，是否會引發(fā)主備不一致的問題 如果statement格式會引起主備不一致的問題，自動使用row格式。 如果statement格式不會引起主備不一致的問題，那么就用statement格式，
四、恢復數(shù)據(jù)
當然，我們還建議把MySQL 的binlog設置成 row 模式，因為它可以用于數(shù)據(jù)恢復。我們來看下 insert、update、delete 三種DML操作如何來恢復數(shù)據(jù)的。
1、delete：
當我們執(zhí)行 delete 命令時，如果 binlog_row_image 設置了 'FULL'，那么 Delete_rows 里面，包含了刪掉的行的所有字段的值。
如果誤刪了，因為 binlog 記錄了所有字段的值，反向執(zhí)行 insert ?就可以了。
當 binlog_row_image 設置為 MINIMAL，只記錄關鍵信息，比如 id=80 2、insert：
row 格式下，binlog 會 記錄 insert 的所有字段值。
如果誤操作，只需要根據(jù)這些值找到對應的行，再執(zhí)行 delete 操作即可
3、update：
row 格式下，binlog 會 記錄 update 修改前、修改后的整行數(shù)據(jù)。
如果誤操作，只需要用修改前的數(shù)據(jù)覆蓋即可。
通過命令來恢復數(shù)據(jù)：
如果要執(zhí)行數(shù)據(jù)恢復，可以使用下面命令
mysqlbinlog?mysql-bin.000001??--start-position=1??--stop-position=3000?|?mysql?-h192.168.0.1?-P3306?-u$user?-p$pwd;
將 mysql-bin.000001 文件位置從 1到3000 的 binlog 在 192.168.0.1機器的數(shù)據(jù)庫上回放，還原。
OK，搞定，再也不怕刪庫跑路了。
沒有什么使我停留——除了目的，縱然岸旁有玫瑰、有綠蔭、有寧靜的港灣，我是不系之舟。
推薦閱讀：