MySQL 主從同步操作文檔

MySQL 主從同步操作文檔

取自 NSOP-WIKI

目錄

1 同步原理說明

2 同步設定基本步驟(以下設定均涉及my.cnf檔案的修改)

2.1 設定主(she4 zhi4 zhu3)伺服器:啟用bin-log日誌、同步帳號

2.2 設定從伺服器:設定需要同步的內容

2.3 拷貝主伺服器資料到從伺服器

2.3.1 方式1:停止主伺服器或其中一台從伺服器並拷貝資料

2.3.2 方式2：在主伺服器或其中一台從伺服器上設定鎖，禁止一切讀寫操作並拷貝資料

2.4 啟動同步

3 執行個體參考:

4 FAQ

1 同步原理說明

MySQL的Replication基於主伺服器在二進位日誌中跟蹤所有對資料庫的更改(更新、刪除等)。

MySQL使用3個線程來完成Replication工作，具體分布是(gong1 zuo1 _ju4 ti3 fen1 bu4 shi4)主上1個相關線程、從上2個相關線程；

主的相關線程可以理解為主伺服器上SHOW PROCESSLIST的輸出中的Binlog Dump線程、從伺服器分別為IO和SQL線程;

主
伺服器建立將binlog中的內容發送到從伺服器。從伺服器I/O線程讀取主伺服器Binlog
Dump線程發送的內容並將該資料拷貝到從伺服器資料目錄中的中繼記錄檔（relay-log）裡，SQL線程用於讀取中繼(xian4 cheng2
yong4 yu2 du2 qu3 zhong1 ji4)日誌並執行日誌中包含的更新。

MySQL的Replication是單向，非同步同步

MySQL同
步機制基於master把所有對資料庫的更新、刪除等)都記錄在二進位日誌裡。因此，想(dou ji lu zai er jin zhi ri
zhi li _yin ci _xiang)要啟用同步機制，在master就必須啟用二進
制日誌。每個slave接受來自master上在二進位日誌中記錄的更新操作，因此在slave上執行了這個操作的一個拷貝。應該非常重要地意識到，二進
制日誌只是從啟用二進位日誌開始的時刻才記錄更新操作的。所有的
slave必須在啟用二進位日誌時把master上已經存在的資料拷貝過來。如果運行同步時slave上的資料(shang4 de0 shu3
ju4)和master上啟用二進位日誌時的資料不
一致的話，那麼slave同步就會失皬嫞把master上的資料拷貝過來的方法之一實在slave上執行 LOAD DATA FROM MASTER
語句。不過要注意(yu ju _bu guo yao zhu yi)，LOAD DATA FROM MASTER 是從MySQL 4.0.0
之後才開始可以(zhi hou cai kai shi ke yi)用的，而且只支援master上的 MyISAM
類型表。同樣地，這個操作需要一個全域的讀鎖，這樣的話傳送記錄到slave的時候在master上就不會有更新操作了。(shang jiu bu
hui you geng xin cao zuo le _)當實現了自由鎖表熱備份時(在 MySQL
5.0中)，全域讀鎖就沒必要了。由於有這些限制，因此我們建議只在master上相關資料比較小的時候才執行 LOAD DATA FROM
MASTER 語句，或(yu3 ju4 _huo4)者在master上允許一個長時間的讀鎖。由於每個系統之間 LOAD DATA FROM
MASTER
的速度各不一樣，一個比較好的衡量規則是每秒能拷貝1MB資料。這隻是的粗略的估計，不過master和slave都是奔騰700MHz的機器且用
100MBit/s網路連接時就能達到這個速度了。slave上已經完整拷貝master資料後，就可以串連到master上然後等待處理更新了
(shang ran hou deng dai chu li geng xin le)。如果
master當機或者slave串連斷開，slave會定期嘗試串連到master上直到能重連並且等待更新。重試的時間間隔由
--master-connect-retry 選項來控制，它的默(xuan3 xiang4 lai2 kong4 zhi4 _ta1 de0
mo4)認值是60秒。每個slave都記錄了它關閉時的日誌位置。master是不知道有多少個slave串連(lian
jie)上來或者哪個slave從什麼 時候開始更新。

MySQL同步功能由3個線
程(master上1個，slave上2個)來實現。執行 START SLAVE 語句後，slave就創(jiu
chuang)建一個I/O線程。I/O線程串連到master上，並請求master發送二進位日誌中的語句。master建立一個(chuang
jian yi ge)線程來把日 志的內容發送到slave上。這個線程在master上執行 SHOW PROCESSLIST 語句後的結果中的
Binlog Dump 線程便是。slave上的I/O線程讀取master的 Binlog Dump
線程發送的語句，並且把它們拷貝到其資料目錄下的中繼日誌(relay
logs)中。第三個是SQL線程，salve用它來讀取中繼日誌，然後執行它們來更新資料。如上所述，每個mster/slave上都有3個線程。每個
master上有多個線程，它為每個slave串連都建立一個線程，每個slave只有I/O和SQL線程。在MySQL 4.0.2
以前，同步只需2個線程(master和slave各一個)。slave上的I/O和SQL線程合并成一個了，它不使用中繼日誌。slave上
使用2個線程的優點是，把讀日誌和執行分開成2個獨立的任務。執行任務如果慢的話，讀日誌任務不會跟著慢下來。例如，如果slave停止了一段時間，那麼

I/O線程可以在slave啟動後很快地從master上讀取全部日誌，儘管SQL線程可能落後I/O線程好幾的小時。如果slave在SQL線程沒全部
執行完就停(zhi xing wan jiu
ting)止了，但I/O線程卻已經把所有的更新日誌都讀取並且儲存在本地的中繼日誌（relay-log）中了，因此在slave再次啟動後就會繼續執
行它們了。這就允許在 master上清除二進位日誌，因為slave已經無需去master讀取更新日誌了。執行 SHOW PROCESSLIST
語句就會告訴我們所關心的master和slave上發生的情況。

2 同步設定基本(tong bu she zhi ji ben)步驟(以下設定均涉及my.cnf檔案的修改)

2.1 設定主伺服器:啟用bin-log日誌、同步帳號

server-id = n #設定ID

log-bin #啟用bin-log記錄，如需要指定binlog檔案名稱，可使用如下文法

#log-bin=binlog #binlog檔案名稱為binlog.0，binlog.1……

set-variable=binlog-ignore-db=mysql #不記錄資料庫mysql的更新日誌

binlog-do-db = #記錄指定的資料庫的更新日誌，還可以單獨處理只更新表內容

此外，需要注意兩個環境變數：

innodb_flush_log_at_trx_commit=1 #Default: 1。在使用了事務的資料庫中，強烈建議該值顯式的設定為1，以確保在每個事務提交時，日誌緩衝被寫到記錄檔，並將該寫操作同步到磁碟上

sync_binlog=1 #在每進行1次binlog寫操作後就將該操作同步到硬碟上。設為更高的值可以有效降低io並獲得更好的效能，但會增加bilog丟失的風險

為從伺服器上的同步帳號授權：

mysql>GRANT REPLICATION SLAVE ON *.* TO 同步帳號的使用者名稱@從伺服器ip地址 IDENTIFIED BY '同步帳號的密碼';

如果想要從伺服器上有許可權執行”LOAD TABLE FROM MASTER”和”LOAD DATA FROM MASTER”還需要增加”FILE”和”SELECT”許可權;

2.2 設定從伺服器:設定需要同步的內容

server-id = n #設定ID

master-host = #設定主伺服器的ip

master-user = #設定同步帳戶的使用者名稱

master-password = #設定(she4 zhi4)同步帳戶的密碼

master-port = #設定TCP／IP連接埠，預設為3306

set-variable=replicate-ignore-db=mysql #設定忽略的同步資料庫名

set-variable=replicate-db-db= #設定同pass_adm步的資料庫名若需要單獨設定可以針對錶級設定的資料多個，請設定多次

這裡的設定可以在後面登陸進從資料庫之後用CHANGE MASTER TO來修改。

2.3 拷貝主伺服器資料到從伺服器

2.3.1 方式1:停止(ting2 zhi3)主伺服器或其中一台從伺服器並拷貝資料

在需要拷貝資料的伺服器上停止mysqld，完全停止服務。如果是從伺服器，則需要在停止mysqld之前，停止同步進程。

mysql>stop slave;

$mysqladmin -uroot -p shutdown

確認mysqld停止之後，將其中的資料檔案ib*，master.info需要同步的資料庫目錄和其他相關檔案拷貝到新機器上。binlog和relay-log不用拷貝

2.3.2 方式2：在主伺服器或其中一台從伺服器上設定鎖，禁止一切讀寫操作並拷貝資料

在主伺服器上執行:

mysql>FLUSH TABLES WITH READ LOCK;

匯出資料到從資料庫(可以使用tar、scp、mysqldump等方式，此處實現比較靈活):

$cd ~/mysql/

$tar zcvf var.tar.gz var/ #資料目錄可能不為var目錄

然後是拷貝此var.tar.gz包到從伺服器並解壓縮到嫌う目錄

在主伺服器上執行:

mysql>UNLOCK TABLES;

說
明： 拷貝主伺服器的資料到從伺服器的實現方式太多：可以直接停止mysqld程式拷(cheng xu
kao)貝資料、可以直接tar方式打包處理、可以直接scp方式處理、可以直接匯出嫌う表結構等，具體實現方式看具體應用，適而擇之，主要保證在拷貝數
據的時候沒有資料寫入和更改即可

2.4 啟動同步

啟動從資料庫的mysqld推薦使用--skip-slave-start啟動，進入mysql後再start slave啟動同步。

說
明：
通常使用的mysqld_safe會記住啟動時的參數，例如--skip-slva-start。如果從庫因為意外原因重啟，且該資料庫啟動的時候使用了
mysqld_safe --skip-slave-start，則需要手工啟動同步進程，否則同步進程會始終處於停止狀態

從伺服器上執行：

mysql>SHOW SLAVE STATUS

若Slave_IO_Running 和 Slave_SQL_Running 兩行的值都為 "Yes"，這表明 Slave 的 I/O 和 SQL 線程都在正常運行。

說明： 推薦使用CHANGE MASTER TO 語句進行同步參數設定; 具體參照help change master to文法如下：

master_def:aster_def [ master_def] ...

master_def:

MASTER_HOST = 'host_name'

MASTER_USER = 'user_name'

MASTER_PASSWORD = 'password'

MASTER_PORT = port_num

MASTER_CONNECT_RETRY = count

MASTER_LOG_FILE = 'master_log_name'

MASTER_LOG_POS = master_log_pos

RELAY_LOG_FILE = 'relay_log_name'

RELAY_LOG_POS = relay_log_pos

MASTER_SSL =

MASTER_SSL_CA = 'ca_file_name'

MASTER_SSL_CAPATH = 'ca_directory_name'

MASTER_SSL_CERT = 'cert_file_name'

MASTER_SSL_KEY = 'key_file_name'

MASTER_SSL_CIPHER = 'cipher_list'

3 執行個體參考:

主要資料庫A的my.cnf配置：

server-id = 1 #設定ID

log-bin #啟用bin-log記錄

binlog-do-db = iknow #記錄指定庫的binlog更新記錄，每個資料庫一行

binlog-do-db = iknow_adm

binlog-do-db = ...

現在，新搭建一個從資料庫B。需要進行如下步驟

1.修改設定檔: 修改my.cnf，加入如下項(_jia ru ru xia xiang)：

server-id = 2 #指定server id，每台機器的id必須保證唯一

log-bin #啟用binlog記錄

master-connect-retry = 60 #串連主庫的稍候再試

master-host = xxx.xxx.xxx.xxx #指定主庫A的IP，不能用虛IP

master-port = 3306 #指定主庫連接埠

master-user = rep #指定同步處理的使用者的使用者名稱為(zhi ding tong bu yong hu de yong hu ming wei)replication

master-password = rep #指定同步處理的使用者的密碼為PassWORd

replicate-do-db = iknow #指定需要同步的庫，每個庫獨佔一行

replicate-do-db = iknow_adm

replicate-do-db = ...

log-slave-updates #在從庫上對同步進行日誌記錄，磁碟佔用量較大，建議從庫運行穩定後將其去除，如要作為第二層主要資料庫，則此項必須

這裡的設定可以在後面登陸進從資料庫之後用CHANGE MASTER TO來修改。

2.拷貝資料:從一台從資料庫C拷貝資料到B 從資料庫沒有對外提供服(cong2 shu3 ju4 ku4 mei2 you3 dui4 wai4 ti2 gong1 fu2)務，因此可以直接停掉從資料庫拷貝資料。

注意：

停
從資料庫的時候(ting cong shu ju ku de shi hou)需要顯式的執行stop slave，並執行show slave
status，確認Read_Master_Log_Pos和Exec_Master_Log_Pos一致，再執行mysqladmin -uroot
-p shutdown停掉mysqld。

mysql>stop slave;

mysql>show slave status

確認Read_Master_Log_Pos和Exec_Master_Log_Pos一致

$ mysqladmin -uroot -p shutdown

待mysqld完全退出後，將var目錄下的ib*，master.info，以及需要同步的庫cp到本地目錄var.$DATE下（主要是為了縮短C上mysqld的服務停止時間）

$ cd $MYSQL/var; mkdir ../var.$DATE

$ cp -r ib* iknow iknow_adm master.info ../var.$DATE/

$ scp $MYSQL/var.$DATE B:~/mysql

3.在主要資料庫上為一台從庫授權同步

mysql>GRANT REPLICATION SLAVE ON *.* TO replication@B機器的ip地址 IDENTIFIED BY 'Password;

4.在B上啟動mysqld

$ mysqld_safe –-skip-slave-start &

5.進入mysql觀察slave status

mysql> show slave status

6.觀察master.info和relay-log.info

$ cat $MYSQL/master.info

$ cat $MYSQL/relay-log.info

在這裡不需要change master一步是因為master.info中已記錄了master_log_file和master_log_pos等相關資訊。這是一種比較保險而且簡單的辦法

7.複查確認my.cnf中replication slave配置正確如果master.info檔案存在的話，mysql是
優先讀取它的，但在某些情況下，比如mysqld異常重啟等，有可能會讀取 my.cnf中配置，因此需要保證my.cnf中的replication
slave配置正確
由於mysqld在正常情況下優先讀取master.info，因此，在主要資料庫上一定要保證master.info是不存在的，否則可能出現不
(shi4 bu4 cun2 zai4 de0 _fou3 ze2 ke3 neng2 chu1 xian4 bu4)可預知的 後果。

8. CHANGE MASTER TO語句這是用於在mysql命令列中顯式的更改主庫的語句。在mysql命令列中執(ming ling xing zhong zhi)行

mysql> help change master to

可獲得關於這條語句的詳細資料。如果在進行主從資料庫切換後，或需要臨時更改主庫，而又不想重啟資料庫的時候，可以使用如下語句動態更改主庫：

mysql>
CHANGE MASTER TO MASTER_HOST=xxx.xxx.xxx.xxx MASTER_USER='replication'
MASTER_PASSWORD='Password' MASTER_LOG_FILE='master-binlog.'
MASTER_LOG_POS='';

以上語句各項含義如下：

MASTER_HOST主庫的機器名或IP地址

MASTER_USER主庫為從庫授權的同步帳號

MASTER_PASSWORD主庫為(zhu3 ku4 wei4)從庫授權的同步帳號密碼

MASTER_LOG_FILE主庫的binlog檔案名稱

MASTER_LOG_POS主庫的binlog檔案位移(wen jian pian yi)

MASTER_LOG_FILE
和MASTER_LOG_POS兩項需要在啟動主庫之(liang3 xiang4 xu1 yao4 zai4 qi3 dong4 zhu3 ku4
zhi1)前執行show master status記錄下來，並且必須保證準確無誤，否則會出現同步失敗的情況。

4 FAQ

1.
scp了整個var目錄到新的從庫(mu lu dao xin de cong ku)，start slave後，發現同步(hou _fa
xian tong bu)無法正常進行，Slave_IO_Running正常，但Slave_SQL_Running的狀態為No，並且mysql錯誤記錄檔中報找不到一個releylog檔案

答：這是由於reley-log.info檔案的存在造成的，它記錄了資料庫當前正在使用的relaylog，而mysql默
認的relaylog
命名是以機器名命名的，因此如果從一台機器拷貝到另一台機器，它是找不到這個檔案的。如果可以確認Read_Master_Log_Pos和
Exec_Master_Log_Pos一致，那麼可以直接將relay-log.info刪除，重啟mysqld，再start
slave就可以解決。

2. start slave後，
Slave_IO_Running和Slave_SQL_Running的狀態都為No，並且日誌中報類似“Slave I/O thread:
Failed reading log event reconnecting to retry log 'tc-nsop-test00-bin.'
position 08”的錯誤

答：這是由於主庫對從庫(da _zhe shi you yu zhu ku dui cong ku)的同步帳號授權不正確造成的，更改並確認授權正確之後，重新start slave，就可以正常同步。

3. 如果錯誤記錄檔中出現如下提示“Warning: You should set server-id to a non-0 value if master_host is set; we force server id to 2 but this MySQL server will not act as a slave.”，並且 Slave_IO_Running和Slave_SQL_Running的狀態都為No

答：檢查主庫的my.cnf，這樣的錯誤是由於沒有設定主庫的server id或者server id不合法造成的。更改主庫server id並重啟主庫後，start slave，同步可以恢複正常。

4. 如果Slave_SQL_Running狀態為No，並且錯誤記錄檔中有類似“Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: ……”這樣的錯誤

答：這是由於同步某個行的時候恰好遇到有其他SQL進程對該行進行了鎖定，並且鎖定時間較長導致同步進程等待逾時。直接start slave即可。

5. 同時啟動多台從庫的同步進程對主庫有什麼影響

答：通常情況會導致主庫的io和網卡流量增加。MySQL的Binlog Dump進程是沒有限速的，因此會全速進行binlog讀取和資料分發，給主庫帶來較大的負擔。在實際操作中，盡量依次啟動從庫，並在啟動後觀察主庫 io，確保Binlog Dump進程讀取完binlog後，再啟動下一台。