大家好，我是Tom哥~

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

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

那么有沒有什么解決方案?

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

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

什么是 MySQL 主備

情況一：

客戶端的業(yè)務(wù)操作，讀、寫訪問的是主庫

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

主庫由于有些原因，無法正常響應(yīng)客戶端的請求

情況二：

完成主備切換

客戶端讀寫，訪問的是備庫(此時(shí)備庫升級為新主庫)

那么，這里面最核心的數(shù)據(jù)同步是如何實(shí)現(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主機(jī)名(或IP地址)
• MASTER_PORT ：mysql實(shí)例端口號
• MASTER_USER：用戶名
• 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、備庫執(zhí)行 start slave 命令，備庫啟動(dòng)兩個(gè)線程：I/O thread 和 SQL thread

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

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

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

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

binlog 有哪幾種格式

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

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

接下來，我們開始一個(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)前正在寫入的binlog文件：

查看 binlog 中的內(nèi)容，我們先來看下 row 模式

show binlog events in 'mysql-bin.000001';

說明：

• SET @@SESSION.GTID_NEXT='ANONYMOUS’
• BEGIN 開始一個(gè)事務(wù)
• Table_map 記錄更新了哪個(gè)庫、哪張表
• 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è)字段，不用原始名稱，是為了節(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 語句的原文。

其中，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í)行時(shí)可能使用的是索引 A;但是同步給備庫執(zhí)行時(shí)，可能用了 索引B。

索引不同，同一條sql語句，運(yùn)行結(jié)果可能也不一樣。

針對這個(gè)場景，我們建議采用 row 格式的 binlog。

即使我們使用了帶where 條件(如：income>720)的delete語句，但 binlog 記錄的是要?jiǎng)h除的主鍵id(id =80 )，所以不會出現(xiàn)差錯(cuò)。

mixed 格式 的binlog 是個(gè)啥?

由于 statement 格式的binlog 可能會導(dǎo)致主庫、備庫間的數(shù)據(jù)同步不一致，因此我們會采用 row 格式。

但是，row 格式占用的空間很大，寫 binlog 也要占用大量的 IO 資源。

所以，官方提出一種mixed混合模式，集成了兩者的優(yōu)點(diǎn)。

內(nèi)容如下：

• mysql會自動(dòng)判斷statement格式，是否會引發(fā)主備不一致的問題
• 如果statement格式會引起主備不一致的問題，自動(dòng)使用row格式。
• 如果statement格式不會引起主備不一致的問題，那么就用statement格式，

恢復(fù)數(shù)據(jù)

當(dāng)然，我們還建議把MySQL 的binlog設(shè)置成row 模式，因?yàn)樗梢杂糜跀?shù)據(jù)恢復(fù)。我們來看下 insert、update、delete 三種DML操作如何來恢復(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 會 記錄 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機(jī)器的數(shù)據(jù)庫上回放，還原。