MySQL主從復(fù)制

MySQL的主從復(fù)制有利于數(shù)據(jù)庫(kù)架構(gòu)的健壯性、提升訪問(wèn)速度和易于維護(hù)管理。

• 主從服務(wù)器互為備份

• 主從服務(wù)器讀寫分離分擔(dān)網(wǎng)站壓力

• 根據(jù)服務(wù)器拆分業(yè)務(wù)獨(dú)立并分擔(dān)壓力

MySQL主從復(fù)制原理過(guò)程

以下簡(jiǎn)單描述下MySQL Replication的復(fù)制過(guò)程

1. slave服務(wù)器上執(zhí)行start slave命令,開啟主從復(fù)制開關(guān)；

2. slave服務(wù)器的IO線程會(huì)通過(guò) 在Master上授權(quán)的復(fù)制用戶權(quán)限請(qǐng)求連接Master服務(wù)器，并請(qǐng)求從指定binlog日志文件位置(日志文件名和位置在配置主從復(fù)制服務(wù)時(shí)執(zhí)行change master 命令時(shí)指定)之后發(fā)送binlog日志內(nèi)容；

3. master服務(wù)器接受來(lái)自slave服務(wù)器的IO線程的請(qǐng)求后，master服務(wù)器上負(fù)責(zé)復(fù)制的IO線程根據(jù)slave服務(wù)器的IO線程請(qǐng)求的信息讀取指定binlog日志文件指定位置之后的binlog日志信息，然后返回給slave的IO線程，返回的信息中除了binlog日志內(nèi)容外，還有本次返回日志內(nèi)容后再master服務(wù)器端的新的binlog文件名稱以及在binlog中的下一個(gè)指定更新位置；

4. 當(dāng)slave服務(wù)器的IO線程獲取到來(lái)自master服務(wù)器上IO線程發(fā)送日志內(nèi)容以及日志文件以及位置點(diǎn)后，將binlog日志內(nèi)容一次寫入到slave端自身的relaylog(中繼日志)文件(mysql-relay-bin.xxxxxx)的末尾，并將新的binlog文件名和位置記錄到master-info文件中，以便下次讀取master端新binlog日志時(shí)能夠告訴master服務(wù)器需要從新binlog日志的哪個(gè)文件哪個(gè)位置開始請(qǐng)求新的binlog日志內(nèi)容；

5. slave服務(wù)器的SQL線程會(huì)實(shí)時(shí)的檢測(cè)本地relaylog中新增加的日志內(nèi)容，然后及時(shí)的把log文件中的內(nèi)容解析成在master端曾經(jīng)執(zhí)行的SQL語(yǔ)句的內(nèi)容，并在自身的slave服務(wù)器上按語(yǔ)句的順序執(zhí)行應(yīng)用這些SQL語(yǔ)句，應(yīng)用完畢后清理用過(guò)的日志；

6. 經(jīng)過(guò)上面的過(guò)程，就可以確保在master端和slave端執(zhí)行了同樣的SQL語(yǔ)句。當(dāng)復(fù)制狀態(tài)正常的情況下，master端和slave端的數(shù)據(jù)是完全一樣的，MySQL的同步機(jī)制是有一些特殊的情況，具體請(qǐng)參考官方的說(shuō)明，大多數(shù)情況下，我們不用擔(dān)心。

主數(shù)據(jù)庫(kù)配置步驟

1. 開啟主數(shù)據(jù)庫(kù)的binlog（二進(jìn)制日志功能），并設(shè)置server-id

2. 創(chuàng)建用于同步數(shù)據(jù)的賬號(hào)rep

3. 鎖表并查看當(dāng)前日志名稱和位置(pos)[如果備份時(shí)使用了master-info參數(shù)可不鎖表]

4. 備份當(dāng)前主數(shù)據(jù)庫(kù)的全部數(shù)據(jù)（全備）

5. 解鎖主數(shù)據(jù)庫(kù)，讓主數(shù)據(jù)庫(kù)繼續(xù)提供服務(wù)

6. 繼續(xù)往主數(shù)據(jù)庫(kù)寫數(shù)據(jù)

從數(shù)據(jù)庫(kù)配置步驟

1. 把主數(shù)據(jù)庫(kù)備份的全備數(shù)據(jù)恢復(fù)到從數(shù)據(jù)庫(kù)

2. 設(shè)置從數(shù)據(jù)庫(kù)server-id，并確保這個(gè)ID沒(méi)有被別的MySQL服務(wù)所使用。

3. 在從數(shù)據(jù)庫(kù)上設(shè)置主從同步的相關(guān)信息，如主數(shù)據(jù)庫(kù)服務(wù)器的IP地址、端口號(hào)、同步賬號(hào)、密碼、binlog文件名、binlog位置(pos)點(diǎn)

4. 開始主從同步start slave；

5. 查看是否同步成功，show slave status\G;

MySQL主從復(fù)制生產(chǎn)標(biāo)準(zhǔn)實(shí)踐過(guò)程

MySQL主從同步配置步驟

1. 準(zhǔn)備兩臺(tái)數(shù)據(jù)庫(kù)環(huán)境，或者單臺(tái)多實(shí)例環(huán)境，保證能正常啟動(dòng)和登錄；

2. 配置my.cnf文件,主庫(kù)配置log-bin和server-id參數(shù)；從庫(kù)配置server-id(不能喝主庫(kù)和其他從庫(kù)一致)，一般不開啟

   log-bin

   從庫(kù)功能。（注意：修改完后重啟生效）；

3. 登錄主庫(kù)使用grant語(yǔ)句增加用于從庫(kù)連接主庫(kù)同步的賬號(hào)，例如：rep并授予

   replication slave

   同步的權(quán)限；

4. 登錄主庫(kù)，使用

   flush table with read lock

   （在同步的過(guò)程中窗口關(guān)閉失效，超時(shí)參數(shù)[

   interactive_timeout

   和

   wait_timeout

   ]到了也失效），然后

   show master status

   查看binlog的位置狀態(tài)；

5. 新增加一個(gè)窗口，linux命令行備份或?qū)С鲈袛?shù)據(jù)庫(kù)數(shù)據(jù)，并拷貝到從庫(kù)所在服務(wù)器（如果數(shù)據(jù)量很大，并且允許停機(jī)，可以停機(jī)打包，而不用

   mysqldump

   ）；

6. unlock tables

   解鎖主庫(kù)；

7. 把主庫(kù)到處的原有數(shù)據(jù)備份恢復(fù)到從庫(kù)；

8. 根據(jù)主庫(kù)的

   show master； status

   查看到的binlog日志文件名和位置信息，在從庫(kù)上執(zhí)行

   change master to ...

   語(yǔ)句；

9. 從庫(kù)

   start slave

   開啟同步開關(guān)；

10. 從庫(kù)

    show slave status\G

    檢查同步狀態(tài)，并在主庫(kù)進(jìn)行更新測(cè)試。

注意：
5.1版本的MySQL版本的鎖表語(yǔ)句是flush tables with read lock;
5.5版本的MySQL的鎖表語(yǔ)句是flush table with read lock;

定義服務(wù)器角色

主庫(kù)(mysql master): [ip為192.168.247.129的3306端口]

從庫(kù)(mysql slave):[ip為192.168.247.129的3309端口]

數(shù)據(jù)庫(kù)環(huán)境準(zhǔn)備

具備單機(jī)單數(shù)據(jù)庫(kù)多實(shí)例的環(huán)境或者兩臺(tái)服務(wù)器每臺(tái)機(jī)器有一個(gè)數(shù)據(jù)的環(huán)境

主庫(kù)上需要執(zhí)行的操作

編輯主庫(kù)的配置文件

打開binlog日志(從庫(kù)不做級(jí)聯(lián)的話不建議開啟)和配置不重復(fù)的server-id

[root@curder.centos5 /usr/local/mysql/data]
# egrep 'log-bin|server-id' /usr/local/mysql/data/3306/my.cnf 
log-bin=mysql-bin
server-id = 3306

查看開啟結(jié)果

# mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock -e "show variables like 'log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

建立同步賬號(hào)密碼

首先登陸mysql3306實(shí)例主數(shù)據(jù)庫(kù)
mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock

執(zhí)行授權(quán)語(yǔ)句

mysql> grant replication slave on *.* to 'rep'@'192.168.0.%' identified by 'reppasswd';
mysql> flush privileges;

授權(quán)局域網(wǎng)內(nèi) rep 用戶通過(guò) reppasswd 密碼進(jìn)行同步

另外：如果不存在全備 需要在主庫(kù)上執(zhí)行如下語(yǔ)句獲得全備

mysql> flush table with read lock; #　備份期間禁止用戶寫操作(窗口不允許關(guān)閉)

備份語(yǔ)句,獲得全備

# mysqldump -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock -B curder --events --master-data=2 > rep.sql

最后操作完別忘了解除鎖定 unlock tables;

注意：
5.1版本的MySQL版本的鎖表語(yǔ)句是flush tables with read lock;
5.5版本的MySQL的鎖表語(yǔ)句是flush table with read lock;

從庫(kù)上需要執(zhí)行的操作

將全備導(dǎo)入到數(shù)據(jù)庫(kù)

mysql -uroot -paaaaaa -S /usr/local/mysql/data/3308/mysql.sock < ~/rep.sql

登陸從庫(kù)執(zhí)行change master語(yǔ)句

change master to 
master_host='192.168.0.15',
master_port=3306,
master_user='rep', # 賬號(hào)信息為主庫(kù)建立的用戶信息
master_password='reppasswd', # 在主庫(kù)設(shè)置同步賬戶時(shí)設(shè)置
master_log_file='mysql-bin.000003', # 該值通過(guò)在mysql master服務(wù)器全備前鎖定的值上show master status;查看
master_log_pos=2840; # 該值通過(guò)在master服務(wù)器上show master status;查看

上述語(yǔ)句信息以文件的形式存放在從庫(kù)的master.info中。

開啟同步

start slave

查看是否同步成功語(yǔ)句

show slave status\G

Slave_IO_Running: Yes # 從庫(kù)IO進(jìn)程(從master服務(wù)器取log的線程)
Slave_SQL_Running: Yes # 從庫(kù)SQL進(jìn)程(讀取relaylog 寫數(shù)據(jù))
Seconds_Behind_Master: 0 # 落后主庫(kù)的秒數(shù)

另外

一些常用語(yǔ)句

show master status; # 查看master的狀態(tài), 尤其是當(dāng)前的二進(jìn)制日志及位置
show slave status; # 查看slave的狀態(tài).
reset slave;      # 重置slave狀態(tài).
start slave;     # 啟動(dòng)slave狀態(tài)(一旦啟動(dòng) 則開始監(jiān)聽msater的變化)
stop slave;     # 暫停slave狀態(tài);

MySQL主從復(fù)制原理要點(diǎn)

1. 異步方式同步；

2. 邏輯同步模式，多種模式，默認(rèn)是通過(guò)SQL語(yǔ)句執(zhí)行；

3. 主庫(kù)通過(guò)記錄binlog實(shí)現(xiàn)對(duì)從庫(kù)的同步；

4. 主庫(kù)1個(gè)IO線程，從庫(kù)1個(gè)IO線程和一個(gè)SQL線程來(lái)完成；

5. 從庫(kù)關(guān)鍵字文件

   master.info

   ,

   relay-log

   ,

   relay-info

   功能，

6. 如果從庫(kù)還想級(jí)聯(lián)從庫(kù)，需要打開

   log-bin

   和

   log-slave-updates

   參數(shù)

生產(chǎn)場(chǎng)景快速配置MySQL主從復(fù)制方案

1. 安裝好要配置的主從的數(shù)據(jù)庫(kù)，配置好log-bin和server-id參數(shù)

2. 無(wú)需配置主庫(kù)my.cnf文件，主庫(kù)的log-bin和server-id參數(shù)默認(rèn)就是配置好的

3. 登錄主庫(kù)增加用于從庫(kù)連接主庫(kù)同步的賬號(hào)，例如：rep，并授權(quán)replication slave同步的權(quán)限

4. 使用凌晨mysqldump命令帶--master-data=1備份的全備數(shù)據(jù)恢復(fù)到從庫(kù)

5. 在從庫(kù)執(zhí)行change master to...語(yǔ)句，無(wú)需binlog文件以及對(duì)應(yīng)位置點(diǎn)(在步驟4中以及通過(guò)備份參數(shù)指定)

6. 從庫(kù)start slave開啟同步開關(guān)

7. 從庫(kù)show slave status\G，檢查同步狀態(tài)，并在主庫(kù)進(jìn)行更新測(cè)試

MySQL線程狀態(tài)

復(fù)制主線程狀態(tài)show processlist\G

1. sending binlog event to slave
   二進(jìn)制日志由各種事件組成，一個(gè)事件通常為一個(gè)更新加一些其他信息。線程已經(jīng)從二進(jìn)制日志讀取了一個(gè)事件并且正將它發(fā)送到從服務(wù)器

2. finished reading one binlog;switching to next binlog
   線程已經(jīng)讀完二進(jìn)制日志文件并且正打開下一個(gè)要大宋到服務(wù)器的日志文件

3. has send all binlog to slave;waiting for binlog to be updated
   線程已經(jīng)從二進(jìn)制日志讀取所有主要的更新并已經(jīng)發(fā)送到了從服務(wù)器。 現(xiàn)在正空閑，等待由主服務(wù)器上新的更新導(dǎo)致的出現(xiàn)在二進(jìn)制日志中的新事件

4. waiting to finalize termination
   線程停止時(shí)發(fā)生的一個(gè)很簡(jiǎn)單的狀態(tài)

復(fù)制從I/O線程狀態(tài)

該狀態(tài)出現(xiàn)在Slave_IO_State列，可以通過(guò)show slave status顯示。

• Connection to master
  線程正試圖連接主服務(wù)器

• Checking master version
  建立同主服務(wù)器之間的連接后立即臨時(shí)出現(xiàn)的狀態(tài)

• Registering slave onmaster
  建立同主服務(wù)器之間的連接后立即臨時(shí)出現(xiàn)的狀態(tài)

• Requesting binlog dump
  建立同主服務(wù)器之間的連接后立即臨時(shí)出現(xiàn)的狀態(tài),線程向主服務(wù)器發(fā)送一條請(qǐng)求，索取從請(qǐng)求的二進(jìn)制日志文件名和位置開始的二進(jìn)制日志的內(nèi)容

• Waiting to reconnect after a failed binlog request
  如果二進(jìn)制日志轉(zhuǎn)儲(chǔ)請(qǐng)求失敗(由于沒(méi)有連接)，線程進(jìn)入休眠裝填，然后定期嘗試重新連接。可以使用--master-connect-retry選項(xiàng)指定重試之間的間隔

• Reconnecting after a failed binlog dump request
  線程正嘗試重新連接主服務(wù)器

• Waiting for master to send event
  線程已經(jīng)連接上主服務(wù)器，正等待二進(jìn)制日志事件到達(dá)。如果主服務(wù)器正空閑，會(huì)持續(xù)較長(zhǎng)的時(shí)間。如果等到持續(xù)slave_read_timeout秒，則發(fā)生超時(shí)。此時(shí)線程認(rèn)為連接被中斷并企圖重新連接。

• Queueing master event to the relay log
  線程已經(jīng)讀取一個(gè)事件，正將它復(fù)制到中繼日志供SQL線程來(lái)處理

• Waiting to reconnect after a failed master event read
  讀取時(shí)(由于沒(méi)有連接)出現(xiàn)錯(cuò)誤。線程企圖重新連接前將休眠master-connect-retry秒

• Reconnectiong after a failed master event read
  線程正嘗試重新連接主服務(wù)器，當(dāng)連接重新建立后，狀態(tài)變?yōu)?!-- /react-text -->Waiting for master to send

• Waiting for slave SQL thread to free enough relay log space
  正使用一個(gè)非零relay_log_space_limit值，中繼日志已經(jīng)增長(zhǎng)到其組合大小超過(guò)該值。I/O線程正等待知道SQL線程處理中繼日志內(nèi)容并刪除部分中繼日志文件來(lái)釋放足夠的空間

• Waiting for slave mutex on exit
  線程停止時(shí)發(fā)生的一個(gè)很簡(jiǎn)單的狀態(tài)，I/O線程的State列也可以顯示語(yǔ)句的文本，這說(shuō)明已經(jīng)從中繼日志讀取了一個(gè)事件，從中提取了語(yǔ)句，并且正在執(zhí)行語(yǔ)句。

復(fù)制從SQL線程狀態(tài)

• Reading event from the relay log
  線程已經(jīng)從中繼日志讀取一個(gè)事件，可以對(duì)事件進(jìn)行處理

• Has read all relay log;waiting for the slave I/O thread to update it
  線程已經(jīng)處理了中繼日志文件中的所有事件，現(xiàn)在正等待I/O線程將新事件寫入中繼日志