由于mysql互为主从只满足2台数据库，环形可以满足大于2台数据库多主同步机制。相对于互为主从要注意就是每个数据库同步完之后需要更新bin-log日志，这样才能让自己从服务也执行更新（log-slave-updates = true）
1、搭建环形多主
1) 试验环境
主数据库1：192.168.70.128
主数据库2：192.168.70.129
主数据库3：192.168.70.130
为了防止干扰，关闭防火墙 service iptables stop
2）各自配置数据库配置文件my.cnf
① 主数据库1：192.168.70.128
[mysqld]
port = 3306
log-bin=mysql-bin
# server-id需要唯一
server-id = 1
#以下两步是主从复制没有的，为了防止自增id写入冲突
#步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_increment=3
#起始值。一般填第n台主MySQL。此时为第一台主MySQL
auto_increment_offset=1
#同步之后更新bin-log日志(这里跟互为主从的区别配置)
log-slave-updates = true
②主数据库2：192.168.70.129
[mysqld]
port = 3306
log-bin=mysql-bin
# 唯一
server-id = 2
auto_increment_increment=3
# 第二台设置为2
auto_increment_offset=2
log-slave-updates = true
③主数据库3：192.168.70.130
[mysqld]
port = 3306
log-bin=mysql-bin
# 唯一
server-id = 3
auto_increment_increment=3
# 第三台设置为3
auto_increment_offset=3
log-slave-updates = true
#分别重启数据库service mysql restart
3) 以主数据1为准，主数据库2、3同步其数据
① 主数据库1：192.168.70.128
[root@192 ~]# mysqldump -uroot -proot -A > all_data.sql
[root@192 ~]# scp all_data.sql root@192.168.70.129:~
[root@192 ~]# scp all_data.sql root@192.168.70.130:~
②主数据库2：192.168.70.129
[root@192 ~]# mysql -uroot -proot < ~/all_data.sql
③主数据库3：192.168.70.130
[root@192 ~]# mysql -uroot -proot < ~/all_data.sql
4）查看当前bin-log的文件跟位置，并且授权给从数据库登录
① 主数据库1：192.168.70.128 主数据库3是主数据库1的从数据库，所以授权给主数据库3即可
[root@192 ~]# mysql -uroot -proot
mysql> show master status;
mysql> GRANT REPLICATION SLAVE ON *.* TO mrslave@192.168.70.130 identified by "123456";
mysql> flush privileges;
②主数据库2：192.168.70.129
主数据库1是主数据库2的从数据库，所以授权给主数据库1即可
[root@192 ~]# mysql -uroot -proot
mysql> show master status;
mysql> GRANT REPLICATION SLAVE ON *.* TO mrslave@192.168.70.128 identified by "123456";
mysql> flush privileges;
③主数据库3：192.168.70.130
主数据库2是主数据库3的从数据库，所以授权给主数据库2即可
[root@192 ~]# mysql -uroot -proot
mysql> show master status;
mysql> GRANT REPLICATION SLAVE ON *.* TO mrslave@192.168.70.129 identified by "123456";
mysql> flush privileges;
5） 开启从数据库
① 主数据库1：192.168.70.128
mysql> CHANGE MASTER TO master_host ='192.168.70.129', master_user ='mrslave',master_password ='123456',master_log_file ='mysql-bin.000041',master_log_pos =909987;
mysql> start slave;
mysql>show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.70.129
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 连接状态
②主数据库2：192.168.70.129
mysql>CHANGE MASTER TO master_host ='192.168.70.130',master_user ='mrslave',master_password ='123456',master_log_file =' mysql-bin.000041',master_log_pos = 909987;
mysql> start slave;
mysql>show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.70.130
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 连接状态
③主数据库3：192.168.70.130
mysql>CHANGE MASTER TO master_host ='192.168.70.128',master_user ='mrslave',master_password ='123456',master_log_file ='mysql-bin.000036',master_log_pos = 1865;
mysql> start slave;
mysql>show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.70.128
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 连接状态
6)测试同步数据
① 主数据库1：192.168.70.128
mysql> use test;
mysql> CREATE TABLE `tt` (
>`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
>`title` varchar(255) DEFAULT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
mysql> INSERT INTO tt (`title`)VALUES('t1');
mysql> SELECT * FROM tt;
+----+-------+
| id | title |
+----+-------+
| 1 | t1 |
+----+-------+
②主数据库2：192.168.70.129
mysql> use test;
mysql> SELECT * FROM tt;
+----+-------+
| id | title |
+----+-------+
| 1 | t1 |
+----+-------+
③主数据库3：192.168.70.130
mysql> use test;
mysql> SELECT * FROM tt;
+----+-------+
| id | title |
+----+-------+
| 1 | t1 |
+----+-------+
版权声明：未经博主允许不得转载。http://smister.com/post-42/mysql-circle-master.html