MySQL运维之分库分表（二）

MyCat配置

schema.xml

schema.xml 作为 MyCat 中最重要的配置文件之一，涵盖了 MyCat 的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。

主要包含以下三组标签：

• schema 标签

• datanode 标签

• datahost 标签

schema标签

1、schema 定义逻辑库

schema 标签用于定义 MyCat 实例中的逻辑库，一个 MyCat 实例中, 可以有多个逻辑库，可以通过 schema 标签来划分不同的逻辑库。MyCat 中的逻辑库的概念，等同于 MySQL 中的 database 概念，需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。

核心属性：

• name:指定自定义的逻辑库库名

• checkSQLschema:在 SQL 语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除

• sqlMaxLimit:如果未指定 limit 进行查询，列表查询模式查询多少条记录

2、schema 中的table定义逻辑表

table 标签定义了 MyCat 中逻辑库 schema 下的逻辑表，所有需要拆分的表都需要在 table 标签中定义。

核心属性：

• name:定义逻辑表表名，在该逻辑库下唯一

• dataNode:定义逻辑表所属的 dataNode ，该属性需要与 dataNode 标签中 name 对应；多个 dataNode 逗号分隔

• rule:分片规则的名字，分片规则名字是在 rule.xml 中定义的

• primaryKey:逻辑表对应真实表的主键

• type:逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为 global

 datanode标签

核心属性：

• name:定义数据节点名称

• dataHost:数据库实例主机名称，引用自 dataHost 标签中 name 属性

• database:定义分片所属数据库

 datahost标签

该标签在 MyCat 逻辑库中作为底层标签存在，直接定义了具体的数据库实例、读写分离、心跳语句。

核心属性：

• name:唯一标识，供上层标签使用

• maxCon/minCon:最大连接数/最小连接数

• balance:负载均衡策略，取值 0,1,2,3

• writeType:写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）

• dbDriver:数据库驱动，支持 native、jdbc

rule.xml

rule.xml 中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签：tableRule、Function。

server.xml

server.xml 配置文件包含了 MyCat 的系统配置信息，主要有两个重要的标签：system、user.

1、system 标签

主要配置 MyCat 中的系统配置信息，对应的系统配置项及其含义，如下：

2、user标签

配置 MyCat 中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限描述方式及配置说明如下：

在测试权限操作时，我们只需要将 privileges 标签的注释放开。在 privileges 下的 schema 标签中配置的 dml 属性配置的是逻辑库的权限。 在privileges 的 schema 下的 table标签的 dml 属性中配置逻辑表的权限。

MyCat 分片

垂直拆分

 场景

在业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。

现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库服务器，用户及省市区表拆分到一个服务器。最终结构如下：

准备

准备三台服务器，IP 地址如图所示：

并且在 192.168.200.210、192.168.200.213,、192.168.200.214 上面创建数据库 shopping.

#3.5.1.3 配置

1、schema.xml

<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
  <table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
  <table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
  <table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
  <table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
  <table name="tb_goods_item" dataNode="dn1" primaryKey="id" />
  <table name="tb_order_item" dataNode="dn2" primaryKey="id" />
  <table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
  <table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />
  <table name="tb_user" dataNode="dn3" primaryKey="id" />
  <table name="tb_user_address" dataNode="dn3" primaryKey="id" />
  <table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
  <table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
  <table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
  </schema>
  <dataNode name="dn1" dataHost="dhost1" database="shopping" />
  <dataNode name="dn2" dataHost="dhost2" database="shopping" />
  <dataNode name="dn3" dataHost="dhost3" database="shopping" />
  <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"slaveThreshold="100">
  <heartbeat>select user()</heartbeat>
  <writeHost host="master" url="jdbc:mysql://192.168.200.210:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
  </dataHost>
  <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"slaveThreshold="100">
  <heartbeat>select user()</heartbeat>
  <writeHost host="master" url="jdbc:mysql://192.168.200.213:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
  </dataHost>
  <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"slaveThreshold="100">
  <heartbeat>select user()</heartbeat>
  <writeHost host="master" url="jdbc:mysql://192.168.200.214:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
  </dataHost>

2、server.xml

<user name="root" defaultAccount="true">
  <property name="password"> 123456 </property>
  <property name="schemas">SHOPPING</property>
  <!-- 表级 DML 权限设置 --><!--
<privileges check="true">
<schema name="DB01" dml="0110" >
<table name="TB_ORDER" dml="1110"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
  <property name="password"> 123456 </property>
  <property name="schemas">SHOPPING</property>
  <property name="readOnly">true</property>
  </user>

 测试

1、上传测试 SQL 脚本到服务器的 /root 目录

2、执行指令导入测试数据

重新启动 MyCat 后，在 MyCat 的命令行中，通过source指令导入表结构，以及对应的数据，查看数据分布情况。

将表结构及对应的测试数据导入之后，可以检查一下各个数据库服务器中的表结构分布情况。检查是否和我们准备工作中规划的服务器一致。

source /root/shopping-table.sql
source /root/shopping-insert.sql

3、查询用户的收件人及收件人地址信息(包含省、市、区)。

在 MyCat 的命令行中，当我们执行以下多表联查的SQL语句时，可以正常查询出数据。

select ua.user_id, ua.contact, p.province, c.city, r.area, ua.address from tb_user_address ua, tb_areas_city c, tb_areas_provinces p, tb_areas_region r where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id = r.areaid;

4、查询每一笔订单及订单的收件地址信息(包含省、市、区)。

实现该需求对应的SQL语句如下：

SELECT order_id, payment, receiver, province, city, area FROM tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r WHERE o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid;

但是现在存在一个问题，订单相关的表结构是在 192.168.200.213 数据库服务器中，而省市区的数据库表是在 192.168.200.214 数据库服务器中。那么在MyCat中执行是否可以成功呢？经过测试，我们看到，SQL语句执行报错。原因就是因为MyCat在执行该SQL语句时，需要往具体的数据库服务器中路由，而当前没有一个数据库服务器完全包含了订单以及省市区的表结构，造成SQL语句失败，报错。

对于上述的这种现象，我们如何来解决呢？ 下面我们介绍的全局表，就可以轻松解决这个问题。

全局表

对于省、市、区/县表 tb_areas_provinces、tb_areas_city、tb_areas_region 是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。

修改 schema.xml 中的逻辑表的配置，修改 tb_areas_provinces、tb_areas_city、tb_areas_region 三个逻辑表，增加 type 属性，配置为global，就代表该表是全局表，就会在所涉及到的dataNode中创建给表。对于当前配置来说，也就意味着所有的节点中都有该表了。

<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id"type="global"/>
<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id"type="global"/>
<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id"type="global"/>

配置完毕后，重新启动 MyCat.

1、删除原来每一个数据库服务器中的所有表结构

2、通过 source 指令，导入表及数据

source /root/shopping-table.sqlsource /root/shopping-insert.sql

3、检查每一个数据库服务器中的表及数据分布，看到三个节点中都有这三张全局表

4、然后再次执行上面的多表联查的 SQL 语句是可以正常执行成功的。

SELECT order_id, payment, receiver, province, city, area FROM tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r WHERE o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid;

5、当在MyCat中更新全局表的时候，我们可以看到，所有分片节点中的数据都发生了变化，每个节点的全局表数据时刻保持一致。

水平拆分

场景

在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分。

准备

准备三台服务器，具体的结构如下：

并且，在三台数据库服务器中分表创建一个数据库 itcast.

配置

1、schema.xml

<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
</schema>

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

tb_log表最终落在 3 个节点中，分别是 dn4、dn5、dn6,而具体的数据分别存储在 dhost1、dhost2、dhost3 的 itcast 数据库中。

2、server.xml

<user name="root" defaultAccount="true">
  <property name="password"> 123456 </property>
  <property name="schemas">SHOPPING,ITCAST</property>
  <!-- 表级 DML 权限设置 --><!--
<privileges check="true">
<schema name="DB01" dml="0110" >
<table name="TB_ORDER" dml="1110">
</table>
</schema>
</privileges>
-->
</user>

配置 root 用户既可以访问 SHOPPING 逻辑库，又可以访问 ITCAST 逻辑库。

 测试

配置完毕后，重新启动 MyCat，然后在 MyCat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数据分布情况。

CREATE TABLE tb_log (
  id bigint( 20 ) NOT NULL COMMENT 'ID',
  model_name varchar( 200 ) DEFAULT NULL COMMENT '模块名',
  model_value varchar( 200 ) DEFAULT NULL COMMENT '模块值',
  return_value varchar( 200 ) DEFAULT NULL COMMENT '返回值',
  return_class varchar( 200 ) DEFAULT NULL COMMENT '返回值类型',
  operate_user varchar( 20 ) DEFAULT NULL COMMENT '操作用户',
  operate_time varchar( 20 ) DEFAULT NULL COMMENT '操作时间',
  param_and_value varchar( 500 ) DEFAULT NULL COMMENT '请求参数名及参数值',
  operate_class varchar( 200 ) DEFAULT NULL COMMENT '操作类',
  operate_method varchar( 200 ) DEFAULT NULL COMMENT '操作方法',
  cost_time bigint( 20 ) DEFAULT NULL COMMENT '执行方法耗时, 单位 ms',
  source int( 1 ) DEFAULT NULL COMMENT '来源 : 1 PC , 2 Android , 3 IOS',
  PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source)
  VALUES('1','user','insert','success','java.lang.String','10001','2022-01-06
18:12:28','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','10', 1 );
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)
VALUES('2','user','insert','success','java.lang.String','10001','2022-01-06
18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.contro
ller.UserController','insert','23', 1 );
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)
VALUES('3','user','update','success','java.lang.String','10001','2022-01-06
18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.contro
ller.UserController','update','34', 1 );
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)
VALUES('4','user','update','success','java.lang.String','10001','2022-01-06
18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.contro
ller.UserController','update','13', 2 );
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)
VALUES('5','user','insert','success','java.lang.String','10001','2022-01-06
18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.co
ntroller.UserController','insert','29', 3 );
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)
VALUES('6','user','find','success','java.lang.String','10001','2022-01-06
18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.co
ntroller.UserController','find','29', 2 );

分片规则

 范围分片

1、介绍

根据指定的字段及其配置的范围与数据节点的对应情况， 来决定该数据属于哪一个分片。

2、配置 schema.xml 逻辑表配置：

<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

schema.xml 数据节点配置：

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />

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="defaultNode"> 0 </property>
  </function>

分片规则配置属性含义：

在 rule.xml 中配置分片规则时，关联了一个映射配置文件 autopartition-long.txt，该配置文件的配置如下：

# range start-end ,data node index
# K=1000,M=10000.
0-500M= 0
500M-1000M= 1
1000M-1500M= 2

含义：0-500万之间的值，存储在 0 号数据节点(数据节点的索引从 0 开始) ；500万-1000万 间的数据存储在 1 号数据节点；1000万-1500万的数据节点存储在 2 号节点；

该分片规则，主要是针对于数字类型的字段适用。 在MyCat的入门程序中，我们使用的就是该分片规则。

因为文章篇幅较长，后续文章请查看后续文章

#推荐阅读

MySQL运维之分库分表（一）

介绍 问题分析随着互联网及移动互联网的发展，应用系...

MySQL运维之分库分表（三）

取模分片1、介绍根据指定的字段值与节点数量进行求...

MySQL运维之分库分表（四）

自然月分片1、介绍使用场景为按照月份来分片, 每个自然月为一...

标签： MySQL维护 MySQL教程 MySQL日志 MySQL

选择打赏方式

微信 支付宝

打赏

打赏