基于Docker搭建Mycat环境实现Mysql数据库的读写分离

搭建环境

搭建Mysql主从服务

下载Mysql镜像

1
docker pull mysql:5.7

创建Mysql挂载目录

1
mkdir -p /opt/mysql/master/{conf,data,logs}
1
mkdir -p /opt/mysql/slave/{conf,data,logs}

进入conf目录中编辑my.cnf

主配置文件

1
2
3
4
5
6
7
8
9
10
11
12
[client]     
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 配置大小写不敏感
lower_case_table_names=1
server_id=66 ###服务器id
log-bin=mysql-bin ###开启日志文件

从配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 配置大小写不敏感
lower_case_table_names=1
server_id=88 ###服务器id
log-bin=mysql-bin ###开启日志文件
binlog_do_db=test

运行Mysql容器

1
docker run -it --name mysql-master -p 3308:3306 -e MYSQL_ROOT_PASSWORD=root --privileged=true -v /opt/mysql/master/conf/my.cnf:/etc/mysql/my.cnf -v /opt/mysql/master/data:/var/lib/mysql -v /opt/mysql/master/logs:/var/log/mysql -d --restart=always mysql:5.7
1
docker run -it --name mysql-slave -p 3309:3306 -e MYSQL_ROOT_PASSWORD=root --privileged=true -v /opt/mysql/slave/conf/my.cnf:/etc/mysql/my.cnf -v /opt/mysql/slave/data:/var/lib/mysql -v /opt/mysql/slave/logs:/var/log/mysql -d --restart=always mysql:5.7

运行mysql容器也可用docker-compose启动yml

新建docker 网桥

1
docker network create --subnet=172.101.0.0/16 Mycat-net

主 Mycat-mysql57-master.yml

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
#mkdir -p /data/Mycat-mysql57-master/{conf,data,logs,mysql}
#docker-compose -f Mycat-mysql57-master.yml up -d
#Mycat-mysql57-master.yml 配置文件如下
version: "2"

services:
Mycat-mysql57-master:
image: mysql:5.7
ports:
- "3308:3306"
command: --default-authentication-plugin=mysql_native_password
volumes:
- /data/Mycat-mysql57-master/conf:/etc/mysql
- /data/Mycat-mysql57-master/logs:/var/log/mysql
- /data/Mycat-mysql57-master/data:/var/lib/mysql
- /data/Mycat-mysql57-master/conf/my.cnf:/etc/mysql/my.cnf
environment:
- TZ=Asia/Shanghai
- LANG=C.UTF-8
- MYSQL_ROOT_PASSWORD=root
restart: always
container_name: Mycat-mysql57-master
privileged: true
networks:
Mycat-net:
ipv4_address: 172.101.0.10
networks:
Mycat-net:
external: true

从 Mycat-mysql57-slave.yml

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
#mkdir -p /data/Mycat-mysql57-slave/{conf,data,logs,mysql}
#docker-compose -f Mycat-mysql57-slave.yml up -d
#Mycat-mysql57-slave.yml 配置文件如下
version: "2"

services:
Mycat-mysql57-slave:
image: mysql:5.7
ports:
- "3309:3306"
command: --default-authentication-plugin=mysql_native_password
volumes:
- /data/Mycat-mysql57-slave/conf:/etc/mysql
- /data/Mycat-mysql57-slave/logs:/var/log/mysql
- /data/Mycat-mysql57-slave/data:/var/lib/mysql
- /data/Mycat-mysql57-slave/conf/my.cnf:/etc/mysql/my.cnf
environment:
- TZ=Asia/Shanghai
- LANG=C.UTF-8
- MYSQL_ROOT_PASSWORD=root
restart: always
container_name: Mycat-mysql57-slave
privileged: true
networks:
Mycat-net:
ipv4_address: 172.101.0.11
networks:
Mycat-net:
external: true

查看容器

1
2
3
4
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
7a54d7ecc8b7 mysql:5.7 "docker-entrypoint.s…" 2 minutes ago Up About a minute 33060/tcp, 0.0.0.0:13309->3306/tcp, :::13309->3306/tcp Mycat-mysql57-slave
da02d2ffff9b mysql:5.7 "docker-entrypoint.s…" 2 minutes ago Up About a minute 33060/tcp, 0.0.0.0:13308->3306/tcp, :::13308->3306/tcp Mycat-mysql57-master

连接到主服务器
查询主对应配置文件中的server_id,查询到了说明已经以上步骤ok。

1
2
3
4
5
6
show variables like '%server_id%';


Variable_name Value
server_id 66
server_id_bits 32

查询主状态信息

1
2
3
4
5
show master status;


File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000005 154

连接从服务器
同步主服务器配置

1
change master to master_host='172.101.0.10',master_user='root',master_password='root', master_log_file='mysql-bin.000005',master_log_pos=154;

开始同步

1
start slave;

检查从服务器复制功能状态

1
2
3
4
5
SHOW SLAVE STATUS;


Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_UUID Master_Info_File SQL_Delay SQL_Remaining_Delay Slave_SQL_Running_State Master_Retry_Count Master_Bind Last_IO_Error_Timestamp Last_SQL_Error_Timestamp Master_SSL_Crl Master_SSL_Crlpath Retrieved_Gtid_Set Executed_Gtid_Set Auto_Position Replicate_Rewrite_DB Channel_Name Master_TLS_Version
Waiting for master to send event 172.101.0.10 root 3306 60 mysql-bin.000005 154 7a54d7ecc8b7-relay-bin.000014 367 mysql-bin.000005 Yes Yes 0 0 154 747 None 0 No 0 No 0 0 66 bd58294d-9a8c-11ec-9dfd-0242ac65000a /var/lib/mysql/master.info 0 Slave has read all relay log; waiting for more updates 86400 0

Slave_IO_Running 、Slave_SQL_Running 都为Yes说明配置成功,验证效果,在主节点,新建数据库,和表,从库自动生成。

注意:不要在从库操作,会造成同步不正常,主从配置失效;
解决办法,将从库同步配置停止,将主库数据重新导入从库,再次执行一次主从配置;

搭建Mycat环境

下载 mycat包

1
wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

若出现 wget: unable to resolve host address ‘dl.mycat.io’

解决方案:
编辑/etc/hosts文件,添加 210.51.26.184 dl.mycat.io配置

1
2
vim /etc/hosts
210.51.26.184 dl.mycat.io
1
2
3
4
root@bigdata01 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
210.51.26.184 dl.mycat.io

编写Dockerfile

1
2
3
4
5
6
7
8
9
10
FROM java:8
USER root
COPY Mycat-server-1.6.5-release-20180122220033-linux.tar.gz /
RUN tar -zxf /Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
ENV MYCAT_HOME=/mycat
ENV PATH=$PATH:${MYCAT_HOME}/bin
WORKDIR $MYCAT_HOME/bin
RUN chmod u+x ./mycat
EXPOSE 8066 9066
CMD ["./mycat", "console"]

获取配置文件

1
2
tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz 
mv mycat/conf/ conf

1.2.4 修改mycat配置文件
真正需要修改的只有三个文件,具体官方文档写的很详细;
(1):schema.xml ,(2):server.xml,(3):rule.xml;
现在搭建的是Mysql读写分离,修改schema.xml、server.xm即可。rule.xml用于分表分库配置文件。

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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- TESTDB1 是mycat的逻辑库名称,链接需要用的 -->
<schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<!-- database 是MySQL数据库的库名 -->
<dataNode name="dn1" dataHost="localhost1" database="test" />
<!--
dataNode节点中各属性说明:
name:指定逻辑数据节点名称;
dataHost:指定逻辑数据节点物理主机节点名称;
database:指定物理主机节点上。如果一个节点上有多个库,可使用表达式db$0-99, 表示指定0-99这100个数据库;

dataHost 节点中各属性说明:
name:物理主机节点名称;
maxCon:指定物理主机服务最大支持1000个连接;
minCon:指定物理主机服务最小保持10个连接;
writeType:指定写入类型;
0,只在writeHost节点写入;
1,在所有节点都写入。慎重开启,多节点写入顺序为默认写入根据配置顺序,第一个挂掉切换另一个;
dbType:指定数据库类型;
dbDriver:指定数据库驱动;
balance:指定物理主机服务的负载模式。
0,不开启读写分离机制;
1,全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡;
2,所有的readHost与writeHost都参与select语句的负载均衡,也就是说,当系统的写操作压力不大的情况下,所有主机都可以承担负载均衡;
-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 可以配置多个主从 -->
<writeHost host="hostM1" url="192.168.0.171:3308" user="root" password="root">
<!-- 可以配置多个从库 -->
<readHost host="hostS2" url="192.168.0.171:3309" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
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
<?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/">

<!-- 读写都可用的用户 -->
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">mycat_testdb</property>

<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>

<!-- 只读用户 -->
<user name="user">
<property name="password">user</property>
<property name="schemas">mycat_testdb</property>
<property name="readOnly">true</property>
</user>

</mycat:server>

执行Dockerfile

1
docker build -t mycat:1.6.5 .

启动mycat

1
docker run -d --rm  -p:8066:8066 --name mycat-mysql -p 9066:9066 -v $PWD/logs:/mycat/logs -v $PWD/conf:/mycat/conf mycat:1.6.5

也可通过docker-compse启动yml

Mycat1.6.5.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#mkdir -p /data/Mycat/{conf,logs}
#docker-compose -f Mycat1.6.5.yml up -d
#Mycat1.6.5.yml 配置文件如下
version: "2"

services:
mycat:
image: mycat:1.6.5
ports:
- "8066:8066"
- "9066:9066"
volumes:
- /data/Mycat/logs:/mycat/logs
- /data/Mycat/conf:/mycat/conf
restart: always
container_name: mycat
privileged: true
networks:
Mycat-net:
ipv4_address: 172.101.0.9
networks:
Mycat-net:
external: true

验证效果

连接这四个服务

在root服务修改数据,其他三个服务的数据自动同步相应的修改。

在user服务修改数据报错,只允许读数据。

说明:
8066为mycat 操作myslq 的端口,可查询库,和增删改查等操作

9066为mycat 管理myslq 的端口,可查询操作日志,操作用户,和用时等

一辈子很短,努力的做好两件事就好:
第一件事是热爱生活,好好的去爱身边的人;
第二件事是努力学习,在工作中取得不一样的成绩,实现自己的价值,而不是仅仅为了赚钱。

继开 wechat
欢迎加我的微信,共同交流技术