京東到家程序員刪庫跑路 ！ 講一講 MySQL 數(shù)據(jù)備份殺手锏 binlog
點擊上方公眾號進入?微觀技術(shù)?主頁
然后點擊右上角?“設(shè)為標(biāo)星”?
比別人更快接收硬核文章
大家好，我是Tom哥~
我們都知道，數(shù)據(jù)非常重要
網(wǎng)上也經(jīng)?？吹揭恍┒巫?，某公司程序員對工作不滿，刪庫跑路，老板損失慘重，欲哭無淚。這不最近又爆出一例，京東到家程序員離職當(dāng)天刪庫跑路！
那么有沒有什么解決方案？
即使數(shù)據(jù)庫真的被刪了，也有備份數(shù)據(jù)，能快速恢復(fù)。甚至可以做到實時熱備，即使內(nèi)部炸掉外部用戶也感知不到，一片風(fēng)平浪靜。
MySQL 作為當(dāng)下流行數(shù)據(jù)庫，在數(shù)據(jù)備份、高可用方面非常有競爭力，今天，我們就重點來講下
什么是 MySQL 主備
情況一：
客戶端的業(yè)務(wù)操作， 讀、寫訪問的是主庫主庫通過某種機制，將數(shù)據(jù)實時同步給備庫 主庫由于有些原因，無法正常響應(yīng)客戶端的請求
情況二：
完成主備切換 客戶端讀寫，訪問的是備庫（此時備庫升級為新主庫）
那么，這里面最核心的數(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的復(fù)制協(xié)議 MASTER_RETRY_COUNT：重連次數(shù) MASTER_HEARTBEAT_PERIOD：復(fù)制心跳的周期
https://www.docs4dev.com/docs/zh/mysql/5.7/reference/change-master-to.html
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 有哪幾種格式
現(xiàn)在，讓我們近距離看下 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模式：
查看當(dāng)前正在寫入的binlog文件：
查看 binlog 中的內(nèi)容，我們先來看下 row 模式
show?binlog?events?in?'mysql-bin.000001';
說明：
SET @@SESSION.GTID_NEXT='ANONYMOUS’ BEGIN ?開始一個事務(wù) Table_map ?記錄更新了哪個庫、哪張表 Write_rows 記錄做了什么操作，詳細看binlog需要借助mysqlbinlog工具。 COMMIT /* xid=157 */ ?結(jié)束一個事務(wù)
查找 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 格式，設(shè)置為 STATEMENT ，查看日志格式：
set?global?binlog_format='STATEMENT';
設(shè)置之后，需要退出mysql重新連接，才能看到生效
show?binlog?events?in?'mysql-bin.000001';
從圖中我們可以看出，當(dāng) binlog_format=statement 時，binlog 里面記錄的就是 SQL 語句的原文。
其中，use tomge ?：表示要先切到對應(yīng)的數(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）
當(dāng) 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 可能會導(dǎo)致主庫、備庫間的數(shù)據(jù)同步不一致，因此我們會采用 row 格式。
但是，row 格式占用的空間很大，寫 binlog 也要占用大量的 IO 資源。
所以，官方提出一種mixed混合模式，集成了兩者的優(yōu)點。
內(nèi)容如下：
mysql會自動判斷 statement格式，是否會引發(fā)主備不一致的問題如果 statement格式會引起主備不一致的問題，自動使用row格式。如果 statement格式不會引起主備不一致的問題，那么就用statement格式，
恢復(fù)數(shù)據(jù)
當(dāng)然，我們還建議把MySQL 的binlog設(shè)置成 row 模式，因為它可以用于數(shù)據(jù)恢復(fù)。我們來看下 insert、update、delete 三種DML操作如何來恢復(fù)數(shù)據(jù)的。
1、delete：
當(dāng)我們執(zhí)行 delete 命令時，如果 binlog_row_image 設(shè)置了 'FULL'，那么 Delete_rows 里面，包含了刪掉的行的所有字段的值。
如果誤刪了，因為 binlog 記錄了所有字段的值，反向執(zhí)行 insert ?就可以了。
當(dāng)
binlog_row_image設(shè)置為MINIMAL，只記錄關(guān)鍵信息，比如 id=80
2、insert：
row 格式下，binlog 會 記錄 insert 的所有字段值。
如果誤操作，只需要根據(jù)這些值找到對應(yīng)的行，再執(zhí)行 delete 操作即可
3、update：
row 格式下，binlog 會 記錄 update 修改前、修改后的整行數(shù)據(jù)。
如果誤操作，只需要用修改前的數(shù)據(jù)覆蓋即可。
通過命令來恢復(fù)數(shù)據(jù)：
如果要執(zhí)行數(shù)據(jù)恢復(fù)，可以使用下面命令
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ù)庫上回放，還原。
推薦閱讀：
【萬字長文】創(chuàng)業(yè)公司就應(yīng)該技術(shù)選型 Spring Cloud Alibaba
Redis 使用 List 實現(xiàn)消息隊列的利與弊
歡迎關(guān)注微信公眾號：互聯(lián)網(wǎng)全棧架構(gòu)，收取更多有價值的信息。