分布式数据库中间件Mycat双主双从环境配置

基于docker的一主一从配置

 

M1---mysql1，S1---mysql2，M2---mysql3，S2---mysql4

在这基础上

1、再添加2个实例

docker run --name mysql3 -p 33063:3306 -e MYSQL_ROOT_PASSWORD=1234 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
docker run --name mysql4 -p 33064:3306 -e MYSQL_ROOT_PASSWORD=1234 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

2、修改M1的mysqld.cnf

[mysqld]
log-bin=/var/lib/mysql/binlog
server-id=1
log-slave-updates//增加的，加上这行呢，作为从机，同步更新了，也需要在log中更新，使得附属他的从机也更新
binlog-do-db = mesdb
binlog-do-db = test1
binlog-do-db = test2
binlog-do-db = test3

3、修改M2的mysqld.cnf

[mysqld]
log-bin=/var/lib/mysql/binlog
server-id=3
log-slave-updates
binlog-do-db = mesdb
binlog-do-db = test1
binlog-do-db = test2
binlog-do-db = test3

4、修改S2的mysqld.cnf

[mysqld]
server-id=4

5、连上mysql3，配置账户

GRANT REPLICATION SLAVE ON *.* to 'slave2'@'%' identified by '1234';

6、设置M2和S1同步M1

在M1中执行

在M2中执行

change master to master_host='192.168.99.100',master_port=33061,master_user='slave1',master_password='1234',master_log_file='binlog.000009',master_log_pos=154;

S1之前已经配置好了

7、配置S2和M1同步M2

在M2中执行

在M1、S2中执行

change master to master_host='192.168.99.100',master_port=33063,master_user='slave2',master_password='1234',master_log_file='binlog.000001',master_log_pos=154;

8、Mycat配置

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" 
writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.99.100:33061" user="root" password="1234" >
        <readHost host="hostS1" url="192.168.99.100:33062" user="root"password="1234" />
    </writeHost>
    <writeHost host="hostM2" url="192.168.99.100:33063" user="root" password="1234" >
        <readHost host="hostS2" url="192.168.99.100:33064" user="root"password="1234" />
    </writeHost>
</dataHost>