MySQL建立雙向主備複製server配置方法

標籤：

1、環境描寫敘述

　　serverA(主) 192.85.1.175

　　serverB(從) 192.85.1.176

　　Mysql版本號碼：5.1.61

       系統版本號碼：System OS：ubuntu 10.10 X86

（系統安裝及資料環境搭建，省略）

2. 建立同步處理的使用者：

在服務B命令列運行 ：

      grant replication slave on *.* to 'replication'@'%' identified by 'mysqlsync';        flush privileges;        grant FILE,SELECT, replication slave on *.* to 'replication'@'192.85.1.175' identified by 'mysqlsync';        flush privileges;  

在serverA命令列運行：

      grant replication slave on *.* to 'replication'@'%' identified by 'mysqlsync';        flush privileges;        grant FILE,SELECT, replication slave on *.* to 'replication'@'192.85.1.176' identified by 'mysqlsync';        flush privileges;  

在A與Bserver上檢查主備複製帳號是否正常：

（1）分別在A與Bserver上的MYSQL命令列運行 “  show grants for ‘replication‘@‘%‘;”

假設輸出結果類似於例如以下，表示正常，其它均為異常

mysql> show grants for 'replication'@'%';+--------------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                                             |+--------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY PASSWORD '*47E2485DF0DBED84B9BD90AF25F48A36E7EAD57E' |+--------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

（2）使用主備複製帳號在Aserver遠程登入B ，在Bserver遠程登入A ，測試 ，假設能夠正常登入說明，帳號正常，能夠使用。

假設出現例如以下錯誤資訊：

ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.175' (111)[email protected]:/var/log/mysql$ ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.175' (111) ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.175' (111)

或

ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.176' (111)[email protected]:/var/log/mysql$ ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.176' (111) ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.176' (111)

導致原因是，MYSQL資料庫不同意遠程訪問，須要分別改動A與Bserver 中資料庫設定檔my.cnf  ，凝視掉 “   bind-address           = 127.0.0.1 ” 即為 “#bind-address           = 127.0.0.1”

分別又一次啟動MYSQLserver ，測試正常 

3.配置資料主切換 之serverA ，改動其相應的資料設定檔----my.cnf ,在 [MYSQLD}加入例如以下行

#mysql replication  server-id = 1 log_bin = /var/log/mysql/mysql-bin.log  master-host = 192.85.1.176  master-user = replication master-password = mysqlsync master-port = 3306 master-connect-retry = 60 binlog-do-db = hrkip_zhhwd binlog-ignore-db = mysql   binlog-ignore-db = information_schema binlog-ignore-db = phpmyadmin replicate-do-db = hrkip_zhhwd    replicate-ignore-db = mysql,information_schema 

配置資訊簡介：

server-id               = 1    #主機標示，整數
log_bin                 = /var/log/mysql/mysql-bin.log   #確保此檔案可寫
binlog-do-db         =hrkip_zhhwd   #須要備份資料，多個寫多行
binlog-ignore-db    =mysql #不須要備份的資料庫，多個寫多行

master-user = replication   #遠程登入資料庫username稱
master-password = mysqlsync #遠程登入資料庫使用者密碼
master-port = 3306 #遠端資料庫server連接埠號碼，能夠使用 show variables like ‘port‘; 命令查看，必要與目標資料庫server連接埠號碼一致

4.配置資料主切換 之serverB ，改動其相應的資料設定檔----my.cnf ,在 [MYSQLD}加入例如以下行

#mysql replication  server-id = 2 log_bin = /var/log/mysql/mysql-bin.log  master-host = 192.85.1.175  master-user = replication master-password = mysqlsync master-port = 3306 master-connect-retry = 60 binlog-do-db = hrkip_zhhwd binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = phpmyadmin replicate-do-db = hrkip_zhhwd replicate-ignore-db = mysql,information_schema 

5.檢查配置是否正常（l兩台server上分別檢查）：

啟動 slave 服務 ，在MYSQL命令列運行

mysql> slave start ;                Query OK, 0 rows affected, 1 warning (0.00 sec)

查看slave 狀態 ，運行命令：

mysql> show slave status \G ;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.85.1.175                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 106               Relay_Log_File: ubutun-relay-bin.000005                Relay_Log_Pos: 251        Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: hrkip_zhhwd          Replicate_Ignore_DB: mysql,information_schema           Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 106              Relay_Log_Space: 552              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified

假設須要又一次啟動slave server，首先 運行 slave stop  ,再運行 reset  slave , 刪除  MYSQL 資料庫相應的my.cnf 檔案裡log_bin檔案夾（ /var/log/mysql/ ）下的"  mysql-bin."開頭的全部檔案，之後又一次啟動MYSQL服務,之後再運行slave start 命令 。

6.進行測試，主備是否正常工作，slave 啟動時，兩個server上都啟動，啟動後不要任意關閉再重新啟動，比如在serverA上啟動後。

在serverA資料中插入

INSERT INTO ddgl_qdlx ( qdlxmc, lxjc, qdlxbz) VALUES('175a', '75a', '175a');

在serverB中能夠看到相應的記錄 。

相同在serverB中插入 ：

INSERT INTO ddgl_qdlx (qdlx_id, qdlxmc, lxjc, qdlxbz) VALUES(  '176b', '76b', '176b') ;

在serverA中能夠看到相應的記錄 。

測試OK 。

MySQL建立雙向主備複製server配置方法