使用mycat分库分表

背景

由于公司部分项目使用了mycat来做分库分表，所以就花了点时间了解一下mycat

配置

下面是mycat主要配置文件

目录	说明
bin	mycat命令，启动、重启、停止等
catlet	catlet为Mycat的一个扩展功能
conf	Mycat 配置信息,重点关注
lib	Mycat引用的jar包，Mycat是java开发的
logs	日志文件，包括Mycat启动的日志和运行的日志。

Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

文件	说明
server.xml	Mycat的配置文件，设置账号、参数等
schema.xml	Mycat对应的物理数据库和数据库表的配置
rule.xml	Mycat分片（分库分表）规则

Mycat的架构其实很好理解，Mycat是代理，Mycat后面就是物理数据库。和Web服务器的Nginx类似。对于使用者来说，访问的都是Mycat，不会接触到后端的数据库。
我们现在做一个主从、读写分离，简单分表的示例。

服务器	IP	说明
Mycat	192.168.0.2	mycat服务器，连接数据库时，连接此服务器
database1	192.168.0.3	物理数据库1，真正存储数据的数据库
database2	192.168.0.4	物理数据库2，真正存储数据的数据库

Mycat作为主数据库中间件，肯定是与代码弱关联的，所以代码是不用修改的，使用Mycat后，连接数据库是不变的，默认端口是8066。连接方式和普通数据库一样，如：jdbc:mysql://192.168.0.2:8066/

server.xml

示例

<user name="test">
		<property name="password">test</property>  
    	<property name="schemas">lunch</property>  
    	<property name="readOnly">false</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	登录的用户名，也就是连接Mycat的用户名
–password	登录的密码，也就是连接Mycat的密码
–schemas	数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如需要这个用户需要管理两个数据库db1,db2，则配置db1,dbs
–privileges	配置用户针对表的增删改查的权限，具体见文档吧

我这里配置了一个账号test 密码也是test,针对数据库lunch,读写权限都有，没有针对表做任何特殊的权限。

schema.xml
schema.xml是最主要的配置项，首先看我的配置文件。

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

<!-- 数据库配置，与server.xml中的数据库对应 -->
	<schema name="lunch" checkSQLschema="false" sqlMaxLimit="100">
		<table name="lunchmenu" dataNode="dn1"  />
		<table name="restaurant" dataNode="dn1"  />
		<table name="userlunch" dataNode="dn1"  />
		<table name="users" dataNode="dn1"  />
		<table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2"  rule="mod-long" />

		
	</schema>

<!-- 分片配置 -->
	<dataNode name="dn1" dataHost="test1" database="lunch" />
	<dataNode name="dn2" dataHost="test2" database="lunch" />

<!-- 物理数据库配置 -->
	<dataHost name="test1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user();</heartbeat>
		<writeHost host="hostM1" url="192.168.0.2:3306" user="root" password="123456">	
		</writeHost>
	</dataHost>

	<dataHost name="test2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user();</heartbeat>
		<writeHost host="hostS1" url="192.168.0.3:3306" user="root" password="123456">	
		</writeHost>
	</dataHost>

</mycat:schema>

参数	说明
schema	数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应
dataNode	分片信息，也就是分库相关配置
dataHost	物理数据库，真正存储数据的数据库

每个节点的属性逐一说明：

schema:

属性	说明
name	逻辑数据库名，与server.xml中的schema对应
checkSQLschema	数据库前缀相关设置，建议看文档，这里暂时设为folse
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	心跳检测语句，注意语句结尾的分号要加。

应用场景

数据库分表分库

配置如下：

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

<!-- 数据库配置，与server.xml中的数据库对应 -->
	<schema name="lunch" checkSQLschema="false" sqlMaxLimit="100">
		<table name="lunchmenu" dataNode="dn1"  />
		<table name="restaurant" dataNode="dn1"  />
		<table name="userlunch" dataNode="dn1"  />
		<table name="users" dataNode="dn1"  />
		<table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2"  rule="mod-long" />

		
	</schema>

<!-- 分片配置 -->
	<dataNode name="dn1" dataHost="test1" database="lunch" />
	<dataNode name="dn2" dataHost="test2" database="lunch" />

<!-- 物理数据库配置 -->
	<dataHost name="test1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user();</heartbeat>
		<writeHost host="hostM1" url="192.168.0.2:3306" user="root" password="123456">	
		</writeHost>
	</dataHost>

	<dataHost name="test2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user();</heartbeat>
		<writeHost host="hostS1" url="192.168.0.3:3306" user="root" password="123456">	
		</writeHost>
	</dataHost>

</mycat:schema>

我在192.168.0.2、192.168.0.3均有数据库lunch。
lunchmenu、restaurant、userlunch、users这些表都只写入节点dn1，也就是192.168.0.2这个服务，而dictionary写入了dn1、dn2两个节点，也就是192.168.0.2、192.168.0.3这两台服务器。分片的规则为：mod-long。
主要关注rule属性，rule属性的内容来源于rule.xml这个文件，Mycat支持10种分表分库的规则，基本能满足你所需要的要求，这个必须赞一个，其他数据库中间件好像都没有这么多。
table中的rule属性对应的就是rule.xml文件中tableRule的name,具体有哪些分表和分库的实现，建议还是看下文档。我这里选择的mod-long就是将数据平均拆分。因为我后端是两台物理库，所以rule.xml中mod-long对应的function count为2，见下面部分代码：

<tableRule name="mod-long">
		<rule>
			<columns>id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
	</tableRule>
	
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<property name="count">2</property>
	</function>

数据库读写分离

配置如下：

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

<!-- 数据库配置，与server.xml中的数据库对应 -->
	<schema name="lunch" checkSQLschema="false" sqlMaxLimit="100">
		<table name="lunchmenu" dataNode="dn1"  />
		<table name="restaurant" dataNode="dn1"  />
		<table name="userlunch" dataNode="dn1"  />
		<table name="users" dataNode="dn1"  />
		<table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1"  />

		
	</schema>

<!-- 分片配置 -->
	<dataNode name="dn1" dataHost="test1" database="lunch" />


<!-- 物理数据库配置 -->
	<dataHost name="test1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user();</heartbeat>
		<writeHost host="hostM1" url="192.168.0.2:3306" user="root" password="123456">	
		<readHost host="hostM1" url="192.168.0.3:3306" user="root" password="123456">	
		</readHost>
		</writeHost>
	</dataHost>


</mycat:schema>

这样的配置与前一个示例配置改动如下：
删除了table分配的规则,以及datanode只有一个
datahost也只有一台，但是writehost总添加了readhost,balance改为1，表示读写分离。
以上配置达到的效果就是102.168.0.2为主库，192.168.0.3为从库。

注意：Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的主从复制将数据复制到readhost，这个问题当时候我纠结了好久，数据写入writehost后，readhost一直没有数据，以为是自己配置的问题，后面才发现Mycat就没有实现主从复制的功能，毕竟数据库本身自带的这个功能才是最高效稳定的。

至于其他的场景，如同时主从和分表分库也是支持的了，只要了解这个实现以后再去修改配置，都是可以实现的。而热备及故障专业官方推荐使用haproxy配合一起使用，大家可以试试。

使用

Mycat的启动也很简单，启动命令在Bin目录：

##启动
mycat start

##停止
mycat stop

##重启
mycat restart

如果在启动时发现异常，在logs目录中查看日志。

wrapper.log 为程序启动的日志，启动时的问题看这个
mycat.log 为脚本执行时的日志，SQL脚本执行报错后的具体错误内容,查看这个文件。mycat.log是最新的错误日志，历史日志会根据时间生成目录保存。

mycat启动后，执行命令不成功，可能实际上配置有错误，导致后面的命令没有很好的执行。

Mycat带来的最大好处就是使用是完全不用修改原有代码的，在mycat通过命令启动后，你只需要将数据库连接切换到Mycat的地址就可以了。如下面就可以进行连接了：

1	`mysql -h192.168.0.1 -P8806 -uroot -p123456`

连接成功后可以执行sql脚本了。
所以，可以直接通过sql管理工具（如：navicat、datagrip）连接，执行脚本。我一直用datagrip来进行日常简单的管理，这个很方便。

Mycat还有一个管理的连接，端口号是9906.

1	`mysql -h192.168.0.1 -P9906 -uroot -p123456`

连接后可以根据管理命令查看Mycat的运行情况，当然，喜欢UI管理方式的人，可以安装一个Mycat-Web来进行管理，有兴趣自行搜索。

简而言之，开发中使用Mycat和直接使用Mysql机会没有差别。

myCat查询原理

核心原理：
a、非分片关键字（取模的字段）查询会搜索所有的分库分表，结果归并后按照sql语句排序返回，如果未设置排序，则按分库随机返回结果
b、分片关键字查询会直接定位到对应的分库，执行相应的sql语句返回结果。

以求模分片方式为例验证：
修改/usr/local/mycat/conf/log4j2.xml日志级别为debug，即可查询查询语句。

1.非分片关键字查询，则会读取所有分片中的结果，然后按sql需求返回。

![image-20230603160350838](/Users/lindinghao/Library/Application Support/typora-user-images/image-20230603160350838.png)

2.分片关键字查询，则直接读取所在库。

![image-20230603160406237](/Users/lindinghao/Library/Application Support/typora-user-images/image-20230603160406237.png)

（如果是C端，一般不存在全量查询，所以比较适合mycat，但如果是后台数据报表的需求，很多全量查询的话，性能就不好了）

mycat缺点

1、Mycat单库分表需要准备空白表，提前制定好分表规则。

分表的时候使用到了该字段作为分表字段，数据会按照制定好的分表规则存入不同的数据库或表里面。Mycat中是不允许修改作为分表依据的列的，所以更新是需要去掉此列。

2、非关键字查询

3、由于读写分离后，从数据库的数据可能与主数据库的数据不一致，因此需要特别关注数据一致性问题。

![image-20230603163618803](/Users/lindinghao/Library/Application Support/typora-user-images/image-20230603163618803.png)（可以用注解的方式强制走主库）

与sharding jdbc的区别

相同点：

a、设计理念相同，主流程都是SQL解析–>SQL路由–>SQL改写–>SQL执行–>结果归并

b、查询的原理一致，非分片字段查询都会查询所有分片然后结果归并，分片字段查询则直接搜索分片数据库查询。

不同点：

他们类似于SpringCloud Ribbon与Nginx区别。

Mycat是基于Proxy，类似于nginx，它复写了MySQL协议，将MycatServer伪装成一个MySQL数据库。优点是保证数据库的安全性，归并数据结果完全解耦，缺点是效率偏低。

Sharding-JDBC是基于JDBC的扩展，是以jar包的形式提供轻量级服务的。优点是效率较高，缺点是归并数据结果没有实现解耦，有可能会影响到我们业务逻辑代码。还容易内存溢出，所以要做分页处理。

MyCat是一个第三方服务器端数据库中间件，客户端所有的jdbc请求都必须要先交给MyCat，再有MyCat转发到具体的真实服务器中。
Sharding-Jdbc是一个本地数据库中间件框架，采用Jar形式，在本地应用层重写Jdbc原生的方法，实现数据库分片形式。

sharding jdbc工作原理

以下是 Sharding JDBC 的一般工作原理：

数据源路由：Sharding JDBC 通过数据源路由功能，将 SQL 语句路由到相应的数据源（数据库实例）。路由的规则通常基于某种分片策略，比如按照分片键的哈希值或范围进行路由。这样可以将数据分散存储在不同的数据库实例中。
SQL 解析和执行：Sharding JDBC 在收到 SQL 语句后，会解析其中涉及的表名、字段等信息，然后根据配置的分片规则决定具体的路由目标。接着，它会将 SQL 语句发送到对应的数据源执行，并将结果合并返回给应用程序。

#mysql

使用mycat分库分表

http://example.com/2021/06/09/mycat分库分表/

Author

John Doe

Posted on

June 9, 2021

Licensed under

一条sql更新语句是如何执行的？ Previous

链表环的入口数学原理 Next