MySQL復制的概述、安裝、故障與技巧

本文介紹一下MySQL復制的概述、安裝、故障與技巧。同MongoDB，Redis這樣的NoSQL數據庫的復制相比，MySQL復制顯得相當復雜！

概述

首先主服務器把數據變化記錄到主日志，然後從服務器通過I/O線程讀取主服務器上的主日志，並且把它寫入到從服務器的中繼日志中，接著SQL線程讀取中繼日志，並且在從服務器上重放，從而實現MySQL復制。具體如下圖所示：

MySQL復制

整個過程反映到從服務器上，對應三套日志信息，可在從服務器上用如下命令查看：

mysql> SHOW SLAVE STATUS;

Master_Log_File & Read_Master_Log_Pos：下一個傳輸的主日志信息。

Relay_Master_Log_File & Exec_Master_Log_Pos：下一個執行的主日志信息。

Relay_Log_File & Relay_Log_Pos：下一個執行的中繼日志信息。

理解這些日志信息的含義對於解決故障至關重要，後文會詳細闡述。

安裝

先在主服務器上創建復制賬號：

mysql> GRANT REPLICATION SLAVE ON *.*
       TO '<SLAVE_USER>'@'<SLAVE_HOST>'
       IDENTIFIED BY '<SLAVE_PASSWORD>';

注：出於安全性和靈活性的考慮，不要把root等具有SUPER權限用戶作為復制賬號。

然後設置主服務器配置文件（缺省：/etc/my.cnf）：

[mysqld]

server_id = 100
log_bin = mysql-bin
log_bin_index = mysql-bin.index
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1

注：一定要保證主從服務器各自的server_id唯一，避免沖突。

注：如果沒有指定log_bin的話，缺省會使用主機名作為名字，如此一來一旦主機名發生改變，就會出問題，所以推薦指定log_bin（從服務器的relay_log存在一樣的問題）。

注：sync_binlog，innodb_flush_log_at_trx_commit，innodb_support_xa三個選項都是出於安全目的設置的，不是復制的必須選項。

接著設置從服務器配置文件（缺省：/etc/my.cnf）：

[mysqld]

server_id = 200
log_bin = mysql-bin
log_bin_index = mysql-bin.index
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
read_only = 1
skip_slave_start = 1
log_slave_updates = 1

注：如果用戶有SUPER權限，則read_only無效。

注：有了skip_slave_start，除非使用START SLAVE命令，否則從服務器不會開始復制。

注：設置log_slave_updates，讓從服務器記錄日志，有助於在必要時把從切換成主。

下面最重要的步驟是如何克隆一份主服務器的數據：

如果數據庫使用的是MyISAM表類型的話，可按如下方式操作：

shell> mysqldump --all-databases --master-data=1 > data.sql

注：master-data選項缺省會打開lock-all-tables，並寫入CHANGE MASTER TO語句。

如果數據庫使用的是InnoDB表類型的話，則應該使用single-transcation：

shell> mysqldump --all-databases --single-transaction --master-data=1 > data.sql

有了數據文件，傳輸到從服務器上並導入：

shell> mysql < data.sql

如果數據量很大的話，mysqldump會非常慢，此時直接拷貝數據文件能節省不少時間：

在拷貝之前要先鎖定數據，然後再獲得相關的日志信息（FILE & POSITION）：

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

接下來拷貝數據文件時，如果是MyISAM表類型的話，直接拷貝即可；如果是InnoDB表類型的話，一定要先停止MySQL服務再拷貝，否則拷貝文件可能無法使用。把拷貝的數據文件直接復制到從服務器的數據目錄。

最後還需要再指定一下日志信息：

mysql> CHANGE MASTER TO
       MASTER_HOST='<MASTER_HOST>',
       MASTER_USER='<SLAVE_USER>',
       MASTER_PASSWORD='<SLAVE_PASSWORD>',
       MASTER_LOG_FILE='<FILE>',
       MASTER_LOG_POS=<POSITION>;

注：不要在my.cnf配置文件裡設置MASTER_USER和MASTER_PASSWORD，因為最終生效的是CHANGE MASTER TO生成的master.info文件裡的信息。

在主服務器上直接拷貝數據文件雖然很快，但需要鎖表或者停止服務，這會影響線上服務。如果先前已經有了從服務器，那麼可以用舊的從服務器做母本來克隆新的從服務器：

先在舊的從服務器上查詢日志信息：

mysql> SHOW SLAVE STATUS;

我們需要的是其中的Relay_Master_Log_File & Exec_Master_Log_Pos。

然後在舊的從服務器上按照前面的方法得到數據，並在新的從服務器上還原。

接著在新的從服務器上設置日志信息：

mysql> CHANGE MASTER TO
       MASTER_HOST='<MASTER_HOST>',
       MASTER_USER='<SLAVE_USER>',
       MASTER_PASSWORD='<SLAVE_PASSWORD>',
       MASTER_LOG_FILE='<Relay_Master_Log_File>',
       MASTER_LOG_POS=<Exec_Master_Log_Pos>;

不管用那個方法，最後記得在從服務器上啟動復制，並檢查工作是否正常：

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS;

如果IO線程和SQL線程都顯示Yes，就可以感謝上帝了：

Slave_IO_Running 對應：Master_Log_File & Read_Master_Log_Pos

Slave_SQL_Running 對應：Relay_Master_Log_File & Exec_Master_Log_Pos

如果顯示No，則說明前面某些配置步驟出錯，或者對應的日志文件有問題。

故障

問題：主從復制不止何故停止了，我該怎麼辦？

答案：復制錯誤多半是因為日志錯誤引起的，所以首先要搞清楚是主日志錯誤還是中繼日志錯誤，從錯誤信息裡一般就能判斷，如果不能可以使用類似下面的mysqlbinlog命令：

shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null

shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null

如果沒有錯誤，則不會有任何輸出，反之如果有錯誤，則會顯示出來。

如果是主日志錯誤，則需要在從服務器使用SET GLOBAL sql_slave_skip_counter，如下：

mysql> SET GLOBAL sql_slave_skip_counter = 1;

mysql> START SLAVE;

注：如果有多個錯誤，可能需要執行多次（提醒：主從服務器數據可能因此不一致）。

如果是中繼日志錯誤，只要在從服務器使用SHOW SLAVE STATUS結果中的日志信息重新CHANGE MASTER TO即可，系統會拋棄當前的中繼日志，重新下載：

mysql> CHANGE MASTER TO
       MASTER_LOG_FILE='<Relay_Master_Log_File>