Mycat单库分表

目录

一、前期准备

二、使用mycat开始进行单库分表

A、按区分片

1、修改server.xml，设置连接 Mycat 账户名和密码

2、修改 schema.xml ,配置需要拆分的表

3、修改 rule.xml 配置分表规则

4、修改springboot项目配置

5、启动项目执行插入方法，验证数据表

B、日期（天）分片

1、修改 schema.xml ,配置需要拆分的表

2、修改 rule.xml 配置分表规则

---

一、前期准备

    1、构建一个可连接数据库的springboot项目
    2、下载安装本地mycat组件（本文使用1.6.7.6，window本地服务）
        地址：http://dl.mycat.org.cn/

PS：mycat 就是个单独组件，后面修改项目数据库连接地址即可，不需要任何编码。

二、使用mycat开始进行单库分表

A、按区分片

解压后的mycat目录结构

进入conf目录

1、修改server.xml，设置连接 Mycat 账户名和密码

	<user name="root" defaultAccount="true">
		<property name="password">admin123</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、password、schemas，其中 name 和 password 要在Mycat连接MySQL数据库时使用，schemas是逻辑库，要和 schema.xml 里的 schema name 对应。

2、修改 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"> -->
	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
		<!-- auto sharding by id (long) -->
		<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
        <!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
		<table name="interface_log_message" subTables="interface_log_message2021$1-5"  primaryKey="id" dataNode="dn1" rule="auto-sharding-long" autoIncrement="true" fetchStoreNodeByJdbc="true">
			<!-- <childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable> -->
		</table>
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="platform" />

	<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="123456">
        <!-- 读实例 -->
        <!-- <readHost host="hostS1" url="localhost:4406" user="root" password="123456" /> -->
		</writeHost>
	</dataHost>
	
</mycat:schema>

 schema name 与 server.xml 中user 的 schemas 名一致 
    定义一个MyCat的模式，此处定义了一个逻辑数据库名称TESTDB
     “checkSQLschema”：描述的是当前的连接是否需要检测数据库的模式
     “sqlMaxLimit”：表示返回的最大的数据量的行数
     “dataNode="dn1"”：该操作使用的数据节点是dn1的逻辑名称

 table  name：逻辑表名（所有分表的总表）
           primaryKey：真实主键
           autoIncrement：是否开启主键自增
           dataNode：指定数据节点（绑定真实数据库）
           subTables：指定所有真实分表，interface_log_message2021$1-5为缩写
           fetchStoreNodeByJdbc： 启用ER表使用JDBC方式获取DataNode
           rule:指定路由策略,与 rule.xml 中 tableRule 的 name 属性值保持一致（下面介绍）
           childTable： 用于定义 E-R 分片的子表。通过标签上的属性与父表进行关联。具体可参考Mycat系列教程-配置文件(一)、schema.xml之childTable 标签(03)

dataNode 定义个数据的操作节点，以后这个节点会进行一些库表分离使用 
    “dataHost="localhost1"”：定义数据节点的逻辑名称
    “database="platform"”：定义数据节点要使用的真实数据库名称

 dataHost 定义数据节点，包括各种逻辑项的配置
    balance 属性：负载均衡类型，目前的取值有 3 种：
    1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
    2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
    3. balance="2"，所有读操作都随机的在 writeHost、 readhost 上分发。
    4. balance="3"，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有
    writeType 属性：负载均衡类型，目前的取值有 3 种
    1. writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
    2. writeType="1"，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。

使用 navicat 在原来的 mysql 数据库快速创建5张分表

3、修改 rule.xml 配置分表规则

    <tableRule name="auto-sharding-long">
		<rule>
			<columns>id</columns>
			<algorithm>rang-long</algorithm>
		</rule>
	</tableRule>
	<!-- 范围约定 -->
	<function name="rang-long"
			  class="io.mycat.route.function.AutoPartitionByLong">
		<property name="mapFile">autopartition-long.txt</property> <!-- 规则定义 -->
		<property name="count">5</property> <!-- 一共分几个表，不过测试了下，不设置该项也可以正常落到分表中 -->
	</function>

autopartition-long.txt 在conf目录里面，内容如下

# range start-end ,data node index
# K=1000,M=10000.
# 0-500M=0
# 500M-1000M=1
# 1000M-1500M=2
0-5=0
5-10=1
10-15=2
16-20=3
21-25=4

这里的范围差值设置小一些，已达到演示效果，0-5=0表示通过mycat插入的数据，id 从1-5到会落到分表上（ 0 分表即：interface_log_message20211 表上，以此类推5张）

以上配置完成后，可进入mycat安装目录下的bin启动 startup_nowrap.bat

双击启动，如果本地 window 启动 闪退，大部分是某个配置信息出错了，具体可使用cmd命令进入目录启动或是查看logs目录下的日志，会打印出对应的错误信息。

4、修改springboot项目配置

进入springboot项目，把链接 mysql 的数据库链接信息改为 mycat（包括账号名、和密码）,mycat的数据库端口默认是 8066，控制台端口是9066

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: 123456
    url: jdbc:mysql://localhost:8066/TESTDB?connectTimeout=1000&socketTimeout=3000&autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8

5、启动项目执行插入方法，验证数据表

执行的时候，报错如下

### Cause: java.sql.SQLException: mycat sequnce err.io.mycat.config.util.ConfigException: can't find definition for sequence :INTERFACE_LOG_MESSAGE
; uncategorized SQLException; SQL state [HY000]; error code [1003]; mycat sequnce err.io.mycat.config.util.ConfigException: can't find definition for sequence :INTERFACE_LOG_MESSAGE; nested exception is java.sql.SQLException: mycat sequnce err.io.mycat.config.util.ConfigException: can't find definition for sequence :INTERFACE_LOG_MESSAGE
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

虽然我们的表的主键是自增长，但在使用 mycat 的时候，插入数据是需要我们 自行生成 id 来插入。

在原有代码里面，我们在插入对象时，设置 object.setId(id) 主键属性值，重新启动。

执行插入方法后，在项目代码显示插入成功，那我们可通过mysql终端命令来进入mycat，查看结果数据。

在cmd终端进入mysql安装的bin目录执行： 

mysql -hlocalhost -uroot -p123456 -P8066

进入之后输入查询语句

如果不喜欢使用mysql终端命令来查看mysql逻辑库TESTDB的表情况，可在物理表同一个库中，把逻辑表

回到本地 navicat 查看物理库查询分表：

插入成功，继续插入5条数据。根据我们一开始的规则分表。

前5条在 1号表，第6条落到 2号表

如果插入过多，超过我们预设的25条（5张表，每表5条），会直接报错，

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: can't find any valid datanode :INTERFACE_LOG_MESSAGE -> ID -> 26
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)

 所以需要定期扩容数据，新增物理表，并且修改 schema.xml 中的分表范围 $1-?，进入mycat控制台，重新运行mycat配置命令，无需重启

1、进入mysql安装bin目录执行命令，配置的是mycat的账号密码端口

mysql -hlocalhost -uroot -p123456 -P9066

2、重启命令

reload @@config;

 

流程走下来，有一个最大问题点，那就是全局ID的生成问题，网上有推荐设置全局ID的三种方式常见方式
a：文件定义
b：数据库函数定义
c：时间戳
3种看下来，都不太符合大数据量情况下的需求，所以又重新记录了一种，分片方式，按天记录，id采用UUID随机获取。

B、日期（天）分片

1、修改 schema.xml ,配置需要拆分的表

前面的准备和大体步骤一样，在原来的基础上，修改配置规则 rule="sharding-by-date"

		<table name="interface_log_message" subTables="interface_log_message2021_03_$12-21"  dataNode="dn1" rule="sharding-by-date" fetchStoreNodeByJdbc="true">
		</table>

2、修改 rule.xml 配置分表规则

    <tableRule name="sharding-by-date">
		<rule>
			<columns>create_date</columns>
			<algorithm>partbyday</algorithm>
		</rule>
	</tableRule>

	<function name="partbyday"
			  class="io.mycat.route.function.PartitionByDate">
		<property name="dateFormat">yyyy-MM-dd</property>
		<property name="sBeginDate">2021-03-12</property>
		<!-- <property name="sEndDate">2021-03-21</property> -->
		<property name="sPartionDay">1</property>
	</function>

在原有表添加字段

ALTER TABLE interface_log_message2021_03_12 ADD create_date date DEFAULT NULL COMMENT '创建日期';

columns : 需要分表的依据字段，create_date，其表里的类型必须定义为Date，否则无法正确分表，定义为varchar2，DateTime都不可以
dateFormat:日期分片格式
sBeginDate:开始日期
sEndDate:结束日期
  有配置：如果超过结束日期，会重新按起始日期表排序再加载存在 ，如：2021-03-22日期的数据，超过时间，会存入到xxxx2021-03-12表内）
  无配置：超过schema.xml 定义的日期范围，直接报错：nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Index: 13, Size: 11
sPartionDay:分表时间间隔，1:一天间隔存一张表

配置完成后，重启命令

reload @@config;

结果可用，效果这里就不截图了。

其他规则可参考： Mycat水平拆分之十种分片规则

查询转载相关文章：

Mycat使用之MySQL单库分表及均分数据

mycat单数据库按日分表,按月分表

Mycat之——按日期(天)分片