当前位置： 代码迷 >> 综合 >> mycat 水平分库
  详细解决方案

mycat 水平分库

热度：61   发布时间：2024-01-26 04:05:35.0

注意 jdk必须是1.7的

为什么把 order_master 单拿出来，因为之前做过垂直发分库

原来一台服务器写请求，分散到了多个数据库中 ，写瓶颈带来的性能瓶颈
在这里插入图片描述

一、分片原则

  1. 能不切分尽量不要切分 没法历史归档
  2. 选择合适的切分规则和分片键
  3. 尽量避免跨分片JOIN操作

二、分片后如何处理查询

在这里插入图片描述

如果是非分片键(select * from order where id =1234),mycat就无法在根据分片键和分片函数来进行查询路由了，这是就把分片擦查询分发到所有的节点上进行查询，在返回结果，极大消耗mysql和mycat的服务器的资源

三、水平分片的步骤

读写频繁，数量大的

  1. 根据业务状态确定要进行水平切分的表
  2. 分析业务模型选择分片键及分片算法
  3. 使用MyCAT部署分片集群
  4. 测试分片集群
    - 业务和数据分批迁移到分片集群上
  5. 业务及数据迁移

四、如何选择分片键

注意: 主键做分片键并不是很好的选择

  1. 尽可能的比较均匀分布数据到各个节点上
  2. 该业务字段是频繁的或者最重要的查询条件

五、分析业务模型选择分片键及分片算法

  1. 对订单相关表进行水平切分
    大表使用全局表并不适合
  2. 订单表 来说: 订单号 下单人的用户id作为分片键
  3. 采用简单取模分片算法

六、使用MyCAT部署分片集群

  1. 使用schema.xml配置逻辑库及逻辑表
  2. 使用rule.xml配置分片表的分片规则
  3. 使用server.xml配置访问用户及权限

七、机子配置

主机名 IP 角色 数据库
master 192.168.194.154 Mycat
slave 192.168.194.155 Mysql orderdb01 orderdb02
slave 192.168.194.157 Mysql orderdb03 orderdb04

八、配置

增加了数据节点

<dataNode name="orderdb01" dataHost="mysql0155" database="orderdb01" />
<dataNode name="orderdb02" dataHost="mysql0155" database="orderdb02" />
<dataNode name="orderdb03" dataHost="mysql0157" database="orderdb03" />
<dataNode name="orderdb04" dataHost="mysql0157" database="orderdb04" />

逻辑表的名称不能改变的，必须要跟后端的物理数据节点物理表保持一致

分片规则的名字 rule=“order_master”

[root@bogon conf]# vim rule.xml 
[root@bogon conf]# pwd
/usr/local/mycat/conf

<?xml version="1.0" encoding="UTF-8"?>
<!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"><!-- how many data nodes --><property name="count">4</property></function>
</mycat:rule>              

<columns>customer_id</columns> 订单表切分 
<algorithm>mod-long</algorithm>  简单取模的算法

#这是类库

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">

#四片

<property name="count">4</property>

[root@bogon logs]# mysql -uapp_imooc -p -h192.168.194.154 -P8077

#这是配置
[root@bogon conf]# vim 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_detail" primaryKey="order_detail_id" dataNode="ordb" /><table name="order_cart" primaryKey="cart_id" dataNode="ordb" /><table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ordb" /><table name="region_info" primaryKey="region_id" dataNode="ordb,prodb,custdb" type="global" /><table name="shipping_info" primaryKey="ship_id" dataNode="ordb" /><table name="warehouse_info" primaryKey="w_id" dataNode="ordb" /><table name="warehouse_proudct" primaryKey="wp_id" dataNode="ordb" /><table name="product_brand_info" primaryKey="brand_id" dataNode="prodb" /><table name="product_category" primaryKey="category_id" dataNode="prodb" /><table name="product_comment" primaryKey="comment_id" dataNode="prodb" /><table name="product_info" primaryKey="product_id" dataNode="prodb" /><table name="product_supplier_info" primaryKey="supplier_id" dataNode="prodb" /><table name="product_pic_info" primaryKey="product_pic_id" dataNode="prodb" /><table name="customer_balance_log" primaryKey="balance_id" dataNode="custdb" /><table name="customer_inf" primaryKey="customer_inf_id" dataNode="custdb" /><table name="customer_level_inf" primaryKey="customer_level" dataNode="custdb" /><table name="customer_login" primaryKey="login_id" dataNode="custdb" /><table name="customer_point_log" primaryKey="point_id" dataNode="custdb" /><table name="customer_login_log" primaryKey="login_id" dataNode="custdb" /></schema><schema name="order_db" checkSQLschema="false" sqlMaxLimit="100"><table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master" /></schema><dataNode name="custdb" dataHost="mysql0158" database="customer_db" /><dataNode name="ordb" dataHost="mysql0155" database="order_db" /><dataNode name="prodb" dataHost="mysql0157" database="product_db" /><dataNode name="orderdb01" dataHost="mysql0155" database="orderdb01" /><dataNode name="orderdb02" dataHost="mysql0155" database="orderdb02" /><dataNode name="orderdb03" dataHost="mysql0157" database="orderdb03" /><dataNode name="orderdb04" dataHost="mysql0157" database="orderdb04" /><dataHost name="mysql0155" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.155" url="192.168.194.155:3306" user="im_mycat" password="12345678"/></dataHost><dataHost name="mysql0157" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.157" url="192.168.194.157:3306" user="im_mycat" password="12345678"/></dataHost><dataHost name="mysql0158" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.158" url="192.168.194.158:3306" user="im_mycat" password="12345678"/></dataHost></mycat:schema>

[root@bogon conf]# vim server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/"><system><property name="serverPort">8077</property><property name="managerPort">9066</property><property name="nonePasswordLogin">0</property><property name="bindIp">0.0.0.0</property><property name="frontWriteQueueSize">2048</property><property name="charset">utf8</property><property name="txIsolation">2</property><property name="processors">8</property><property name="idleTimeout">1800000</property><property name="sqlExecuteTimeout">300</property><property name="useSqlStat">0</property><property name="useGlobleTableCheck">0</property><property name="sequnceHandlerType">1</property><property name="defaultMaxLimit">100</property><property name="maxPacketSize">104857600</property></system><user name="app_imooc" defaultAccount="true"><property name="usingDecrypt">1</property><property name="password">I2LskkzmyIwIUiKU4UC8oDXVkz84k7sHuwoZlewVXM+6+jACm884c6sK7VdvtddwW/nwzEjdZJ69/Ujk0BELWw==</property><property name="schemas">imooc_db,order_db</property></user></mycat:server>

[root@bogon conf]# vim rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!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"><!-- how many data nodes --><property name="count">4</property></function>
</mycat:rule>

插入数据 是逻辑表 mysql -uapp_imooc -h127.0.0.1 -p12345678 -P8077 imooc_db的数据库

 insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324590122',3122,concat('im_',3122),138,1094,3816,'',3,0,0,0,0,now());

#是自己创建的
orderdb01
orderdb02
orderdb03
orderdb04

在这里插入图片描述

#取的是 customer_id

select mod(7586,4)

问题 你会看出order_id 有重复的 因为4个数据库都用了自增id的
在这里插入图片描述
九、全局唯一的自增id

  1. imooc_db 中的order_master是在垂直拆分后为了不影响前端应用访问，所以在MyCAT中把拆分后的多个库组成了一个逻辑库
  2. 垂直拆分后如果要对order_master进行水平拆分，就要把order_master单独拿出来

找一个地方，统一生成order_id ,使用redis ，或者单独数据库，生成order_id ,生成完以后，插入订单表中

删除表内容

delete from order_master;

#新建一个数据库,是物理库

mysql> create database mycat;

#导入dbseq.sql

[root@bogon conf]# pwd
/usr/local/mycat/conf
[root@bogon conf]# mysql -uroot -p mycat < dbseq.sql

—是在dbseq.sql里面

#增加一个函数
DROP FUNCTION IF EXISTS mycat_seq_currval;

#CREATE TABLE MYCAT_SEQUENCE ( name VARCHAR(64) NOT NULL, current_value BIGINT(20) NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) 每个对象的存储的序列号
在这里插入图片描述

一、全局序列号介绍

1.本地文件方式
使用服务器本地磁盘文件的方式

2.数据库方式
使用专用数据库的方式

3.本地时间戳方式
使用时间戳算法方式

4.分布式ZK ID 生成器
基于ZK 与本地配置的分布式ID 生成器(可以通过ZK 获取集群（机房）唯一InstanceID，也可以通过配置文
件配置InstanceID)

5.Zk 递增方式
另一种ZK生成方式

6.其他方式

#全局序列号参考文章

https://blog.csdn.net/weixin_42018518/article/details/90051803#2%E3%80%81%E6%95%B0%E6%8D%AE%E5%BA%93%E6%96%B9%E5%BC%8F
https://blog.51cto.com/mynode/1910570
https://cloud.tencent.com/developer/article/1522714

<property name="sequnceHandlerType">1</property>

增加数据节点 是刚才创建的mycat 数据库的

#相关表和函数所在的数据节点

[root@bogon conf]# vim sequence_db_conf.properties

GLOBAL=mycat
ORDER_MASTER=mycat

在这里插入图片描述
mysql> grant execute on . to ‘im_mycat’@‘192.168.194.%’;

如果还是不行就all 权限

select user,host from user;

mysql> show grants for im_mycat@'192.168.194.%';

#主机节点
dataHost

#数据节点
dataNode

#刚才创建的表mycat，增加一条记录
mysql> insert into MYCAT_SEQUENCE values (‘ORDER_MASTER’,1,1);
在这里插入图片描述
或者

insert into MYCAT_SEQUENCE values (‘ORDER_MASTER’,1,100);
在这里插入图片描述

#对mycat逻辑表 order_master 启用全局自增id的配置

<table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master" autoIncrement="true" />

#这是因为 这个里面之前没有这个服务器的配置 建立mycat表 物理表 是在mycat服务器创建的
在有mycat上的服务器 创建mycat表

     <dataNode name="mycat" dataHost="mysql0160" database="mycat" /><dataHost name="mysql0160" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.160" url="192.168.194.160:3306" user="im_mycat" password="12345678"/></dataHost>

[root@bogon conf]# vim 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">
<!--<able name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04"  rule="order_master" autoIncrement="true" />
--><table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" /><table name="order_cart" primaryKey="cart_id" dataNode="ordb" /><table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ordb" /><table name="region_info" primaryKey="region_id" dataNode="ordb,prodb,custdb" type="global" /><table name="shipping_info" primaryKey="ship_id" dataNode="ordb" /><table name="warehouse_info" primaryKey="w_id" dataNode="ordb" /><table name="warehouse_proudct" primaryKey="wp_id" dataNode="ordb" /><table name="product_brand_info" primaryKey="brand_id" dataNode="prodb" /><table name="product_category" primaryKey="category_id" dataNode="prodb" /><table name="product_comment" primaryKey="comment_id" dataNode="prodb" /><table name="product_info" primaryKey="product_id" dataNode="prodb" /><table name="product_supplier_info" primaryKey="supplier_id" dataNode="prodb" /><table name="product_pic_info" primaryKey="product_pic_id" dataNode="prodb" /><table name="customer_balance_log" primaryKey="balance_id" dataNode="custdb" /><table name="customer_inf" primaryKey="customer_inf_id" dataNode="custdb" /><table name="customer_level_inf" primaryKey="customer_level" dataNode="custdb" /><table name="customer_login" primaryKey="login_id" dataNode="custdb" /><table name="customer_point_log" primaryKey="point_id" dataNode="custdb" /><table name="customer_login_log" primaryKey="login_id" dataNode="custdb" /></schema><schema name="order_db" checkSQLschema="false" sqlMaxLimit="100"><table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master"  autoIncrement="true" /></schema><dataNode name="custdb" dataHost="mysql0158" database="customer_db" /><dataNode name="ordb" dataHost="mysql0155" database="order_db" /><dataNode name="prodb" dataHost="mysql0157" database="product_db" /><dataNode name="orderdb01" dataHost="mysql0155" database="orderdb01" /><dataNode name="orderdb02" dataHost="mysql0155" database="orderdb02" /><dataNode name="orderdb03" dataHost="mysql0157" database="orderdb03" /><dataNode name="orderdb04" dataHost="mysql0157" database="orderdb04" /><dataNode name="mycat" dataHost="mysql0162" database="mycat" /><dataHost name="mysql0162" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.164" url="192.168.194.164:3306" user="im_mycat" password="12345678"/></dataHost><dataHost name="mysql0155" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.161" url="192.168.194.161:3306" user="im_mycat" password="12345678"/></dataHost><dataHost name="mysql0157" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.163" url="192.168.194.163:3306" user="im_mycat" password="12345678"/></dataHost><dataHost name="mysql0158" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.158" url="192.168.194.158:3306" user="im_mycat" password="12345678"/></dataHost></mycat:schema>

[root@bogon conf]# vim rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!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"><!-- how many data nodes --><property name="count">4</property></function>
</mycat:rule>

[root@bogon conf]# vim server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/"><system><property name="serverPort">8077</property><property name="managerPort">9066</property><property name="nonePasswordLogin">0</property><property name="bindIp">0.0.0.0</property><property name="frontWriteQueueSize">2048</property><property name="charset">utf8</property><property name="txIsolation">2</property><property name="processors">8</property><property name="idleTimeout">1800000</property><property name="sqlExecuteTimeout">300</property><property name="useSqlStat">0</property><property name="useGlobleTableCheck">0</property><property name="sequnceHandlerType">1</property><property name="defaultMaxLimit">100</property><property name="maxPacketSize">104857600</property></system><user name="app_imooc" defaultAccount="true"><property name="usingDecrypt">1</property><property name="password">I2LskkzmyIwIUiKU4UC8oDXVkz84k7sHuwoZlewVXM+6+jACm884c6sK7VdvtddwW/nwzEjdZJ69/Ujk0BELWw==</property><property name="schemas">imooc_db,order_db</property><!--    <property name="schemas">order_db</property>--></user></mycat:server>

[root@bogon conf]# vim sequence_db_conf.properties

#sequence stored in datanode
GLOBAL=mycat
ORDER_MASTER=mycat

还是不清楚看这篇文章
https://www.linuxidc.com/Linux/2016-03/129179.htm

十、ER分片

ER关系分片的 ，可以把需要关联的两个表根据他们的关联键，把他们需要关联的数据分布到同一个分片内，就可以解决问题

在数据 orderdb01、orderdb02、orderdb03、orderdb04都要建立好order_detail

ORDER_DETAIL 是个表名字

insert into MYCAT_SEQUENCE values('ORDER_DETAIL',1,1);

mysql> select * from MYCAT_SEQUENCE;

在这里插入图片描述

[root@bogon conf]# vim 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">
<!--<able name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04"  rule="order_master" autoIncrement="true" />
--><!--<table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" />--><table name="order_master" primaryKey="order_id" dataNode="ordb" /><table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" /><table name="order_cart" primaryKey="cart_id" dataNode="ordb" /><table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ordb" /><table name="region_info" primaryKey="region_id" dataNode="ordb,prodb,custdb" type="global" /><table name="shipping_info" primaryKey="ship_id" dataNode="ordb" /><table name="warehouse_info" primaryKey="w_id" dataNode="ordb" /><table name="warehouse_proudct" primaryKey="wp_id" dataNode="ordb" /><table name="product_brand_info" primaryKey="brand_id" dataNode="prodb" /><table name="product_category" primaryKey="category_id" dataNode="prodb" /><table name="product_comment" primaryKey="comment_id" dataNode="prodb" /><table name="product_info" primaryKey="product_id" dataNode="prodb" /><table name="product_supplier_info" primaryKey="supplier_id" dataNode="prodb" /><table name="product_pic_info" primaryKey="product_pic_id" dataNode="prodb" /><table name="customer_balance_log" primaryKey="balance_id" dataNode="custdb" /><table name="customer_inf" primaryKey="customer_inf_id" dataNode="custdb" /><table name="customer_level_inf" primaryKey="customer_level" dataNode="custdb" /><table name="customer_login" primaryKey="login_id" dataNode="custdb" /><table name="customer_point_log" primaryKey="point_id" dataNode="custdb" /><table name="customer_login_log" primaryKey="login_id" dataNode="custdb" /></schema><schema name="order_db" checkSQLschema="false" sqlMaxLimit="100"><table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master"  autoIncrement="true"><childTable name="order_detail" primaryKey="order_detail_id" joinKey="order_id" parentKey="order_id" autoIncrement="true" /></table></schema><dataNode name="custdb" dataHost="mysql0158" database="customer_db" /><dataNode name="ordb" dataHost="mysql0155" database="order_db" /><dataNode name="prodb" dataHost="mysql0157" database="product_db" /><dataNode name="orderdb01" dataHost="mysql0155" database="orderdb01" /><dataNode name="orderdb02" dataHost="mysql0155" database="orderdb02" /><dataNode name="orderdb03" dataHost="mysql0157" database="orderdb03" /><dataNode name="orderdb04" dataHost="mysql0157" database="orderdb04" /><dataNode name="mycat" dataHost="mysql0162" database="mycat" /><dataHost name="mysql0162" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.164" url="192.168.194.164:3306" user="im_mycat" password="12345678"/></dataHost><dataHost name="mysql0155" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.161" url="192.168.194.161:3306" user="im_mycat" password="12345678"/></dataHost><dataHost name="mysql0157" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.163" url="192.168.194.163:3306" user="im_mycat" password="12345678"/></dataHost><dataHost name="mysql0158" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="192.168.194.158" url="192.168.194.158:3306" user="im_mycat" password="12345678"/></dataHost></mycat:schema>

[root@bogon conf]# vim server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/"><system><property name="serverPort">8077</property><property name="managerPort">9066</property><property name="nonePasswordLogin">0</property><property name="bindIp">0.0.0.0</property><property name="frontWriteQueueSize">2048</property><property name="charset">utf8</property><property name="txIsolation">2</property><property name="processors">8</property><property name="idleTimeout">1800000</property><property name="sqlExecuteTimeout">300</property><property name="useSqlStat">0</property><property name="useGlobleTableCheck">0</property><property name="sequnceHandlerType">1</property><property name="defaultMaxLimit">100</property><property name="maxPacketSize">104857600</property></system><user name="app_imooc" defaultAccount="true"><property name="usingDecrypt">1</property><property name="password">I2LskkzmyIwIUiKU4UC8oDXVkz84k7sHuwoZlewVXM+6+jACm884c6sK7VdvtddwW/nwzEjdZJ69/Ujk0BELWw==</property><property name="schemas">imooc_db,order_db</property><!--    <property name="schemas">order_db</property>--></user>
</mycat:server>

[root@bogon conf]# vim rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!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"><!-- how many data nodes --><property name="count">4</property></function>
</mycat:rule>

[root@bogon conf]# vim sequence_db_conf.properties

#sequence stored in datanode
GLOBAL=mycat
ORDER_MASTER=mycat
ORDER_DETAIL=mycat

insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324592548',9548,concat('im_',9548),280,2350,15404,'',3,0,0,0,0,now());

insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(10,27,'[columbia]毛衣示例商品-637',1,666.30000,666.30000,1);

这是 物理表 ，也是装mycat的服务器上
select order_id,order_sn,customer_id,mod(customer_id,4) from order_master;
在这里插入图片描述

在数据库orderdb01
select order_id from order_detail;
在这里插入图片描述
在数据库orderdb01
select order_id,order_sn,customer_id from order_master;

在这里插入图片描述

mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324590122',3122,concat('im_',3122),138,1094,3816,'',3,0,0,0,0,now());
Query OK, 1 row affected (1.70 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324594411',4411,concat('im_',4411),132,1294,3716,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.09 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324592211',2211,concat('im_',2211),112,1394,8716,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.11 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324598761',8761,concat('im_',8761),112,1394,1123,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.66 sec)mysql> mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,distrey,shipping_money,payment_money,create_time) values('20180324596407',9407,concat('im_',9407),3,40,430,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.04 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324595653',5653,concat('im_',5653),21,280,2318,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.09 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324599778',9778,concat('im_',9778),13,194,1624,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.20 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_monmysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324599778',9778,concat('im_',9778),13,194,1624,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.00 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324595928',9778,concat('im_',5928),229,1924,15564,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.02 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324591060',1060,concat('im_',1060),12,175,1489,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.02 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324599600',2600,concat('im_',2600),23,302,2503,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.05 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324594444',4444,concat('im_',4444),84,773,16054,'',3,0,0,0,0,now());
Query OK, 1 row affected (0.12 sec)mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324592548',9548,concat('im_',9548),280,2350,15404,'',3,0,0,0,0,now());insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(3,2491,'[诺诗兰]毛衣示例商品-355',1,69.360000,69.360000,1);insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(4,3037,'[探路者]毛衣示例商品-433',1,68.480000,68.480000,1)insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(5,2431,'[Lee]毛衣示例商品-347',1,242.850000,252.850000,1)insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(6,2699,'[探路者]毛衣示例商品-385',1,392.690000,392.690000,1)insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(7,3541,'[伯希和]毛衣示例商品-505',1,295.30000,295.30000,1)insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(8,3257,'[探路者]毛衣示例商品-467',1,292.30000,292.30000,1)insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(9,4457,'[NB]毛衣示例商品-637',1,307.30000,307.30000,1)insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(10,27,'[columbia]毛衣示例商品-637',1,666.30000,666.30000,1)insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(11,3341,'[北极狐]毛衣示例商品-637',1,833.30000,833.30000,1)insert into order_detail(order_id,product_id,product_name,product_cnt,product_price,average_cost,w_id) values(12,2813,'[太平鸟]毛衣示例商品-637',1,547.30000,547.30000,1)

十一、SQL拦截
记录所有记录

[root@bogon conf]# vim server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/"><system><property name="serverPort">8077</property><property name="managerPort">9066</property><property name="nonePasswordLogin">0</property><property name="bindIp">0.0.0.0</property><property name="frontWriteQueueSize">2048</property><property name="charset">utf8</property><property name="txIsolation">2</property><property name="processors">8</property><property name="idleTimeout">1800000</property><property name="sqlExecuteTimeout">300</property><property name="useSqlStat">0</property><property name="useGlobleTableCheck">0</property><property name="sequnceHandlerType">1</property><property name="defaultMaxLimit">100</property><property name="maxPacketSize">104857600</property>#拦截器 下面三个<property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property><property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property><property name="sqlInterceptorFile">/tmp/sql.txt</property></system><user name="app_imooc" defaultAccount="true"><property name="usingDecrypt">1</property><property name="password">I2LskkzmyIwIUiKU4UC8oDXVkz84k7sHuwoZlewVXM+6+jACm884c6sK7VdvtddwW/nwzEjdZJ69/Ujk0BELWw==</property><property name="schemas">imooc_db,order_db</property><!--    <property name="schemas">order_db</property>--></user></mycat:server>

[root@bogon tmp]# vim sql2019-12-31.txt
在这里插入图片描述

十二、SQL防火墙

配置白名单服务器上

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/"><system><property name="serverPort">8077</property><property name="managerPort">9066</property><property name="nonePasswordLogin">0</property><property name="bindIp">0.0.0.0</property><property name="frontWriteQueueSize">2048</property><property name="charset">utf8</property><property name="txIsolation">2</property><property name="processors">8</property><property name="idleTimeout">1800000</property><property name="sqlExecuteTimeout">300</property><property name="useSqlStat">0</property><property name="useGlobleTableCheck">0</property><property name="sequnceHandlerType">1</property><property name="defaultMaxLimit">100</property><property name="maxPacketSize">104857600</property><property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property><property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property><property name="sqlInterceptorFile">/tmp/sql.txt</property></system><firewall>#只允许192.168.194.161<whitehost><host user="app_imooc" host="192.168.194.161"></host></whitehost>#不容许 删除不带条件的  黑名单 <blacklist check="true"><property name="deleteWhereNoneCheck">true</property></blacklist></firewall><user name="app_imooc" defaultAccount="true"><property name="usingDecrypt">1</property><property name="password">I2LskkzmyIwIUiKU4UC8oDXVkz84k7sHuwoZlewVXM+6+jACm884c6sK7VdvtddwW/nwzEjdZJ69/Ujk0BELWw==</property><property name="schemas">imooc_db,order_db</property><!--    <property name="schemas">order_db</property>--></user></mycat:server>

mysql> delete from order_master;
ERROR 3012 (HY000): The statement is unsafe SQL, reject for user ‘app_imooc’

  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.