开源中文网

您的位置: 首页 > 数据库应用 > MySQL > 正文

配置好主从服务器之后,延展到双向主从操作

来源:  作者:

配置好主从服务器之后,延展到双向主从操作 

刚才配置好的,只是单向主从,现在将其加入双向主从,130和131都既是是主服务器同时又是从服务器 
这样,130上操作的数据可以同步到131上,反之131上操作的数据也可以同步到130就比较舒服了。 

好,开始操作了, 
之前130是主,131是从, 
现在反向配置 
在131上添加备份用户信息 

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456'; 

然后在130上操作如下 

CHANGE MASTER TO MASTER_HOST='172.16.18.131', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=326; 
发现操作不成功,提示: 
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first 

好的,执行: 
stop slave 
呵呵,OK了 
然后再执行 

CHANGE MASTER TO MASTER_HOST='172.16.18.131', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=326; 

这下,可以了。 

再执行如下: 

mysql> START SLAVE; 
Query OK, 0 rows affected (0.00 sec) 

好像可以了 

mysql> show processlist; 
+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 
| Id | User | Host | db | Command | Time | State | Info | 
+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 
| 3 | backup | 172.16.18.131:1164 | NULL | Binlog Dump | 75 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | 
| 4 | root | localhost | test | Query | 0 | NULL | show processlist | 
| 5 | system user | | NULL | Connect | 7 | Waiting for master to send event | NULL | 
| 6 | system user | | NULL | Connect | 7 | Has read all relay log; waiting for the slave I/O thread to update it | NULL | 
+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 
4 rows in set (0.00 sec) 

检查发现已经在运行了, 

在131上同样可以看到如下 
mysql> show processlist; 
+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 
| Id | User | Host | db | Command | Time | State | Info | 
+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 
| 1 | system user | | NULL | Connect | 7109 | Waiting for master to send event | NULL | 
| 2 | system user | | NULL | Connect | 5284 | Has read all relay log; waiting for the slave I/O thread to update it | NULL | 
| 3 | root | localhost | test | Query | 0 | NULL | show processlist | 
| 5 | backup | 172.16.18.130:1086 | NULL | Binlog Dump | 33 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | 
+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 

还可以通过如下命令查看 

linux2:/ # netstat -an|grep 3306 
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 
tcp 0 0 172.16.18.131:3306 172.16.18.130:1086 ESTABLISHED 
tcp 0 0 172.16.18.131:1164 172.16.18.130:3306 ESTABLISHED 

发现双向监控了 

在130上也是,而且是相反的 

web1:/etc # netstat -an|grep 3306 
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 
tcp 0 0 172.16.18.130:3306 172.16.18.131:1164 ESTABLISHED 
tcp 0 0 172.16.18.130:1086 172.16.18.131:3306 ESTABLISHED 

有意思,呵呵。

Tags:配置 主从 服务器
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明