Skip to content

Latest commit

 

History

History
1225 lines (961 loc) · 73.4 KB

Mycat-Install-And-Settings.md

File metadata and controls

1225 lines (961 loc) · 73.4 KB
Raw

Mycat 安装和配置

部署的环境

  • 系统：CentOS 6.7 / CentOS 7.4
  • JDK：jdk-8u72-linux-x64.tar.gz
  • Mycat：Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
  • 推荐测试机子配置：1 CPU + 2 GB RAM + Docker

Docker 快速部署 MySQL

docker run --name mysql1 -p 3316:3306 -e MYSQL_ROOT_PASSWORD=root -d daocloud.io/library/mysql:5.7.13
docker run --name mysql2 -p 3326:3306 -e MYSQL_ROOT_PASSWORD=root -d daocloud.io/library/mysql:5.7.13
docker run --name mysql3 -p 3336:3306 -e MYSQL_ROOT_PASSWORD=root -d daocloud.io/library/mysql:5.7.13

Mycat 安装

  • 官网（页头有一个 PDF 要记得下载，这本资料写得很好）：http://mycat.io/
  • 官网下载（官网下载地址很乱，如果哪天右边这个地址不行了，到官网加群问下吧）：http://dl.mycat.io/
  • 项目 Github：https://github.com/MyCATApache/Mycat-Server
  • 此时（20170122） 最新稳定版本为：1.6，下载下来的文件名称：Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
  • 安装前的准备：
  • 开始安装：
    • 假设 Mycat 安装包的完整路径是：/opt/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
      • 解压：cd /opt ; tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
      • 移动解压的目录到官方建议的目录下：mv /opt/mycat /usr/local/
    • 设置 Mycat 的环境变量
      • vim /etc/profile，添加如下内容：
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin
  • 刷新配置：source /etc/profile
  • 到这里就安装好了，但是先不启动，需要先去配置相应的配置文件。

Mycat 只分库配置，并且要可预期租户数量（没有分表、读写分离、高可用）

  • 请至少预留 512M 内存给 Mycat
  • 使用 Mycat 这几个配置文件必定会改动到。这一个文件所代表的含义几句话说不了，还请你自己看下官网的文档，在目录：conf 下

server.xml 配置详解

  • server.xml，主要用于配置系统变量、用户管理、用户权限等。
  • 默认配置中有一个 TESTDB 相关的数据库用户配置，都要自己手工去掉。建议可以把配置文件中 TESTDB 相关的单词都替换成你的数据库名称（你最好能读懂各个配置）。
  • 配置用户，添加下面内容：
<!-- 定义登录mycat对的用户权限 -->  
<user name="adg_system_user">  
    <property name="password">123456</property>  
    <!-- 可访问数据库配置，多个数据库用英文逗号隔开-->  
    <property name="schemas">adg_system</property>  
    <!-- 配置是否允许只读，true 只读 -->  
    <property name="readOnly">false</property>  
    <!-- 定义限制前端整体的连接数，如果其值为0，或者不设置，则表示不限制连接数量 -->  
    <property name="benchmark">0</property>  
    <!-- 设置是否开启密码加密功能，默认为0不开启加密，为1则表示开启加密 -->  
    <property name="usingDecrypt">0</property>

    <!-- 表级 DML 权限设置（一般不建议设置） -->
    <!--
    <privileges check="false">
        <schema name="TESTDB" dml="0110" >
            <table name="tb01" dml="0000"></table>
            <table name="tb02" dml="1111"></table>
        </schema>
    </privileges>
     -->
</user>
  • mycat 中间件的全局配置：
<system>
    <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

    <property name="sequnceHandlerType">2</property>
    <!--1为开启mysql压缩协议-->
    <!--<property name="useCompression">1</property>--> 
    <!--设置模拟的MySQL版本号-->
    <!--<property name="fakeMySQLVersion">5.6.20</property>-->
    <!--指定每次分配 socker direct buffer 的值，默认是4096字节-->    
    <!--<property name="processorBufferChunk">40960</property>--> 
   
    <!--指定系统可用的线程数量，默认值为机器 CPU 核心 X 每个核心运行线程的数量-->    
    <!--<property name="processors">1</property>-->
    <!--<property name="processorExecutor">32</property>-->
     
    <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
    <property name="processorBufferPoolType">0</property>
    <!--默认是65535 64K 用于sql解析时最大文本长度 -->
    <!--<property name="maxStringLiteralLength">65535</property>-->
    <!--<property name="sequnceHandlerType">0</property>-->
    <!--<property name="backSocketNoDelay">1</property>-->
    <!--<property name="frontSocketNoDelay">1</property>-->
    <!--<property name="processorExecutor">16</property>-->
    <!--
    <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
    <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
    <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> 
    -->
    <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
    <property name="handleDistributedTransactions">0</property>

    <!--
    off heap for merge/order/group/limit      1开启   0关闭
    -->
    <property name="useOffHeapForMerge">1</property>

    <!--
        单位为m
    -->
    <property name="memoryPageSize">1m</property>

    <!--
        单位为k
    -->
    <property name="spillsFileBufferSize">1k</property>

    <property name="useStreamOutput">0</property>

    <!--
        单位为m
    -->
    <property name="systemReserveMemorySize">384m</property>


    <!--是否采用zookeeper协调切换（这里默认下载下来填写的是 true，如果你不用 zookeeper，可以改为 false）  -->
    <property name="useZKSwitch">false</property>


</system>

schema.xml 配置详解

  • schema.xml，用于设置 Mycat 的逻辑库、表、数据节点、dataHost 等内容，分库分表、读写分离等等都是在这里进行配置的
  • schema.xml 中特别注意的是分片节点的配置。如下，其中 adg_system_0000,adg_system_0001,adg_system_0002 是需要我们自己在 mysql 对应的机子上人工创建这三个空白数据库和表。
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <!--======================================================-->

    <schema name="adg_system" checkSQLschema="false" sqlMaxLimit="100">
        <!--
        建设有下面 5 张表，至于表做啥就不需要深究，知道大体关系即可。
        adg_ads
        adg_ads_set
        adg_ads_campaign
        adg_channel
        adg_shop
        adg_shop_channel
        
        TRUNCATE TABLE `adg_ads`;
        TRUNCATE TABLE `adg_ads_set`;
        TRUNCATE TABLE `adg_ads_campaign`;
        TRUNCATE TABLE `adg_channel`;
        TRUNCATE TABLE `adg_shop`;
        TRUNCATE TABLE `adg_shop_channel`;
        -->
        
        <!--全局表 start-->
        <table name="adg_channel" primaryKey="channel_id" type="global" dataNode="dn0,dn1,dn2"/>
        <table name="adg_shop_channel" primaryKey="shop_channel_id" type="global" dataNode="dn0,dn1,dn2"/>
        <table name="adg_shop" primaryKey="shop_id" type="global" dataNode="dn0,dn1,dn2"/>
        <!--全局表 end-->

        <!--sharding-by-shop-id 为自己已定义的规则：根据店铺 ID 分库-->
        <!--父子表采用 ER 关系分片，规则是由上面的 adg_ads_campaign 表分片规则决定 -->
        <!--primaryKey 物理表中主键字段-->       
        <!--joinKey 物理表中关联父表字段-->       
        <!--parentKey 物理父表的主键字段-->       
        <table name="adg_ads_campaign" primaryKey="ads_campaign_id" dataNode="dn0,dn1,dn2" rule="sharding-by-shop-id">
            <childTable name="adg_ads_set" primaryKey="ads_set_id" joinKey="shop_id" parentKey="shop_id">
                <childTable name="adg_ads" joinKey="ads_set_id" parentKey="ads_set_id"/>
            </childTable>
        </table>
    </schema>

    <!--======================================================-->

    <!-- dataNode标签定义了mycat中的数据节点，这也就是我们通常所说的数据分片，一个单独的dataNode就是一个独立的数据分片-->
    <!--1）name属性：定义数据节点的唯一名字-->
    <!--2）dataHost属性：定义该分片所属的数据库实例，属性引用自dataHost标签上定义的name属性-->
    <!--3）database属性：定义该分片所属的数据库实例上的具体数据库。-->

    <dataNode name="dn0" dataHost="mysql_host_0" database="adg_system_0000"/>
    <dataNode name="dn1" dataHost="mysql_host_1" database="adg_system_0001"/>
    <dataNode name="dn2" dataHost="mysql_host_2" database="adg_system_0002"/>

    <!--======================================================-->

    <!-- 定义数据库实例，读写分离和心跳语句-->
    <!--1)name 属性：标识唯一的dataHost-->
    <!-- 2)maxCon属性：指定每个读写实例连接池的最大连接数。内嵌writeHost、readHost标签会使用这个属性的值来实例化连接池的最大连接数-->
    <!--3)minCon属性：指定每个读写实例连接池的最小连接数。初始化连接池的大小的属性。-->
    <!--4）balance属性：负债均衡类型，有四种-->
    <!--   balance="0" : 不开启读写分离机制，所有的读操作都发送到当前可以用的writeHost上-->
    <!--   balance="1" : 全部的readHost与stand by writeHost（双主从模式下的master） 都参与select语句的负债均衡-->
    <!--   balance="2" : 所有的读操作都随机的往writeHost和readHost上分发-->
    <!--   balance="3" : 所有的读分发到readHost上，writeHost负责写-->
    <!--5）writeType属性：负载均衡目前的取值有两种：-->
    <!--   writeType="0"：所有的写操作都发送到第一个writeHost,writeHost1挂了，则切换到writeHost2上，重新恢复writeHost1后，还是以writeHost2为准-->
    <!--   writeType="1"：所有的写操作都随机的发送到配置的writeHost上，1.5版本以后不推荐使用该值。-->
    <!--6）dbType属性：制定后端后端数据的类型：mysql，oracle、mongoDB-->
    <!--7）dbDriver属性：制定后端数据库使用的Driver.目前可选的值为native和JDBC。native 支持 MySQL/MariaDB-->
    <!--8）switchType属性：默认值为1，自动切换。-->
    <!--                  -1表示不自动切换-->
    <!--                   2表示基于mysql主从同步的状态决定是否切换。-->
    <!--                  3表示基于mysql galaxy cluster 的切换机制-->
    <!--9）tempReadHostAvailable属性：如果配置了writeHost属性，下面的readHost依旧可以使用，默认为0-->
    <dataHost name="mysql_host_0" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="116.196.111.68:3316" user="root" password="root"/>
    </dataHost>
    
    <dataHost name="mysql_host_1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="116.196.111.68:3326" user="root" password="root"/>
    </dataHost>
    
    <dataHost name="mysql_host_2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="116.196.111.68:3336" user="root" password="root"/>
    </dataHost>
    
    <!--======================================================-->

</mycat:schema>

如果节点数据很多的情况，我们有一种简便写法

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <!--======================================================-->

    <schema name="adg_system" checkSQLschema="false" sqlMaxLimit="100">
        
        <table name="adg_channel" primaryKey="channel_id" type="global" dataNode="dn$0-49"/>
        
        <table name="adg_ads_campaign" primaryKey="ads_campaign_id" dataNode="dn$0-49" rule="sharding-by-shop-id">
            <childTable name="adg_ads_set" primaryKey="ads_set_id" joinKey="shop_id" parentKey="shop_id">
                <childTable name="adg_ads" joinKey="ads_set_id" parentKey="ads_set_id"/>
            </childTable>
        </table>
    </schema>

    <!--======================================================-->

    <dataNode name="dn$0-49" dataHost="mysql_host_0" database="adg_system_$0-49"/>

    <!--======================================================-->

    <dataHost name="mysql_host_0" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="116.196.111.68:3316" user="root" password="root"/>
    </dataHost>
    
    <!--======================================================-->

</mycat:schema>

rule.xml 配置详解

  • rule.xml，设置分片规则。
  • 下面我设置一个根据店铺进行分片的规格：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">


    <tableRule name="rule1">
        <rule>
            <columns>id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>

    <tableRule name="rule2">
        <rule>
            <columns>user_id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>

    <tableRule name="sharding-by-intfile">
        <rule>
            <columns>sharding_id</columns>
            <algorithm>hash-int</algorithm>
        </rule>
    </tableRule>
    <tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-murmur">
        <rule>
            <columns>id</columns>
            <algorithm>murmur</algorithm>
        </rule>
    </tableRule>
    <tableRule name="crc32slot">
        <rule>
            <columns>id</columns>
            <algorithm>crc32slot</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-month">
        <rule>
            <columns>create_time</columns>
            <algorithm>partbymonth</algorithm>
        </rule>
    </tableRule>
    <tableRule name="latest-month-calldate">
        <rule>
            <columns>calldate</columns>
            <algorithm>latestMonth</algorithm>
        </rule>
    </tableRule>

    <tableRule name="auto-sharding-rang-mod">
        <rule>
            <columns>id</columns>
            <algorithm>rang-mod</algorithm>
        </rule>
    </tableRule>

    <tableRule name="jch">
        <rule>
            <columns>id</columns>
            <algorithm>jump-consistent-hash</algorithm>
        </rule>
    </tableRule>
    
    <!--======================== start ==============================-->

    <tableRule name="sharding-by-shop-id">
        <rule>
            <columns>shop_id</columns>
            <algorithm>by-shop-id</algorithm>
        </rule>
    </tableRule>


    <function name="by-shop-id" class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">sharding-by-shop-id.txt</property>
        <property name="type">1</property><!-- 默认是0，表示 Integer，非零表示 String。因为我们这里是根据 shop_id 来分，而 shop_id 在表设计的时候是 bigint 类型，值是 18 位，所以这里必须填写非零值才行。-->
        <property name="defaultNode">0</property>
    </function>
    
    <!--======================== end ==============================-->

    
    <function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
        <property name="seed">0</property><!-- 默认是0 -->
        <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
        <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
        <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
        <!-- 
           <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
            用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西
         -->
    </function>

    <function name="crc32slot" class="io.mycat.route.function.PartitionByCRC32PreSlot">
        <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
    </function>
    
    <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
    </function>
    
    <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>
    
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <property name="count">3</property>
    </function>

    <function name="func1" class="io.mycat.route.function.PartitionByLong">
        <property name="partitionCount">8</property>
        <property name="partitionLength">128</property>
    </function>
    
    <function name="latestMonth" class="io.mycat.route.function.LatestMonthPartion">
        <property name="splitOneDay">24</property>
    </function>
    
    <function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2015-01-01</property>
    </function>

    <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
        <property name="mapFile">partition-range-mod.txt</property>
    </function>

    <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
        <property name="totalBuckets">3</property>
    </function>

</mycat:rule>
  • 还需要在 conf 新增文件 sharding-by-shop-id.txt 文件，内容是：
  • 需要注意的是：
417454619141211000=0
417454619141211001=1
417454619141211002=2
  • 表示 shop_id 为 417454619141211000 的时候，用 adg_system_0000 库
  • 表示 shop_id 为 417454619141211001 的时候，用 adg_system_0001 库
  • 表示 shop_id 为 417454619141211002 的时候，用 adg_system_0002 库
  • 其中第一个库是从下表 0 开始的。

log4j2.xml 配置详解

  • log4j2.xml，用于设置 Mycat 的日志输出规则，默认日志文件是输出 mycat 根目录下的 logs 目录下。

只分库的 demo 脚本

  • 在 MySQL 实例 1 中执行如下初始化脚本：
CREATE DATABASE /*!32312 IF NOT EXISTS*/`adg_system_0000` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `adg_system_0000`;
  • 在 MySQL 实例 2 中执行如下初始化脚本：
CREATE DATABASE /*!32312 IF NOT EXISTS*/`adg_system_0001` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `adg_system_0001`;
  • 在 MySQL 实例 3 中执行如下初始化脚本：
CREATE DATABASE /*!32312 IF NOT EXISTS*/`adg_system_0002` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `adg_system_0002`;

其他设置

  • 假设你上面的配置文件都配置好了：
    • 开放 8066 端口
      • 如果只是临时测试，可以临时关掉防火墙：service iptables stop
      • 不然就添加防火墙规则：
        • 添加规则：sudo iptables -I INPUT -p tcp -m tcp --dport 8066 -j ACCEPT
        • 保存规则：sudo service iptables save
        • 重启 iptables：sudo service iptables restart
  • 启动/停止/重启
    • 注意：在启动之前请一定要确保你可以用 mysql 工具连上你们的三个 mysql 和对应的数据库。有时候你连得上 mysql，但是你的账号不一定能连上这个 mysql 对应的数据库。
    • 启动有两种，一种是后台启动，启动后看不到任何信息。一种是控制台启动，启动后进入 Mycat 的控制台界面，显示当前 Mycat 的活动信息，按 Ctrl + C 停止控制台的时候 Mycat 也跟着停止。
    • 进入 Mycat 目录：cd /usr/local/mycat/bin
    • 后台启动：./mycat start && tail -300f /usr/local/mycat/logs/mycat.log
    • 控制台启动：./mycat console
    • 重启：./mycat restart
    • 停止：./mycat stop
  • 连接 Mycat
    • 连接 Mycat 的过程跟连接普通的 MySQL 表面上是没啥区别的，使用的命令都是一个样。但是需要注意的是，很容易出问题。对连接客户端有各种意外，目前我做了总结：
    • 连接命令：mysql -h192.168.1.112 -uroot -p -P8066，然后输入 mycat 的 root 用户密码（在上面介绍的 server.xml 中配置的）
    • 不建议 的连接方式：
      • SQLyog 软件，我这边是报：find no Route:select * from youmeek_nav.nav_url limit 0, 1000
      • Windows 系统下使用 cmd 去连接，我这边是报：ERROR 1105 (HY000): Unknown character set: 'gbk'
      • MySQL-Front 软件，没用过，但是别人说是有兼容性问题
    • 建议 的连接方式：
      • Navicat for mysql 软件
      • Linux 下的 MySQL 客户端命令行

使用 Navicat 连接 MyCat 测试 SQL

创建表 SQL

CREATE TABLE `adg_ads` (
  `ads_id` BIGINT(20) NOT NULL COMMENT '广告表ID',
  `ads_set_id` BIGINT(20) NOT NULL COMMENT '广告组表ID',
  `ads_title` VARCHAR(32) NOT NULL COMMENT '广告标题',
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`ads_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='广告表';


CREATE TABLE `adg_ads_set` (
  `ads_set_id` BIGINT(20) NOT NULL COMMENT '广告组表ID',
  `ads_set_title` VARCHAR(32) NOT NULL COMMENT '广告组标题',
  `ads_campaign_id` BIGINT(20) NOT NULL COMMENT '广告系列表ID',
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`ads_set_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='广告组表';


CREATE TABLE `adg_ads_campaign` (
  `ads_campaign_id` BIGINT(20) NOT NULL COMMENT '广告系列表ID',
  `ads_campaign_title` VARCHAR(32) NOT NULL COMMENT '广告系列标题',
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`ads_campaign_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='广告系列表';


CREATE TABLE `adg_channel` (
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`channel_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='渠道表';


CREATE TABLE `adg_shop` (
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  PRIMARY KEY (`shop_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';


CREATE TABLE `adg_shop_channel` (
  `shop_channel_id` BIGINT(20) NOT NULL COMMENT '店铺渠道中间表ID',
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`shop_channel_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='店铺渠道中间表';

创建数据 SQL

INSERT  INTO `adg_shop`(`shop_id`,`shop_name`) VALUES (417454619141211000,'NC站');
INSERT  INTO `adg_shop`(`shop_id`,`shop_name`) VALUES (417454619141211001,'BG站');

INSERT  INTO `adg_channel`(`channel_id`,`channel_name`) VALUES (1,'Facebook');
INSERT  INTO `adg_channel`(`channel_id`,`channel_name`) VALUES (2,'Google');
INSERT  INTO `adg_channel`(`channel_id`,`channel_name`) VALUES (3,'Twitter');

INSERT  INTO `adg_shop_channel`(`shop_channel_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (1,417454619141211000,1,'NC站','Facebook');
INSERT  INTO `adg_shop_channel`(`shop_channel_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (2,417454619141211000,2,'NC站','Google');
INSERT  INTO `adg_shop_channel`(`shop_channel_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (3,417454619141211001,1,'BG站','Facebook');
INSERT  INTO `adg_shop_channel`(`shop_channel_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (4,417454619141211001,2,'BG站','Google');

INSERT  INTO `adg_ads_campaign`(`ads_campaign_id`,`ads_campaign_title`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (1,'第1个广告系列',417454619141211000,1,'NC站','Facebook');
INSERT  INTO `adg_ads_campaign`(`ads_campaign_id`,`ads_campaign_title`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (2,'第2个广告系列',417454619141211001,2,'BG站','Google');

INSERT  INTO `adg_ads_set`(`ads_set_id`,`ads_set_title`,`ads_campaign_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (1,'第1个广告集',1,417454619141211000,1,'NC站','Facebook');
INSERT  INTO `adg_ads_set`(`ads_set_id`,`ads_set_title`,`ads_campaign_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (2,'第2个广告集',2,417454619141211001,2,'BG站','Google');

INSERT  INTO `adg_ads`(`ads_id`,`ads_set_id`,`ads_title`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (1,1,'第1个广告',417454619141211000,1,'NC站','Facebook');
INSERT  INTO `adg_ads`(`ads_id`,`ads_set_id`,`ads_title`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (2,2,'第2个广告',417454619141211001,2,'BG站','Google');

mycat 正常启动的 log 内容

  • tail -300f wrapper.log
2018-02-05 14:15:41.432  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.<init>(PhysicalDBPool.java:100)) - total resouces of dataHost mysql_host_0 is :1
2018-02-05 14:15:41.435  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.<init>(PhysicalDBPool.java:100)) - total resouces of dataHost mysql_host_2 is :1
2018-02-05 14:15:41.435  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.<init>(PhysicalDBPool.java:100)) - total resouces of dataHost mysql_host_1 is :1
2018-02-05 14:15:41.442  INFO [WrapperSimpleAppMain] (io.mycat.cache.CacheService.createLayeredPool(CacheService.java:125)) - create layer cache pool TableID2DataNodeCache of type encache ,default cache size 10000 ,default expire seconds18000
2018-02-05 14:15:41.445  INFO [WrapperSimpleAppMain] (io.mycat.cache.DefaultLayedCachePool.createChildCache(DefaultLayedCachePool.java:80)) - create child Cache: TESTDB_ORDERS for layered cache TableID2DataNodeCache, size 50000, expire seconds 18000
2018-02-05 14:15:41.597  INFO [WrapperSimpleAppMain] (io.mycat.config.classloader.DynaClassLoader.<init>(DynaClassLoader.java:34)) - dyna class load from ./catlet,and auto check for class file modified every 60 seconds
2018-02-05 14:15:41.601  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:266)) - ===============================================
2018-02-05 14:15:41.602  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:267)) - MyCat is ready to startup ...
2018-02-05 14:15:41.602  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:279)) - Startup processors ...,total processors:1,aio thread pool size:4 each process allocated socket buffer pool  bytes ,a page size:2097152  a page's chunk number(PageSize/ChunkSize) is:512  buffer page's number is:20
2018-02-05 14:15:41.602  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:280)) - sysconfig params:SystemConfig [processorBufferLocalPercent=100, frontSocketSoRcvbuf=1048576, frontSocketSoSndbuf=4194304, backSocketSoRcvbuf=4194304, backSocketSoSndbuf=1048576, frontSocketNoDelay=1, backSocketNoDelay=1, maxStringLiteralLength=65535, frontWriteQueueSize=2048, bindIp=0.0.0.0, serverPort=8066, managerPort=9066, charset=utf8mb4, processors=1, processorExecutor=4, timerExecutor=2, managerExecutor=2, idleTimeout=1800000, catletClassCheckSeconds=60, sqlExecuteTimeout=300, processorCheckPeriod=1000, dataNodeIdleCheckPeriod=300000, dataNodeHeartbeatPeriod=10000, clusterHeartbeatUser=_HEARTBEAT_USER_, clusterHeartbeatPass=_HEARTBEAT_PASS_, clusterHeartbeatPeriod=5000, clusterHeartbeatTimeout=10000, clusterHeartbeatRetry=10, txIsolation=3, parserCommentVersion=50148, sqlRecordCount=10, bufferPoolPageSize=2097152, bufferPoolChunkSize=4096, bufferPoolPageNumber=20, maxResultSet=524288, bigResultSizeSqlCount=10, bufferUsagePercent=80, flowControlRejectStrategy=0, clearBigSqLResultSetMapMs=600000, defaultMaxLimit=100, sequnceHandlerType=2, sqlInterceptor=io.mycat.server.interceptor.impl.DefaultSqlInterceptor, sqlInterceptorType=select, sqlInterceptorFile=/usr/local/mycat/logs/sql.txt, mutiNodeLimitType=0, mutiNodePatchSize=100, defaultSqlParser=druidparser, usingAIO=0, packetHeaderSize=4, maxPacketSize=16777216, mycatNodeId=1]
2018-02-05 14:15:41.667  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:381)) - using nio network handler 
2018-02-05 14:15:41.681  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:397)) - $_MyCatManager is started and listening on 9066
2018-02-05 14:15:41.682  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:401)) - $_MyCatServer is started and listening on 8066
2018-02-05 14:15:41.682  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:403)) - ===============================================
2018-02-05 14:15:41.682  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:407)) - Initialize dataHost ...
2018-02-05 14:15:41.682  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.initSource(PhysicalDBPool.java:294)) - init backend myqsl source ,create connections total 10 for hostM1 index :0
2018-02-05 14:15:41.683  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.686  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.686  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.689  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.690  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.690  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.690  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.690  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.690  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.690  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0000
2018-02-05 14:15:41.834  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=1, lastTime=1517811341834, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=793, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.835  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=2, lastTime=1517811341835, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=797, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.835  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=4, lastTime=1517811341835, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=795, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.836  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=8, lastTime=1517811341836, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=796, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.836  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=7, lastTime=1517811341836, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=794, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.837  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=3, lastTime=1517811341837, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=801, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.837  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=5, lastTime=1517811341837, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=798, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.838  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=6, lastTime=1517811341838, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=799, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.840  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=10, lastTime=1517811341840, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=800, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.842  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=9, lastTime=1517811341842, user=root, schema=adg_system_0000, old shema=adg_system_0000, borrowed=true, fromSlaveDB=false, threadId=802, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:41.899  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.initSource(PhysicalDBPool.java:319)) - init result :finished 10 success 10 target count:10
2018-02-05 14:15:41.899  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.init(PhysicalDBPool.java:265)) - mysql_host_0 index:0 init success
2018-02-05 14:15:41.899  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.saveDataHostIndex(MycatServer.java:604)) - save DataHost index  mysql_host_0 cur index 0
2018-02-05 14:15:41.907  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.initSource(PhysicalDBPool.java:294)) - init backend myqsl source ,create connections total 10 for hostM1 index :0
2018-02-05 14:15:41.907  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.908  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.908  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.909  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.912  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.915  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.916  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.916  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.916  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:41.917  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0002
2018-02-05 14:15:42.021  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=12, lastTime=1517811342021, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=779, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.023  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=11, lastTime=1517811342022, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=780, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.027  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=13, lastTime=1517811342027, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=781, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.027  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=14, lastTime=1517811342027, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=782, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.030  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=17, lastTime=1517811342030, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=783, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.032  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=16, lastTime=1517811342032, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=785, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.034  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=15, lastTime=1517811342034, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=784, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.036  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=18, lastTime=1517811342036, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=786, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.043  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=19, lastTime=1517811342043, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=787, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.043  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=20, lastTime=1517811342043, user=root, schema=adg_system_0002, old shema=adg_system_0002, borrowed=true, fromSlaveDB=false, threadId=788, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3336, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.118  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.initSource(PhysicalDBPool.java:319)) - init result :finished 10 success 10 target count:10
2018-02-05 14:15:42.118  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.init(PhysicalDBPool.java:265)) - mysql_host_2 index:0 init success
2018-02-05 14:15:42.118  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.saveDataHostIndex(MycatServer.java:604)) - save DataHost index  mysql_host_2 cur index 0
2018-02-05 14:15:42.118  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.initSource(PhysicalDBPool.java:294)) - init backend myqsl source ,create connections total 10 for hostM1 index :0
2018-02-05 14:15:42.119  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.120  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.120  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.121  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.121  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.122  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.122  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.122  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.123  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.123  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:413)) - no ilde connection in pool,create new connection for hostM1 of schema adg_system_0001
2018-02-05 14:15:42.235  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=21, lastTime=1517811342235, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=776, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.237  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=23, lastTime=1517811342237, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=777, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.239  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=26, lastTime=1517811342239, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=781, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.240  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=22, lastTime=1517811342240, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=778, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.243  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=25, lastTime=1517811342243, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=779, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.245  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=24, lastTime=1517811342245, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=780, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.249  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=29, lastTime=1517811342249, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=782, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.249  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=30, lastTime=1517811342249, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=783, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.250  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=28, lastTime=1517811342250, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=785, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.251  INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly MySQLConnection [id=27, lastTime=1517811342251, user=root, schema=adg_system_0001, old shema=adg_system_0001, borrowed=true, fromSlaveDB=false, threadId=784, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=116.196.111.68, port=3326, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2018-02-05 14:15:42.324  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.initSource(PhysicalDBPool.java:319)) - init result :finished 10 success 10 target count:10
2018-02-05 14:15:42.324  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.init(PhysicalDBPool.java:265)) - mysql_host_1 index:0 init success
2018-02-05 14:15:42.324  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.saveDataHostIndex(MycatServer.java:604)) - save DataHost index  mysql_host_1 cur index 0

使用细节

  • 声明：
    • mycat 的库表这里我们称作虚拟库表
  • 创建新表流程：
    • 先编辑 /conf/schema.xml 文件，增加对应的表信息
    • 把创建表 SQL 放在虚拟库上执行，则各个节点的物理库表会增加对应的表结构

只垂直分库流程

  • 垂直切分缺点
    • 如果不采用全局表那就只能通过 API 接口关联表数据（为了增加吞吐，可以考虑多线程并发执行 API 接口后整合）
    • 对于访问频繁、数据大的表，性能瓶颈依旧会存在
  • 这里只是写个大体思路，基础知识上面已经说了。
  • 假设以电商系统为例，拆分出：商品库、用户库、订单库，有 3 个 MySQL 实例各自存储一个业务库
    1. 因为不进行水平切分，所以不需要修改 rule.xml
    1. 修改 server.xml，增加用户和权限
    1. 修改 schema.xml，增加逻辑库配置
    • dataHost 配置 3 个（只有 3 个 MySQL 实例）
    • dataNode 配置 3 个，分别对应：商品库（1 个）、用户库（1 个）、订单库（1 个）
    • schema 配置：
<schema name="adg_system" checkSQLschema="false" sqlMaxLimit="100">

    <!--全局表 start-->
    <table name="adg_common" primaryKey="id" type="global" dataNode="dn0,dn1,dn2"/>
    <table name="adg_region" primaryKey="id" type="global" dataNode="dn0,dn1,dn2"/>
    <!--全局表 end-->
    
    <!-- 分库表 start-->
    <table name="adg_product" primaryKey="id" dataNode="dn0"/>
    <table name="adg_sku" primaryKey="id" dataNode="dn0"/>
    <table name="adg_category" primaryKey="id" dataNode="dn0"/>
    
    <table name="adg_user" primaryKey="id" dataNode="dn1"/>
    <table name="adg_role" primaryKey="id" dataNode="dn1"/>
    
    <table name="adg_order" primaryKey="id" dataNode="dn2"/>
    <table name="adg_order_item" primaryKey="id" dataNode="dn2"/>
    <!-- 分库表 end-->

</schema>

垂直分库基础上进行水平切分

  • 水平分片原则
    • 能不切分是最好的，能用归档方式分开存储，分开查询的尽可能通过产品思维层面解决
    • 一般只推荐那些数据量大，并且读写频繁的表进行切分
    • 选择合适的切分规则、分片键
    • 尽可能避免跨分片 JOIN 操作
  • 水平分片的步骤
    • 选择分片键和分片算法
      • 一般分片键推荐的是查询条件基本都会带上的那个字段，或者影响面很广的字段
      • 分片键是能尽可能均匀把数据分片到各个节点
      • 没有什么可以选择的时候，推荐就是主键
    • MyCAT 配置分片节点
    • 测试分片节点
    • 业务数据迁移

对订单相关业务进行水平切分

  • 一般选择订单号或者所属用户 ID 进行分片，这里推荐使用所属用户 ID，因为查询订单信息基本都是从用户角度发起的
    1. 前面垂直分库已经修改 server.xml，这里不需要
    1. 修改 rule.xml，修改分片规则
<tableRule name="sharding-by-user-id-to-order">
    <rule>
        <columns>user_id</columns>
        <algorithm>by-user-id-to-order</algorithm>
    </rule>
</tableRule>

<function name="by-user-id-to-order" class="io.mycat.route.function.PartitionByMod">
	<!-- 订单只有 3 个物理库，所以这里填写 3 -->
    <property name="count">3</property>
</function>
    1. 修改 schema.xml，增加逻辑库配置
    • dataHost 配置 3 个（只有 3 个 MySQL 实例）
    • dataNode 配置 5 个，分别对应：商品库（1 个）、用户库（1 个）、订单库（3 个）
    • schema 配置，这里使用取模分片算法：
<schema name="adg_system" checkSQLschema="false" sqlMaxLimit="100">

    <!--全局表 start-->
    <table name="adg_common" primaryKey="id" type="global" dataNode="dn0,dn1,dn2"/>
    <table name="adg_region" primaryKey="id" type="global" dataNode="dn0,dn1,dn2"/>
    <!--全局表 end-->
    
    <!-- 分库表 start-->
    <table name="adg_product" primaryKey="id" dataNode="dn0"/>
    <table name="adg_sku" primaryKey="id" dataNode="dn0"/>
    <table name="adg_category" primaryKey="id" dataNode="dn0"/>
    
    <table name="adg_user" primaryKey="id" dataNode="dn1"/>
    <table name="adg_role" primaryKey="id" dataNode="dn1"/>
    
    <table name="adg_order" primaryKey="id" dataNode="order_01,order_02,order_03" rule="sharding-by-user-id-to-order">
	    <childTable name="adg_order_item" primaryKey="id" joinKey="order_id" parentKey="id"/>
	</table>
    <!-- 分库表 end-->

</schema>

其他常用配置

SQL 拦截（做审计，不分该 SQL 是否执行成功与否）

  • 修改 server.xml（只拦截 UPDATE,DELETE,INSERT）
<property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property>
<property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property>
<property name="sqlInterceptorFile">/opt/mycat-log.txt</property>

SQL 防火墙

  • 作用
    • 限制某些用户只能通过某些主机访问（whitehost 标签）
    • 屏蔽一些 SQL 语句（blacklist 标签）
<firewall> 
	<whitehost>
		<host user="adg_system_user" host="127.0.0.1"/>
		<host user="adg_system_user" host="127.0.0.2"/>
	</whitehost>
	<blacklist check="true">
		<!-- 不允许执行 delete 语句中不带 where 条件的 SQL -->
		<property name="deleteWhereNoneCheck">true</property>
	</blacklist>
</firewall>

高可用方案（MySQL + MyCAT + Zookeeper + HAProxy + Keepalived）

MySQL（3 节点）

  • 端口使用：
    • 3406
    • 3407
    • 3408
docker run -p 3406:3306 --name mycat-mysql-1 -e MYSQL_ROOT_PASSWORD=adgADG123456 -d mysql:5.7

docker run -p 3407:3306 --name mycat-mysql-2 -e MYSQL_ROOT_PASSWORD=adgADG123456 -d mysql:5.7

docker run -p 3408:3306 --name mycat-mysql-3 -e MYSQL_ROOT_PASSWORD=adgADG123456 -d mysql:5.7

MyCAT + Zookeeper

Zookeeper 单机多个实例（集群）
version: '3.1'

services:
  zoo1:
    image: zookeeper
    restart: always
    hostname: zoo1
    ports:
      - 2281:2181
    environment:
      ZOO_MY_ID: 1
      ZOO_SERVERS: server.1=0.0.0.0:2888:3888 server.2=zoo2:2888:3888 server.3=zoo3:2888:3888

  zoo2:
    image: zookeeper
    restart: always
    hostname: zoo2
    ports:
      - 2282:2181
    environment:
      ZOO_MY_ID: 2
      ZOO_SERVERS: server.1=zoo1:2888:3888 server.2=0.0.0.0:2888:3888 server.3=zoo3:2888:3888

  zoo3:
    image: zookeeper
    restart: always
    hostname: zoo3
    ports:
      - 2283:2181
    environment:
      ZOO_MY_ID: 3
      ZOO_SERVERS: server.1=zoo1:2888:3888 server.2=zoo2:2888:3888 server.3=0.0.0.0:2888:3888
  • 启动：docker-compose -f zookeeper.yml -p zk_test up -d
    • 参数 -p zk_test 表示这个 compose project 的名字，等价于：COMPOSE_PROJECT_NAME=zk_test docker-compose -f zookeeper.yml up -d
    • 不指定项目名称，Docker-Compose 默认以当前文件目录名作为应用的项目名
    • 报错是正常情况的。
  • 停止：docker-compose -f zookeeper.yml -p zk_test stop
MyCAT 单机多个实例
  • 必须有 JDK 环境（我这里使用的是：1.8.0_171）
tar -zxvf Mycat-server-1.6.5-release-20180503154132-linux.tar.gz

mv mycat mycat-1
  • cd /usr/local/mycat-1/conf
  • vim server.xml
<!-- 定义登录mycat对的用户权限 -->  
<user name="adg_system_user">  
    <property name="password">123456</property>  
    <!-- 可访问数据库配置，多个数据库用英文逗号隔开-->  
    <property name="schemas">adg_system</property>  
    <!-- 配置是否允许只读，true 只读 -->  
    <property name="readOnly">false</property>  
    <!-- 定义限制前端整体的连接数，如果其值为0，或者不设置，则表示不限制连接数量 -->  
    <property name="benchmark">0</property>  
    <!-- 设置是否开启密码加密功能，默认为0不开启加密，为1则表示开启加密 -->  
    <property name="usingDecrypt">0</property>
</user>
  • cd /usr/local/mycat-1/conf
  • vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <!--======================================================-->

    <schema name="adg_system" checkSQLschema="false" sqlMaxLimit="100">
        
        <!--全局表 start-->
        <table name="adg_channel" primaryKey="channel_id" type="global" dataNode="dn0,dn1,dn2"/>
        <table name="adg_shop_channel" primaryKey="shop_channel_id" type="global" dataNode="dn0,dn1,dn2"/>
        <table name="adg_shop" primaryKey="shop_id" type="global" dataNode="dn0,dn1,dn2"/>
        <!--全局表 end-->

        <table name="adg_ads_campaign" primaryKey="ads_campaign_id" dataNode="dn0,dn1,dn2" rule="sharding-by-shop-id">
            <childTable name="adg_ads_set" primaryKey="ads_set_id" joinKey="shop_id" parentKey="shop_id">
                <childTable name="adg_ads" joinKey="ads_set_id" parentKey="ads_set_id"/>
            </childTable>
        </table>
    </schema>

    <!--======================================================-->

    <dataNode name="dn0" dataHost="mysql_host_0" database="adg_system_0000"/>
    <dataNode name="dn1" dataHost="mysql_host_1" database="adg_system_0001"/>
    <dataNode name="dn2" dataHost="mysql_host_2" database="adg_system_0002"/>

    <!--======================================================-->

    <dataHost name="mysql_host_0" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.0.105:3406" user="root" password="adgADG123456"/>
    </dataHost>
    
    <dataHost name="mysql_host_1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.0.105:3407" user="root" password="adgADG123456"/>
    </dataHost>
    
    <dataHost name="mysql_host_2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.0.105:3408" user="root" password="adgADG123456"/>
    </dataHost>
    
    <!--======================================================-->

</mycat:schema>
  • cd /usr/local/mycat-1/conf
  • vim rule.xml
<tableRule name="sharding-by-shop-id">
    <rule>
        <columns>shop_id</columns>
        <algorithm>by-shop-id</algorithm>
    </rule>
</tableRule>


<function name="by-shop-id" class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">sharding-by-shop-id.txt</property>
    <property name="type">1</property><!-- 默认是0，表示 Integer，非零表示 String。因为我们这里是根据 shop_id 来分，而 shop_id 在表设计的时候是 bigint 类型，值是 18 位，所以这里必须填写非零值才行。-->
    <property name="defaultNode">0</property>
</function>

还需要在 conf 新增文件 sharding-by-shop-id.txt 文件，内容是：
需要注意的是：
417454619141211000=0
417454619141211001=1
417454619141211002=2

CREATE DATABASE adg_system_0000 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE adg_system_0001 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE adg_system_0002 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  • 使用 Zookeeper 配置

  • vim /usr/local/mycat-1/conf/myid.properties

loadZk=true                                                                                                  
zkURL=192.168.0.105:2281,192.168.0.105:2282,192.168.0.105:2283
clusterId=mycat-cluster
myid=mycat_fz_01
clusterSize=2
clusterNodes=mycat_fz_01,mycat_fz_02
#server  booster  ;   booster install on db same server,will reset all minCon to 2
type=server
boosterDataHosts=dataHost1
  • 同步节点配置到 Zookeeper
cd /usr/local/mycat-1/conf
cp -f schema.xml server.xml rule.xml sharding-by-shop-id.txt zkconf/

sh /usr/local/mycat-1/bin/init_zk_data.sh
  • 重要参数：
clusterSize=2 表示有几个 MyCAT 节点数量
  • 解压另外一个节点：
tar -zxvf Mycat-server-1.6.5-release-20180503154132-linux.tar.gz

mv mycat mycat-2

因为是单机多节点，所以这里需要修改几个端口参数
vim /usr/local/mycat-2/conf/server.xml

旧值：
<property name="serverPort">8066</property>
<property name="managerPort">9066</property> 

新值：
<property name="serverPort">8067</property>
<property name="managerPort">9067</property> 


vim /usr/local/mycat-2/conf/wrapper.conf

旧值：
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984

新值：
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1985

  • 修改另外一个节点配置：

  • vim /usr/local/mycat-2/conf/myid.properties

loadZk=true                                                                                                  
zkURL=192.168.0.105:2281,192.168.0.105:2282,192.168.0.105:2283
clusterId=mycat-cluster
myid=mycat_fz_02
clusterSize=2
clusterNodes=mycat_fz_01,mycat_fz_02
#server  booster  ;   booster install on db same server,will reset all minCon to 2
type=server
boosterDataHosts=dataHost1
  • 启动节点：
cd /usr/local/mycat-1/bin
后台启动：./mycat start && tail -300f /usr/local/mycat-1/logs/mycat.log
控制台启动：./mycat console
控制台启动：cd /usr/local/mycat-1/bin && ./mycat console
重启：./mycat restart
停止：./mycat stop

cd /usr/local/mycat-2/bin
后台启动：./mycat start && tail -300f /usr/local/mycat-2/logs/mycat.log
控制台启动：./mycat console
控制台启动：cd /usr/local/mycat-2/bin && ./mycat console
重启：./mycat restart
停止：./mycat stop
  • 创建数据结构：
CREATE TABLE `adg_ads` (
  `ads_id` BIGINT(20) NOT NULL COMMENT '广告表ID',
  `ads_set_id` BIGINT(20) NOT NULL COMMENT '广告组表ID',
  `ads_title` VARCHAR(32) NOT NULL COMMENT '广告标题',
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`ads_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='广告表';


CREATE TABLE `adg_ads_set` (
  `ads_set_id` BIGINT(20) NOT NULL COMMENT '广告组表ID',
  `ads_set_title` VARCHAR(32) NOT NULL COMMENT '广告组标题',
  `ads_campaign_id` BIGINT(20) NOT NULL COMMENT '广告系列表ID',
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`ads_set_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='广告组表';


CREATE TABLE `adg_ads_campaign` (
  `ads_campaign_id` BIGINT(20) NOT NULL COMMENT '广告系列表ID',
  `ads_campaign_title` VARCHAR(32) NOT NULL COMMENT '广告系列标题',
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`ads_campaign_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='广告系列表';


CREATE TABLE `adg_channel` (
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`channel_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='渠道表';


CREATE TABLE `adg_shop` (
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  PRIMARY KEY (`shop_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';


CREATE TABLE `adg_shop_channel` (
  `shop_channel_id` BIGINT(20) NOT NULL COMMENT '店铺渠道中间表ID',
  `shop_id` BIGINT(20) NOT NULL COMMENT '店铺ID',
  `channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
  `shop_name` VARCHAR(32) NOT NULL COMMENT '店铺名称',
  `channel_name` VARCHAR(32) NOT NULL COMMENT '渠道名称',
  PRIMARY KEY (`shop_channel_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='店铺渠道中间表';

INSERT  INTO `adg_shop`(`shop_id`,`shop_name`) VALUES (417454619141211000,'NC站');
INSERT  INTO `adg_shop`(`shop_id`,`shop_name`) VALUES (417454619141211001,'BG站');

INSERT  INTO `adg_channel`(`channel_id`,`channel_name`) VALUES (1,'Facebook');
INSERT  INTO `adg_channel`(`channel_id`,`channel_name`) VALUES (2,'Google');
INSERT  INTO `adg_channel`(`channel_id`,`channel_name`) VALUES (3,'Twitter');

INSERT  INTO `adg_shop_channel`(`shop_channel_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (1,417454619141211000,1,'NC站','Facebook');
INSERT  INTO `adg_shop_channel`(`shop_channel_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (2,417454619141211000,2,'NC站','Google');
INSERT  INTO `adg_shop_channel`(`shop_channel_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (3,417454619141211001,1,'BG站','Facebook');
INSERT  INTO `adg_shop_channel`(`shop_channel_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (4,417454619141211001,2,'BG站','Google');

INSERT  INTO `adg_ads_campaign`(`ads_campaign_id`,`ads_campaign_title`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (1,'第1个广告系列',417454619141211000,1,'NC站','Facebook');
INSERT  INTO `adg_ads_campaign`(`ads_campaign_id`,`ads_campaign_title`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (2,'第2个广告系列',417454619141211001,2,'BG站','Google');

INSERT  INTO `adg_ads_set`(`ads_set_id`,`ads_set_title`,`ads_campaign_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (1,'第1个广告集',1,417454619141211000,1,'NC站','Facebook');
INSERT  INTO `adg_ads_set`(`ads_set_id`,`ads_set_title`,`ads_campaign_id`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (2,'第2个广告集',2,417454619141211001,2,'BG站','Google');

INSERT  INTO `adg_ads`(`ads_id`,`ads_set_id`,`ads_title`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (1,1,'第1个广告',417454619141211000,1,'NC站','Facebook');
INSERT  INTO `adg_ads`(`ads_id`,`ads_set_id`,`ads_title`,`shop_id`,`channel_id`,`shop_name`,`channel_name`) VALUES (2,2,'第2个广告',417454619141211001,2,'BG站','Google');

HAProxy + Keepalived

资料

  • 书：《分布式数据库架构及企业实践-基于 Mycat 中间件》