MySql集群配置-三主互从

MySql集群配置-三主互从

1. 在每台MySql服务器中创建从库用户

   #登录mysql
   cd /home/mysql/mysql3306/bin/
   ./mysql -uroot -p -h 127.0.0.1 --socket=../tmp/mysql.sock --port=3306
   
   CREATE USER 'his'@'%';
   ALTER USER 'his'@'%' IDENTIFIED WITH mysql_native_password BY 'suxia@backup2021';
   GRANT REPLICATION SLAVE ON *.* TO 'his'@'%';
   flush privileges;
   
   CREATE USER 'his'@'192.168.10.%';
   ALTER USER 'his'@'192.168.10.%' IDENTIFIED WITH mysql_native_password BY 'suxia@backup2021';
   GRANT REPLICATION SLAVE ON *.* TO 'his'@'192.168.10.%';
   flush privileges;
   

2. 查看主库状态

   #1、停止已经启动的绑定
   stop slave;
   
   #2、重置绑定
   reset master;
   
   #查看主库状态
   show master status;
   
   

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

3. 配置每台数据库的双主

   # 192.168.10.110指定双主
   change master to master_host='10.170.207.221',master_port=3306,master_user='his',master_password='suxia@backup2021',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master1';
   
   change master to master_host='10.170.207.222',master_port=3306,master_user='his',master_password='suxia@backup2021',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
   

   # 192.168.10.120指定双主
   change master to master_host='10.170.207.220',master_port=3306,master_user='his',master_password='suxia@backup2021',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master1';
   
   change master to master_host='10.170.207.222',master_port=3306,master_user='his',master_password='suxia@backup2021',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
   

   
   # 192.168.10.130指定双主
   change master to master_host='10.170.207.220',master_port=3306,master_user='his',master_password='suxia@backup2021',master_log_file='mysql-bin.000001',master_log_pos=156 for channel 'master1';
   
   change master to master_host='10.170.207.221',master_port=3306,master_user='his',master_password='suxia@backup2021',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