mysql資料庫的主從複製和主主複製

標籤：mysql   主從複製   

Mysql主從架構技術說明

Mysql內建的複製功能是構建大型，高效能應用程式的基礎。將Mysql的資料分布到多個系統上去，這種分布的機制，是通過將Mysql的某一台主機（Master）的資料複製到其它主機（slaves）上，並重新執行一遍來實現的。複製過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。主伺服器將更新寫入二進位記錄檔，這些日誌可以記錄發送到從伺服器的更新。當一個從伺服器串連主伺服器時，它通知主伺服器從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知新的更新。

資料庫複寫特性

MySQL複製技術有以下一些特點：

(1) 資料分布 (Datadistribution )

(2) Server Load Balancer(load balancing)

(3) 備份(Backups)

(4) 高可用性和容錯性 Highavailability and failover

Mysql複製如何工作

1、該過程的第一部分就是master記錄二進位日誌。在每個事務更新資料完成之前，master在二進位日誌記錄這些改變。MySQL將事務串列的寫入二進位日誌，在事件寫入二進位日誌完成後，master通知儲存引擎提交事務。此後可接收slave的請求

2、下一步就是slave將master的binary log拷貝到它自己的中繼日誌。首先，slave開始一個背景工作執行緒——I/O線程。I/O線程在master上開啟一個普通的串連，然後開始在主節點上binlog dump process（二進位轉存線程）。Binlog dump process從master的二進位日誌中讀取事件，如果已經跟上master，它會睡眠並等待master產生新的事件。I/O線程將這些事件寫入中繼日誌。

3、 SQL slave thread（SQL從線程）處理該過程的最後一步。SQL線程從中繼日誌讀取事件，並重放其中的事件而更新slave的資料，使其與master中的資料一致。只要該線程與I/O線程保持一致，中繼日誌通常會位於OS的緩衝中，所以中繼日誌的開銷很小。

主從複製實驗

實驗環境：

centos 系統伺服器2 台、一台使用者做Mysql 主伺服器，一台用於做Mysql從伺服器，配置好yum 源、防火牆關閉、各節點時鐘服務同步、各節點之間可以通過主機名稱互相通訊。實驗中我用的是centos7。

Mysql主伺服器：192.168.182.173

mysql從伺服器: 192.168.182.174

實驗步驟：

一：配置主伺服器

1.修改設定檔

vim /etc/my.cnf

server-id=1 #配置server-id，讓主伺服器有唯一ID號

log-bin=mysql-bin #開啟Mysql日誌，日誌格式為二進位

skip-name-resolve #關閉名稱解析，（非必須）

2．查看主伺服器狀態

在Master的資料庫執行show master status，查看主伺服器二進位日誌狀態

650) this.width=650;" src="https://s1.51cto.com/oss/201711/21/02f822560452a577638d060771d97ff1.png" title="2.png" style="width:500px;height:102px;" width="500" vspace="0" hspace="0" height="102" border="0" alt="02f822560452a577638d060771d97ff1.png" />

3.建立複製帳號

在Master的資料庫中建立一個備份帳戶：每個slave使用標準的MySQL使用者名稱和密碼串連master。進行複製操作的使用者會授予replication slave許可權。

grant replication slave on *.* to ‘slave‘@‘192.168.182.174‘identified by ‘magedu‘;

二：配置從伺服器

1.修改設定檔

vim /etc/my.cnf

server-id=2 #配置server-id，讓從伺服器有唯一ID號

relay_log = mysql-relay-bin  #開啟Mysql日誌，日誌格式為二進位

read_only = 1 #設定唯讀許可權

log_bin = mysql-bin #開啟從伺服器二進位日誌

log_slave_updates = 1 #使得更新的資料寫進二進位日誌中

2.啟動從伺服器複製線程

讓slave串連master，並開始重做master二進位日誌中的事件。

CHANGE MASTER TO MASTER_HOST=‘192.168.222.128‘,

MASTER_USER=‘slave‘,

MASTER_PASSWORD=‘magedu‘,

MASTER_LOG_FILE=‘mysql-bin.000001‘,

MASTER_LOG_POS=245;

執行start slave；# 啟動複製線程。

3.查看從伺服器狀態

可使用SHOW SLAVE STATUS\G查看從伺服器狀態，如下所示，也可用show processlist \G查看當前複製狀態：

Slave_IO_Running: Yes #IO線程正常運行

Slave_SQL_Running: Yes #SQL線程正常運行

650) this.width=650;" src="https://s5.51cto.com/oss/201711/21/692e1f17aa851c44fcdf47b8113239db.png" title="3.png" style="width:500px;height:187px;" width="500" vspace="0" hspace="0" height="187" border="0" alt="692e1f17aa851c44fcdf47b8113239db.png" />

接下來就可以測試了

在主伺服器上建立一個名為xiaomi的表，在主伺服器上可以查看到，如果在從伺服器上也可以查看到，說明資料已同步，實現成功。

主伺服器：

650) this.width=650;" src="https://s3.51cto.com/oss/201711/21/1e0ec7baa374fe2568aff9461549a76f.png" title="4.png" style="width:400px;height:221px;" width="400" vspace="0" hspace="0" height="221" border="0" alt="1e0ec7baa374fe2568aff9461549a76f.png" />

從伺服器：

650) this.width=650;" src="https://s4.51cto.com/oss/201711/21/5b1f64fd9e4adf8b60118884c6b63122.png" title="5.png" style="width:400px;height:206px;" width="400" vspace="0" hspace="0" height="206" border="0" alt="5b1f64fd9e4adf8b60118884c6b63122.png" />

主主複製

主主實際上也是互為主從，互為主從：兩個節點各自都要開啟binlog和relay log；

1、資料不一致；

2、自動成長id；

定義一個節點使用奇數id

auto_increment_increment=2#表示自增長欄位每次遞增的量

auto_increment_offset=1#表示自增長欄位從那個數開始

另一個節點使用偶數id

auto_increment_increment=2

auto_increment_offset=2

實驗：主主複製

在上面主從實驗的基礎上，實現主主複製。

主伺服器：192.168.182.174

從伺服器：192.168.136.173

一：配置主伺服器

vim /etc/my.cnf

server-id=2 #配置server-id，讓主伺服器有唯一ID號

log-bin=mysql-bin #開啟Mysql日誌，日誌格式為二進位

relay_log=mysql-relay-log

auto_increment_increment=2
auto_increment_increment=2

650) this.width=650;" src="https://s4.51cto.com/oss/201711/21/3c309cd39f22b392a13b38b32bd446cc.png" title="6.png" style="width:300px;height:138px;" width="300" vspace="0" hspace="0" height="138" border="0" alt="3c309cd39f22b392a13b38b32bd446cc.png" />

2．查看主伺服器狀態

在Master的資料庫執行show master status，查看主伺服器二進位日誌狀態

3.建立複製帳號

在Master的資料庫中建立一個備份帳戶：每個slave使用標準的MySQL使用者名稱和密碼串連master。進行複製操作的使用者會授予replication slave許可權。

grant replication slave on *.* to ‘slave‘@‘192.168.182.174‘identified by ‘magedu‘;

二：修改從伺服器設定檔

1.vim /etc/my.cnf

server-id=2 #配置server-id，讓主伺服器有唯一ID號

log-bin=mysql-bin #開啟Mysql日誌，日誌格式為二進位

relay_log=mysql-relay-log

auto_increment_increment=2
auto_increment_increment=2

2.啟動從伺服器複製線程

讓slave串連master，並開始重做master二進位日誌中的事件。

CHANGE MASTER TO MASTER_HOST=‘192.168.182.174‘,

MASTER_USER=‘slave1‘,

MASTER_PASSWORD=‘magedu1‘,

MASTER_LOG_FILE=‘mysql-bin.000001‘,

MASTER_LOG_POS=245;

執行start slave；# 啟動複製線程。

3.查看從伺服器狀態

可使用SHOW SLAVE STATUS\G查看從伺服器狀態，如下所示，也可用show processlist \G查看當前複製狀態：

Slave_IO_Running: Yes #IO線程正常運行

Slave_SQL_Running: Yes #SQL線程正常運行

測試：無論在哪個伺服器的資料庫中建立資料，另一個都可以同步資料。

本文出自 “13162732” 部落格，請務必保留此出處http://13172732.blog.51cto.com/13162732/1983889

mysql資料庫的主從複製和主主複製