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双主双从集群配置

?

1 2 3 4 5

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的远程密码

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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配置

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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配置

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

?

1 2 3 4 5

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

4、修改从机slave2

?

1 2 3 4 5

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

重启服务

?

1	docker ps -a -q|xargs docker restart

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

1、节点1配置主从

查看master1状态

?

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

#创建用户并授权

?

1 2 3 4 5 6 7 8 9 10 11

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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配置主从

?

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

#创建用户并授权

?

1 2 3 4 5 6 7 8 9 10 11

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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的同步配置

?

1 2 3 4 5 6 7

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的同步配置

?

1 2 3 4 5 6 7 8

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;

验证集群

建库建表插入数据

?

1 2 3 4 5 6 7 8 9

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配置

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

[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>

验证读写分离

?

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