mysql数据库集群三主互从搭建

环境准备：

三个节点分别部署mysql数据库，信息如下

数据库	ip
数据库1	1.1.1.1
数据库2	2.2.2.2
数据库3	3.3.3.3

1、登陆数据库1，创建其他俩个数据库同步用的用户

#登陆数据库
mysql -u root -p

#创建用户for 2.2.2.2

CREATE USER 'root'@'2.2.2.2';

ALTER USER 'root'@'2.2.2.2' IDENTIFIED WITH mysql_native_password BY 'password';

GRANT REPLICATION SLAVE ON *.* TO 'root'@'2.2.2.2';

flush privileges;

#创建用户for 3.3.3.3

CREATE USER 'root'@'3.3.3.3';

ALTER USER 'root'@'3.3.3.3' IDENTIFIED WITH mysql_native_password BY 'password';

GRANT REPLICATION SLAVE ON *.* TO 'root'@'3.3.3.3';

flush privileges;

数据库2、3的也像上面创建两个用户

2、登陆数据库1

#若之前配置过相关的数据库主从
#stop slave；
#reset master;

#查看主库状态
show master satatus;

+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      156 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+

 登陆数据库1，为其指定双主：

# 1.1.1.1指定双主
change master to master_host='2.2.2.2',master_port=3306,master_user='root',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master1';

change master to master_host='3.3.3.3',master_port=3306,master_user='root',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master2';

# 启动slave
start slave for channel 'master1';
start slave for channel 'master2';

# 查看slave状态
show slave status\G；

登陆数据库2，为其指定双主：

# 2.2.2.2指定双主
change master to master_host='1.1.1.1',master_port=3306,master_user='root',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master1';

change master to master_host='3.3.3.3',master_port=3306,master_user='root',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master2';

# 启动slave
start slave for channel 'master1';
start slave for channel 'master2';

# 查看slave状态
show slave status\G；

登陆数据库3，为其指定双主：

# 3.3.3.3指定双主
change master to master_host='1.1.1.1',master_port=3306,master_user='root',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master1';

change master to master_host='2.2.2.2',master_port=3306,master_user='root',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master2';

# 启动slave
start slave for channel 'master1';
start slave for channel 'master2';

# 查看slave状态
show slave status\G；