京東到家程序員刪庫(kù)跑路 ！ 講一講 MySQL 數(shù)據(jù)備份殺手锏 binlog

大家好，我是Tom哥~


我們都知道，數(shù)據(jù)非常重要

網(wǎng)上也經(jīng)常看到一些段子，某公司程序員對(duì)工作不滿(mǎn)，刪庫(kù)跑路，老板損失慘重，欲哭無(wú)淚。這不最近又爆出一例，京東到家程序員離職當(dāng)天刪庫(kù)跑路！

那么有沒(méi)有什么解決方案？

即使數(shù)據(jù)庫(kù)真的被刪了，也有備份數(shù)據(jù)，能快速恢復(fù)。甚至可以做到實(shí)時(shí)熱備，即使內(nèi)部炸掉外部用戶(hù)也感知不到，一片風(fēng)平浪靜。

MySQL 作為當(dāng)下流行數(shù)據(jù)庫(kù)，在數(shù)據(jù)備份、高可用方面非常有競(jìng)爭(zhēng)力，今天，我們就重點(diǎn)來(lái)講下

什么是 MySQL 主備

情況一：

客戶(hù)端的業(yè)務(wù)操作，讀、寫(xiě)訪(fǎng)問(wèn)的是主庫(kù)

主庫(kù)通過(guò)某種機(jī)制，將數(shù)據(jù)實(shí)時(shí)同步給備庫(kù)

主庫(kù)由于有些原因，無(wú)法正常響應(yīng)客戶(hù)端的請(qǐng)求

情況二：

完成主備切換

客戶(hù)端讀寫(xiě)，訪(fǎng)問(wèn)的是備庫(kù)（此時(shí)備庫(kù)升級(jí)為新主庫(kù)）

那么，這里面最核心的數(shù)據(jù)同步是如何實(shí)現(xiàn)的？

主從同步原理

1、在備庫(kù)執(zhí)行 change master 命令 ，綁定主庫(kù)的信息

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主機(jī)名（或IP地址）

MASTER_PORT ：mysql實(shí)例端口號(hào)

MASTER_USER：用戶(hù)名

MASTER_PASSWORD：密碼

MASTER_AUTO_POSITION：如果進(jìn)行change master to時(shí)使用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、備庫(kù)執(zhí)行 start slave 命令，備庫(kù)啟動(dòng)兩個(gè)線(xiàn)程：I/O thread 和 SQL thread

3、master主庫(kù)，有數(shù)據(jù)更新，將此次更新的事件類(lèi)型寫(xiě)入到主庫(kù)的 binlog 文件中

4、主庫(kù)會(huì)創(chuàng)建log dump 線(xiàn)程，通知slave有數(shù)據(jù)更新

5、slave，向master節(jié)點(diǎn)的 log dump線(xiàn)程請(qǐng)求一份指定binlog文件位置的副本，并將請(qǐng)求回來(lái)的binlog存到本地的Relay log 中繼日志中

6、slave 再開(kāi)啟一個(gè)SQL 線(xiàn)程讀取Relay log日志，解析出日志里的命令，并執(zhí)行，從而保證主備庫(kù)數(shù)據(jù)同步

整理了一份大廠?？济嬖囶}，這份pdf包括 Java基礎(chǔ)、Java并發(fā)、JVM、MySQL、Redis、Spring、MyBatis、Kafka、設(shè)計(jì)模式等面試題，分享給大家。

下載地址：百度云鏈接：https://pan.baidu.com/s/1XHT4ppXTp430MEMW2D0-Bg 提取碼: s3ab


binlog 有哪幾種格式

現(xiàn)在，讓我們近距離看下 binlog 日志。

binlog 格式有三種：row、statement、mixed

接下來(lái)，我們開(kāi)始一個(gè)實(shí)驗(yàn)：

先創(chuàng)建一個(gè)表

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='個(gè)人收支表';

插入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)前正在寫(xiě)入的binlog文件：












查看 binlog 中的內(nèi)容，我們先來(lái)看下 row 模式
show?binlog?events?in?'mysql-bin.000001';












說(shuō)明：
SET @@SESSION.GTID_NEXT='ANONYMOUS’
BEGIN ?開(kāi)始一個(gè)事務(wù)
Table_map ?記錄更新了哪個(gè)庫(kù)、哪張表
Write_rows 記錄做了什么操作，詳細(xì)看binlog需要借助mysqlbinlog工具。
COMMIT /* xid=157 */ ?結(jié)束一個(gè)事務(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 的第幾個(gè)字段，不用原始名稱(chēng)，是為了節(jié)省空間。
修改 binlog 格式，設(shè)置為 STATEMENT ，查看日志格式：
set?global?binlog_format='STATEMENT';
設(shè)置之后，需要退出mysql重新連接，才能看到生效
show?binlog?events?in?'mysql-bin.000001';












從圖中我們可以看出，當(dāng) binlog_format=statement 時(shí)，binlog 里面記錄的就是 SQL 語(yǔ)句的原文。
其中，use tomge ?：表示要先切到對(duì)應(yīng)的數(shù)據(jù)庫(kù)
如果想從指定位置查看binlog，可以增加 from 可選參數(shù)，如下：
show?binlog?events?in?'mysql-bin.000001'??from?5168;
statement 與 row 對(duì)比：
statement 格式的binlog記錄的是sql語(yǔ)句；row 格式的binlog記錄的是event（Table_map，Write_rows，Delete_rows）












當(dāng) binlog 在 statement 格式下，記錄的是sql語(yǔ)句，在主庫(kù)執(zhí)行時(shí)可能使用的是索引 A；但是同步給備庫(kù)執(zhí)行時(shí)，可能用了 索引B。
索引不同，同一條sql語(yǔ)句，運(yùn)行結(jié)果可能也不一樣。
針對(duì)這個(gè)場(chǎng)景，我們建議采用 row 格式的 binlog。
即使我們使用了帶where 條件（如：income>720）的delete語(yǔ)句，但 binlog 記錄的是要?jiǎng)h除的主鍵id（id =80 ），所以不會(huì)出現(xiàn)差錯(cuò)。
























mixed 格式 的binlog 是個(gè)啥？
由于 statement 格式的binlog 可能會(huì)導(dǎo)致主庫(kù)、備庫(kù)間的數(shù)據(jù)同步不一致，因此我們會(huì)采用 row 格式。
但是，row 格式占用的空間很大，寫(xiě) binlog 也要占用大量的 IO 資源。
所以，官方提出一種mixed混合模式，集成了兩者的優(yōu)點(diǎn)。
內(nèi)容如下：
mysql會(huì)自動(dòng)判斷statement格式，是否會(huì)引發(fā)主備不一致的問(wèn)題
如果statement格式會(huì)引起主備不一致的問(wèn)題，自動(dòng)使用row格式。
如果statement格式不會(huì)引起主備不一致的問(wèn)題，那么就用statement格式，
恢復(fù)數(shù)據(jù)
當(dāng)然，我們還建議把MySQL 的binlog設(shè)置成 row 模式，因?yàn)樗梢杂糜跀?shù)據(jù)恢復(fù)。我們來(lái)看下 insert、update、delete 三種DML操作如何來(lái)恢復(fù)數(shù)據(jù)的。
1、delete：
當(dāng)我們執(zhí)行 delete 命令時(shí)，如果 binlog_row_image 設(shè)置了 'FULL'，那么 Delete_rows 里面，包含了刪掉的行的所有字段的值。
如果誤刪了，因?yàn)?binlog 記錄了所有字段的值，反向執(zhí)行 insert ?就可以了。
當(dāng) binlog_row_image 設(shè)置為 MINIMAL，只記錄關(guān)鍵信息，比如 id=80
2、insert：
row 格式下，binlog 會(huì) 記錄 insert 的所有字段值。
如果誤操作，只需要根據(jù)這些值找到對(duì)應(yīng)的行，再執(zhí)行 delete 操作即可
3、update：
row 格式下，binlog 會(huì) 記錄 update 修改前、修改后的整行數(shù)據(jù)。
如果誤操作，只需要用修改前的數(shù)據(jù)覆蓋即可。
通過(guò)命令來(lái)恢復(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機(jī)器的數(shù)據(jù)庫(kù)上回放，還原。

關(guān)于我：
Tom哥，計(jì)算機(jī)研究生，校招進(jìn)阿里，期間還拿了百度、華為、中興、騰訊?等6家大廠offer，P7 技術(shù)專(zhuān)家。出過(guò)專(zhuān)利，CSDN博客專(zhuān)家。
多年的大廠浸染，參加多次淘寶雙11大促活動(dòng)，在系統(tǒng)架構(gòu)方面有豐富經(jīng)驗(yàn)，沉淀總結(jié)在微信公眾號(hào)：微觀技術(shù)
他專(zhuān)注于微服務(wù)、高并發(fā)、高性能緩存、分布式架構(gòu)、高可用、團(tuán)隊(duì)管理等方面，喜歡挖掘開(kāi)源框架亮點(diǎn)設(shè)計(jì)，內(nèi)容都是面試官喜歡考察的。強(qiáng)烈推薦關(guān)注一波。
 
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。
 
相關(guān)閱讀更多精彩內(nèi)容
友情鏈接更多精彩內(nèi)容