1、環境描述
服務器A(主) 192.85.1.175
服務器B(從) 192.85.1.176Mysql版本：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;
在服務器A命令行執行：
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;
（1）分別在A與B服務器上的MYSQL命令行執行 “ show grants for 'replication'@'%';”
如果輸出結果類似於如下，表示正常，其他均為異常
mysql> show grants for 'replication'@'%'; +--------------------------------------------------------------------------------------------------------------------------------------+ | Grants for replication@% | +--------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY PASSWORD '*47E2485DF0DBED84B9BD90AF25F48A36E7EAD57E' | +--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
（2）使用主備復制賬號在A服務器遠程登錄B ，在B服務器遠程登錄A ，測試 ，如果可以正常登錄說明，賬號正常，可以使用。
如果出現如下錯誤信息：
ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.175' (111) sxzichen@ubutun:/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) sxzichen@ubutun:/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與B服務器 中數據庫配置文件my.cnf ，注釋掉 “ bind-address = 127.0.0.1 ” 即為 “#bind-address = 127.0.0.1”
分別重新啟動MYSQL服務器 ，測試正常
3.配置數據主切換 之服務器A ，修改其對應的數據配置文件----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 #遠程登錄數據庫用戶名稱
master-password = mysqlsync #遠程登錄數據庫用戶密碼
master-port = 3306 #遠程數據庫服務器端口號，可以使用 show variables like 'port'; 命令查看，必要與目標數據庫服務器端口號一致
4.配置數據主切換 之服務器B ，修改其對應的數據配置文件----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兩台服務器上分別檢查）：
啟動 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: 0
Master_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
6.進行測試，主備是否正常工作，slave 啟動時，兩個服務器上都啟動，啟動後不要隨意關閉再重啟，例如在服務器A上啟動後。
在服務器A數據中插入
INSERT INTO ddgl_qdlx ( qdlxmc, lxjc, qdlxbz) VALUES
('175a', '75a', '175a');
在服務器B中可以看到對應的記錄 。
同樣在服務器B中插入 ：
INSERT INTO ddgl_qdlx (qdlx_id, qdlxmc, lxjc, qdlxbz) VALUES ( '176b', '76b', '176b') ;在服務器A中可以看到對應的記錄 。
測試OK 。
