MySQL數(shù)據(jù)庫的實(shí)時(shí)備份知識(shí)點(diǎn)詳解

前言

數(shù)據(jù)庫實(shí)時(shí)備份的需求很常見，MySQL本身提供了 Replication 機(jī)制，摘譯官方介紹如下：

MySQL Replication 可以將一個(gè)主數(shù)據(jù)庫中的數(shù)據(jù)同步到一個(gè)或多個(gè)從數(shù)據(jù)庫中。并且這個(gè)同步過程默認(rèn)以異步方式工作，不需要保持主從數(shù)據(jù)庫的實(shí)時(shí)連接（即允許連接中斷）。同時(shí)允許自定義配置需同步的數(shù)據(jù)庫及數(shù)據(jù)表。

MySQL Replication 的優(yōu)點(diǎn)及應(yīng)用場(chǎng)景如下：

1、通過 MySQL Replication 實(shí)現(xiàn)負(fù)載均衡與讀寫分離（主數(shù)據(jù)庫僅更新，從數(shù)據(jù)庫僅讀取），提升數(shù)據(jù)庫性能。

2、通過 MySQL Replication 實(shí)現(xiàn)數(shù)據(jù)的實(shí)時(shí)備份，保證數(shù)據(jù)安全。

3、通過 MySQL Replication 實(shí)現(xiàn)數(shù)據(jù)的離線分析（主數(shù)據(jù)庫生成數(shù)據(jù)，從數(shù)據(jù)庫分析計(jì)算不影響主數(shù)據(jù)庫性能）。

4、數(shù)據(jù)分發(fā)。

MySQL Replication完整的官方文檔請(qǐng)參閱：https://dev.mysql.com/doc/refman/5.7/en/replication.html

工作原理

1111

1、Master中的所有數(shù)據(jù)庫變更事件寫入Binary Log文件

2、當(dāng)在Slave中執(zhí)行“SLAVE START”命令時(shí)，開啟Slave I/O Thread，并連接Master

3、Master偵測(cè)到Slave I/O Thread的連接，開啟Log Jump Thread進(jìn)行響應(yīng)

4、Master Binary Log經(jīng)Master Log Jump Thread和Slave I/O Thread傳輸至Slave Relay Log

5、Slave SQL Thread將Relay Log還原至數(shù)據(jù)，同步完成

注：可使用“SHOW PROCESSLIST”命令在Master和Slave中查看對(duì)應(yīng)線程的運(yùn)行情況

配置Master

開啟Binary Log并設(shè)置ServerID，ServerID必須唯一，取值范圍1至232-1

[mysqld]
# 開啟Binary Log
log-bin=mysql-bin
# 設(shè)置全局ID
server-id=1

# 指定需同步的數(shù)據(jù)庫（因?yàn)閿?shù)據(jù)庫名稱可能包含逗號(hào)，因此多個(gè)數(shù)據(jù)庫必須重復(fù)配置多次而不能以逗號(hào)分隔）
binlog-do-db=database_name
# 指定禁止同步的數(shù)據(jù)庫
binlog-ignore-db=database_name
# 指定Binary Log格式
binlog_format=MIXED

創(chuàng)建同步帳號(hào)

因?yàn)槊總€(gè)Slave均需要使用帳號(hào)密碼連接至主數(shù)據(jù)庫，所以在主數(shù)據(jù)庫上必須提供帳號(hào)。建議使用一個(gè)獨(dú)立帳號(hào)，僅授權(quán)數(shù)據(jù)同步權(quán)限。

CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

獲取Binary Log信息

Slave啟動(dòng)I/O Thread時(shí)需要傳入Binary Log部分信息，因此需要獲取一下Binary Log信息：

SHOW MASTER STATUS;

使用“SHOW MASTER STATUS”命令獲取Binary Log信息，并記錄File和Position字段值。

同步前保證Master與Slave的數(shù)據(jù)一致

在Slave啟動(dòng)I/O Thread前，需確保Master與Slave的數(shù)據(jù)一致，因此先對(duì)Master進(jìn)行鎖定（防止數(shù)據(jù)變更），手動(dòng)同步并確保數(shù)據(jù)一致后再解鎖。

FLUSH TABLES WITH READ LOCK;

手動(dòng)數(shù)據(jù)同步相關(guān)操作略...

UNLOCK TABLES;

配置Slave

設(shè)置ServerID，可不必開啟BinLog：

[mysqld]
# 設(shè)置全局ID
server-id=2

# 指定同步的數(shù)據(jù)庫
replicate-do-db=database_name
# 指定禁止同步的數(shù)據(jù)庫
replicate_ignore_db=database_name

設(shè)置Master信息，執(zhí)行如下命令：

mysql> CHANGE MASTER TO
  ->   MASTER_HOST='master_host_name',
  ->   MASTER_PORT='master_host_port',
  ->   MASTER_USER='replication_user_name',
  ->   MASTER_PASSWORD='replication_password',
  ->   MASTER_LOG_FILE='recorded_log_file_name',
  ->   MASTER_LOG_POS=recorded_log_position;

啟動(dòng)I/O Thread

START SLAVE;

查看同步狀態(tài)：

SHOW SLAVE STATUS;

Master的binlog_format 參數(shù)

binlog_format用于配置Binary Log的格式，支持如下三種類型：

Row

按數(shù)據(jù)行的變化進(jìn)行記錄，該模式與SQL語句、存儲(chǔ)過程、函數(shù)、觸發(fā)器等無關(guān)，它只關(guān)心每一行的數(shù)據(jù)是否發(fā)生變化，如變化則記錄，因此Row模式是準(zhǔn)確度最高的。但它的缺點(diǎn)是某些情況下會(huì)產(chǎn)生大量?jī)?nèi)容而導(dǎo)致效率下降，比如表結(jié)構(gòu)發(fā)生變更時(shí)。

Statement

按SQL語句進(jìn)行記錄，很明顯這個(gè)解決了Row模式的短板，但問題是準(zhǔn)確度不夠高，因?yàn)镾QL語句可以非常復(fù)雜并且容易出現(xiàn)意外情況。

Mixed

Row與Statement混合模式，由MySQL自動(dòng)決定什么時(shí)候使用Row，什么時(shí)候使用Statement，這也是默認(rèn)模式。

replicate-do-db注意事項(xiàng)

當(dāng)在Slave中使用replicate-do-db和replicate-ignore-db配置項(xiàng)時(shí)，需特別注意，跨數(shù)據(jù)庫的SQL語句將不會(huì)被同步，如：

replicate-do-db=a
use b;
update a.some_table set some_field = 'some value';

解決方案是使用replicate_wild_do_table和replicate_wild_ignore_table，如：

replicate_wild_do_table=database_name.%
replicate_wild_ignore_table=database_name.%