MYSQL 主從配置詳解

hot0539

一、主從配置的原理:

MySQL的 Replication 是一個(gè)異步的復(fù)制過(guò)程，從一個(gè) MySQL instace(我們稱之為 Master)復(fù)制到另一個(gè)MySQL instance(我們稱之 Slave)。在 Master 與 Slave之間的實(shí)現(xiàn)整個(gè)復(fù)制過(guò)程主要由三個(gè)線程來(lái)完成，其中兩個(gè)線程(Sql線程和IO線程)在 Slave 端，另外一個(gè)線程(IO線程)在 Master端。

要實(shí)現(xiàn) MySQL 的 Replication ，首先必須打開(kāi) Master 端的Binary Log(MySQL-bin.xxxxxx)功能，否則無(wú)法實(shí)現(xiàn)。因?yàn)檎麄�(gè)復(fù)制過(guò)程實(shí)際上就是Slave從Master端獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作。打開(kāi) MySQL 的 Binary Log 可以通過(guò)在啟動(dòng) MySQL Server 的過(guò)程中使用“—log-bin” 參數(shù)選項(xiàng)，或者在 my.cnf 配置文件中的 MySQLd 參數(shù)組([MySQLd]標(biāo)識(shí)后的參數(shù)部分)增加“l(fā)og-bin” 參數(shù)項(xiàng)。

MySQL 復(fù)制的基本過(guò)程如下：

 1. Slave 上面的IO線程連接上 Master，并請(qǐng)求從指定日志文件的指定位置(或者從最開(kāi)始的日志)之后的日志內(nèi)容;

2. Master 接收到來(lái)自 Slave 的 IO 線程的請(qǐng)求后，通過(guò)負(fù)責(zé)復(fù)制的 IO線程根據(jù)請(qǐng)求信息讀取指定日志指定位置之后的日志信息，返回給 Slave 端的 IO線程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息在 Master 端的 Binary Log 文件的名稱以及在 BinaryLog 中的位置;

 3. Slave 的 IO 線程接收到信息后，將接收到的日志內(nèi)容依次寫入到 Slave 端的RelayLog文件(MySQL-relay-bin.xxxxxx)的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時(shí)候能夠清楚的高速Master“我需要從某個(gè)bin-log的哪個(gè)位置開(kāi)始往后的日志內(nèi)容，請(qǐng)發(fā)給我”

4. Slave 的 SQL 線程檢測(cè)到 Relay Log 中新增加了內(nèi)容后，會(huì)馬上解析該 Log 文件中的內(nèi)容成為在 Master端真實(shí)執(zhí)行時(shí)候的那些可執(zhí)行的 Query 語(yǔ)句，并在自身執(zhí)行這些 Query。這樣，實(shí)際上就是在 Master 端和 Slave端執(zhí)行了同樣的 Query，所以兩端的數(shù)據(jù)是完全一樣的。

二、設(shè)置MySQL主從配置的優(yōu)點(diǎn)

1、解決web應(yīng)用系統(tǒng)，數(shù)據(jù)庫(kù)出現(xiàn)的性能瓶頸，采用數(shù)據(jù)庫(kù)集群的方式來(lái)實(shí)現(xiàn)查詢負(fù)載；一個(gè)系統(tǒng)中數(shù)據(jù)庫(kù)的查詢操作比更新操作要多得多，通過(guò)多臺(tái)查詢服務(wù)器將數(shù)據(jù)庫(kù)的查詢分擔(dān)到不同的查詢服務(wù)器上從而提高查詢效率。

2、MySQL數(shù)據(jù)庫(kù)支持?jǐn)?shù)據(jù)庫(kù)的主從復(fù)制功能，使用主數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)的插入、刪除與更新操作，而從數(shù)據(jù)庫(kù)則專門用來(lái)進(jìn)行數(shù)據(jù)查詢操作，這樣可以將更新操作和查詢操作分擔(dān)到不同的數(shù)據(jù)庫(kù)上，從而提高了查詢效率。

三、主從數(shù)據(jù)庫(kù)服務(wù)器的配置

1、主數(shù)據(jù)庫(kù)服務(wù)器的配置

(1)、修改MySQL的配置文件（/etc/my.cnf）在配置文件中設(shè)置：

server-id       = 1   ###每一個(gè)數(shù)據(jù)庫(kù)服務(wù)器都要制定一個(gè)唯一的server-id，通常主服務(wù)器制定為1。

log-bin=MySQL-bin     ###MySQL進(jìn)行主從復(fù)制是通過(guò)二進(jìn)制的日志文件來(lái)進(jìn)行的，所以必須開(kāi)啟MySQL的日志功能（這個(gè)是/etc/my.cnf的默認(rèn)配置，保持不變即可）

(2)、 GRANT REPLICATION SLAVE ON *.* TO ' replication'@'192.168.1.10' IDENTIFIED BY 'koncept';        #####給主數(shù)據(jù)庫(kù)服務(wù)器授予一個(gè)可以進(jìn)行復(fù)制的用戶，192.168.1.10為從服務(wù)器的IP，這樣從服務(wù)器就能有錢先來(lái)訪問(wèn)主數(shù)據(jù)庫(kù)服務(wù)器

2、從數(shù)據(jù)庫(kù)服務(wù)器的設(shè)置

修改數(shù)據(jù)庫(kù)配置文件/etc/my.cnf，配置如下內(nèi)容：

#server-id      = 1  ####必須把server-id      = 1注釋掉，

server-id       = 2  ####設(shè)置從的ID號(hào)

master-host     =  192.168.1.9  #####設(shè)置主服務(wù)器的IP

master-user     =   replication  #####設(shè)置連接主服務(wù)器的用戶名

master-password =   concept     #####設(shè)置連接主服務(wù)器的密碼

replicate-do-db=dbname  ######設(shè)置你要同步的數(shù)據(jù)庫(kù)，可以設(shè)置多個(gè)   

####就是我們前面建的用戶名和密碼，另外如果有端口號(hào)的變化還要配置端口

master-port     =  <port> 配置成你設(shè)置的端口就OK了！

3、分別重新啟動(dòng)主從服務(wù)器

如果不重新啟動(dòng)主服務(wù)器在后面查看status的時(shí)候會(huì)出現(xiàn)問(wèn)題！

4、在從服務(wù)器上登錄MySQL，

輸入：show slave status\G  如果發(fā)現(xiàn)有：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

就說(shuō)明已經(jīng)成功了，如果這兩個(gè)選項(xiàng)不全是Yes，那就說(shuō)明你前面的某個(gè)配置錯(cuò)了，

我做的時(shí)候沒(méi)有把主服務(wù)器重啟，就出現(xiàn)  Slave_IO_Running: NO。重啟后好了！

四、監(jiān)控服務(wù)器的狀態(tài)

1、監(jiān)控主服務(wù)器的狀態(tài)

可通過(guò)show master status來(lái)監(jiān)控主服務(wù)器的狀態(tài)，內(nèi)容如下：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| MySQL-bin.000003 |     1164 |              |                  |
+------------------+----------+--------------+------------------+

其中File表示日志文件記錄，Position表示日志文件的位置，這個(gè)也是數(shù)據(jù)庫(kù)執(zhí)行復(fù)制操作的必須標(biāo)識(shí)，后面兩字段表示復(fù)制的數(shù)據(jù)庫(kù)名和不復(fù)制的數(shù)據(jù)庫(kù)名，也可以在配置文件中你進(jìn)行配置。

2、監(jiān)控從服務(wù)器的狀態(tài)

可以通過(guò)：show slave status\G來(lái)查看，另外如果從數(shù)據(jù)庫(kù)在復(fù)制的過(guò)程中出現(xiàn)問(wèn)題，可以通過(guò)命令reset slave從數(shù)據(jù)庫(kù)服務(wù)器復(fù)制的線程，從數(shù)據(jù)庫(kù)服務(wù)器的通常操作命令有：start slave；  ####啟動(dòng)復(fù)制線程

stop slave；  ####停止復(fù)制線程

reset slave；  ####重置復(fù)制線程

change master to； ###動(dòng)態(tài)改變到主服務(wù)器的配置

3、從數(shù)據(jù)庫(kù)常用命令

slave start     --啟動(dòng)復(fù)制線程

slave stop      --停止復(fù)制線程

reset slave     --重置復(fù)制線程

show slave status   --顯示復(fù)制線程的狀態(tài)

show slave status\G --顯示復(fù)制線程的狀態(tài)（分行顯示）

show master status\G    --顯示主數(shù)據(jù)庫(kù)的狀態(tài)（分行顯示）

show master logs    --顯示主數(shù)據(jù)庫(kù)日志，需在主數(shù)據(jù)庫(kù)上運(yùn)行

change master to    --動(dòng)態(tài)改變到主數(shù)據(jù)庫(kù)的配置

show processlist    --顯示有哪些線程在運(yùn)行

五、常見(jiàn)問(wèn)題與解答

1、現(xiàn)象：修改完配置后，

重啟MySQL，提示MySQL manager or server PID file could not be found

解決：查找進(jìn)程中的MySQL，kill掉

> service MySQL restart

> ps -aux|grep myslq

> kill 進(jìn)程號(hào)

# service MySQL start

注：MySQL.sock文件在MySQL啟動(dòng)后才會(huì)生成，位置為/tmp下或/var/lib/MySQL/下

2、現(xiàn)象：從數(shù)據(jù)庫(kù)無(wú)法同步，

show slave status顯示Slave_SQL_Running為No,Seconds_Behind_Master為null

原因：  a.程序可能在slave上進(jìn)行了寫操作

b.也可能是slave機(jī)器重起后，事務(wù)回滾造成的.

解決：方法一：

MySQL> slave stop;

MySQL> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

MySQL> slave start;

解決：方法二：

Slave庫(kù)，MySQL> slave stop; --停掉slave服務(wù)

Master庫(kù)，MySQL> show master status;

+----------------------+----------+--------------+------------------+

|File                  |Position  |Binlog_Do_DB  |Binlog_Ignore_DB  |

+----------------------+----------+--------------+------------------+

| MySQL-bin.000040     | 324      |              |                  |

+----------------------+----------+--------------+------------------+

然后到slave服務(wù)器上執(zhí)行手動(dòng)同步

MySQL> change master to 

> master_host='192.168.1.9',

 > master_user='repuser',

> master_password='123456',

 > master_port=3306,

> master_log_file='MySQL-bin.000040',

> master_log_pos=324;

啟動(dòng)slave服務(wù)，MySQL> slave start;

通過(guò)show slave status查看Slave_SQL_Running為Yes,Seconds_Behind_Master為0，即為正常。

注：手動(dòng)同步需要停止master的寫操作！

3、現(xiàn)象：從數(shù)據(jù)庫(kù)無(wú)法同步，

show slave status顯示Slave_IO_Running為No,Seconds_Behind_Master為null

解決：重啟主數(shù)據(jù)庫(kù)

service MySQL restart

MySQL> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| MySQL-bin.000001 |       98 |              |                  |

+------------------+----------+--------------+------------------+

MySQL> slave stop;

MySQL> change master to Master_Log_File=‘MySQL-bin.000001',Master_Log_Pos=98

MySQL> slave start;