MySQL複製的概述、安裝、故障、技巧、工具(火丁分享)

同MongoDB，Redis這樣的NoSQL資料庫的複製相比，MySQL複製顯得相當複雜！

概述

首先主伺服器把資料變化記錄到主日誌，然後從伺服器通過I/O線程讀取主伺服器上的主日誌，並且把它寫入到從伺服器的中繼日誌中，接著SQL線程讀取中繼日誌，並且在從伺服器上重放，從而實現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>',
MASTER_LOG_POS=<Exec_Master_Log_Pos>;
mysql> START SLAVE;

至於為什麼使用的是Relay_Master_Log_File & Exec_Master_Log_Pos，參見概述。
問題：主伺服器宕機了，如何把從伺服器提升會主伺服器？
答案：在一主多從的環境總，需選擇資料最新的從伺服器做新的主伺服器。如所示：

提升從伺服器為主伺服器

在一主（Server1）兩從（Server2，、Server3）的環境中，Server1宕機後，等到Server1和Server2把宕機前同步到的日誌都執行完，比較Master_Log_File和Read_Master_Log_Pos就可以判斷出誰快誰慢，因為Server2從 Server1同步的資料（1582）比Server3從Server1同步的資料（1493）新，所以應該提升Server2為新的主伺服器，那麼 Server3在CHANGE MASTER TO到Server2的時候應該使用什麼樣的參數呢？1582-1493=89，而Server2的最後的二進位日誌位置是8167，所以答案是 8167-89=8078。

技巧

主從伺服器中的表可以使用不同的表類型。比如主伺服器可以使用InnoDB表類型，提供事務，行鎖等進階特性，從伺服器可以使用MyISAM表類型，記憶體消耗少，易備份等優點。還有一個例子，一台主伺服器如果同時帶很多個從伺服器的話，勢必會影響其效能，此時可以拿出一台伺服器作為從伺服器代理，使用BLACKHOLE表類型，只記錄日誌，不寫資料，由它帶多台從伺服器，從而提升效能。

主從伺服器中的表可以使用不同的鍵類型。比如主伺服器用InnoDB，鍵用VARCHAR的話節省空間的，從伺服器使用MyISAM，鍵用CHAR提高速度，因為MyISAM有靜態表一說。

主從伺服器中的表可以使用不同的索引。主伺服器主要用來應付寫操作，所以除了主鍵和唯一索引等保證資料關係的索引一般都可以不加，從伺服器一般用來應付讀操作，所以可以針對查詢特徵設定索引，再進一步，不同的從伺服器可以針對不同的查詢設定不同的索引。

工具

有一些優秀的工具可以讓你的複製工作得到事半功倍的效果，詳細內容請參考各自文檔：

Multi-Master Replication Manager for MySQL

Percona XtraBackup

Maatkit

Tungsten-replicator

此外，Google Project Hosting裡還有很多有趣的項目，可用mysql+replication標籤搜尋。

說明：本文參考了下面列出的書籍中相關的內容：

High Performance MySQL： Optimization， Backups， Replication， and More

MySQL High Availability： Tools for Building Robust Data Centers

（來源：火丁筆記）