使用Mycat+binlog实现Mysql的主从复制+读写分离

Mycat介绍

mycat是基于阿里Cobar开发的国产数据中间件，可以比较简单的和mysql的binlog组合实现主从复制、读写分离、分库分表。社区比较活跃，但是基于xml的配置真的让我头皮发麻
官网地址：mycat官网，不过相信我，官网你什么都学不到

环境搭建

这里我们还是使用docker-machine创建4个docker vm，来模拟4台mysql服务器。如果你是土豪也可以用4台真机，请随意
请先确保自己安装了docker和docker-machine

docker-machine create --driver virtualbox master1
docker-machine create --driver virtualbox slave1
docker-machine create --driver virtualbox master2
docker-machine create --driver virtualbox slave2

service name	ip	info
master1	192.168.99.100	主1
slave1	192.168.99.101	从1
master2	192.168.99.102	主2
slave2	192.168.99.103	从2

master1配置

ssh登陆master1

docker-machine ssh master1

创建mysql文件夹及my.cnf配置文件

sudo mkdir mysql
cd mysql
sudo mkdir conf
cd conf
sudo touch my.cnf
sudo vi my.cnf

懒人版如下，效果同上

sudo mkdir mysql;cd mysql;sudo mkdir conf;cd conf;sudo touch my.cnf;sudo vi my.cnf

此时目录结构如下：

master1的conf配置文件

[client]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
default-time-zone=timezone
default-time-zone='+8:00'
bind-address=0.0.0.0

server_id=1
log-bin=mysql-bin
binlog-do-db=logistics_scheduler
auto_increment_increment=2
auto_increment_offset=1
log-slave-updates
sync_binlog=1

创建mysql的docker容器
注意：这里需要先回到mysql的根目录

cd ..

docker run -p 3306:3306 --name mysql -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=2wsx@WSX -d mysql:5.6

进入容器内部，配置mysql

docker exec -it mysql /bin/bash
mysql -uroot -p2wsx@WSX

将master1作为主服务，并创建用于复制的用户和权限，最后刷新master状态

create user 'mytest'@'%' identified by '123456';\
grant replication slave on *.* to 'mytest'@'%' identified by '123456';\
flush privileges;
show master status;

此时会显示master的状态，如果出现empty set需检查my.cnf的设置是否正确

配置slave1

重复master1的1-4步骤，只有my.cnf不一样，slave1的配置如下

[client]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
default-time-zone=timezone
default-time-zone='+8:00'
bind-address=0.0.0.0

server_id=2
replicate-do-db=logistics_scheduler

配置master2

重复master1的1-4步骤，只有my.cnf不一样，master2的配置如下

[client]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
default-time-zone=timezone
default-time-zone='+8:00'
bind-address=0.0.0.0

server_id=3
log-bin=mysql-bin
binlog-do-db=logistics_scheduler
auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1

进入容器内部，配置mysql

docker exec -it mysql /bin/bash
mysql -uroot -p2wsx@WSX

将master2作为主服务，并创建用于复制的用户和权限，最后刷新master状态

create user 'mytest'@'%' identified by '123456';\
grant replication slave on *.* to 'mytest'@'%' identified by '123456';\
flush privileges;
show master status;

配置slave2

重复master1的1-4步骤，只有my.cnf不一样，slave2的配置如下

[client]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
default-time-zone=timezone
default-time-zone='+8:00'
bind-address=0.0.0.0

server_id=4
replicate-do-db=logistics_scheduler

配置主从

这里我们的master1和master2互为主从，master1和slave1做读写分离，master2和slave2做读写分离

进入master1设置

change master to master_host='192.168.99.102',master_user='mytest',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=576;
start slave;
show slave status \G;

进入slave1设置

change master to master_host='192.168.99.100',master_user='mytest',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=576;
start slave;
show slave status \G;

进入master2设置

change master to master_host='192.168.99.100',master_user='mytest',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=576;
start slave;
show slave status \G;

进入slave2设置

change master to master_host='192.168.99.102',master_user='mytest',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=576;
start slave;
show slave status \G;

验证

此时在master1或master2上新建数据库logistics_scheduler，4个数据库上均会同步生成。且在两个主服务上的操作都会同步

mycat配置

这里我们在本机使用docker和docker-compose搭建mycat的环境

目录结构如下

需要从官网下载mycat的压缩包，这里使用的是1.6版本
Dockerfile如下：

FROM openjdk:8
ADD mycat.tar.gz /usr/local/ 
VOLUME /usr/local/mycat/conf 
ENV MYCAT_HOME=/usr/local/mycat 
EXPOSE 8066 9066 
RUN chmod -R 777 /usr/local/mycat/bin 
CMD ["/usr/local/mycat/bin/mycat", "console","&"]

docker-compose如下：

version: '3.2'
services:
  mycat:
    container_name: mycat
    build: .
    ports:
      - 8066:8066
      - 9066:9066
    restart: always
    volumes:
      - ./conf/:/usr/local/mycat/conf/

conf文件夹是mycat的配置文件夹
主要有3个地方需要配置，我是从mycat的源码里面解压拷贝过来的

rule.xml 配置分片规则
schema.xml 配置dataHost、nodeHost及数据库信息
server.xml 配置mycat的连接信息

我们暂时不用分表，所以rule不用配置

server.xml配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
		<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
		<property name="useHandshakeV10">1</property>
		<property name="useSqlStat">1</property>  <!-- 1为开启实时统计、0为关闭 -->
		<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
		<property name="sequnceHandlerType">2</property>
		<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
		<property name="processorBufferPoolType">0</property>
		<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
		<property name="handleDistributedTransactions">0</property>
		<property name="useOffHeapForMerge">1</property>
		<property name="memoryPageSize">64k</property>
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<property name="systemReserveMemorySize">384m</property>

		<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
		<property name="strictTxIsolation">false</property>
		
	</system>

	<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">logistics_scheduler</property>
                <property name="readOnly">false</property>
	</user>

</mycat:server>

schema.xml配置如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="logistics_scheduler" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	</schema>

	<dataNode name="dn1" dataHost="localhost1" database="logistics_scheduler" />

	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="master1" url="192.168.99.100:3306" user="root"
				   password="2wsx@WSX">
			<!-- can have multi read hosts -->
			<readHost host="slave1" url="192.168.99.101:3306" user="root" password="2wsx@WSX" />
		</writeHost>

		<writeHost host="master2" url="192.168.99.102:3306" user="root"
				   password="2wsx@WSX">
			<!-- can have multi read hosts -->
			<readHost host="slave2" url="192.168.99.103:3306" user="root" password="2wsx@WSX" />
		</writeHost>
	</dataHost>

</mycat:schema>

mycat连接方式和mysql一样，可以用数据库连接工具试一哈

host：127.0.0.1
port：8066
username：root
password：123456

java项目里面使用同样的方式连接，且只需要只想mycat，mycat会给我们做读写的分发

至此实现了mycat+binlog的主从复制和读写分离，我暂时没有将slave设为只读，mycat的一些策略也未做详细介绍，具体可参考mycat的其它教程

Aweew