MySQL復(fù)制的概述、安裝、故障、技巧、工具(火丁分享)
概述
首先主服務(wù)器把數(shù)據(jù)變化記錄到主日志，然后從服務(wù)器通過(guò)I/O線程讀取主服務(wù)器上的主日志，并且把它寫入到從服務(wù)器的中繼日志中，接著SQL線程讀取中繼日志，并且在從服務(wù)器上重放，從而實(shí)現(xiàn)MySQL復(fù)制。具體如下圖所示：
整個(gè)過(guò)程反映到從服務(wù)器上，對(duì)應(yīng)三套日志信息，可在從服務(wù)器上用如下命令查看：
mysql> SHOW SLAVE STATUS;
Master_Log_File & Read_Master_Log_Pos：下一個(gè)傳輸?shù)闹魅罩拘畔ⅰ?
Relay_Master_Log_File & Exec_Master_Log_Pos：下一個(gè)執(zhí)行的主日志信息。
Relay_Log_File & Relay_Log_Pos：下一個(gè)執(zhí)行的中繼日志信息。
理解這些日志信息的含義對(duì)于解決故障至關(guān)重要，后文會(huì)詳細(xì)闡述。
安裝
先在主服務(wù)器上創(chuàng)建復(fù)制賬號(hào)：
mysql> GRANT REPLICATION SLAVE ON *.*
TO '<SLAVE_USER>'@'<SLAVE_HOST>'
IDENTIFIED BY '<SLAVE_PASSWORD>';
注：出于安全性和靈活性的考慮，不要把root等具有SUPER權(quán)限用戶作為復(fù)制賬號(hào)。然后設(shè)置主服務(wù)器配置文件（缺?。?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
注：一定要保證主從服務(wù)器各自的server_id唯一，避免沖突。
注：如果沒(méi)有指定log_bin的話，缺省會(huì)使用主機(jī)名作為名字，如此一來(lái)一旦主機(jī)名發(fā)生改變，就會(huì)出問(wèn)題，所以推薦指定log_bin（從服務(wù)器的relay_log存在一樣的問(wèn)題）。
注：sync_binlog，innodb_flush_log_at_trx_commit，innodb_support_xa三個(gè)選項(xiàng)都是出于安全目的設(shè)置的，不是復(fù)制的必須選項(xiàng)。
接著設(shè)置從服務(wù)器配置文件（缺?。?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權(quán)限，則read_only無(wú)效。
注：有了skip_slave_start，除非使用START SLAVE命令，否則從服務(wù)器不會(huì)開始復(fù)制。
注：設(shè)置log_slave_updates，讓從服務(wù)器記錄日志，有助于在必要時(shí)把從切換成主。
下面最重要的步驟是如何克隆一份主服務(wù)器的數(shù)據(jù)：
如果數(shù)據(jù)庫(kù)使用的是MyISAM表類型的話，可按如下方式操作：
shell> mysqldump --all-databases --master-data=1 > data.sql
注：master-data選項(xiàng)缺省會(huì)打開lock-all-tables，并寫入CHANGE MASTER TO語(yǔ)句。
如果數(shù)據(jù)庫(kù)使用的是InnoDB表類型的話，則應(yīng)該使用single-transcation：
shell> mysqldump --all-databases --single-transaction --master-data=1 > data.sql
有了數(shù)據(jù)文件，傳輸?shù)綇姆?wù)器上并導(dǎo)入：
shell> mysql < data.sql
如果數(shù)據(jù)量很大的話，mysqldump會(huì)非常慢，此時(shí)直接拷貝數(shù)據(jù)文件能節(jié)省不少時(shí)間：
在拷貝之前要先鎖定數(shù)據(jù)，然后再獲得相關(guān)的日志信息（FILE & POSITION）：
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
接下來(lái)拷貝數(shù)據(jù)文件時(shí)，如果是MyISAM表類型的話，直接拷貝即可；如果是InnoDB表類型的話，一定要先停止MySQL服務(wù)再拷貝，否則拷貝文件可能無(wú)法使用。把拷貝的數(shù)據(jù)文件直接復(fù)制到從服務(wù)器的數(shù)據(jù)目錄。
最后還需要再指定一下日志信息：
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配置文件里設(shè)置MASTER_USER和MASTER_PASSWORD，因?yàn)樽罱K生效的是CHANGE MASTER TO生成的master.info文件里的信息。
在主服務(wù)器上直接拷貝數(shù)據(jù)文件雖然很快，但需要鎖表或者停止服務(wù)，這會(huì)影響線上服務(wù)。如果先前已經(jīng)有了從服務(wù)器，那么可以用舊的從服務(wù)器做母本來(lái)克隆新的從服務(wù)器：
先在舊的從服務(wù)器上查詢?nèi)罩拘畔ⅲ?
mysql> SHOW SLAVE STATUS;
我們需要的是其中的Relay_Master_Log_File & Exec_Master_Log_Pos。
然后在舊的從服務(wù)器上按照前面的方法得到數(shù)據(jù)，并在新的從服務(wù)器上還原。
接著在新的從服務(wù)器上設(shè)置日志信息：
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>;
不管用那個(gè)方法，最后記得在從服務(wù)器上啟動(dòng)復(fù)制，并檢查工作是否正常：
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;
如果IO線程和SQL線程都顯示Yes，就可以感謝上帝了：
Slave_IO_Running 對(duì)應(yīng)：Master_Log_File & Read_Master_Log_Pos
Slave_SQL_Running 對(duì)應(yīng)：Relay_Master_Log_File & Exec_Master_Log_Pos
如果顯示No，則說(shuō)明前面某些配置步驟出錯(cuò)，或者對(duì)應(yīng)的日志文件有問(wèn)題。
故障
問(wèn)題：主從復(fù)制不止何故停止了，我該怎么辦？
答案：復(fù)制錯(cuò)誤多半是因?yàn)槿罩惧e(cuò)誤引起的，所以首先要搞清楚是主日志錯(cuò)誤還是中繼日志錯(cuò)誤，從錯(cuò)誤信息里一般就能判斷，如果不能可以使用類似下面的mysqlbinlog命令：
shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null
shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null
如果沒(méi)有錯(cuò)誤，則不會(huì)有任何輸出，反之如果有錯(cuò)誤，則會(huì)顯示出來(lái)。
如果是主日志錯(cuò)誤，則需要在從服務(wù)器使用SET GLOBAL sql_slave_skip_counter，如下：
mysql> SET GLOBAL sql_slave_skip_counter = 1;
mysql> START SLAVE;
注：如果有多個(gè)錯(cuò)誤，可能需要執(zhí)行多次（提醒：主從服務(wù)器數(shù)據(jù)可能因此不一致）。
如果是中繼日志錯(cuò)誤，只要在從服務(wù)器使用SHOW SLAVE STATUS結(jié)果中的日志信息重新CHANGE MASTER TO即可，系統(tǒng)會(huì)拋棄當(dāng)前的中繼日志，重新下載：
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，參見概述。
問(wèn)題：主服務(wù)器宕機(jī)了，如何把從服務(wù)器提升會(huì)主服務(wù)器？
答案：在一主多從的環(huán)境總，需選擇數(shù)據(jù)最新的從服務(wù)器做新的主服務(wù)器。如下圖所示：
提升從服務(wù)器為主服務(wù)器
在一主（Server1）兩從（Server2，、Server3）的環(huán)境中，Server1宕機(jī)后，等到Server1和Server2把宕機(jī)前同步到的日志都執(zhí)行完，比較Master_Log_File和Read_Master_Log_Pos就可以判斷出誰(shuí)快誰(shuí)慢，因?yàn)镾erver2從 Server1同步的數(shù)據(jù)（1582）比Server3從Server1同步的數(shù)據(jù)（1493）新，所以應(yīng)該提升Server2為新的主服務(wù)器，那么 Server3在CHANGE MASTER TO到Server2的時(shí)候應(yīng)該使用什么樣的參數(shù)呢？1582-1493=89，而Server2的最后的二進(jìn)制日志位置是8167，所以答案是 8167-89=8078。
技巧
主從服務(wù)器中的表可以使用不同的表類型。比如主服務(wù)器可以使用InnoDB表類型，提供事務(wù)，行鎖等高級(jí)特性，從服務(wù)器可以使用MyISAM表類型，內(nèi)存消耗少，易備份等優(yōu)點(diǎn)。還有一個(gè)例子，一臺(tái)主服務(wù)器如果同時(shí)帶很多個(gè)從服務(wù)器的話，勢(shì)必會(huì)影響其性能，此時(shí)可以拿出一臺(tái)服務(wù)器作為從服務(wù)器代理，使用BLACKHOLE表類型，只記錄日志，不寫數(shù)據(jù)，由它帶多臺(tái)從服務(wù)器，從而提升性能。
主從服務(wù)器中的表可以使用不同的鍵類型。比如主服務(wù)器用InnoDB，鍵用VARCHAR的話節(jié)省空間，從服務(wù)器使用MyISAM，鍵用CHAR提高速度，因?yàn)镸yISAM有靜態(tài)表一說(shuō)。
主從服務(wù)器中的表可以使用不同的索引。主服務(wù)器主要用來(lái)應(yīng)付寫操作，所以除了主鍵和唯一索引等保證數(shù)據(jù)關(guān)系的索引一般都可以不加，從服務(wù)器一般用來(lái)應(yīng)付讀操作，所以可以針對(duì)查詢特征設(shè)置索引，再進(jìn)一步，不同的從服務(wù)器可以針對(duì)不同的查詢?cè)O(shè)置不同的索引。
工具
有一些優(yōu)秀的工具可以讓你的復(fù)制工作得到事半功倍的效果，詳細(xì)內(nèi)容請(qǐng)參考各自文檔：
Multi-Master Replication Manager for MySQL
此外，Google Project Hosting里還有很多有趣的項(xiàng)目，可用mysql+replication標(biāo)簽搜索。
說(shuō)明：本文參考了下面列出的書籍中相關(guān)的內(nèi)容：
High Performance MySQL： Optimization， Backups， Replication， and More
MySQL High Availability： Tools for Building Robust Data Centers
（來(lái)源：火丁筆記）
相關(guān)文章
本篇文章介紹了，使用java處理字符串公式運(yùn)算的方法。需要的朋友參考下2013-04-04
表的增刪查改，簡(jiǎn)稱表的?CURD?操作?:?Create(創(chuàng)建)，Update(更新)，Retrieve(讀取)，Delete（刪除），這篇文章主要介紹了MySQL表的基本查詢操作，感興趣的朋友一起看看吧2024-02-02
SQL中current_date()函數(shù)的實(shí)現(xiàn)
日期時(shí)間類型的數(shù)據(jù)也是經(jīng)常要用到的,SQL中也提供了一些函數(shù)對(duì)這些數(shù)據(jù)進(jìn)行處理,本文主要介紹了SQL中current_date()函數(shù)的實(shí)現(xiàn),具有一定的參考價(jià)值2024-02-02
mysql利用參數(shù)sql_safe_updates限制update/delete范圍詳解
這篇文章主要給大家介紹了關(guān)于mysql如何利用參數(shù)sql_safe_updates限制update/delete范圍的相關(guān)資料文中介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2017-10-10
mysql創(chuàng)建函數(shù)出現(xiàn)1418錯(cuò)誤的解決辦法
本篇文章是對(duì)在Mysql中創(chuàng)建函數(shù)報(bào)“ERROR 1418”的解決方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
這篇文章主要介紹了企業(yè)生產(chǎn)MySQL優(yōu)化介紹的相關(guān)內(nèi)容，文中敘述了其優(yōu)化內(nèi)容，各個(gè)方面介紹的比較詳細(xì)，需要的朋友可以參考。2017-10-10
mysql登錄時(shí)報(bào)socket找不到的問(wèn)題及解決
這篇文章主要介紹了mysql登錄時(shí)報(bào)socket找不到的問(wèn)題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-07-07
這篇文章詳細(xì)介紹了分庫(kù)分表的概念,原因,如何實(shí)現(xiàn),以及不同中間件的優(yōu)缺點(diǎn),同時(shí),也介紹了如何進(jìn)行數(shù)據(jù)遷移和擴(kuò)容縮容,以及如何處理分庫(kù)分表后的ID和事務(wù)問(wèn)題2025-02-02
在MySQL數(shù)據(jù)庫(kù)中,經(jīng)常需要查詢某個(gè)時(shí)間段內(nèi)的數(shù)據(jù),所以本文給大家介紹了MySQL各個(gè)特殊時(shí)間段的查詢方法,并提供相應(yīng)的源代碼示例,具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2024-01-01
MySQL學(xué)習(xí)之?dāng)?shù)據(jù)庫(kù)表五大約束詳解小白篇
本篇文章非常適合MySQl初學(xué)者，主要講解了MySQL數(shù)據(jù)庫(kù)的五大約束及約束概念和分類，有需要的朋友可以借鑒參考下，希望可以有所幫助2021-09-09