一、mysql主從復(fù)制介紹

mysql的主從復(fù)制并不是數(shù)據(jù)庫(kù)磁盤(pán)上的文件直接拷貝，而是通過(guò)邏輯的binlog日志復(fù)制到要同步的服務(wù)器本地，然后由本地的線程讀取日志里面的sql語(yǔ)句，重新應(yīng)用到mysql數(shù)據(jù)庫(kù)中。

mysql數(shù)據(jù)庫(kù)支持單向，雙向，鏈?zhǔn)郊?jí)聯(lián)，環(huán)狀等不同業(yè)務(wù)場(chǎng)景的復(fù)制，一臺(tái)服務(wù)器充當(dāng)主服務(wù)器master，接收來(lái)自用戶(hù)的更新，而一個(gè)或多個(gè)其他服務(wù)器充當(dāng)從服務(wù)器slave，接收來(lái)自主服務(wù)器binlog文件的日志內(nèi)容，解析出sql，更新到從服務(wù)器。

一主一從 (A -> B, A為主,B為從)

一主多從 (A -> B, A -> C, A為主,B與C為從)

雙主雙向同步 (A -> B , B -> A , A與B同為主,互相備份)

線性級(jí)聯(lián) (A -> B -> C , A與B主主互備，C為從)

環(huán)狀級(jí)聯(lián) (A -> B -> C -> A, A、B、C都為主,各節(jié)點(diǎn)都可寫(xiě)入數(shù)據(jù))

二、實(shí)現(xiàn)mysql主從讀寫(xiě)分離的方案

1、通過(guò)程序?qū)崿F(xiàn)讀寫(xiě)分離(判斷語(yǔ)句關(guān)鍵字，去連接主從數(shù)據(jù)庫(kù))

2、通過(guò)開(kāi)源的軟件實(shí)現(xiàn)讀寫(xiě)分離(mysql-proxy,amoeba,穩(wěn)定性和功能一般,不建議生產(chǎn)使用)

3、獨(dú)立開(kāi)發(fā)DAL層軟件

三、mysql主從復(fù)制原理介紹

mysql主從復(fù)制是一個(gè)異步的復(fù)制過(guò)程，將一個(gè)主庫(kù)復(fù)制到一個(gè)從庫(kù)，master與slave之間實(shí)現(xiàn)整個(gè)過(guò)程是由三個(gè)線程參與完成。其中sql線程與I/O線程在slave端，另一個(gè)I/O線程在master端。

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

1、在slave上執(zhí)行start slave命令，開(kāi)啟主從復(fù)制開(kāi)關(guān)，開(kāi)始進(jìn)行主從復(fù)制。

2、slave的I/O線程通過(guò)master上已經(jīng)授權(quán)的復(fù)制用戶(hù)請(qǐng)求master，請(qǐng)求指定binlog日志的指定位置。

3、master接收slave的I/O線程的請(qǐng)求后，其自身負(fù)責(zé)復(fù)制的I/O線程會(huì)根據(jù)slave的請(qǐng)求信息分批讀取指定binlog日志的指定位置之后的日志信息，然后返回給slave的I/O線程，返回信息除了binlog日志外，還有master的新binlog文件名稱(chēng)，以及新binlog中的下一個(gè)指定更新位置。

4、slave獲取來(lái)自master上I/O線程發(fā)送的binlog日志內(nèi)容，日志文件及位置點(diǎn)后，會(huì)將binlog內(nèi)容依次寫(xiě)到slave自身的relay log(中繼日志)文件尾部，并將新的binlog文件名和位置記錄到master-info文件中，以便下次從master讀取新binlog日志時(shí)，能告訴master從新binlog的新位置讀取。

5、slave的sql線程會(huì)實(shí)時(shí)檢測(cè)本地relay log中I/O線程新增加的日志內(nèi)容，及時(shí)把relay log文件中的內(nèi)容解析成sql語(yǔ)句，并按解析sql語(yǔ)句的位置順序執(zhí)行這些sql語(yǔ)句。relay-log.info中記錄當(dāng)前應(yīng)用中繼日志的文件名及位置點(diǎn)。

四、mysql主從復(fù)制操作

我這里是mysql單機(jī)多實(shí)例，3306、3308、3309

主庫(kù)為3306，從庫(kù)為3308,3309

(1)、在master主庫(kù)上

1、設(shè)置server-id值并開(kāi)啟binlog功能

> vi /etc/my.cnf 

 [mysqld]
 #用于同步的每臺(tái)機(jī)器server-id都不能相同

server-id = 10

log-bin = /data/mysql56/data/mysql-bin 

2、重啟主庫(kù)

> service mysqld restart 

3、登陸主庫(kù)，查看server-id

> mysql -uroot -p

> show variables like 'server_id'; 

4、主庫(kù)上建立用于從庫(kù)復(fù)制的賬號(hào)

> grant replication slave on *.* to "rep"@"%" identified by "123456";

> flush privileges;

> select user,host from mysql.user;

> show grants for rep@"%"; 

5、對(duì)主庫(kù)數(shù)據(jù)庫(kù)鎖表只讀(當(dāng)前窗口不要關(guān)掉)

> flush table with read lock; 

查看主庫(kù)狀態(tài)

> show master status; 

6、備份主庫(kù)所有數(shù)據(jù)文件

> mysqldump -uroot -p -A -B | gzip > /data/mysql_bak.$(date +%F).sql.gz 

7、備份完主庫(kù)數(shù)據(jù)后，解鎖

> unlock tables; 

8、把主庫(kù)導(dǎo)出的數(shù)據(jù)遷移到從庫(kù)

(2)、在slave從庫(kù)上

1、設(shè)置server-id值并關(guān)閉binlog功能  

①有兩種情況下需打開(kāi)binlog   

②級(jí)聯(lián)同步A->B->C中間的B，就要開(kāi)啟binlog   

③在從庫(kù)做數(shù)據(jù)庫(kù)備份，必須要有全備和binlog日志才是完整備份。

> vi /mysql-instance/3308/my.cnf 

[mysqld]

server-id = 11

relay-log = /mysql-instance/3308/relay-bin

relay-log-info-file = /mysql-instance/3308/relay-log.info 

2、重啟從庫(kù)

> /mysql-instance/3308/mysql restart 

3、登陸從庫(kù)檢查參數(shù)

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock

> show variables like 'log_bin';

> show variables like 'server_id'; 

4、把主庫(kù)mysqldump導(dǎo)出的數(shù)據(jù)恢復(fù)到從庫(kù)

> gzip -d /data/mysql_bak.2017-01-15.sql.gz 

 把主庫(kù)數(shù)據(jù)還原到從庫(kù)

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock < /data/mysql_bak.2017-01-15.sql 

5、登錄從庫(kù)，配置復(fù)制參數(shù)

CHANGE MASTER TO

MASTER_HOST='127.0.0.1',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=396; 

注意上面的MASTER_LOG_FILE和MASTER_LOG_POS是在主庫(kù)中用show master status;查看的信息。

查看master.info文件

> cat /mysql-instance/3308/data/master.info 

6、啟動(dòng)從庫(kù)同步開(kāi)關(guān)，測(cè)試主從復(fù)制情況

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e "start slave;"

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e "show slave status\G;"

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e "show slave status\G" | egrep "IO_Running|SQL_Running|_Behind_Master" 

7、測(cè)試主從復(fù)制

> mysql -uroot -p -e "create database wohehe;"

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e "show databases;" 

五、mysql主從復(fù)制線程狀態(tài)說(shuō)明及用途

1、主庫(kù)線程的同步狀態(tài)

> show processlist\G; 


*************************** 1. row ***************************

   Id: 5

  User: rep

  Host: localhost:47605

   db: NULL

Command: Binlog Dump

  Time: 4728

 State: Master has sent all binlog to slave; waiting for binlog to be updated

  Info: NULL 

說(shuō)明主庫(kù)線程已從binlog讀取更新，發(fā)送到了從庫(kù)，線程處理空閑狀態(tài)，等待binlog的事件更新。

2、從庫(kù)線程的同頻狀態(tài)

> show processlist\G; 

*************************** 2. row ***************************

   Id: 6

  User: system user

  Host:

   db: NULL

Command: Connect

  Time: 5305

 State: Slave has read all relay log; waiting for the slave I/O thread to update it

  Info: NULL 

說(shuō)明從庫(kù)已讀取所有中繼日志，等待從庫(kù)I/O線程的更新。

六、主從復(fù)制故障

如果我在從庫(kù)上創(chuàng)建了一個(gè)庫(kù)，然后去主庫(kù)創(chuàng)建同名的庫(kù)，那么這就會(huì)沖突了。

> show slave status; 

Slave_IO_Running: Yes

Slave_SQL_Running: No

Seconds_Behind_Master: NULL

Last_Error: Error 'Can't create database 'xxxxx'; database exists' on query. Default database: 'xxxxx'. Query: 'create database xxxxx' 

對(duì)于該沖突解決方法

方法一

> stop slave;

#將同步指針移動(dòng)下一個(gè)，如果多次不同步，可重復(fù)操作

> set global sql_slave_skip_counter = 1;

> start slave; 

方法二

> vi /mysql-instance/3308/my.cnf 

#把可以忽略的錯(cuò)誤號(hào)事先在配置文件中配置

slave-skip-errors = 1002,1007,1032 

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論