MySQL的復制功能

什么是復制功能？

MySQL 的復制功能是將備份的數據移動到其他服務器的功能，通過簡單的設定即可使用

主→從（master ->slave）架構。

主服務器上發生數據變更，變更內容傳送到從服務器，從服務器接受主服務器的變更內容，

將變更內容反映到數據庫。

復制功能的優點

1. 提高查詢性能

查詢處理負載高的情況下，可以通過增加從服務器來實現負載均衡，提高性能

2. 實現高可用性

主服務器發生故障時，可以將從服務器升級為主服務器

3. 實現異地復制

構建異地容災系統

4. 用作備份服務器

在從服務器上進行備份，可以不影響主服務器的性能

例如可以實現主服務器不停機運行，將從服務器的數據庫停止以后進行備份

復制功能的組成

主服務器的所有變更點都記錄在二進制日志里面。二進制日志里記錄了與更新相關的SQL文，執行查詢的日期時間等元數據也記錄在內。事務提交的同時以二進制的形式進行記錄(sync_binlog=1)。

mysqlbinlog 命令可以查看日志內容。指定啟動選項進行輸出二進制日志。

--log-bin[=file_name]

推薦將輸出路徑與數據文件路徑放在不同的硬盤上。日志文件的擴展名以連號的形式記錄

例）file_name-bin.001, file_name-bin.002, 等等。當前正在使用的日志號碼會記錄在索引文件里面 (file_name.index)

主服務器的所有變更點都記錄在日志里面，在從服務器啟動復制功能，將二進制日志的內容傳送到從服務器后執行。

從服務器上的文件、線程

文件

中繼日志文件：記錄主服務器變更點的文件。

二進制日志文件：記錄從服務器變更點的文件?！?log-slave-updates有效時才會輸出)

master.info ：記錄連接主服務器的必要信息、讀取日志起始位置等信息的文件。 (MySQL 5.6開始可以保存在表內)

relay-log.info ：記錄中繼日志執行位置的文件。  (MySQL 5.6開始可以保存在表內)

線程

I/O 線程：將從主服務器上接收的二進制日志作為中繼日志保存

SQL 線程：將中繼日志里面的更新內容反映到DB里面

復制的種類

二進制日志的記錄方式不同

STATEMENT ：文(SQL文)

ROW ：行

MIXED ：文和行混合

不確定的SQL文——執行過程中結果可能會發生變化的SQL

UUID() 、UUID_SHORT()

USER()

FOUND_ROWS()

LOAD_FILE()

SYSDATE()

GET_LOCK() 、RELEASE_LOCK()

IS_FREE_LOCK() 、IS_USED_LOCK()

MASTER_POS_WAIT()

SLEEP()

VERSION()

沒有排序的LIMIT句

UDF 、非決定性的存儲過程/函數

查詢INFORMATION_SCHEMA

READ-COMMITTED/READ-UNCOMMITTED

同步方式不同

異步：將變更點異步傳送

半同步：將變更點同步傳送，向DB反映時候為異步

異步(默認)

異步傳送變更點

優點：相比半同期方式，主服務器的更新響應迅速

缺點：主服務器發生故障時，故障發生前的部分內容可能沒有傳送到從服務器

適用于負載均衡
(故障發生前的數據需要保護的情況下，需要應用程序對應)

半同步(MySQL 5.5開始追加的功能)

變更點同步傳送，異步將數據向DB反映

優點：主服務器發生故障時，故障發生前的更新數據可以確保傳送到從服務器

缺點：與異步模式相比主服務器的更新響應差

適用于高可用性的運用 (需要保護故障發生前的更新數據)

是否使用GTID

不使用GTID傳統的復制模式

使用GTID：MySQL 5.6新追加的模式

多臺服務器組成的復制環境里可以很容易的跟蹤比較事務

事務在全局里擁有唯一的識別ID，可以記錄在二進制日志里面

使用時和傳統的方式有變化

復制開始時自動識別位置

故障切換時，可以自動識別最新的從服務器

很容易組成多層復制

GTID 的優點：

可以自動識別日志文件里的位置，不需手動指定
(主服務器發生故障時，無需確認日志的位置可以實現故障切換)

GTID 的缺點：

限制因素

只能使用InnoDB

不能使用“CREATE TABLE ... SELECT“

不能在事務中使用“CREATE TEMPORARY TABLE” 和 "DROP TEMPORARY TABLE"

不支持sql_slave_skip_counter

從服務器需要輸出二進制日志

無法使用復制功能的過濾功能，使用過濾功能GTID里面會出現從未使用過的ID

使GTID有效需要全部的服務器停止后再轉成GTID模式。

MySQL 5.7 可以每臺服務器輪番啟動設置GTID

復制功能的設定方法(不使用GTID)

1 ．設定復制功能的參數

2 ．在主服務器上建立復制用的用戶

3 ．備份主服務器數據，將其恢復到從服務器，需要記錄備份時的二進制日志名稱和位置

4 ．從服務器上執行 CHANGE MASTER TO

5 ．從服務器上執行 START SLAVE

主服務器：設置下記選項后啟動

server-id

log-bin

datadir *

從服務器：設置下記選項后啟動

server-id

datadir *

port *

socket *   (Lunix 系OS)

read_only   ( 推薦設置)

給用戶賦予“REPLICATION SLAVE”權限

例

CREATE USER 'repl'@'localhost' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost';

將主服務器的備份數據恢復到從服務器(不使用GTID)

將冷備份的數據恢復

使用mysqldump進行數據備份和恢復

備份

 $ mysqldump --user=root --password=root --master-data=2 \

 --socket=/usr/local/mysql/data/mysql.sock \

 --hex-blob --default-character-set=utf8 --all-databases \

 --single-transaction > mysql_bkup_dump.sql

※需要記錄備份的日志文件名稱和位置

補充：mysqldump的選項

--master-data=2

將備份時的文件名和位置作為注釋記錄在備份文件里

--hex-blob

將二進制類型(BINARY、VARBINARY、BLOG) 和BIT類型的數據以16進制輸出

--default-character-set

設置mysqldump的默認字符

通常情況下與系統變量default-character-set設置一致

--all-databases

備份全部的數據庫

--lock-all-tables

將所有的表加鎖后進行備份

--single-transaction

利用InnoDB支持的事務處理，對InnoDB的表進行一致性備份

注意事項：使用mysqldump備份

為了保證數據的完整性，備份中不執行DDL(※)

ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE

引用手冊里關于”--single-transaction”的說明

「While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.

4 ．從服務器上執行 CHANGE MASTER TO

5 ．從服務器上執行 START SLAVE  (不使用GTID)

執行CHANGE MASTER TO

執行START SLAVE

例

CHANGE MASTER TO MASTER_HOST=‘localhost',

 -> MASTER_USER=‘repl',

 -> MASTER_PASSWORD=‘repl',

 -> MASTER_LOG_FILE='bin.000001',

 -> MASTER_LOG_POS=1790;

 START SLAVE;

  復制功能的設定方法(使用GTID)

1 ．設定復制功能的參數

2 ．在主服務器上建立復制用戶

3 ．將主服務器的數據備份后恢復到從服務器，無需使用日志的名稱和位置

4 ．從服務器執行 CHANGE MASTER TO

5 ．從服務器執行 START SLAVE

1 ．設置復制功能的參數(使用GTID)

主服務器：設置以下選項后啟動

server-id

log-bin

datadir *

gtid-mode=on

enforce-gtid-consistency=on

log-slave-updates

從服務器：設置以下選項后啟動

server-id

log-bin

datadir *

port *

socket *   ( 使用Lunix)

read_only   ( 推薦設置)

gtid-mode=on

enforce-gtid-consistency=on

log-slave-updates

賦予用戶“REPLICATION SLAVE”權限

例

CREATE USER ‘repl'@'localhost' IDENTIFIED BY ‘repl';

GRANT REPLICATION SLAVE ON *.* TO ‘repl'@'localhost';

3 ．將主服務器的數據備份后恢復到從服務器(使用GTID)

冷備份后恢復數據

刪除Datadir下面的auto.cnf

( 目的為主從服務器的server-uuid一致(※))

使用mysqldump進行備份和恢復

備份例

 $ mysqldump --user=root --password=root –master-data=2 \

 --socket=/usr/local/mysql/data/mysql.sock \

 --hex-blob --default-character-set=utf8 --all-databases \

 --single-transaction --triggers --routines --events > mysql_bkup_dump.sql

 4 ．從服務器執行 CHANGE MASTER TO

 5 ．從服務器執行 START SLAVE (使用GTID)

例

CHANGE MASTER TO MASTER_HOST=‘localhost',

 -> MASTER_USER=‘repl',

 -> MASTER_PASSWORD=‘repl',

 -> MASTER_AUTO_POSITION=1;

 START SLAVE;

管理日志

使用SHOW MASTER STATUS 確認現在的日志名稱和位置

使用SHOW MASTER LOGS 列出全部的日志文件名

使用FLUSH [BINARY] LOGS 命令或者MySQL服務器再啟動的時候日志輪換

使用PURGE MASTER 刪除特定時點的日志

使用RESET MASTER 刪除全部的日志

管理復制功能的命令(從服務器)

START SLAVE [SLAVE_TYPE] 啟動從服務器

STOP SLAVE [SLAVE_TYPE] 停止從服務器

SHOW SLAVE STATUS 確認從服務器的狀態

確認I/O線程傳送二進制日志的位置

確認SQL線程執行的relay日志位置

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

STAT SLAVE; 忽略下一個事務

發生錯誤時，確認狀態以后需要將特定的事務忽略的情況下使用

GTID 模式下該方法不適用

其他注意事項

MySQL 復制自身不具備的功能

高可用時候的故障切換功能

　=>MySQL 5.6的MySQL Utilities提供自動故障切換的腳本

讀寫分離、負載均衡的控制功能

　=>Connector/J(Java)或mysqlnd_ms(PHP)等可以控制

一次不要執行大量的更新處理

防止從服務器的延遲

主服務器事務提交后，將變更的內容傳送到從服務器，如果事務執行時間過長的話，反映到從服務器會產生延遲

監視復制功能

通過SHOW SLAVE STATUS的結果，來監視下面內容

I/O 線程、SQL線程是否正?；顒?？

I/O 線程 ： Slave_IO_Running

SQL 線程 ： Slave_SQL_Running

復制是否有延遲？

復制是否有延遲 ： Seconds_Behind_Master

二進制日志和中繼日志的執行位置等

二進制日志的傳送狀況 ： Master_Log_File、Read_Master_Log_Pos

中繼日志的執行狀況 : Relay_Master_Log_File、Exec_Master_Log_Pos

確認網絡延時需要在主服務器上執行SHOW MASTER STATUS 來確認。(比較SHOW SLAVE STATUS 的Master_Log_File、Read_Master_Log_Pos)

監視慢查詢日志

確認延遲原因和查詢執行時間長的原因

主從服務器的磁盤剩余空間

從服務器的磁盤空間減少的話無法刪除中繼日志，復制功能會停止

主從服務器的資源使用狀況

(CPU 、內存、I/O量，網絡流量)

復制功能構成高可用性

優點

MySQL 的標準功能不需要使用共享磁盤、軟件等等，成本低廉

可以實現高可用性和查詢處理的負載均衡

缺點

故障切換需要使用其他方法實現，運用的時候考慮事項較多

發生故障時候，使用什么樣的方法進行切換？

發生故障切換的時候，應用程序的連接需要切換

(MySQL 5.5 之前) 沒有崩潰安全機制，從服務器發生故障時，需要重新安裝從服務器。