mysql双主双从集群+mycat读写分离

 

 实验：

环境准备

环境：centos7.3、docker

角色	ip	端口
master1	100.98.100.186	3306
master2	100.98.100.186	3307
slave1	100.98.100.186	3308
slave2	100.98.100.186	3309
mycat	100.98.100.186	8066

 

 

1、使用docker启动4个docker节点

mysql双主双从集群配置

mkdir -p /data/mysql{1..4}/{conf,logs,data}
docker run -p 3306:3306 --name master1 -v /data/mysql1/conf:/etc/mysql/conf.d -v /data/mysql1/logs:/logs -v /data/mysql1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker run -p 3307:3306 --name master2 -v /data/mysql2/conf:/etc/mysql/conf.d -v /data/mysql2/logs:/logs -v /data/mysql2/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker run -p 3308:3306 --name slave1 -v /data/mysql3/conf:/etc/mysql/conf.d -v /data/mysql3/logs:/logs -v /data/mysql3/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker run -p 3309:3306 --name slave2 -v /data/mysql4/conf:/etc/mysql/conf.d -v /data/mysql4/logs:/logs -v /data/mysql4/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

2、配置每个docker的远程密码

docker exec -it master1 /bin/bash
mysql -uroot -p123456
GRANT ALL ON *.* TO 'root'@'%';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
exit
exit

docker exec -it master2 /bin/bash
mysql -uroot -p123456
GRANT ALL ON *.* TO 'root'@'%';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
exit
exit

docker exec -it slave1 /bin/bash
mysql -uroot -p123456
GRANT ALL ON *.* TO 'root'@'%';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
exit
exit

docker exec -it slave2 /bin/bash
mysql -uroot -p123456
GRANT ALL ON *.* TO 'root'@'%';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
exit
exit

集群配置

1、修改master1配置

cat <<EOF > /data/mysql1/conf/my.cnf
[mysqld]
#主服务器唯一id
server-id=1
#启用二进制日志
log-bin=mysql-bin
#设置不用复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=test
#设置logbin格式
binlog_format=STATEMENT
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
#log-slave-updates
log_replica_updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围1..65535
auto-increment-increment=2
#表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1..65535
auto-increment-offset=1
EOF

2、修改master2配置

cat <<EOF > /data/mysql2/conf/my.cnf
[mysqld]
#主服务器唯一id 2留给master1的slave
server-id=3
#启用二进制日志
log-bin=mysql-bin
#设置不用复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=test
#设置logbin格式
binlog_format=STATEMENT
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
#log-slave-updates
log_replica_updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围1..65535
auto-increment-increment=2
#表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1..65535，这儿应该是2了
auto-increment-offset=2
EOF

3、修改从机slave1

cat <<EOF > /data/mysql3/conf/my.cnf
[mysqld]
server-id=2
relay-log=mysql-relay
EOF

4、修改从机slave2

cat <<EOF > /data/mysql4/conf/my.cnf
[mysqld]
server-id=4
relay-log=mysql-relay
EOF

重启服务

docker ps -a -q|xargs docker restart

创建同步用户、slave配置同步

1、节点1配置主从

查看master1状态

docker exec -it master1 /bin/bash
mysql -uroot -p123456

#创建用户并授权

create user 'slave'@'%' identified by  '123123';
grant REPLICATION SLAVE ON *.* TO 'slave'@'%' with grant option;
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123123';
flush privileges;
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000011 |      616 | test         | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

slave1节点加入到master1

docker exec -it slave1 /bin/bash
mysql -uroot -p123456
mysql> CHANGE MASTER TO MASTER_HOST='100.98.100.186',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_PORT= 3306,
MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=616;
#启动
mysql> start slave;
mysql> show slave status \G;
--如果出问题重新初始化
stop slave;
reset master;

2、节点2配置主从

docker exec -it master2 /bin/bash
mysql -uroot -p123456

#创建用户并授权

create user 'slave'@'%' identified by  '123123';
grant REPLICATION SLAVE ON *.* TO 'slave'@'%' with grant option;
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123123';
flush privileges;
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      860 | test         | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

slave2节点加入到master2

docker exec -it slave2 /bin/bash
mysql -uroot -p123456
mysql> CHANGE MASTER TO MASTER_HOST='100.98.100.186',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_PORT= 3307,
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=860;
#启动
mysql> start slave;
mysql> show slave status \G;
--如果出问题重新初始化
stop slave;
reset master;

3、master1和master2两个都是复制对方

master1 使用master2的binglog，即slave2的同步配置

CHANGE MASTER TO MASTER_HOST='100.98.100.186',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_PORT= 3307,
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=860;
mysql> start slave;
mysql> show slave status \G;

master2 使用master1的binglog，即slave1的同步配置

CHANGE MASTER TO MASTER_HOST='100.98.100.186',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_PORT= 3306,
MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=616;
#启动
mysql> start slave;
mysql> show slave status \G;

验证集群

建库建表插入数据

create database test 
 DEFAULT CHARACTER SET utf8mb4
 DEFAULT COLLATE utf8mb4_0900_ai_ci;
 use test;
create table student(
      id int,
      name varchar(100)
)
insert into student value(1,'zs');

mycat配置

 

 

[root@node-1 conf]# more schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="node-1" database="test" />
        <dataHost name="node-1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="master1" url="100.98.100.186:3306" user="root"  password="123456">
                   <readHost host="slave1" url="100.98.100.186:3308" user="root" password="123456" />
                </writeHost>
                <writeHost host="master2" url="100.98.100.186:3307" user="root"  password="123456">
                   <readHost host="slave2" url="100.98.100.186:3309" user="root" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

验证读写分离

mysql -uroot -p123456 -h100.98.100.186 -P8066
 mysql> select @@hostname from student;