由于mysql互为主从只满足2台数据库,环形可以满足大于2台数据库多主同步机制。相对于互为主从要注意就是每个数据库同步完之后需要更新bin-log日志,这样才能让自己从服务也执行更新(log-slave-updates = true)

mysql-circle-master-1.png

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-master-master-2.png

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-master-master-3.png

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-master-master-3.png  

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 | 

+----+-------+

版权声明:未经博主允许不得转载。https://yigainian.com/post-42/mysql-circle-master.html