MySQL主從雙向同步複製

MySQL支援單向、非同步複製，複製過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當 從伺服器。主伺服器將更新寫入二進位制日誌檔案，並維護日誌檔案的一個索引以跟蹤日誌迴圈。當一個從伺服器連線到主伺服器時，它通知主伺服器從伺服器在日誌 中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知下一次更新。

 為什麼使用主從複製？

1、主伺服器/從伺服器設定增加了健壯性。主伺服器出現問題時，你可以切換到從伺服器作為備份。

2、通過在主伺服器和從伺服器之間切分處理客戶查詢的負荷，可以得到更好的客戶響應時間。但是不要同時在主從伺服器上進行更新，這樣可能引起衝突。

3、使用複製的另一個好處是可以使用一個從伺服器執行備份，而不會干擾主伺服器。在備份過程中主伺服器可以繼續處理更新。

 MySQL使用3個執行緒來執行復制功能(其中1個在主伺服器上，另兩個在從伺服器上。當發出START SLAVE時，從伺服器建立一個I/O執行緒，以連線主伺服器並讓主伺服器傳送二進位制日誌。主伺服器建立一個執行緒將二進位制日誌中的內容傳送到從伺服器。從服 務器I/O執行緒讀取主伺服器Binlog Dump執行緒傳送的內容並將該資料拷貝到從伺服器資料目錄中的本地檔案中，即中繼日誌。第3個執行緒是SQL執行緒，從伺服器使用此執行緒讀取中繼日誌並執行日 志中包含的更新。SHOW PROCESSLIST語句可以查詢在主伺服器上和從伺服器上發生的關於複製的資訊。

 預設中繼日誌使用host_name-relay-bin.nnnnnn形式的檔名，其中host_name是從伺服器主機名，nnnnnn是 序列號。用連續序列號來建立連續中繼日誌檔案，從000001開始。從伺服器跟蹤中繼日誌索引檔案來識別目前正使用的中繼日誌。預設中繼日誌索引檔名為 host_name-relay-bin.index。在預設情況，這些檔案在從伺服器的資料目錄中被建立。中繼日誌與二進位制日誌的格式相同，並且可以用 mysqlbinlog讀取。當SQL執行緒執行完中繼日誌中的所有事件後，中繼日誌將會被自動刪除。

 從伺服器在資料目錄中另外建立兩個狀態檔案--master.info和relay-log.info。狀態檔案儲存在硬碟上，從伺服器關閉時不會丟失。下次從伺服器啟動時，讀取這些檔案以確定它已經從主伺服器讀取了多少二進位制日誌，以及處理自己的中繼日誌的程度。

 

下面來看一下具體的配置：

一、準備伺服器

由於MySQL不同版本之間的(二進位制日誌)binlog格式可能會不一樣，因此最好的搭配組合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高於Slave版本。

本文中，我們假設主伺服器(以下簡稱Master)和從伺服器(以下簡稱Slave)的版本都是5.1.35，作業系統是Centos 4.7。

假設同步Master的主機名為：master(IP:192.168.1.123)，Slave主機名 為：slave(IP:192.168.1.124)，2個MySQL的basedir目錄都是/usr/local/mysql，datadir都是： /var/lib/mysql。

二、設定同步伺服器

1、設定同步Master

修改 my.cnf 檔案，在

# Replication Master Server (default)

# binary logging is required for replication

新增如下內容：

#log-bin=/var/log/mysql/updatelog  server-id = 1 

auto_increment_increment = 2 

auto_increment_offset = 1 

binlog-do-db=discuz 

binlog-ignore-db=mysql 

 #log-slave-updates 

slave-skip-errors=all

注：

slave-skip-errors 是跳過錯誤，繼續執行復制操作

多主互備和主從複製有一些區別，因為多主中 都可以對伺服器有寫許可權，所以設計到自增長重複問題

出現的問題（多主自增長ID重複）
1:首先我們通過A,B的test表結構
2:掉A，在B上對資料表test(存在自增長ID)執行插入操作，返回插入ID為1
3:後停掉B，在A上對資料表test(存在自增長ID)執行插入操作，返回的插入ID也是1
4:然後 我們同時啟動A,B，就會出現主鍵ID重複

解決方法：
我們只要保證兩臺伺服器上插入的自增長資料不同就可以了
如：A查奇數ID，B插偶數ID，當然如果伺服器多的話，你可以定義演算法，只要不同就可以了

在這裡我們在A,B上加入引數，以實現奇偶插入

A：my.cnf上加入引數

 

auto_increment_offset = 1  auto_increment_increment = 2

這樣A的auto_increment欄位產生的數值是：1, 3, 5, 7, …等奇數ID了

B：my.cnf上加入引數

 

auto_increment_offset = 2  auto_increment_increment = 2

這樣B的auto_increment欄位產生的數值是：2, 4, 6, 8, …等偶數ID了

可 以看出，你的auto_increment欄位在不同的伺服器之間絕對不會重複，
所以Master-Master結構就沒有任何問題了。當然，你還可以使 用3臺，4臺，或者N臺伺服器，
只要保證auto_increment_increment = N 再設定一下auto_increment_offset為適當的初始值就可以了，
那樣，我們的MySQL可以同時有幾十臺主伺服器，而不會出現自增長ID 重複。

 

重啟MySQL,建立一個MySQL帳號為同步專用

# /usr/local/mysql/bin/mysql -u root -p 

 mysql> GRANT REPLICATION SLAVE ON *.* TO 'back'@'%' IDENTIFIED BY 'back'; 

 mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO   'back'@'%' IDENTIFIED BY 'back'; 

mysql> FLUSH PRIVILEGES ;

如果想要在Slave上有許可權執行 "LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 語句的話，必須授予全域性的 FILE 和 SELECT 許可權：

2、設定同步Slave

修改my.cnf檔案，新增

server-id = 2 

auto_increment_increment = 2 

auto_increment_offset = 2 

 master-host = 192.168.1.123 

master-user = back 

 master-password = back  master-port = 3306 

replicate-ignore-db=mysql 

replicate-do-db=discuz  #log-slave-updates   slave-skip-errors=all

重啟MySQL

 

3、啟動同步

在主伺服器master MySQL命令符下：

# /usr/local/mysql/bin/mysql -u root -p 

mysql> show master status;  顯示（當然這個是我機器的情況，你的不可能跟我一樣哈，只是個例子）： 

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

 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

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

| mysql-bin.000009 | 98 | discuz | mysql | 

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

 

在從伺服器master MySQL命令符下：

# /usr/local/mysql/bin/mysql -u root -p  mysql> slave stop; 

mysql> change master to master_host='192.168.1.123', 

master_user='back', 

master_password='back', 

master_log_file='mysql-bin.000009',

 master_log_pos=98; 

mysql> slave start; 

mysql> show slave status\G; 

Slave_IO_Running: Yes 

Slave_SQL_Running: Yes

如果都是yes，那代表已經在同步

往表裡面寫點資料測試一下看是否同步成功，如果不成功，絕對不是你的RP問題，再檢查一下操作步驟！

 

4、設定雙向同步

修改slave伺服器的my.cnf，新增

log-bin=/var/log/mysql/updatelog  binlog-do-db=discuz  binlog-ignore-db=mysql

重啟MySQL,建立一個MySQL帳號為同步專用

mysql> GRANT REPLICATION SLAVE ON *.* TO 'back'@'%' IDENTIFIED BY 'back'; 

 mysql> GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO   'back'@'%' IDENTIFIED BY 'back'; 

mysql> FLUSH PRIVILEGES ;

 

修改master伺服器的my.cnf,新增

master-host = 192.168.1.124 

 master-user = back 

 master-password = back 

master-port = 3306 

replicate-ignore-db=mysql 

 replicate-do-db=discuz

重啟MySQL

 

在主伺服器slave MySQL命令符下：

 

show master status; 

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

 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

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

 | mysql-bin.000013 | 98 | discuz | mysql | 

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

 

在伺服器A MySQL命令符下：

mysql> slave stop； 

 mysql> change master to master_host='192.168.1.124', master_user='back', 

                           master_password='back', master_log_file='mysql-bin.000013', master_log_pos=98; 

 mysql> slave start;

其實也就是A->B單向同步的反向操作！雙向同步，就這麼簡單啦！ 

 

提示：如果修改了主伺服器的配置，記得刪除從伺服器上的master.info檔案。否則從伺服器使用的還是老配置，可能會導致錯誤。

-----------------------------------------------------------------------------------

注意：關於要複製多個資料庫時，binlog-do-db和replicate-do-db選項的設定，網上很多人說是用半形逗號分隔，經過測試，這樣的說法是錯誤的，MySQL官方文件也明確指出，如果要備份多個資料庫，只要重複設定相應選項就可以了。

比如：

binlog-do-db=a  binlog-do-db=b  replicate-do-db=a  replicate-do-db=b

補：

#啟用從庫日誌，這樣可以進行鏈式複製  log-slave-updates 

#從庫是否只讀，0表示可讀寫，1表示只讀  read-only=1 

#只複製某個表  replicate-do-table=tablename 

 #只複製某些表（可用匹配符）  replicate-wild-do-table=tablename% 

 #只複製某個庫  replicate-do-db=dbname 

 #只複製某些庫  replicte-wild-do-db=dbname% 

#不復制某個表  replicate-ignore-table=tablename 

#不復制某些表  replicate-wild-ignore-table=tablename% 

 #不復制某個庫  replicate-ignore-db=dbname 

#複製完的sql語句是否立即從中繼日誌中清除，1表示立即清除  relay-log-purge=1

第一部分：主從關係的同步

做開發的時候要做MySQL的資料庫同步，兩臺安裝一樣的系統，都是FreeBSD5.4，安裝了Apache 2.0.55和PHP 4.4.0，MySQL的版本是4.1.15，都是目前最新的版本。

1. 安裝配置

兩臺伺服器， 分別安裝好MySQL，都安裝在 /usr/local/MySQL 目錄下（安裝步驟省略，請參考相關文件），兩臺伺服器的IP分別是192.168.0.1和192.168.0.2，我們把192.168.0.1作為 master資料庫，把192.168.0.2作為slave伺服器，我們採用單向同步的方式，就是master的資料是主的資料，然後slave主動去 master哪兒同步資料回來。

兩臺伺服器的配置一樣，我們把關鍵的配置檔案拷貝一下，預設的配置檔案是在 /usr/local/MySQL/share/MySQL目錄下，分別有 my-large.cnf, my-medium.cnf, my-small.cnf等幾個文家，我們只是測試，使用my-medium.cnf就行了。MySQL安裝完後，預設的配置檔案是指定在資料庫存放目錄 下的，我們用的是4.1.X的，所以配置檔案就應該在 /usr/local/MySQL/var 目錄下，於是把配置檔案拷貝過去：

cp /usr/local/MySQL/share/MySQL/my-medium.cnf /usr/local/MySQL/var/my.cnf

兩臺伺服器做相同的拷貝配置檔案操作。

2. 配置Master伺服器

我們要把192.168.0.1配置為主MySQL伺服器（master），那麼我們就要考慮我們需要同步那個資料庫，使用那個使用者同步，我們這裡為了簡單起見，就使用root使用者進行同步，並且只需要同步資料庫abc。

開啟配置檔案：

vi /usr/local/MySQL/var/my.cnf

找到一下資訊：

# required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 //1為master，2為salve

新增兩行：

sql-bin-update-same //同步形式 binlog-do-db = abc //要同步的資料庫

重啟192.168.0.1的MySQL伺服器：

/usr/local/MySQL/bin/MySQLadmin shutdown /usr/local/MySQL/bin/MySQLd_safe --user=MySQL &

3. 配置Slave伺服器

我們的slave伺服器主要是主動去master伺服器同步資料回來，我們編輯配置檔案：

vi /usr/local/MySQL/var/my.cnf

找到下面類似的資訊：

# required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1

把上面的server-id修改為2，同時新增一些資訊：

server-id = 2 //本MySQL是slave伺服器 master-host = 192.168.0.1 //master伺服器的IP master-user = root //連線master伺服器的使用者 master-password = '' //連線master伺服器的密碼 master-port = 3306 //連線埠 master-connect-retry = 10 //重試次數 replicate-do-db = abc //要同步的資料庫 log-slave-updates //同步的形式

重啟192.168.0.2的MySQL伺服器：

/usr/local/MySQL/bin/MySQLadmin shutdown /usr/local/MySQL/bin/MySQLd_safe --user=MySQL &

4. 測試安裝

首先檢視一下slave的主機日誌：

cat /usr/local/MySQL/var/xxxxx_err （xxx是主機名）

檢查是否連線正常, 看到類似這樣的資訊就成功了

051031 11:42:40 MySQLd started 051031 11:42:41 InnoDB: Started; log sequence number 0 43634 /usr/local/MySQL/libexec/MySQLd: ready for connections. Version: '4.1.15-log' socket: '/tmp/MySQL.sock' port: 3306 Source distribution 051031 11:42:41 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './new4-relay-bin.000001' position: 4 051031 11:43:21 [Note] Slave I/O thread: connected to master 'root@192.168.0.1:3306', replication started in log 'FIRST' at position 4

在Master檢視資訊

/usr/local/MySQL/bin/MySQL -u root

檢視master狀態：

MySQL> show master status;

檢視Master下MySQL程式資訊：

MySQL> show processlist;

在slave上檢視資訊：

/usr/local/MySQL/bin/MySQL -u root

檢視slave狀態：

MySQL> show slave status;

檢視slave下MySQL程式資訊：

MySQL> show processlist;

你再在master的abc庫裡建立表結構並且插入資料，然後檢查slave有沒有同步這些資料，就能夠檢查出是否設定成功。

 

 

第二部分：雙向關係的同步

 

master端 192.168.0.1slave端  192.168.0.2
1。MASTER端a.進入mysql，建立一個資料庫abc：
      create database abc;
b.建立一個用來同步的使用者,指定只能在192.168.0.2登入:
      grant replication slave on *.* to 'ha'@'192.168.0.2' identified by 'hapwd';c.修改master端的/etc/my.cnf檔案     log-bin
     server-id = 1
     sql-bin-update-same 同步模式 ，在mysql5以上的版本都不需要這句，否則會啟動不了mysql服務 
     binlog-do-db= abc    設定同步資料庫，如果有多個資料庫，每個資料庫一行
     binlog-ignore-db = mysql 設定不要同步的資料庫，如有多個資料庫，每個資料庫一行
     master-host=192.168.0.2
     master-user=ha
     master-password='hapwd'
     master-port=3306
     master-connect-retry=10
     replicate-do-db=abc   設定要接收的資料庫，如有多個資料庫，每個資料庫一行
     replicate-ignore-db= mysql   設定不要接收的資料庫，每個資料庫一行   （一般這條可以不寫）
     log-slave-updates    在mysql5以上的版本都不需要這句
d.重啟mysqle.進入mysql，執行：     slave start;
2。SLAVE端a.進入mysql，建立一個資料庫abc：
      create database abc;
b.建立一個用來同步的使用者,指定只能在192.168.0.1登入:
      grant replication slave on *.* to 'ha'@'192.168.0.1' identified by 'hapwd';c.修改slave端的/etc/my.cnf檔案     log-bin     server-id       = 2
     binlog-do-db= abc    設定同步資料庫，如果有多個資料庫，每個資料庫一行
     binlog-ignore-db = mysql 設定不要同步的資料庫，如有多個資料庫，每個資料庫一行
     sql-bin-update-same   同步模式 ，在mysql5以上的版本都不需要這句，否則會啟動不了mysql服務 
     master-host=192.168.0.1
     master-user= ha
     master-password='hapwd'
     master-port=3306
     master-connect-retry=10
     replicate-do-db=abc   設定要接收的資料庫，如有多個資料庫，每個資料庫一行
     replicate-ignore-db= mysql   設定不要接收的資料庫，每個資料庫一行   （一般這條可以不寫）
     log-slave-updates   在mysql5以上的版本都不需要這句
d.重啟mysqle.進入mysql，執行：     slave start;
注意：1。如果有一端修改了資料後，另一端接收不到，手工執行命令load data from master;就可以了2。如果因為誤刪了二進位制日誌檔案導致無法同步，則執行：（這幾步很有用，你甚至可以把slave上的abc庫刪除掉，他會自動從master上覆制過來重建）
slave stop;
reset slave;slave start;3。我們經常會在reset slave後發現重新同步時會出現重複資料無法倒入的情況，

比如：             

Last_Errno: 1062               

Last_Error: Error 'Duplicate entry '1472533' for key 1' on query. Default database: 'epg'. Query: 'insert into boot_info(stbid,begintime)                values('zk124070c819','1259128442')'

這時可以這樣，跳過沖突的這一行：

slave stop;

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

 slave start;