MyCAT水平拆分之最佳实践-亲试ok
功能：
配置水平分片的分片规则
配置分片规则对应的分片函数
|
示例如下
<tableRule name="hash-mod-4_id">
<rule>
<columns>id</columns>
<algorithm>hash-mod-4</algorithm>
</rule>
</tableRule>
指定分片表规则的名字，避免重复
<columns> 指定表的分片列
<algorithm> 指定表的分片算法，即<function> 的name属性
|
schema.xml文件中新增四个dataNode，每个dataNode指向不同dataHost的database。如下
<dataNode name="orderdb01" dataHost="192.168.124.112" database="orderdb01"/>
<dataNode name="orderdb02" dataHost="192.168.124.112" database="orderdb02"/>
<dataNode name="orderdb03" dataHost="192.168.124.113" database="orderdb03"/>
<dataNode name="orderdb04" dataHost="192.168.124.113" database="orderdb04"/>
<table>标签配置如下
<table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master"/>
此处的rule 必须对应 rule.xml中的 tableRule哦
rule.xml配置文件如下
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="order_master">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">4</property>
</function>
</mycat:rule>
操作步骤
主机Slave-112新建两个数据库 orderdb01 orderdb02
mysql> create database orderdb01;
Query OK, 1 row affected (0.05 sec)
mysql> create database orderdb02;
Query OK, 1 row affected (0.00 sec)
mysql> use orderdb01;
Database changed
mysql> show tables;
Empty set (0.00 sec)
创建示例表order_master
mysql> CREATE TABLE `order_master` (
-> `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
-> `order_sn` bigint(20) unsigned NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
-> `customer_id` int(10) unsigned NOT NULL COMMENT '下单人ID',
-> `shipping_user` varchar(10) NOT NULL COMMENT '收货人姓名',
-> `province` smallint(6) NOT NULL COMMENT '收货人所在省',
-> `city` smallint(6) NOT NULL COMMENT '收货人所在市',
-> `district` smallint(6) NOT NULL COMMENT '收货人所在区',
-> `address` varchar(100) NOT NULL COMMENT '收货人详细地址',
-> `payment_method` tinyint(4) NOT NULL COMMENT '支付方式:1现金,2余额,3网银,4支付宝,5微信',
-> `order_money` decimal(8,2) NOT NULL COMMENT '订单金额',
-> `district_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
-> `shipping_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费金额',
-> `payment_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
-> `shipping_comp_name` varchar(10) DEFAULT NULL COMMENT '快递公司名称',
-> `shipping_sn` varchar(50) DEFAULT NULL COMMENT '快递单号',
-> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
-> `shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
-> `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
-> `receive_time` datetime DEFAULT NULL COMMENT '收货时间',
-> `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
-> `order_point` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单积分',
-> `invoice_title` varchar(100) DEFAULT NULL COMMENT '发票抬头',
-> `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
-> PRIMARY KEY (`order_id`),
-> UNIQUE KEY `ux_ordersn` (`order_sn`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='订单主表' ;
Query OK, 0 rows affected (0.21 sec)
mysql> show tables;
+---------------------+
| Tables_in_orderdb01 |
+---------------------+
| order_master |
+---------------------+
1 row in set (0.05 sec)
mysql> use orderdb02;
Database changed
mysql> show tables;
Empty set (0.00 sec)
创建示例表order_master
mysql> CREATE TABLE `order_master` (
-> `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
-> `order_sn` bigint(20) unsigned NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
-> `customer_id` int(10) unsigned NOT NULL COMMENT '下单人ID',
-> `shipping_user` varchar(10) NOT NULL COMMENT '收货人姓名',
-> `province` smallint(6) NOT NULL COMMENT '收货人所在省',
-> `city` smallint(6) NOT NULL COMMENT '收货人所在市',
-> `district` smallint(6) NOT NULL COMMENT '收货人所在区',
-> `address` varchar(100) NOT NULL COMMENT '收货人详细地址',
-> `payment_method` tinyint(4) NOT NULL COMMENT '支付方式:1现金,2余额,3网银,4支付宝,5微信',
-> `order_money` decimal(8,2) NOT NULL COMMENT '订单金额',
-> `district_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
-> `shipping_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费金额',
-> `payment_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
-> `shipping_comp_name` varchar(10) DEFAULT NULL COMMENT '快递公司名称',
-> `shipping_sn` varchar(50) DEFAULT NULL COMMENT '快递单号',
-> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
-> `shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
-> `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
-> `receive_time` datetime DEFAULT NULL COMMENT '收货时间',
-> `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
-> `order_point` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单积分',
-> `invoice_title` varchar(100) DEFAULT NULL COMMENT '发票抬头',
-> `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
-> PRIMARY KEY (`order_id`),
-> UNIQUE KEY `ux_ordersn` (`order_sn`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='订单主表' ;
Query OK, 0 rows affected (0.21 sec)
mysql> show tables;
+---------------------+
| Tables_in_orderdb02 |
+---------------------+
| order_master |
+---------------------+
1 row in set (0.00 sec)
主机Slave-113新建两个数据库 orderdb03 orderdb04
mysql> create database orderdb03;
Query OK, 1 row affected (0.05 sec)
mysql> create database orderdb04;
Query OK, 1 row affected (0.00 sec)
mysql> use orderdb03;
Database changed
mysql> show tables;
Empty set (0.00 sec)
创建示例表order_master
mysql> CREATE TABLE `order_master` (
-> `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
-> `order_sn` bigint(20) unsigned NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
-> `customer_id` int(10) unsigned NOT NULL COMMENT '下单人ID',
-> `shipping_user` varchar(10) NOT NULL COMMENT '收货人姓名',
-> `province` smallint(6) NOT NULL COMMENT '收货人所在省',
-> `city` smallint(6) NOT NULL COMMENT '收货人所在市',
-> `district` smallint(6) NOT NULL COMMENT '收货人所在区',
-> `address` varchar(100) NOT NULL COMMENT '收货人详细地址',
-> `payment_method` tinyint(4) NOT NULL COMMENT '支付方式:1现金,2余额,3网银,4支付宝,5微信',
-> `order_money` decimal(8,2) NOT NULL COMMENT '订单金额',
-> `district_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
-> `shipping_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费金额',
-> `payment_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
-> `shipping_comp_name` varchar(10) DEFAULT NULL COMMENT '快递公司名称',
-> `shipping_sn` varchar(50) DEFAULT NULL COMMENT '快递单号',
-> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
-> `shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
-> `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
-> `receive_time` datetime DEFAULT NULL COMMENT '收货时间',
-> `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
-> `order_point` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单积分',
-> `invoice_title` varchar(100) DEFAULT NULL COMMENT '发票抬头',
-> `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
-> PRIMARY KEY (`order_id`),
-> UNIQUE KEY `ux_ordersn` (`order_sn`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='订单主表' ;
Query OK, 0 rows affected (0.21 sec)
mysql> show tables;
+---------------------+
| Tables_in_orderdb03 |
+---------------------+
| order_master |
+---------------------+
1 row in set (0.07 sec)
mysql> use orderdb04;
Database changed
mysql> show tables;
Empty set (0.00 sec)
创建示例表order_master
mysql> CREATE TABLE `order_master` (
-> `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
-> `order_sn` bigint(20) unsigned NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
-> `customer_id` int(10) unsigned NOT NULL COMMENT '下单人ID',
-> `shipping_user` varchar(10) NOT NULL COMMENT '收货人姓名',
-> `province` smallint(6) NOT NULL COMMENT '收货人所在省',
-> `city` smallint(6) NOT NULL COMMENT '收货人所在市',
-> `district` smallint(6) NOT NULL COMMENT '收货人所在区',
-> `address` varchar(100) NOT NULL COMMENT '收货人详细地址',
-> `payment_method` tinyint(4) NOT NULL COMMENT '支付方式:1现金,2余额,3网银,4支付宝,5微信',
-> `order_money` decimal(8,2) NOT NULL COMMENT '订单金额',
-> `district_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
-> `shipping_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费金额',
-> `payment_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
-> `shipping_comp_name` varchar(10) DEFAULT NULL COMMENT '快递公司名称',
-> `shipping_sn` varchar(50) DEFAULT NULL COMMENT '快递单号',
-> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
-> `shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
-> `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
-> `receive_time` datetime DEFAULT NULL COMMENT '收货时间',
-> `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
-> `order_point` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单积分',
-> `invoice_title` varchar(100) DEFAULT NULL COMMENT '发票抬头',
-> `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
-> PRIMARY KEY (`order_id`),
-> UNIQUE KEY `ux_ordersn` (`order_sn`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='订单主表' ;
Query OK, 0 rows affected (0.21 sec)
mysql> show tables;
+---------------------+
| Tables_in_orderdb04 |
+---------------------+
| order_master |
+---------------------+
1 row in set (0.00 sec)
确认数据
Mycat 端确认数据总数
[root@Master-111 conf]# mysql -uapp_imooc -p -h127.0.0.1 -P8066
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use imooc_db;
Database changed
mysql> select count(*) from order_master;
+--------+
| COUNT0 |
+--------+
| 5095 |
+--------+
1 row in set (1.14 sec)
Master-112 确认数据
|
orderdb01数据如下：
mysql> select count(1) from orderdb01.order_master;
+----------+
| count(1) |
+----------+
| 1291 |
+----------+
1 row in set (0.07 sec)
mysql> select customer_id,mod(customer_id,4) from orderdb01.order_master limit 5;
+-------------+--------------------+
| customer_id | mod(customer_id,4) |
+-------------+--------------------+
| 4648 | 0 |
| 5964 | 0 |
| 7496 | 0 |
| 2280 | 0 |
| 532 | 0 |
+-------------+--------------------+
5 rows in set (0.00 sec)
orderdb02数据如下：
mysql> select count(1) from orderdb02.order_master;
+----------+
| count(1) |
+----------+
| 1213 |
+----------+
1 row in set (0.00 sec)
mysql> select customer_id,mod(customer_id,4) from orderdb02.order_master limit 5;
+-------------+--------------------+
| customer_id | mod(customer_id,4) |
+-------------+--------------------+
| 5141 | 1 |
| 9621 | 1 |
| 5549 | 1 |
| 569 | 1 |
| 7509 | 1 |
+-------------+--------------------+
5 rows in set (0.00 sec)
|
Slave-113 确认数据
|
mysql> use orderdb03;
Database changed
mysql> select count(1) from order_master;
+----------+
| count(1) |
+----------+
| 1346 |
+----------+
1 row in set (0.01 sec)
mysql> select customer_id,mod(customer_id,4) from orderdb03.order_master limit 5;
+-------------+--------------------+
| customer_id | mod(customer_id,4) |
+-------------+--------------------+
| 5914 | 2 |
| 6406 | 2 |
| 1530 | 2 |
| 1866 | 2 |
| 6534 | 2 |
+-------------+--------------------+
5 rows in set (0.00 sec)
mysql> use orderdb04;
Database changed
mysql> select count(1) from order_master;
+----------+
| count(1) |
+----------+
| 1245 |
+----------+
1 row in set (0.00 sec)
mysql> select customer_id,mod(customer_id,4) from order_master limit 5;
+-------------+--------------------+
| customer_id | mod(customer_id,4) |
+-------------+--------------------+
| 2671 | 3 |
| 9043 | 3 |
| 4635 | 3 |
| 4647 | 3 |
| 6731 | 3 |
+-------------+--------------------+
5 rows in set (0.00 sec)
哈哈，各个分库的数据总数 和 Mycat端查询逻辑库的数据总数一致
|
rule.xml配置如下
[root@Master-111 conf]# cat schema.xml
|
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100" >
<table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master"/>
<table name="order_cart" primaryKey="cart_id" dataNode="ord_dn"/>
<table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ord_dn"/>
<table name="shipping_info" primaryKey="ship_id" dataNode="ord_dn"/>
<table name="warehouse_info" primaryKey="w_id" dataNode="ord_dn"/>
<table name="warehouse_proudct" primaryKey="wp_id" dataNode="ord_dn"/>
<table name="region_info" primaryKey="region_id" dataNode="ord_dn,prod_dn,cust_dn" type="global"/>
<table name="product_brand_info" primaryKey="brand_id" dataNode="prod_dn"/>
<table name="product_category" primaryKey="category_id" dataNode="prod_dn"/>
<table name="product_comment" primaryKey="comment_id" dataNode="prod_dn"/>
<table name="product_info" primaryKey="product_id" dataNode="prod_dn"/>
<table name="product_supplier_info" primaryKey="supplier_id" dataNode="prod_dn"/>
<table name="product_pic_info" primaryKey="product_pic_id" dataNode="prod_dn"/>
<table name="customer_balance_log" primaryKey="balance_id" dataNode="cust_dn"/>
<table name="customer_inf" primaryKey="customer_inf_id" dataNode="cust_dn"/>
<table name="customer_level_inf" primaryKey="customer_level" dataNode="cust_dn"/>
<table name="customer_login" primaryKey="customer_id" dataNode="cust_dn"/>
<table name="customer_login_log" primaryKey="login_id" dataNode="cust_dn"/>
<table name="customer_point_log" primaryKey="point_id" dataNode="cust_dn"/>
</schema>
<dataNode name="ord_dn" dataHost="192.168.124.112" database="order_db" />
<dataNode name="prod_dn" dataHost="192.168.124.113" database="product_db" />
<dataNode name="cust_dn" dataHost="192.168.124.114" database="customer_db" />
<dataNode name="orderdb01" dataHost="192.168.124.112" database="orderdb01"/>
<dataNode name="orderdb02" dataHost="192.168.124.112" database="orderdb02"/>
<dataNode name="orderdb03" dataHost="192.168.124.113" database="orderdb03"/>
<dataNode name="orderdb04" dataHost="192.168.124.113" database="orderdb04"/>
<dataHost name="192.168.124.112" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select 1</heartbeat>
<writeHost host="192.168.124.112" url="192.168.124.112:3306" user="root" password="123456" />
</dataHost>
<dataHost name="192.168.124.113" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select 1</heartbeat>
<writeHost host="192.168.124.113" url="192.168.124.113:3306" user="root" password="123456" />
</dataHost>
<dataHost name="192.168.124.114" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select 1</heartbeat>
<writeHost host="192.168.124.114" url="192.168.124.114:3306" user="root" password="123456" />
</dataHost>
|
[root@Master-111 conf]# cat rule.xml
|
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="order_master">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">4</property>
</function>
</mycat:rule>
|
创建时间：2021-09-26 17:17
넶浏览量：0
