配置好主从服务器之后，延展到双向主从操作
时间：2010-04-07 来源：xhq6632
配置好主从服务器之后，延展到双向主从操作
刚才配置好的，只是单向主从，现在将其加入双向主从，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 有意思，呵呵。
然后在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 有意思，呵呵。
相关阅读 更多 +
