MyCAT实现MySQL读写分离
MySQL中间件：
用户连接到MySQL的中间件（代理），中间件接收用户的访问转发给后端的mysql数据库。
MyCat:
是MySQL的一个中间件软件，Mycat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器。
用户通过mysql客户端工具访问到Mycat，Mycat将这些请求转发给后端的MySQL服务器。
MyCat的工作原理：
Mycat它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：判断用户是读还是写，然后把请求发给后端对应的MySQL数据库，并将MySQL返回的数据信息经过处理后返回给用户。
Mycat的安装：
-
Mycat是使用java开发的，所以安装Mycat之前需要安装jdk
-
Mycat官网：http://www.mycat.org.cn/
-
Mycat的下载网站：http://dl.mycat.org.cn/
[root@CentOS8 ~]# yum install java -y
[root@CentOS8 ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-
1.6.7.4-release-20200105164103-linux.tar.gz
[root@CentOS8 ~]# mkdir /apps
[root@CentOS8 ~]# tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
Mycat的目录结构：
[root@CentOS8 mycat]# tree -L 1
.
├── bin
├── catlet
├── conf
├── lib
├── logs
└── version.txt
bin mycat命令，启动、重启、停止等
catlet catlet为Mycat的一个扩展功能
conf Mycat 配置信息,重点关注
lib Mycat引用的jar包，Mycat是java开发的
logs 日志文件，包括Mycat启动的日志和运行的日志
version.txt mycat版本说明
logs目录:
wrapper.log mycat启动日志
mycat.log mycat详细工作日志
Mycat的配置文件
[root@CentOS8 conf]# tree -L 1
.
......
├── rule.xml
├── schema.xml
......
├── server.xml
.......
几个重要的配置文件:
-
server.xml： Mycat软件自身的相关配置。
-
schema.xml： Mycat连接MySQL服务器的相关配置
-
rule.xml： Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等
Mycat 主要配置文件说明
server.xml
存放Mycat软件本身相关的配置文件，比如：连接Mycat的用户，密码，数据库名称等
<?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="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。
在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误-->
<property name="useHandshakeV10">1</property>
<property name="removeGraveAccent">1</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sqlExecuteTimeout">300</property> <!-- SQL 执行超时 单位:秒-->
<property name="sequnceHandlerType">1</property>
<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
-->
<!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
<property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1开启 0关闭
-->
<property name="useOffHeapForMerge">0</property>
<!--
单位为m
-->
<property name="memoryPageSize">64k</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">false</property>
<!-- XA Recovery Log日志路径 -->
<!--<property name="XARecoveryLogBaseDir">./</property>-->
<!-- XA Recovery Log日志名称 -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
<!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行-->
<property name="parallExecute">0</property>
</system>
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着*-->
<!--例如<host host="127.0.0.*" user="root"/>-->
<!--例如<host host="127.0.*" user="root"/>-->
<!--例如<host host="127.*" user="root"/>-->
<!--例如<host host="1*7.*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<!--
<firewall>
<whitehost>
<host host="1*7.0.0.*" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
<!-- 表级 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">TESTDB</property>
<property name="readOnly">true</property>
<property name="defaultSchema">TESTDB</property>
</user>
</mycat:server>
| 参数 | 说明 |
|---|---|
| user | 用户配置节点 |
| name | 客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。 |
| password | 客户端登录MyCAT的密码 |
| schemas | 数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如:db1,db2 |
| privileges | 配置用户针对表的增删改查的权限 |
| readOnly | mycat逻辑库所具有的权限。true为只读，false为读写都有，默认为false |
| 注意： |
-
server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息
-
逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！
-
这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！
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">
<!-- auto sharding by id (long) -->
<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
<table name="customer" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile" autoIncrement="true" fetchStoreNodeByJdbc="true">
<childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable>
</table>
<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
password="root">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
schema.xml文件中配置的参数解释说明：:
| 参数 | 说明 |
|---|---|
| schema | 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应 |
| dataNode | 分片信息，也就是分库相关配置 |
| dataHost | 物理数据库，真正存储数据的数据库 |
配置说明:
schema:
| 属性 | 说明 |
|---|---|
| name | 逻辑数据库名，与server.xml中的schema对应 |
| checkSQLschema | 数据库前缀相关设置，这里为false |
| sqlMaxLimit | select 时默认的limit，避免查询全表 |
table
| 属性 | 说明 |
|---|---|
| name 表名， | 物理数据库中表名 |
| dataNode | 表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的name |
| primaryKey | 主键字段名，自动生成主键时需要设置 |
| autoIncrement | 是否自增 |
| rule | 分片规则名，具体规则下文rule详细介绍 |
dataNode
| 属性 | 说明 |
|---|---|
| name | 节点名，与table中dataNode对应 |
| datahost | 物理数据库名，与datahost中name对应 |
| database | 物理数据库中数据库名 |
dataHost
| 属性 | 说明 |
|---|---|
| name | 物理数据库名，与dataNode中dataHost对应 |
| balance | 均衡负载的方式 |
| writeType | 写入方式 |
| dbType | 数据库类型 |
| heartbeat | 心跳检测语句，注意语句结尾的分号要加 |
注意：
schema.xml文件中有三点需要注意：balance="1"，writeType="0" ,switchType="1"
schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。
balance 属性的取值范围：
-
0：不开启读写分离模式
-
1.表示开启读写分离模式
-
2：所有读操作都随机的在writeHost、 readhost上分发
-
3：所有读请求随机的分发到wiriterHost对应的readhost执行（只在 1.4 及其以后版本 有，1.3没有）
writeHost和readHost 标签：
这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。
唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。
注意：
Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的
主从复制将数据复制到readhost
范例：使用Mycat实现读写分离
实验环境：CentOS8.3
Mycat服务器：10.0.0.10 -- 2G内存以上，内存太小跑不动java
mysql：master：10.0.0.13 -- MySQL8.0
MySQL：slave:10.0.0.14 --- mysql8.0
实验准备：
关闭selinux、防火墙
设置时间同步
创建mysql主从数据库
安装mycat：
[root@CentOS8 ~]# yum install java -y
#下载并安装
[root@centos8 ~]#wgethttp://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-
1.6.7.6-release-20210303094759-linux.tar.gz
[root@centos8 ~]#mkdir /apps
[root@centos8 ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/ #-C:解压到对应的目录
配置环境变量：
[root@CentOS8 bin]# pwd
/apps/mycat/bin
echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
source /etc/profile.d/mycat.sh
安装好Mycat后启动mycat：
启动方法：
[root@CentOS8 bin]# mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@CentOS8 bin]# mycat start
Starting Mycat-server...
#启动以后查看mycat监听的端口：
[root@CentOS8 bin]# ss -ntlup
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
udp UNCONN 0 0 0.0.0.0%virbr0:67 0.0.0.0:* users:(("dnsmasq",pid=1266,fd=3))
udp UNCONN 0 0 0.0.0.0:111 0.0.0.0:* users:(("rpcbind",pid=743,fd=5),("systemd",pid=1,fd=78))
udp UNCONN 0 0 [::]:111 [::]:* users:(("rpcbind",pid=743,fd=7),("systemd",pid=1,fd=80))
tcp LISTEN 0 128 0.0.0.0:111 0.0.0.0:* users:(("rpcbind",pid=743,fd=4),("systemd",pid=1,fd=77))
tcp LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=827,fd=4))
tcp LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* users:(("java",pid=89594,fd=4))
tcp LISTEN 0 128 [::]:111 [::]:* users:(("rpcbind",pid=743,fd=6),("systemd",pid=1,fd=79))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=827,fd=6))
tcp LISTEN 0 50 *:35287 *:* users:(("java",pid=89594,fd=66))
tcp LISTEN 0 50 *:39839 *:* users:(("java",pid=89594,fd=68))
tcp LISTEN 0 50 *:1984 *:* users:(("java",pid=89594,fd=67))
tcp LISTEN 0 100 *:8066 *:* users:(("java",pid=89594,fd=91))
tcp LISTEN 0 70 *:33060 *:* users:(("mysqld",pid=87104,fd=32))
tcp LISTEN 0 100 *:9066 *:* users:(("java",pid=89594,fd=87))
tcp LISTEN 0 128 *:3306 *:* users:(("mysqld",pid=87104,fd=34))
#8066端口用于连接MyCAT
查看mycat的日志，确认是否启动成功
[root@CentOS8 bin]# tail /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/09/27 00:29:37 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/09/27 00:29:38 | Launching a JVM...
INFO | jvm 1 | 2022/09/27 00:29:40 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/09/27 00:29:40 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/09/27 00:29:40 |
INFO | jvm 1 | 2022/09/27 00:29:45 | MyCAT Server startup successfully. see logs in logs/mycat.log
在客户端使用默认密码123456来连接mycat
root@ubuntu1804-1:~# mysql -uroot -p123456 -h 10.0.0.10 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> exit
Bye
在mycat 服务器上修改server.xml文件配置Mycat的连接信息
例如：指定连接名、密码和用户连接上来能看到的数据库名
#这里只将端口号修改为了3306 下面可以修改默认账号和密码
<property name="serverPort">3306</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
修改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" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="10.0.0.13:3306" user="root" password="123456">
<readHost host="hostS1" url="10.0.0.14:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
客户端连接到mycat：
root@ubuntu1804-1:~# mysql -uroot -p123456 -h 10.0.0.10 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
测试：分别开启master和slave的通用日志，会发现读操作都在slave上进行，写操作都在master上进行。
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global general_log=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'general_log_file';
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log_file | /var/lib/mysql/CentOS8.log |
+------------------+----------------------------+
1 row in set (0.02 sec)
