Mysql集群------（三）Mycat的垂直水平分库

最新推荐文章于 2023-06-16 15:56:07 发布

KKang@

最新推荐文章于 2023-06-16 15:56:07 发布

阅读量179

点赞数

分类专栏： mysql Mycat

本文链接：https://blog.csdn.net/qq_41582883/article/details/115676429

版权

mysql 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

Mycat

5 篇文章 0 订阅

订阅专栏

Mycat的垂直水平分库

1 垂直拆分（分库）
2 水平拆分（分表）

垂直分库：按照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直切分

水平分库：根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平切分

垂直分库的特点：规则简单，实施方便，适用于各业务之间的耦合度非常低，相互影响很小，业务逻辑非常清晰的系统。在这种系统中，通过将不同业务模块的表分拆到不同的数据库中。根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则简单。

水平切分比垂直切分复杂。原因是将同一个表中的不同数据拆分到不同的数据库中，对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂。

1 垂直拆分（分库）

实现垂直分库目的是为了，能够分摊主数据库的io压力，一般设置分出去的数据库为读，主服务器为写。主服务器和分服务器要实现主从复制的功能，这样就能实现读写分离，均衡数据库io，从而达到解决数据库瓶颈的目的

同一台主机上的两个库中的表可以关联查询，不同主机上的两个库中的表不能关联查询

拆分客户表和订单表放到不同主机的库中

创建多个空的数据库，在mycat中配置分库的原则，在mycat中创建表

垂直分库的原则：

客户表customer一个库；
订单表orders、订单详细表orders_detail、订单转态表dict_order_type3个表一个库

在这里插入图片描述

（1）编辑mycat（server7）的配置文件：vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	## customer表默认分布到dn2，其他表默认分布到dn1上
         <table name="customer" dataNode="dn2" ></table>
        </schema>
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.1:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.3:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
 
</mycat:schema>

（2）在dn1和dn2创建数据库orders

server1：mysql -p

mysql> CREATE DATABASE orders;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| orders             |
| performance_schema |
| sys                |
| testdb             |
+--------------------+

server3：mysql -p

mysql> CREATE DATABASE orders;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| orders             |
| performance_schema |
| sys                |
| testdb             |
+--------------------+

（3）启动mycat

[root@server7 conf]# mycat start
Starting Mycat-server...

（4）登陆mycat分库：

mysql -uroot -h172.25.12.7 -pwestos -P8066;

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
## 创建表
mysql> create table customer(
    -> id int auto_increment,
    -> name varchar(200),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table orders(
    -> id int auto_increment,
    -> order_type int,
    -> customer_id int,
    -> amount decimal(10,2),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table orders_detail(
    -> id int auto_increment,
    -> order_id int,
    -> detail varchar(200),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table dict_order_type(
    -> id int auto_increment,
    -> order_type varchar(200),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
| dict_order_type  |
| orders           |
| orders_detail    |
+------------------+
4 rows in set (0.00 sec)

（5）customer创建到dn1（server1）中，其他表创建到dn2（server3）
在这里插入图片描述

2 水平拆分（分表）

2.1 分表的实现

将同一用户的订单放到一个数据库

（1）编辑mycat（server7）的配置文件：vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2" ></table>
         ### 分片规则：schema.xml中的规则名称，到 rule.xml中找到mod_rule标签对应的名称，再找到Function标签
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
        </schema>
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.1:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.3:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
 
</mycat:schema>

（2）编辑mycat的规则文件：vim /usr/local/mycat/conf/rule.xml

<tableRule name="mod_rule"> ## 指定rule分片算法的名称
                <rule>  ## rule定义分片算法的具体内容
                        <columns>customer_id</columns> #columns指定对应的表中用于分片的列名
                        <algorithm>mod-long</algorithm> ## 对应function中指定的算法名称
                </rule>
        </tableRule>
.......
##  name指定算法名称 , 该文件中唯一
##  class指定算法的具体类
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property> ## 要分片的数据库节点数量,必须指定,否则没法分片 
        </function>

（2）dn2（server3）创建orders表

mysql> create table orders(id int auto_increment,order_type int,customer_id int,amount decimal(10,2),primary key(id);
);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
| orders           |
+------------------+
2 rows in set (0.01 sec)

（3）启动mycat

[root@server7 conf]# mycat start
Starting Mycat-server...

（4）测试：插入数据

登陆mycat：mysql -uroot -h172.25.12.7 -pwestos -P8066;

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> insert into orders(id,order_type,customer_id,amount) values(1,101,100,81901);
Query OK, 1 row affected (0.09 sec)

mysql> insert into orders(id,order_type,customer_id,amount) values(2,101,100,2348);
Query OK, 1 row affected (0.00 sec)

mysql> insert into orders(id,order_type,customer_id,amount) values(3,102,110,12540);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders(id,order_type,customer_id,amount) values(4,102,105,5240);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders(id,order_type,customer_id,amount) values(5,100,105,15240);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders(id,order_type,customer_id,amount) values(6,102,110,4562);
Query OK, 1 row affected (0.01 sec)

mysql> select * from orders;
+----+------------+-------------+----------+
| id | order_type | customer_id | amount   |
+----+------------+-------------+----------+
|  1 |        101 |         100 | 81901.00 |
|  2 |        101 |         100 |  2348.00 |
|  3 |        102 |         110 | 12540.00 |
|  6 |        102 |         110 |  4562.00 |
|  4 |        102 |         105 |  5240.00 |
|  5 |        100 |         105 | 15240.00 |
+----+------------+-------------+----------+
6 rows in set (0.06 sec)

查看数据的分片（依据customer_id），同一id的用户的订单信息分布到同一数据库
在这里插入图片描述

在这里插入图片描述

2.2 mycat分片的join

基于 E-R 关系的数据分片策略, 子表（orders_detail）的记录与所关联的主表（orders）记录存放在同一个数据分片上，这样关联查询就不需要跨库进行查询，从而提高数据检索的效率
在这里插入图片描述

（1）修改mycat的schema.xml文件：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2" ></table>
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
            <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />  
         </table>
        </schema>
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.1:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.3:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
 
</mycat:schema>

（2）orders_detail在dn1上，在dn2上创建数据库orders_detail

mysql> create table orders_detail(id int auto_increment,order_id int,detail varchar(200),primary key(id));
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
| orders           |
| orders_detail    |
+------------------+
3 rows in set (0.00 sec)

（3）启动mycat

[root@server7 conf]# mycat start
Starting Mycat-server...

（4）登陆mycat：mysql -uroot -h172.25.12.7 -pwestos -P8066;，插入数据

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
| orders           |
| orders_detail    |
| dict_order_type  |
+------------------+
4 rows in set (0.00 sec)

mysql> insert into orders_detail(detail, order_id) values('detail1',1);
Query OK, 1 row affected (0.12 sec)

mysql> insert into orders_detail(detail, order_id) values('detail2',2);
Query OK, 1 row affected (0.06 sec)

mysql> insert into orders_detail(detail, order_id) values('detail3',3);
Query OK, 1 row affected (0.06 sec)

mysql> insert into orders_detail(detail, order_id) values('detail4',4);
Query OK, 1 row affected (0.06 sec)

mysql> insert into orders_detail(detail, order_id) values('detail5',5);
Query OK, 1 row affected (0.06 sec)

mysql> insert into orders_detail(detail, order_id) values('detail6',6);
Query OK, 1 row affected (0.06 sec)

mysql> select a.*,b.detail from orders a join orders_detail b on a.id=b.order_id;
+----+------------+-------------+----------+---------+
| id | order_type | customer_id | amount   | detail  |
+----+------------+-------------+----------+---------+
|  1 |        101 |         100 | 81901.00 | detail1 |
|  2 |        101 |         100 |  2348.00 | detail2 |
|  3 |        102 |         110 | 12540.00 | detail3 |
|  6 |        102 |         110 |  4562.00 | detail6 |
|  4 |        102 |         105 |  5240.00 | detail4 |
|  5 |        100 |         105 | 15240.00 | detail5 |
+----+------------+-------

（5）测试
在这里插入图片描述

2.3 全局表

dn1和dn2都需要字典表，采用数据冗余解决

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2" ></table>
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
            <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />  
         </table>
         <table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
        </schema>
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.1:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.3:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
 
</mycat:schema>

（2）创建字典表

mysql> create table dict_order_type(
id int auto_increment,
order_type varchar(200),
primary key(id)
);
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
| dict_order_type  |
| orders           |
| orders_detail    |
+------------------+
4 rows in set (0.00 sec)

（3）登陆mycat：mysql -uroot -h172.25.12.7 -pwestos -P8066;，插入数据

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into dict_order_type(id,order_type)values(101,'type1');
Query OK, 1 row affected (0.07 sec)

mysql> insert into dict_order_type(id,order_type)values(102,'type2');
Query OK, 1 row affected (0.03 sec)

mysql> select * from dict_order_type;
+-----+------------+
| id  | order_type |
+-----+------------+
| 101 | type1      |
| 102 | type2      |
+-----+------------+
2 rows in set (0.04 sec)

在这里插入图片描述

2.4 常用的分片规则

2.4.1 枚举分片

根据区域分片

（1）编辑mycat的schema文件：vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2" ></table>
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
            <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />  
         </table>
         <table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
         <table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>  ## orders_ware_info数据库及规则
        </schema>
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.1:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.3:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
 
</mycat:schema>

编辑规则文件：vim /usr/local/mycat/conf/rule.xml

<tableRule  name="sharding_by_intfile">
             <rule>
               <columns>areacode</columns> ## 分片字段
               <algorithm>hash-int</algorithm> ## 分片函数
             </rule>
        </tableRule>

	<function name="hash-int"
		class="io.mycat.route.function.PartitionByFileMap">
		<property name="mapFile">partition-hash-int.txt</property> ## 标识配置文件名称
                <property name="type">1</property> ## type 0 int型 1 非 int 型
                <property name="defaultNode">0</property> ## 默认节点,小于 0 表示不设置默认节点, 大于 0 表示设置默认节点
	</function>

编辑标识配置文件：vim /usr/local/mycat/conf/partition-hash-int.txt

110=0
120=1

（2）启动mycat

[root@server7 conf]# mycat start
Starting Mycat-server...

（3）登陆mycat：mysql -uroot -h172.25.12.7 -pwestos -P8066;

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
## 创建库
mysql> create table orders_ware_info(id int auto_increment comment '编号',order_id int comment '订单编号',address varchar(200) comment '地址',areacode varchar(20) comment '区域编号',primary key(id));
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
| dict_order_type  |
| orders           |
| orders_detail    |
| orders_ware_info |
+------------------+
5 rows in set (0.00 sec)
## 插入数据
mysql> insert into orders_ware_info(id,order_id,address,areacode) values(1,1,'北京',110);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders_ware_info(id,order_id,address,areacode) values(2,2,'西安',120);
Query OK, 1 row affected (0.01 sec)

mysql> select * from orders_ware_info;
+----+----------+---------+----------+
| id | order_id | address | areacode |
+----+----------+---------+----------+
|  1 |        1 | 北京    | 110      |
|  2 |        2 | 西安    | 120      |
+----+----------+---------+----------+
2 rows in set (0.06 sec)

（4）测试：数据按照枚举规则areacode分片
在这里插入图片描述

2.4.2 范围约定

（1）编辑mycat的schema文件：vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2" ></table>
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
            <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />  
         </table>
         <table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
         <table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>
         <table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long"></table> ## 根据auto_sharding_long规则分片
        </schema>
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.1:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.3:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
 
</mycat:schema>

编辑mycat的规则文件：vim /usr/local/mycat/conf/rule.xml

<tableRule name="auto_sharding_long">
            <rule>
              <columns>order_id</columns>
              <algorithm>rang-long</algorithm>
      
            </rule>

	<function name="rang-long"
		class="io.mycat.route.function.AutoPartitionByLong">
		<property name="mapFile">autopartition-long.txt</property>
                <property name="defaultNode">0</property>
	</function>

编辑范围autopartition-long.txt 文件：vim /usr/local/mycat/conf/autopartition-long.txt

0-99=0
10-199=1

（2）登陆mycat：mysql -uroot -h172.25.12.7 -pwestos -P8066;，创建数据库并插入数据

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table payment_info(id int auto_increment,order_id int,payment_status int,primary key(id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into payment_info(id,order_id,payment_status) values(1,101,0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into payment_info(id,order_id,payment_status) values(2,120,0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into payment_info(id,order_id,payment_status) values(3,50,0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into payment_info(id,order_id,payment_status) values(4,33,0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into payment_info(id,order_id,payment_status) values(5,166,0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from payment_info;
+----+----------+----------------+
| id | order_id | payment_status |
+----+----------+----------------+
|  3 |       50 |              0 |
|  4 |       33 |              0 |
|  1 |      101 |              0 |
|  2 |      120 |              0 |
|  5 |      166 |              0 |
+----+----------+----------------+
5 rows in set (0.05 sec)

（3）在dn1和dn2上测试，（0～99）分配到dn1；（100～199）分配到dn2
在这里插入图片描述

2.4.3 按照时间分片

（1）编辑mycat的schema文件：vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2" ></table>
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
            <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />  
         </table>
         <table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
         <table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>
         <table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long"></table>
         <table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date"></table>
        </schema>
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.1:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="172.25.12.3:3306" user="root" password="westos">
		</writeHost>
     <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
 
</mycat:schema>

编辑mycat的规则文件：vim /usr/local/mycat/conf/rule.xml


 <tableRule name="sharding_by_date">
            <rule>
              <columns>login_date</columns>
              <algorithm>shardingByDate</algorithm>
            </rule>
        </tableRule>

 <function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
                <property name="dateFormat">yyyy-MM-dd</property>
                <property name="sBeginDate">2021-04-10</property>
                <property name="sEndDate">2021-04-20</property>
                <property name="sPartionDay">10</property>
</function>

（2）登陆mycat：mysql -uroot -h172.25.12.7 -pwestos -P8066;，创建数据库并插入数据

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table login_info(id int auto_increment comment '编号',user_id int comment '用户编号', login_date date comment '登录时间', primary key(id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into login_info(id,user_id,login_date) values(1,101,'2021-04-10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into login_info(id,user_id,login_date) values(2,102,'2021-04-13');
Query OK, 1 row affected (0.00 sec)

mysql> insert into login_info(id,user_id,login_date) values(3,103,'2021-04-17');
Query OK, 1 row affected (0.01 sec)

mysql> insert into login_info(id,user_id,login_date) values(4,104,'2021-04-24');
Query OK, 1 row affected (0.01 sec)

mysql> insert into login_info(id,user_id,login_date) values(5,105,'2021-04-28');
Query OK, 1 row affected (0.01 sec)

mysql> insert into login_info(id,user_id,login_date) values(6,106,'2021-04-12');
Query OK, 1 row affected (0.01 sec)

mysql> select * from login_info;
+----+---------+------------+
| id | user_id | login_date |
+----+---------+------------+
|  1 |     101 | 2021-04-10 |
|  2 |     102 | 2021-04-13 |
|  3 |     103 | 2021-04-17 |
|  6 |     106 | 2021-04-12 |
|  4 |     104 | 2021-04-24 |
|  5 |     105 | 2021-04-28 |
+----+---------+------------+
6 rows in set (0.05 sec)

（3）测试
在这里插入图片描述

在这里插入图片描述

KKang@

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql集群------（三）Mycat的垂直水平分库

垂直分库：按照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直切分水平分库：根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平切分垂直分库的特点：规则简单，实施方便，适用于各业务之间的耦合度非常低，相互影响很小，业务逻辑非常清晰的系统。在这种系统中，通过将不同业务模块的表分拆到不同的数据库中。根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则简单。水平切分比垂直切分复杂。原因是将同一个
复制链接

扫一扫