配置MySQL同步服務(wù)器
2024-07-24 12:40:07
供稿：網(wǎng)友
最近將四臺(tái)MySQL服務(wù)器配置成主從模式以實(shí)現(xiàn)一定的負(fù)載均衡，好象還可以接受，至少現(xiàn)在沒(méi)有出什么大問(wèn)題。
MySQL同步機(jī)制基于master把所有對(duì)數(shù)據(jù)庫(kù)的更新、刪除 等)都記錄在二進(jìn)制日志里。因此，想要啟用同步機(jī)制，在master就必須啟用二進(jìn)制日志。每個(gè)slave接受來(lái)自master上在二進(jìn)制日志中記錄的更新操作，因此在slave上執(zhí)行了這個(gè)操作的一個(gè)拷貝。應(yīng)該非常重要地意識(shí)到，二進(jìn)制日志只是從啟用二進(jìn)制日志開(kāi)始的時(shí)刻才記錄更新操作的。所有的slave必須在啟用二進(jìn)制日志時(shí)把master上已經(jīng)存在的數(shù)據(jù)拷貝過(guò)來(lái)。如果運(yùn)行同步時(shí)slave上的數(shù)據(jù)和master上啟用二進(jìn)制日志時(shí)的數(shù)據(jù)不一致的話，那么slave同步就會(huì)失敗。把master上的數(shù)據(jù)拷貝過(guò)來(lái)的方法之一實(shí)在slave上執(zhí)行 LOAD DATA FROM MASTER 語(yǔ)句。不過(guò)要注意，LOAD DATA FROM MASTER 是從MySQL 4.0.0之后才開(kāi)始可以用的，而且只支持master上的 MyISAM 類型表。同樣地，這個(gè)操作需要一個(gè)全局的讀鎖，這樣的話傳送日志到slave的時(shí)候在master上就不會(huì)有更新操作了。當(dāng)實(shí)現(xiàn)了自由鎖表熱備份時(shí)(在MySQL 5.0中)，全局讀鎖就沒(méi)必要了。由于有這些限制，因此我們建議只在master上相關(guān)數(shù)據(jù)比較小的時(shí)候才執(zhí)行 LOAD DATA FROM MASTER 語(yǔ)句，或者在master上允許一個(gè)長(zhǎng)時(shí)間的讀鎖。由于每個(gè)系統(tǒng)之間 LOAD DATA FROM MASTER 的速度各不一樣，一個(gè)比較好的衡量規(guī)則是每秒能拷貝1MB數(shù)據(jù)。這只是的粗略的估計(jì)，不過(guò)master和slave都是奔騰700MHz的機(jī)器且用100MBit/s網(wǎng)絡(luò)連接時(shí)就能達(dá)到這個(gè)速度了。slave上已經(jīng)完整拷貝master數(shù)據(jù)后，就可以連接到master上然后等待處理更新了。如果master當(dāng)機(jī)或者slave連接斷開(kāi)，slave會(huì)定期嘗試連接到master上直到能重連并且等待更新。重試的時(shí)間間隔由 --master-connect-retry 選項(xiàng)來(lái)控制，它的默認(rèn)值是60秒。每個(gè)slave都記錄了它關(guān)閉時(shí)的日志位置。msater是不知道有多少個(gè)slave連接上來(lái)或者哪個(gè)slave從什么時(shí)候開(kāi)始更新。
MySQL同步功能由3個(gè)線程(master上1個(gè)，slave上2個(gè))來(lái)實(shí)現(xiàn)。執(zhí)行 START SLAVE 語(yǔ)句后，slave就創(chuàng)建一個(gè)I/O線程。I/O線程連接到master上，并請(qǐng)求master發(fā)送二進(jìn)制日志中的語(yǔ)句。master創(chuàng)建一個(gè)線程來(lái)把日志的內(nèi)容發(fā)送到slave上。這個(gè)線程在master上執(zhí)行 SHOW PROCESSLIST 語(yǔ)句后的結(jié)果中的 Binlog Dump 線程便是。slave上的I/O線程讀取master的 Binlog Dump 線程發(fā)送的語(yǔ)句，并且把它們拷貝到其數(shù)據(jù)目錄下的中繼日志(relay logs)中。第三個(gè)是SQL線程，salve用它來(lái)讀取中繼日志，然后執(zhí)行它們來(lái)更新數(shù)據(jù)。如上所述，每個(gè)mster/slave上都有3個(gè)線程。每個(gè)master上有多個(gè)線程，它為每個(gè)slave連接都創(chuàng)建一個(gè)線程，每個(gè)slave只有I/O和SQL線程。在MySQL 4.0.2以前，同步只需2個(gè)線程(master和slave各一個(gè))。slave上的I/O和SQL線程合并成一個(gè)了，它不使用中繼日志。slave上使用2個(gè)線程的優(yōu)點(diǎn)是，把讀日志和執(zhí)行分開(kāi)成2個(gè)獨(dú)立的任務(wù)。執(zhí)行任務(wù)如果慢的話，讀日志任務(wù)不會(huì)跟著慢下來(lái)。例如，如果slave停止了一段時(shí)間，那么I/O線程可以在slave啟動(dòng)后很快地從master上讀取全部日志，盡管SQL線程可能落后I/O線程好幾的小時(shí)。如果slave在SQL線程沒(méi)全部執(zhí)行完就停止了，但I(xiàn)/O線程卻已經(jīng)把所有的更新日志都讀取并且保存在本地的中繼日志中了，因此在slave再次啟動(dòng)后就會(huì)繼續(xù)執(zhí)行它們了。這就允許在master上清除二進(jìn)制日志，因?yàn)閟lave已經(jīng)無(wú)需去master讀取更新日志了。執(zhí)行 SHOW PROCESSLIST 語(yǔ)句就會(huì)告訴我們所關(guān)心的master和slave上發(fā)生的情況。
下面我們來(lái)具體配置
1. 在主服務(wù)器上為從服務(wù)器建立一個(gè)用戶：
grant replication slave on *.* to '用戶名'@'主機(jī)' identified by '密碼'; （在MySQL 4.0.2以前，用 FILE 權(quán)限來(lái)代替 REPLICATION SLAVE）
如果打算在slave上執(zhí)行 LOAD TABLE FROM MASTER 或 LOAD DATA FROM MASTER 語(yǔ)句，那么必須給該帳戶授予附加權(quán)限：
授予全局 SUPER 和 RELOAD 權(quán)限。
授予對(duì)想要加載的所有表上的 SELECT 權(quán)限。在master上任何沒(méi)有 SELECT 權(quán)限的表都會(huì)被 LOAD DATA FROM MASTER 略過(guò)。
2. 編輯主服務(wù)器的配置文件：/etc/my.cnf
server-id = 1
log-bin
binlog-do-db=需要備份的數(shù)據(jù)庫(kù)名，如果備份多個(gè)數(shù)據(jù)庫(kù)，重復(fù)設(shè)置這個(gè)選項(xiàng)即可
binlog-ignore-db=不需要備份的數(shù)據(jù)庫(kù)苦命，如果備份多個(gè)數(shù)據(jù)庫(kù)，重復(fù)設(shè)置這個(gè)選項(xiàng)即可
3. 編輯從服務(wù)器的配置文件：/etc/my.cnf
server-id=2 （配置多個(gè)從服務(wù)器時(shí)依次設(shè)置id號(hào)）
master-host=主機(jī)
master-user=用戶名
master-password=密碼
master-port=端口
replicate-do-db=需要備份的數(shù)據(jù)庫(kù)名，如果備份多個(gè)數(shù)據(jù)庫(kù)，重復(fù)設(shè)置這個(gè)選項(xiàng)即可
記得先手動(dòng)同步一下主從服務(wù)器中要備份的數(shù)據(jù)庫(kù)，然后重啟主，從服務(wù)器。
要驗(yàn)證主從設(shè)置是否已經(jīng)成功，可以登錄從服務(wù)器輸入如下命令：
mysql> show slave statusG
得到的列表會(huì)有類似下面的數(shù)據(jù)：
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果后面兩個(gè)選項(xiàng)不全是Yes，那就說(shuō)明你前面某個(gè)步驟配置錯(cuò)了。
如果你的設(shè)置是正確的，嘗試在主服務(wù)器上插入若干條記錄，然后你再轉(zhuǎn)到從服務(wù)器，會(huì)發(fā)現(xiàn)相應(yīng)的新記錄已經(jīng)自動(dòng)同步過(guò)來(lái)了。
如果你的主從服務(wù)器已經(jīng)配置好了，那么你在應(yīng)用程序中，只要保證所有的insert/delete/update操作是在主服務(wù)器上進(jìn)行的，那么相應(yīng)的數(shù)據(jù)變化會(huì)自動(dòng)同步到從服務(wù)器上，這樣，我們就可以把select操作分擔(dān)到多臺(tái)從數(shù)據(jù)庫(kù)上，從而降低服務(wù)器的載荷。
如果你想使用復(fù)制數(shù)據(jù)文件的方式來(lái)備份數(shù)據(jù)庫(kù)，只要在從服務(wù)器上的mysql命令行先鍵入slave stop;然后復(fù)制數(shù)據(jù)庫(kù)文件，復(fù)制好了，再在mysql命令行鍵入slave start;啟動(dòng)從服務(wù)器，這樣就即備份了數(shù)據(jù)有保證了數(shù)據(jù)完整性，而且整個(gè)過(guò)程中主服務(wù)器的mysql無(wú)需停止。
-----------------------------------------------------------------------------------
提示：如果修改了主服務(wù)器的配置，記得刪除從服務(wù)器上的master.info文件。否則從服務(wù)器使用的還是老配置，可能會(huì)導(dǎo)致錯(cuò)誤。
-----------------------------------------------------------------------------------
注意：關(guān)于要復(fù)制多個(gè)數(shù)據(jù)庫(kù)時(shí)，binlog-do-db和replicate-do-db選項(xiàng)的設(shè)置，如果要備份多個(gè)數(shù)據(jù)庫(kù)，只要重復(fù)設(shè)置相應(yīng)選項(xiàng)就可以了。
比如：
binlog-do-db=a
binlog-do-db=b
replicate-do-db=a
replicate-do-db=b
-----------------------------------------------------------------------------------
補(bǔ)充：
在從服務(wù)器上使用show slave status
Slave_IO_Running,為No,則說(shuō)明IO_THREAD沒(méi)有啟動(dòng)，請(qǐng)執(zhí)行slave start [IO_THREAD]
Slave_SQL_Running為No則復(fù)制出錯(cuò),查看Last_error字段排除錯(cuò)誤后執(zhí)行slave start [SQL_THREAD]
查看Slave_IO_State字段
空 //復(fù)制沒(méi)有啟動(dòng)
Connecting to master//沒(méi)有連接上master
Waiting for master to send event//已經(jīng)連上
補(bǔ)充：可以使用LOAD DATA FROM MASTER語(yǔ)句來(lái)建立slave。但有約束條件：
數(shù)據(jù)表要全部是MyISAM表，必須有SUPER權(quán)限，master的復(fù)制用戶必須具備RELOAD和SUPER權(quán)限。
在master端執(zhí)行RESET MASTER清除已有的日志變更，
此時(shí)slave端會(huì)因?yàn)檎也坏絤aster日志無(wú)法啟動(dòng)IO_THREAD，請(qǐng)清空data目錄下
relay-log.info,hosname-relay-bin*等文件重新啟動(dòng)mysql
中繼日志文件默認(rèn)的文件為hostname-relay-bin.nnn和hostname-relay-bin.index。可用從服務(wù)器的--
relay-log和--relay-log-index選項(xiàng)修改。在從服務(wù)器中還有一個(gè)relay-log.info中繼信息文件，可用
--relay-log-info-file啟動(dòng)選項(xiàng)修改文件名。
雙機(jī)互備則是兩個(gè)mysql同時(shí)配置為master及slave
主服務(wù)器上的相關(guān)命令：
show master status
show slave hosts
show logs
show binlog events
purge logs to 'log_name'
purge logs before 'date'
reset master(老版本flush master)
set sql_log_bin=
從服務(wù)器上的相關(guān)命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此線程把master段的日志寫到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此線程把寫到本地的日志應(yīng)用于數(shù)據(jù)庫(kù)
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //動(dòng)態(tài)改變master信息
PURGE MASTER [before 'date'] 刪除master端已同步過(guò)的日志
