windows下mysql雙向同步備份實現方法

1.1 環境搭建
準備兩台Windows NT 主機，分別安裝好iKEY Server windows 版本，確定版本無誤，確保mysql服務正常啟動，確保兩台主機處於同一個區域網路中，確定好哪台做為主、備機器，假設A為主機，B為備機，假設A主機IP地址為：192.168.1.101，B主機IP地址為192.168.1.102

1.2 建立同步帳戶
分別在A、B節點上登陸mysql 資料庫，建立同步帳戶並賦予同步許可權，如下：

A節點操作：

運行cmd, cd進入iKEY版本安裝目錄下的iKEY\mysql\bin下，然後執行mysql –uroot –p123456 登陸mysql資料庫執行：

mysql>GRANT REPLICATION SLAVE ON *.* TO "yw[j1] "@"192.168.1.10[j2] 2" IDENTIFIED BY 'ym[j3] ';

mysql>flush privileges;

B節點操作步驟如上，許可權賦予如下：

mysql>GRANT REPLICATION SLAVE ON *.* TO "yw[j4] "@"192.168.1.10[j5] 1" IDENTIFIED BY 'ym[j6] ';

mysql>flush privileges;

執行以上操作後，運行net stop mysql, 分別停止兩節點資料庫.

1.3 配置資料同步選項
1.3.1 A主機操作
安裝好的iKEY Server windows 版本後,會在安裝目錄下的iKEY\mysql\backup檔案夾下產生一個my_master.cnf設定檔，在A主機上將my_master.cnf 檔案複製到安裝目錄下的iKEY\mysql\bin檔案夾下，並重新命名為my.cnf,重新命名之前請先備份原有my.cnf檔案，以下介紹my.cnf做的修改之處：

log-bin=mysql-bin #同步事件的日誌記錄檔案

binlog-do-db=ikey_db #提供資料同步服務的資料庫日誌

binlog-do-db=ikey_log #提供資料同步服務的資料庫日誌

server-id=1
master-host=192.168.1.102 #主機B的IP地址
master-user=ym #同步帳戶
master-password=ym #同步帳戶密碼
master-port=3306 #連接埠，主機的MYSQL連接埠
master-connect-retry=60 #稍候再試60秒
replicate-do-db=ikey_db #同步的資料庫

replicate-do-db=ikey_log #同步的資料庫

1.3.2 B主機操作
安裝好的iKEY Server windows 版本後,會在安裝目錄下的iKEY\mysql\backup檔案夾下產生一個my_slave.cnf設定檔，在B備機上將my_slave.cnf 檔案複製到安裝目錄下的iKEY\mysql\bin檔案夾下，並重新命名為my.cnf,重新命名之前請先備份原有my.cnf檔案，修改之處同A主機，不同配置在於以下：

server-id=2
master-host=192.168.1.101 #主機A的地址

以上需要注意的地方是，要定義master-host的主機IP地址，請根據實際情況進行修改。

請確保以上同步處理的使用者、主機IP、和同步帳戶密碼與所設配置相同.

1.3.3 驗證資料同步
當以上配置完畢後，分別重新啟動主機A和備機B資料庫，運行net start mysql,

查看同步配置情況,登陸mysql 資料庫。

在A節點上查看Master 資訊：

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 | 98 | ikey_db,ikey_log | |

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

1 row in set (0.00 sec)

在B節點上查看Slave資訊：

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.101

Master_User: ym

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 98

Relay_Log_File: testBBB-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: ikey_db,ikey_log

通過在B節點上查看同步狀態，我們可以清楚的看到所設定的同步配置資訊以及當前同步狀態。

同樣可以在B節點上查看master 資訊以及在A節點上查看slave 資訊。

添加資料在A主機上，同時測試B備機上資料是否同步，以及反向測試。

1.4 同步維護
當需要更改節點IP 時，Mysql 同步配置也需要進行相應修改，在更改節點IP之前，我們先做如下操作：

分別在A、B節點上執行mysql> stop slave; 停止當前同步狀態。

假如此時A 節點IP 改為192.168.1.103，需要做如下操作：

1.4.1 A節點：
mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 | 118 | ikey_db,ikey_log | |

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

1 row in set (0.00 sec)

1.4.2 B節點：
更改B--àA方向同步的帳戶許可權

mysql>show grants for ym@192.168.1.101(原A節點IP)；

查看到原賦予從192.168.1.101 串連的許可權，此時我們刪除此同步帳戶，並重新賦予192.168.1.103 串連的許可權，如下：

mysql>drop user ym@192.168.1.101;

mysql>flush privileges;

mysql>grant replication slave on *.* to ym@192.168.1.103 identified by 'ym';

mysql>flush privileges;

然後修改A-àB方向同步配置項：

mysql>CHANGE MASTER TO

-> MASTER_HOST='192.168.1.103', # Master伺服器位址

-> MASTER_USER='ym',

->MASTER_PASSWORD='ym', -> ->MASTER_LOG_FILE='mysql-bin.000002', # 剛才我們記錄A節點資料庫要執行複製的記錄檔。

-> MASTER_LOG_POS=118; #剛才記錄的A節點資料庫要複製的記錄檔位置。

Query OK, 0 rows affected (0.02 sec)

同時需要查看此時B節點的Master資訊，同步LOG日誌及POS位置

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 | 98 | ikey_db,ikey_log | |

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

1 row in set (0.00 sec)

同時請修改B節點mysql主設定檔中

master-host=192.168.1.103 #主機A的IP地址

1.4.3 接著A節點：
修改B-àA方向同步配置項：

mysql>CHANGE MASTER TO

->MASTER_LOG_FILE='mysql-bin.000003', # 剛才我們記錄B節點資料庫要執行複製的記錄檔。

-> MASTER_LOG_POS=98; #剛才記錄的B節點資料庫要複製的記錄檔位置。

Query OK, 0 rows affected (0.02 sec)

然後針對A、B節點啟動同步狀態，分別運行start slave; 然後查看同步狀態，確保在每台機器上查看show slave status;得出

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

即處於正常同步狀態。

以上是針對單節點IP修改所做的操作，如果A、B節點IP 都需要修改，參考以上配置進行操作，最好先針對單個節點IP 進行修改，並確保單向同步沒有問題後，再進行第二個節點IP修改以及配置。

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

[j1]執行同步許可權的使用者

[j2]主機B的IP地址

[j3]同步帳戶密碼

[j4]執行同步許可權的使用者

[j5]主機A的IP地址

[j6]同步帳戶密碼

第一步：
在A資料庫的my.ini中添加 複製代碼 代碼如下:server-id=1
log-bin=C:\mysqlback #同步事件的日誌記錄檔案
binlog-do-db=test1 #提供資料同步服務的資料庫
binlog-do-db=test2 #提供資料同步服務的資料庫
binlog-do-db=test3 #提供資料同步服務的資料庫
master-host=192.168.0.102 #主機B的地址
master-user=use102 #主機B提供應B的使用者，該使用者中需要包括資料庫test1 test12test3的許可權
master-password=usepwd102 #訪問密碼
master-port=3306 #連接埠，主機的MYSQL連接埠
master-connect-retry=60 #稍候再試60秒
replicate-do-db=test1 #同步的資料庫
replicate-do-db=test2 #同步的資料庫
replicate-do-db=test3 #同步的資料庫

第二步：
在B資料庫的my.ini中添加 複製代碼 代碼如下:server-id=2
log-bin=C:\mysqlback #同步事件的日誌記錄檔案
binlog-do-db=test1 #提供資料同步服務的資料庫
binlog-do-db=test2 #提供資料同步服務的資料庫
binlog-do-db=test3 #提供資料同步服務的資料庫
master-host=192.168.0.101 #主機B的地址
master-user=use101 #主機A提供給A的使用者，該使用者中需要包括資料庫test1 test12test3的許可權
master-password=usepwd101 #訪問密碼
master-port=3306 #連接埠，主機的MYSQL連接埠
master-connect-retry=60 #稍候再試60秒
replicate-do-db=test1 #同步的資料庫
replicate-do-db=test2 #同步的資料庫
replicate-do-db=test3 #同步的資料庫

第三步：
將A的mysql資料的許可權給B
mysql>GRANT FILE ON *.* TO 'use101'@'192.168.0.102'IDENTIFIEDBY 'pwd101';
將B的Mysql資料的許可權給B操作同上。
第四步：
重啟AB資料庫，後：
B機器：
mysql>slave start;
查看同步配置情況
A機器：
mysql>show master status\G;
B機器：
mysql>show slave status\G;
假如A與B資料庫沒有同步，檢查mysql安裝目錄下的.err檔案。
如果slave日誌中報錯資訊如下：
060807 11:40:17 [ERROR] While trying to obtain the list of slaves from the master 'xxx.xxx.xxx:3306' user 'rep' got the following error: 'Access denied. You need the REPLICATION SLAVE privilegefor this operation'在master上，執行以下語句查看許可權：
mysql>SHOW GRANT FOR 'use101'@'192.168.0.102'\G *************************** 1. row *************************** Grants for rep@192.168.0.102: GRANT Select REPLICATION SLAVE ON *.* TO 'rep'@'192.168.0.102'IDENTIFIED BY PASSWORD 'xxx'已經授予了 REPLICAION SLAVE 許可權了，怎麼還會報這個錯呢？
通過查看手冊和源碼，才知道slave需要執行一個語句來更新slave列表：
SHOW SLAVE HOSTS;而執行這個語句則需要 REPLICAITON CLIENT 許可權，因此才會報錯。因此，只要重新給 帳號加上 REPLICATION CLIENT 許可權就可以了。
grant selectreplication slaveREPLICAION CLIENT on *.* to 'use101'@'192.168.0.102'identified by 'pwd101';