詳解centos7下mysql5.6的主從複製的範例程式碼分享

本篇文章主要介紹了centos7下mysql5.6的主從複製詳解，小編覺得挺好的，現在分享給大家，也給大家做個參考。一起跟著小編過來看看吧

一、mysql主從複製介紹

mysql的主從複製並不是資料庫磁碟上的檔案直接拷貝，而是透過邏輯的binlog日誌複製到要同步的伺服器本地，然後由本地的執行緒讀取日誌裡面的sql語句，重新應用到mysql資料庫中。

mysql資料庫支援單向，雙向，鍊式級聯，環狀等不同業務場景的複製，一台伺服器充當主伺服器master，接收來自使用者的更新，而一個或多個其他伺服器充當從伺服器slave，接收來自主伺服器binlog檔案的日誌內容，解析出sql，更新到從伺服器。

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

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

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

線性級聯(A -> B -> C , A與B主主互備，C為從)

#環狀級聯(A -> B -> C -> A, A、 B、C都為主,各節點都可寫入資料)

二、實作mysql主從讀寫分離的方案

1.透過程式實作讀寫分離(判斷語句關鍵字，去連接主從資料庫)

2、透過開源的軟體實現讀寫分離(mysql-proxy,amoeba,穩定性與功能一般,不建議生產使用)

3、獨立開發DAL層軟體

#三、mysql主從複製原理介紹

mysql主從複製是一個非同步的複製過程，將一個主函式庫複製到一個從函式庫，master與slave之間實作整個過程是由三個執行緒參與完成。其中sql線程與I/O線程在slave端，另一個I/O線程在master端。

複製原理程序

1、在slave上執行start slave指令，開啟主從複製開關，開始進行主從複製。

2、slave的I/O執行緒透過master上已經授權的複製使用者請求master，請求指定binlog日誌的指定位置。

3、master接收slave的I/O執行緒的請求後，其自身負責複製的I/O執行緒會根據slave的請求資訊分批讀取指定binlog日誌的指定位置之後的日誌訊息，然後回傳給slave的I/O線程，回傳資訊除了binlog日誌外，還有master的新binlog檔名稱，以及新binlog中的下一個指定更新位置。

4、slave取得來自master上I/O執行緒發送的binlog日誌內容，日誌檔案及位置點後，會將binlog內容依序寫到slave自身的relay log(中繼日誌)檔案尾部，並將新的binlog檔名和位置記錄到master-info檔中，以便下次從master讀取新binlog日誌時，能告訴master從新binlog的新位置讀取。

5、slave的sql線程會即時偵測本地relay log中I/O線程新增加的日誌內容，及時把relay log檔案中的內容解析成sql語句，並按解析sql語句的位置順序執行這些sql語句。 relay-log.info中記錄目前應用程式中繼日誌的檔案名稱及位置點。

四、mysql主從複製操作

#我這裡是mysql單機多實例，3306、3308、3309

主庫為3306，從庫為3308,3309

(1)、在master主庫上

1、設定server-id值並開啟binlog功能

> vi /etc/my.cnf

 [mysqld]
 #用于同步的每台机器server-id都不能相同

server-id = 10

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

2、重啟主庫

> service mysqld restart

3、登陸主庫，查看server-id

> mysql -uroot -p

> show variables like 'server_id';

4、主庫上建立用於從庫複製的帳號

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

> flush privileges;

> select user,host from mysql.user;

> show grants for rep@"%";

5、對主庫資料庫鎖定表只讀(目前視窗不要關掉)

> flush table with read lock;

查看主庫狀態

> show master status;

6 、備份主庫所有資料檔

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

7、備份完主庫資料後，解鎖

> unlock tables;

8、把主庫匯出的資料移轉到從庫

( 2)、在slave從庫上

1、設定server-id值並關閉binlog功能  

①有兩種情況下需開啟binlog   

②級聯同步A->B->C中間的B，就要開啟binlog   

③在從庫做資料庫備份，必須要有全備和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、重新啟動從庫

> /mysql-instance/3308/mysql restart

3、登陸從庫檢查參數

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

> show variables like 'log_bin';

> show variables like 'server_id';

4、將主庫mysqldump導出的資料恢復到從庫

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

 把主庫資料還原到從庫

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

5、登入從庫，設定複製參數

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是在主庫中用show master status;查看的資訊。

查看master.info檔

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

6、啟動從庫同步開關，測試主從複製情況

> 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、測試主從複製

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

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

五、mysql主从复制线程状态说明及用途

1、主库线程的同步状态

> 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

说明主库线程已从binlog读取更新，发送到了从库，线程处理空闲状态，等待binlog的事件更新。

2、从库线程的同频状态

> 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

说明从库已读取所有中继日志，等待从库I/O线程的更新。

六、主从复制故障

如果我在从库上创建了一个库，然后去主库创建同名的库，那么这就会冲突了。

> 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'

对于该冲突解决方法

方法一

> stop slave;

#将同步指针移动下一个，如果多次不同步，可重复操作

> set global sql_slave_skip_counter = 1;

> start slave;

方法二

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

#把可以忽略的错误号事先在配置文件中配置

slave-skip-errors = 1002,1007,1032

以上是詳解centos7下mysql5.6的主從複製的範例程式碼分享的詳細內容。更多資訊請關注PHP中文網其他相關文章！