MySQL主從復(fù)制

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

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

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

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

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

以下簡單描述下MySQL Replication的復(fù)制過程

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

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

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

4. 當(dāng)slave服務(wù)器的IO線程獲取到來自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è)位置開始請求新的binlog日志內(nèi)容；

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

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

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

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

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

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

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

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

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

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

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

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

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

4. 開始主從同步start slave；

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

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

MySQL主從同步配置步驟

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

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

   log-bin

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

3. 登錄主庫使用grant語句增加用于從庫連接主庫同步的賬號，例如：rep并授予

   replication slave

   同步的權(quán)限；

4. 登錄主庫，使用

   flush table with read lock

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

   interactive_timeout

   和

   wait_timeout

   ]到了也失效），然后

   show master status

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

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

   mysqldump

   ）；

6. unlock tables

   解鎖主庫；

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

8. 根據(jù)主庫的

   show master； status

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

   change master to ...

   語句；

9. 從庫

   start slave

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

10. 從庫

    show slave status\G

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

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

定義服務(wù)器角色

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

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

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

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

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

編輯主庫的配置文件

打開binlog日志(從庫不做級聯(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    |
+---------------+-------+

建立同步賬號密碼

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

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

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

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

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

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

備份語句,獲得全備

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

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

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

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

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

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

登陸從庫執(zhí)行change master語句

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

上述語句信息以文件的形式存放在從庫的master.info中。

開啟同步

start slave

查看是否同步成功語句

show slave status\G

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

另外

一些常用語句

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

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

1. 異步方式同步；

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

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

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

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

   master.info

   ,

   relay-log

   ,

   relay-info

   功能，

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

   log-bin

   和

   log-slave-updates

   參數(shù)

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

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

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

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

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

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

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

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

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è)很簡單的狀態(tài)

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

該狀態(tài)出現(xiàn)在Slave_IO_State列，可以通過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ā)送一條請求，索取從請求的二進(jìn)制日志文件名和位置開始的二進(jìn)制日志的內(nèi)容

• Waiting to reconnect after a failed binlog request
  如果二進(jìn)制日志轉(zhuǎn)儲請求失敗(由于沒有連接)，線程進(jìn)入休眠裝填，然后定期嘗試重新連接?？梢允褂?!-- /react-text -->--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ù)器正空閑，會持續(xù)較長的時(shí)間。如果等到持續(xù)slave_read_timeout秒，則發(fā)生超時(shí)。此時(shí)線程認(rèn)為連接被中斷并企圖重新連接。

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

• Waiting to reconnect after a failed master event read
  讀取時(shí)(由于沒有連接)出現(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)增長到其組合大小超過該值。I/O線程正等待知道SQL線程處理中繼日志內(nèi)容并刪除部分中繼日志文件來釋放足夠的空間

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

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

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

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