数据库应用：Mycat实现读写分离

cronaldo91

已于 2023-08-26 09:13:19 修改

阅读量576

点赞数

分类专栏：数据库应用文章标签：数据库

于 2023-07-21 23:59:03 首次发布

本文链接：https://blog.csdn.net/cronaldo91/article/details/131851978

版权

数据库应用专栏收录该内容

30 篇文章 0 订阅

订阅专栏

一、理论

1.Mycat

（1）概念

MyCAT是一款由阿里Cobar演变而来的用于支持数据库读写分离、分片的分布式中间件。MyCAT可不但支持Oracle、MSSQL、MYSQL、PG、DB2关系型数据库，同时也支持MongoDB等非关系型数据库。

所谓中间件,是一类连接软件组件和应用的计算机软件,以便软件各部件之间的通信。

例如 tomcat，web的中间件。而数据库中间件是连接Java应用程序和数据库中间的软件。

总结：Mycat 是数据库中间件，连接 Java 应用程序和数据库，它的作用如下：

①读写分离

②数据分片

③多数据源整合

（2）架构

MyCAT使用Mysql的通讯协议模拟成了一个Mysql服务器，所有能使用Mysql的客户端以及编程语言都能将MyCAT当成是Mysql Server来使用，不必开发新的客户端协议。

（3）原理

Mycat的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

Mycat 拦截了用户发送过来的 SQL 语句，首先对 SQL 语句进行特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将该 SQL 发送到真实的数据库，并处理返回的结果，再返回给用户。

2.Mycat安装启动

（1）安装

官网：下载压缩包，拷贝到 Linux 并解压

Mycat1.6

（2）三个配置文件

① chema.xml：定义逻辑库，表、分片节点等内容

② rule.xml：定义分片规则

③ server.xml：定义用户以及系统相关变量，如端口等

（3）启动程序，有两种方式

① 控制台启动：mycat/bin 目录下执行 ./mycat console

② 后台启动：mycat/bin 目录下 ./mycat start

（3）登录

登录后台管理窗口，此登录方式用于管理维护 Mycat

mysql -umycat -p123456 -P 9066 -h localhost

登录数据窗口，此登录方式用于通过 Mycat 查询数据

mysql -umycat -p123456 -P 8066 -h localhost

3.Mycat搭建读写分离

（1）目的

通过 Mycat 和 MySQL 的主从复制配合搭建数据库的读写分离，实现 MySQL 的高可用性

（2）主从复制原理

① 主库的更新事件（update、insert、delete）被写到 binlog

② 主库创建一个 binlog dump thread，把 binlog 的内容发送到从库

③ 从库启动并发起连接，连接到主库

④ 从库启动之后，创建一个 I/O 线程，读取主库传过来的 binlog 内容并写入到 relay log

⑤ 从库启动之后，创建一个 SQL 线程，从 relay log 里面读取内容，从 Exec_Master_Log_Pos 位置开始执行读取到的更新事件，将更新内容写入到 slave 的 db

（3）一主一从

一个主机用于处理所有写请求，一台从机负责所有读请求，架构图如下：

（4）双主双从

一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机，架构图如下：

4.垂直分库

（1）概念

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面

（2）分库原则

由于在两台主机上的两个数据库中的表不能关联查询，所以有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里

5.水平分表

（1）概念

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中

（2）常用分片规则

①取模

此规则为对分片字段求摸运算，也是水平分表最常用规则。

②分片枚举

通过在配置文件中配置可能的枚举 id，自己配置分片，本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。

（3）全局序列

①概念

在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式

②本地文件

此方式 Mycat 将 sequence 配置到文件中，当使用到 sequence 中的配置后，Mycat 会取 classpath 中的 sequence_conf.properties 文件中 sequence 当前的值

1）优点：本地加载，读取速度较快

2）缺点：抗风险能力差，Mycat 所在主机宕机后，无法读取本地文件

③ 数据库方式

利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库，这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中，这样大部分读写序列都是在内存中完成的。如果内存中的号段用完了 Mycat 会再向数据库要一次。

④ 时间戳方式

全局序列 ID = 64 位二进制(42(毫秒) + 5(机器 ID) + 5(业务编码) + 12(重复累加) 换算成十进制为 18 位数的 long 类型，每毫秒可以并发 12 位二进制的累加

1）优点：配置简单

2）缺点：18 位 ID 过长

⑤ 自主生成全局序列

在 Java 项目里自己生成全局序列，可以利用 redis 的单线程原子性 incr 来生成序列

6.Mycat高可用

（1）背景

在实际项目中，Mycat 服务也需要考虑高可用性，如果 Mycat 所在服务器出现宕机，或 Mycat 服务故障，需要有备机提供服务，需要考虑 Mycat 集群

（2）高可用方案

可以使用 HAProxy + Keepalived 配合两台 Mycat 搭起 Mycat 集群，实现高可用性。HAProxy 实现了 MyCat 多节点的集群高可用和负载均衡，而 HAProxy 自身的高可用则可以通过 Keepalived 来实现：

7.Mycat安全设置

（1）权限配置

① user标签权限控制

Mycat 对于中间件的连接控制并没有做太复杂的控制，只做了中间件逻辑库级别的读写权限控制，通过 server.xml 的 user 标签进行配置

# server.xml配置文件user部分
<user name="mycat">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
</user>
<user name="user">
     <property name="password">user</property>
     <property name="schemas">TESTDB</property>
     <property name="readOnly">true</property>
</user>

配置说明：

1）name：应用连接中间件逻辑库的用户名

2）password：该用户对应的密码

3）TESTDB：应用当前连接的逻辑库中所对应的逻辑表，schemas 中可以配置一个或多个

4）readOnly：应用连接中间件逻辑库所具有的权限，true 为只读，false 为读写都有，默认为 false

② privileges标签权限控制

在 user 标签下的 privileges 标签可以对逻辑库（schema）、表（table）进行精细化的 DML 权限控制.

privileges 标签下的 check 属性，如为 true 开启权限检查，为 false 不开启，默认为 false。由于 Mycat 一个用户的 schemas 属性可配置多个逻辑库（schema），所以 privileges 的下级节点 schema 节点同样可配置多个，对多库多表进行细粒度的 DML 权限控制：

# server.xml配置文件privileges部分
# 配置orders表没有增删改查权限
<user name="mycat">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
 	<!-- 表级 DML 权限设置 -->
	<privileges check="true">
		<schema name="TESTDB" dml="1111" >
			<table name="orders" dml="0000"></table>
			<!--<table name="tb02" dml="1111"></table>-->
		</schema>
	</privileges>
</user>

配置说明：

③SQL拦截

firewall 标签用来定义防火墙，firewall 下 whitehost 标签用来定义 IP 白名单，blacklist 用来定义 SQL 黑名单。

白名单：

# 配置只有 192.168.204.100 主机可以通过 mycat 用户访问
<firewall>
    <whitehost>
        <host host="192.168.204.100" user="mycat"/>
    </whitehost>
</firewall>

黑名单：

可以通过设置黑名单，实现 Mycat 对具体 SQL 操作的拦截，如增删改查等操作的拦截

# 配置禁止mycat用户进行删除操作
<firewall>
    <whitehost>
        <host host="192.168.204.100" user="mycat"/>
    </whitehost>
    <blacklist check="true">
        <property name="deleteAllow">false</property>
    </blacklist>
</firewall>

可以设置的黑名单 SQL 拦截功能列表：

8.Mycat监控工具

（1）Mycat-web

Mycat-web 是 Mycat 可视化运维的管理和监控平台，弥补了 Mycat 在监控上的空白。帮 Mycat 分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心，可以管理多个节点。 Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等，具备 IP 白名单、邮件告警等模块，还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。

下载地址：

https://zookeeper.apache.org/

（2）Mycat-web 默认数据库

Mycat-web 默认是使用 SQLite数据库，此数据库在 /usr/local/apps/mycat-web/mycat-web/WEB-INF/db，名字为 mycat-web.db。

Mycat-web 的数据库配置文件在 /usr/local/apps/mycat-web/mycat-web/WEB-INF/classes/jdbc.properties

根据配置文件，我们可以将数据库的连接改为 MySQL 的连接方式。它的初始化 SQL 脚本在 /usr/local/apps/mycat-web/mycat-web/WEB-INF/dbmycat-web.sql

由此，可以通过这个 SQL 文件初始化数据库，可将 Mycat-web 的数据库配置方式改成 MySQL，更加的方便我们去控制 Mycat-web 的数据库。
（3）Mycat-web 默认端口

mycat-web 默认端口为 8082，在配置文件 /usr/local/apps/mycat-web/etc/jetty.xml 中配置。

查看该配置文件可以发现Mycat-web的默认端口 port，以及请求地址 contextPath。

vim /usr/local/apps/mycat-web/etc/jetty.xml

（3）Mycat-web配置使用

首先安装 Zookeeper，下载安装包并解压，进入 ZooKeeper 解压后的配置目录（conf），复制配置文件并改名

# 将程序放在 /usr/local/ 下，如果没有则先新建 mkdir /usr/local/   (当然也可以放在其他目录下)
cd /opt

tar zxvf apache-zookeeper-3.7.1-bin.tar.gz

cp -a apache-zookeeper-3.7.1-bin/ /usr/local/

cd apache-zookeeper-3.7.1-bin/conf

cp zoo_sample.cfg zoo.cfg

进入 ZooKeeper 的命令目录（bin），运行启动命令

./zkServer.sh start

ZooKeeper 服务端口为 2181，查看服务已经启动

netstat -ant | grep 2181

（3）Mycat-web安装

下载安装包并解压，进入解压目录下运行启动命令

# 将程序放在 /usr/local/ 下，如果没有则先新建 mkdir /usr/local/   (当然也可以放在其他目录下)
cd /opt

tar zxvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz

cp -a mycat-web/ /usr/local/

cd /usr/local/mycat-web/

vim /usr/local/mycat-web/mycat-web/WEB-INF/classes/mycat.properties

sqlonline.server=192.168.204.250

cd /usr/local/mycat-web/

./start.sh &

Mycat-web 服务端口为 8082，查看服务已经启动

netstat -ant | grep 8082

通过地址访问服务：

http://192.168.204.250:8082/mycat/

先在注册中心配置 ZooKeeper 地址，配置后刷新页面，可以看到配置页面

二、实验

1.Mycat读写分离

（1）实验环境

Master 服务器：192.168.204.10，mysql5.7

Slave1 服务器：192.168.204.11，mysql5.7

Slave2 服务器：192.168.204.12，mysql5.7

Mycat服务器：192.168.204.250，jdk1.6、Mycat-server-1.6.7.6 ，centos7

客户端：192.168.204.100， mysql5.7，centos7

（2）Mycat安装

① 解压

cd /opt
tar zxvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
cp -r mycat /usr/local/

②配置环境变量

export MYCAT_HOME=/usr/local/mycat

设置并刷新环境变量使得mycat可以补全

[root@localhost opt]# echo 'PATH=/usr/local/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@localhost opt]# source /etc/profile.d/mycat.sh
[root@localhost opt]# mycat start

③启动mycat

④ 启动成功日志末尾会出现successfully，mycat只能通过查看日志的方式判断是否启动成功

[root@localhost opt]# tail -f /usr/local/mycat/logs/wrapper.log

创建测试库

master创建，slave同步

⑤ 配置schema.xml

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

        <schema name="mycatdb1" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <schema name="mycatdb2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2">
        </schema>
        <schema name="mycatdb3" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn3">
        </schema>

                <!-- 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"
                        /> -->

        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <dataNode name="dn1" dataHost="localhost1" database="testdb1" />
        <dataNode name="dn2" dataHost="localhost1" database="testdb2" />
        <dataNode name="dn3" dataHost="localhost1" database="testdb3" />
        <!--<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="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="192.168.204.10" url="192.168.204.10:3306" user="root"  password="123456">
                    <readHost host="192.168.204.11" url="192.168.204.11:3306" user="root" password="123456" />
                    <readHost host="192.168.204.12" url="192.168.204.12:3306" user="root" password="123456" />
                </writeHost>

                <writeHost host="192.168.204.11" url="192.168.204.11:3306" user="root"  password="123456" />

                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>

⑥ 配置server.xml

<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">mycatdb1,mycatdb2,mycatdb3</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">mycatdb1,mycatdb2,mycatdb3</property>
		<property name="readOnly">true</property>
	</user>

⑦ 修改MyCat日志级别

"info"修改为"debug"

⑧ 启动成功

⑨ 查看端口

netstat -an|grep 8066

⑩查看mycat进程

ps -ef|grep mycat

⑪登录mysql，查看逻辑库

[root@localhost ~]# mysql -uroot -p123456 -P8066 -h 192.168.204.250

mysql> show databases;

mysql> use mycatdb1;

mysql> show tables;

mysql> select * from t11;

⑫ 读写分离验证

主从服务器开启通用日志并实时查看，在客户端的表中新增内容可以看到只有主有日志变化显示

主从都执行：
mysql -uroot -p123456
#登录数据库
set global general_log=1;
#打开通用日志，通用日志若为yum安装则会在/var/lib/mysql/本机主机名.log文件
quit;
#退出数据库
主服务即主机名为localhost：
tail -f/var/lib/mysql/localhost.log
#实时查看通用日志

从服务即主机名为localhost：
tail -f/var/lib/mysql/localhost.log
#实时查看通用日志

客户端测试：
mysql -uroot -p123456 -h192.168.204.250 -P8066
#登录mycat
use mycatdb1;
insert into t11 values('a','b','c');

⑫ 进入mycatdb1库并在t11表中加入数据

⑬ 查看主从实时日志只有主日志显示变化则读写分离实现,日志截图如下:

[root@localhost mysql]# tail -f /var/lib/mysql/localhost.log

2.Mycat监控安装

（1）解压，重命名配置文件

（2）启动zookeeper

（3）监听端口

（4）启动mycat-web

(5)修改配置文件

(6)启动

（7）监听端口

（8）登录

http://192.168.204.250:8082/mycat/

（9）配置

（10）点击保存后，程序会自动的去更新 mycat.properties 配置文件的 zookeeper配置，文件地址在 /usr/local/mycat-web/mycat-web/WEB-INF/classes

（11）新增Mycat服务管理

(12)新增

（13）配置好后，可以在Mycat-监控菜单中查看Mycat的各种监控，比如：Mycat性能监控、JVM性能监控、主从同步监控等

（14）可以在 SQL-监控 菜单中查看SQL统计、SQL监控、慢SQL统计等功能

（15）也可以检测一些不符合Mycat的SQL语法

三、问题

1.Mycat命令无法补全

（1）报错

（2）解决方法

设置并刷新环境变量使得mycat可以补全

[root@localhost opt]# echo 'PATH=/usr/local/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@localhost opt]# source /etc/profile.d/mycat.sh

启动

[root@localhost opt]# mycat start

2.Mycat启动失败

（1）报错

查看日志

tail -f /usr/local/mycat/logs/wrapper.log

配置文件错误

（2）通用原因分析

①原因：schema 标签中的dataNode 属性与可嵌套的table 标签有依赖关系。如果不设置table标签，就必须设置

INFO | jvm 1 | ----/--/-- --:--:-- | Caused by: io.mycat.config.util.ConfigException: schema TEST didn't config tables,so you must set dataNode property!

②原因：server.xml 中<property name="schemas">TEST</property> 中的schema 的值不要用小写。

INFO | jvm 1 | ----/--/-- --:--:-- | Caused by: io.mycat.config.util.ConfigException: SelfCheck### schema TEST refered by user root is not exist!

③登录数据库后，执行 show tables; 命令执行1分钟(感觉上）。1. schema.xml 中用户名、密码与数据库中保持大小写一致。

ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0

④原因：schema 标签中的dataNode 属性与可嵌套的table 标签有依赖关系。如果不设置table标签，就必须设置dataNode属性。

INFO | jvm 1 | ----/--/-- --:--:-- | Caused by: io.mycat.config.util.ConfigException: schema TEST didn't config tables,so you must set dataNode property!

⑤原因：server.xml 中<property name="schemas">TEST</property> 中的schema 的值不要用小写。

原因：server.xml 中<property name="schemas">TEST</property> 中的schema 的值不要用小写。

⑥登录数据库后，执行 show tables; 命令执行1分钟(感觉上）。1. schema.xml 中用户名、密码与数据库中保持大小写一致。

ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0

（3）解决方法

[root@localhost ~]# cd /usr/local/mycat/conf/
[root@localhost conf]# vim schema.xml

修改文件

添加结束标识，schema 标签中的dataNode 属性与可嵌套的table 标签有依赖关系。如果不设置table标签，就必须设置dataNode属性。

3.zookeeper启动报错

（1）报错

启动时一直报: Starting zookeeper … FAILED TO START

（2）原因分析

以下几种情况:

1. 下载的版本问题（>= 3.5.5）
2. 端口冲突问题（>=3.5.0）

①下载的版本问题（>= 3.5.5）

版本：3.71，实际上只要 >= 3.5.5 版本都会出现这种问题。

问题原因：下载了错误的版本文件。因为Zookeeper 从3.5.5后开始拆分为两个版本，，而且他们的结构还很类似。

标准版本（Apache ZooKeeper x.y.z ），下载的文件名为：apache-zookeeper-x.y.z-bin.tar.gz
另一个是源码版本（Apache ZooKeeper x.y.z Source Release），下载的文件名为：apache-zookeeper-x.y.z.tar.gz

②端口冲突问题（>=3.5.0）
在3.5.5版本及以上，Zookeeper 提供了一个内嵌的Jetty容器来运行 AdminServer，默认占用的是 8080端口，AdminServer 主要是来查看 Zookeeper 的一些状态，如果机器上有其他程序（比如：Tomcat）占用了 8080 端口，也会导致 Starting zookeeper … FAILED TO START 的问题。

（3）解决方法

如果不需要 AdminServer ，可以直接禁用：打开 zoo.cfg 配置文件，直接添加以下语句即可。

禁用 AdminServer 服务

admin.enableServer=false

如果想使用 AdminServer , 那么可以直接在 zoo.cfg 配置文件中修改端口号即可，比如让其绑定 9000。

admin port

admin.serverPort=9000

四、总结

数据的同步在mysql层面实现的，mycat不负责任何的数据库同步；

mycat的搭建比较简单，但也有一些注意点：依赖JDK以及其版本，hostname配置等等。期间如果遇到什么问题，尽量百度，就不要google了，Mycat是中国制造，百度搜索的资料更全；

数据库的读写分离是mycat最常用的场景之一，配置比较简单，细心一点，实现起来应该不难；

读写分离往往还伴随着高可用，同样mycat也支持mysql的高可用，能够自动的进行master的切换。

以下情况不建议用Mycat分库分表：

非分片字段查询，分页排序，任意表JOIN，分布式事务。

Mycat-web 默认是使用 SQLite数据库，此数据库在 /usr/local/apps/mycat-web/mycat-web/WEB-INF/db，名字为 mycat-web.db。

Mycat-web 默认端口为 8082，在配置文件 /usr/local/apps/mycat-web/etc/jetty.xml 中配置。

cronaldo91

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
数据库应用：Mycat实现读写分离

数据的同步在mysql层面实现的，mycat不负责任何的数据库同步；mycat的搭建比较简单，但也有一些注意点：依赖JDK以及其版本，hostname配置等等。期间如果遇到什么问题，尽量百度，就不要google了，Mycat是中国制造，百度搜索的资料更全；数据库的读写分离是mycat最常用的场景之一，配置比较简单，细心一点，实现起来应该不难；读写分离往往还伴随着高可用，同样mycat也支持mysql的高可用，能够自动的进行master的切换。
复制链接

扫一扫