?

Mysql雙向同步復制??

?

http://zhumeng8337797.blog.163.com/blog/static/1007689142011590160330/

2011-06-09 00:16:00|??分類：?my復制?|??標簽：mysql??test1??master??slave??同步??|字號?訂閱

請參考文章《Mysql主從復制詳解》做以下實驗： 1、修改原slave?服務器配置 //?配置原slave服務器/etc/my.cnf文件,添加紅字的內容： server-id=2??????????????????????????#?從服務器ID號，不要和主ID相同? master-host=192.168.20.155???????????#?指定主服務器IP地址 master-user=replication??????????????#?指定在主服務器上可以進行同步的 用戶名 master-password=123456???????????????#?密碼 master-port=3306?????????????????????#?同步所用端口 master-connect-retry=60??????????????#?斷點從新連接時間 replicate-ignore-db=mysql????????????#?屏蔽對mysql庫的同步 replicate-do-db=test1????????????????#?同步的數據庫的名稱 log-bin=/var/log/mysql/updatelog?????#?設定生成log文件名 binlog-do-db=test1???????????????????#?設置同步數據庫名 binlog-ignore-db=mysql???????????????#?避免同步mysql用戶配置，以免不必 要的麻煩 2、創建更新日志的目錄并給mysql用戶的權限 # mkdir /var/log/mysql # chown -R mysql.mysql /var/log/mysql 3、重新啟動mysql服務，創建一個同步專用賬號 # service mysqld restart //給與從服務器用戶replication的同步權限 # mysql -uroot -p123456 mysql>??GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.20.%'IDENTIFIED BY '123456'; //刷新權限，使設置生效 mysql>Flush privileges; 4、修改原master配置文件 //?配置原master務器/etc/my.cnf文件,添加紅字的內容： log-bin=mysql-bin???????????????????#??啟動二進制日志系統 server-id=1?????????????????????????#?本機數據庫ID?標示為主log-bin=/var/log/mysql/updatelog????????#?設定生成log文件名，這里的路徑沒 有mysql目錄要手動創建并給于它 mysql用戶的權限。 binlog-do-db=test1??????????????????#?二進制需要同步的數據庫名 binlog-ignore-db=mysql,test?????????#?避免同步mysql用戶配置，以免不必 要的麻煩 master-host=192.168.20.128??????????#?設置從原slave數據庫同步更新 master-user=replication?????????????#?更新用戶 master-password=123456??????????????#?密碼 master-port=3306????????????????????#?端口 replicate-do-db=test1???????????????#?需要更新的庫 //?重啟mysql服務 # service mysqld restart //在原從服務器查詢 # mysql -uroot -p123456 mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File?????????????| Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | updatelog.000001 |??????106 | test1????????| mysql????????????| +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) //在原主服務器查詢 # mysql -uroot -p123456 mysql> SHOW MASTER STATUS; //先停止slave服務 mysql> SLAVE STOP; mysql> CHANGE MASTER TO MASTER_HOST='192.168.20.128',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='updatelog.000001',MASTER_LOG_POS=106; //?根據上面主服務器的show?master?status的結果，進行從服務器的二進制數據庫記錄回歸，達到同步的效果 //啟動從服務器同步服務 mysql> SLAVE START; 5、驗證配置 //在beijing原主服務器上進入mysql命令行 mysql> SHOW SLAVE STATUS\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes 此處Slave_IO_Running?,Slave_SQL_Running?都應該是yes,表示從庫的I/O,Slave_SQL線程都正確開啟.表明數據庫正在同步。 //在shanghai原從服務器上進入mysql命令行 mysql> SHOW SLAVE STATUS\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes 此處Slave_IO_Running?,Slave_SQL_Running?都應該是yes,表示從庫的I/O,Slave_SQL線程都正確開啟.表明數據庫正在同步。 6、測試 //在beijing服務器上建立一個表 mysql> use test1; mysql> create table test1(id int(4),name varchar(20)); mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | name????????????| |?test1???????????| | user????????????| +-----------------+ 3 rows in set (0.00 sec) //在shanghai服務器上查詢 mysql> use test1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql>??show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | name????????????| |?test1???????????| | user????????????| +-----------------+ 3 rows in set (0.00 sec) //在shanghai服務器上建立一個表 mysql> create table test2(id int(4),name varchar(20)); mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | name????????????| | test1???????????| |?test2???????????| | user????????????| +-----------------+ 4 rows in set (0.00 sec)| user????????????| +-----------------+ 3 rows in set (0.00 sec) //在beijing服務器上查詢 mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | name????????????| | test1???????????| |?test2???????????| | user????????????| +-----------------+ 4 rows in set (0.00 sec) 雙向復制試驗成功！！！

轉載于:https://blog.51cto.com/fccwcom/1179193

總結

以上是生活随笔為你收集整理的Mysql双向同步复制的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。