mycat部署安装及实现数据持久化的分库分表


一.mycat基本介绍总结

1、从阿里cobar升级而来

2、实现了MySQL协议,可以当做一个MySQL数据库来使用

3、通过JDBC支持其他数据库实现分库分表

4、解决了多表join、分布式事务、全局序列号、翻页排序、函数计算的问题

二.核心概念

主机/实例:理解为服务器

物理数据库:理解为服务器上装的mysql的其中一个库

物理表:物理库上的表

分片(切分):把一张表的数据分为多张表,分为横向分片和纵向分片,横向分片是表结构都是一样的,纵向分片是表结构不同的

分片节点:不通的数据库dataNode

分片建:用于分片的字段,解决基于哪个字段分

分片算法:基于分片键做的算法操作,解决基于什么逻辑分

逻辑表:区分物理表,实际是在操作物理表和逻辑表,并没有自己的实体

逻辑数据库:区别于物理数据库,实际是操作物理库

二.安装mycat及解压

http://dl.mycat.org.cn/1.6-RELEASE/

三.基本配置文件的认识

1、server.xml 系统配置信息

<?xml version="1.0" encoding="UTF-8"?>

"server.dtd">
"http://io.mycat/">
    
    "useSqlStat">0  
    "useGlobleTableCheck">0  

        "sequnceHandlerType">2
       
         
    
    
        
        "processorBufferPoolType">0
        
        
        
        
        
        
        
        
        "handleDistributedTransactions">0
        
            
        "useOffHeapForMerge">1

        
        "memoryPageSize">1m

        
        "spillsFileBufferSize">1k

        "useStreamOutput">0

        
        "systemReserveMemorySize">384m


        
        "useZKSwitch">true


    
    
    
    
    
    "root">
        "password">123456
        "schemas">imall,gupao
        
        
        
    

2、schema.xml 逻辑库、逻辑表、表的分片规则、数据源

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

    "imall" checkSQLschema="false" sqlMaxLimit="100">
        "customer" primaryKey="id" dataNode="122-imall,123-imall,124-imall" rule="auto-sharding-long" />
        
"order_info" dataNode="122-imall,123-imall,124-imall" rule="mod-long-order" > "order_detail" primaryKey="id" joinKey="order_id" parentKey="order_id"/>
"mycat_sequence" dataNode="122-imall" autoIncrement="true" primaryKey="id">
"gupao" checkSQLschema="false" sqlMaxLimit="100"> "student" primaryKey="sid" dataNode="122-gupao,123-gupao,124-gupao" rule="mod-long" />
"noshard" primaryKey="id" autoIncrement="true" dataNode="122-gupao" />
"dict" primaryKey="id" type="global" dataNode="122-gupao,123-gupao,124-gupao" />
"fee" primaryKey="id" subTables="fee2025$1-3" dataNode="122-gupao" rule="sharding-by-month" /> "122-imall" dataHost="host122" database="122-imall" /> "123-imall" dataHost="host123" database="123-imall" /> "124-imall" dataHost="host124" database="124-imall" /> "122-gupao" dataHost="host122" database="122-gupao" /> "123-gupao" dataHost="host123" database="123-gupao" /> "124-gupao" dataHost="host124" database="124-gupao" /> "host122" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> select user() "hostM1" url="127.0.0.1:3308" user="root" password="root"> "host123" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> select user() "hostM1" url="127.0.0.1:3308" user="root" password="root"> "host124" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> select user() "hostM1" url="127.0.0.1:3308" user="root" password="root">

3、rule.xml 分片规则和分片算法

<?xml version="1.0" encoding="UTF-8"?>

"rule.dtd">
"http://io.mycat/">
    "rule-fee">
        
            id
            func-fee
        
    
 
    "rule1">
        
            id
            func1
        
    

    "rule2">
        
            user_id
            func1
        
    

    "sharding-by-intfile">
        
            sharding_id
            hash-int
        
    
    "auto-sharding-long">
        
            id
            rang-long
        
    
    "mod-long">
        
            sid
            mod-long
        
    

        "mod-long-order">
                
                        order_id
                        mod-long
                
        
    "sharding-by-murmur">
        
            id
            murmur
        
    
    "crc32slot">
        
            id
            crc32slot
        
    
    "sharding-by-month">
        
            create_time
            partbymonth
        
    
    "latest-month-calldate">
        
            calldate
            latestMonth
        
    
    
    "auto-sharding-rang-mod">
        
            id
            rang-mod
        
    
    
    "jch">
        
            id
            jump-consistent-hash
        
    

    "murmur"
        class="io.mycat.route.function.PartitionByMurmurHash">
        "seed">0
        "count">2
        "virtualBucketTimes">160
        
        
    

        "func-fee" class="io.mycat.route.function.PartitionByMod">
                "count">3
        

    "crc32slot"
              class="io.mycat.route.function.PartitionByCRC32PreSlot">
        "count">2
    
    "hash-int"
        class="io.mycat.route.function.PartitionByFileMap">
        "mapFile">partition-hash-int.txt
    
    "rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        "mapFile">autopartition-long.txt
    
    "mod-long" class="io.mycat.route.function.PartitionByMod">
        
        "count">3
    

    "func1" class="io.mycat.route.function.PartitionByLong">
        "partitionCount">128
        "partitionLength">8
    
    "latestMonth"
        class="io.mycat.route.function.LatestMonthPartion">
        "splitOneDay">24
    
    "partbymonth"
        class="io.mycat.route.function.PartitionByMonth">
        "dateFormat">yyyy-MM-dd
        "sBeginDate">2025-01-01
    
    
    "rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
            "mapFile">partition-range-mod.txt
    
    
    "jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
        "totalBuckets">3
    

 五.运行环境配置

wrapper.conf

#********************************************************************
# Wrapper Properties
#********************************************************************
# Java Application
wrapper.java.command=C:\Program Files\Java\jdk1.8.0_181\bin\java.exe
wrapper.working.dir=..

# Java Main class.  This class must implement the WrapperListener interface
#  or guarantee that the WrapperManager class is initialized.  Helper
#  classes are provided to do this for you.  See the Integration section
#  of the documentation for details.
wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperSimpleApp
set.default.REPO_DIR=lib
set.APP_BASE=.

# Java Classpath (include wrapper.jar)  Add class path elements as
#  needed starting from 1
wrapper.java.classpath.1=lib/wrapper.jar
wrapper.java.classpath.2=conf
wrapper.java.classpath.3=%REPO_DIR%/*

# Java Library Path (location of Wrapper.DLL or libwrapper.so)
wrapper.java.library.path.1=lib

# Java Additional Parameters
#wrapper.java.additional.1=
wrapper.java.additional.1=-DMYCAT_HOME=.
wrapper.java.additional.2=-server
wrapper.java.additional.3=-XX:MaxPermSize=64M
wrapper.java.additional.4=-XX:+AggressiveOpts
wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G
wrapper.java.additional.6=-Dcom.sun.management.jmxremote
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984
wrapper.java.additional.8=-Dcom.sun.management.jmxremote.authenticate=false
wrapper.java.additional.9=-Dcom.sun.management.jmxremote.ssl=false
wrapper.java.additional.10=-Xmx4G
wrapper.java.additional.11=-Xms1G

# Initial Java Heap Size (in MB)
#wrapper.java.initmemory=3

# Maximum Java Heap Size (in MB)
#wrapper.java.maxmemory=64

# Application parameters.  Add parameters as needed starting from 1
wrapper.app.parameter.1=io.mycat.MycatStartup
wrapper.app.parameter.2=start

#********************************************************************
# Wrapper Logging Properties
#********************************************************************
# Format of output for the console.  (See docs for formats)
wrapper.console.format=PM

# Log Level for console output.  (See docs for log levels)
wrapper.console.loglevel=INFO

# Log file to use for wrapper output logging.
wrapper.logfile=logs/wrapper.log

# Format of output for the log file.  (See docs for formats)
wrapper.logfile.format=LPTM

# Log Level for log file output.  (See docs for log levels)
wrapper.logfile.loglevel=INFO

# Maximum size that the log file will be allowed to grow to before
#  the log is rolled. Size is specified in bytes.  The default value
#  of 0, disables log rolling.  May abbreviate with the 'k' (kb) or
#  'm' (mb) suffix.  For example: 10m = 10 megabytes.
wrapper.logfile.maxsize=0

# Maximum number of rolled log files which will be allowed before old
#  files are deleted.  The default value of 0 implies no limit.
wrapper.logfile.maxfiles=0

# Log Level for sys/event log output.  (See docs for log levels)
wrapper.syslog.loglevel=NONE

#********************************************************************
# Wrapper Windows Properties
#********************************************************************
# Title to use when running as a console
wrapper.console.title=Mycat-server

#********************************************************************
# Wrapper Windows NT/2000/XP Service Properties
#********************************************************************
# WARNING - Do not modify any of these properties when an application
#  using this configuration file has been installed as a service.
#  Please uninstall the service before modifying this section.  The
#  service can then be reinstalled.

# Name of the service
wrapper.ntservice.name=mycat

# Display name of the service
wrapper.ntservice.displayname=Mycat-server

# Description of the service
wrapper.ntservice.description=The project of Mycat-server

# Service dependencies.  Add dependencies as needed starting from 1
wrapper.ntservice.dependency.1=

# Mode in which the service is installed.  AUTO_START or DEMAND_START
wrapper.ntservice.starttype=AUTO_START

# Allow the service to interact with the desktop.
wrapper.ntservice.interactive=false

wrapper.ping.timeout=120
configuration.directory.in.classpath.first=conf

六.建库建表sql

最终的结果

 具体sql

--在所有数据库节点上创建数据库imall,创建3张表
-- 范围分片表
CREATE TABLE `customer` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ER分片表
CREATE TABLE `order_info` (
  `order_id` int(11) NOT NULL COMMENT '订单ID',
  `uid` int(11) DEFAULT NULL COMMENT '用户ID',
  `nums` int(11) DEFAULT NULL COMMENT '商品数量',
  `state` int(2) DEFAULT NULL COMMENT '订单状态',
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ER分片表
CREATE TABLE `order_detail` (
  `order_id` int(11) NOT NULL COMMENT '订单号',
  `id` int(11) NOT NULL COMMENT '订单详情',
  `goods_id` int(11) DEFAULT NULL COMMENT '货品ID',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `is_pay` int(2) DEFAULT NULL COMMENT '支付状态',
  `is_ship` int(2) DEFAULT NULL COMMENT '是否发货',
  `status` int(2) DEFAULT NULL COMMENT '订单详情状态',
  PRIMARY KEY (`order_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--测试语句(在mycat连接中imall数据库中执行)
truncate table customer;
truncate table order_info;
truncate table order_detail;

--测试范围分片(在mycat连接中imall数据库中执行)
INSERT INTO `customer` (`id`, `name`) VALUES (6666, '赵先生');
INSERT INTO `customer` (`id`, `name`) VALUES (7777, '钱先生');
INSERT INTO `customer` (`id`, `name`) VALUES (16666, '孙先生');
INSERT INTO `customer` (`id`, `name`) VALUES (17777, '李先生');
INSERT INTO `customer` (`id`, `name`) VALUES (26666, '周先生');
INSERT INTO `customer` (`id`, `name`) VALUES (27777, '吴先生');

--测试ER分片(在mycat连接中imall数据库中执行)
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (1, 1000001, 1, 2, '2025-9-23 14:35:37', '2025-9-23 14:35:37');
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (2, 1000002, 1, 2, '2025-9-24 14:35:37', '2025-9-24 14:35:37');
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (3, 1000003, 3, 1, '2025-9-25 11:35:49', '2025-9-25 11:35:49');

--测试ER分片(在mycat连接中imall数据库中执行)
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (3, 20180001, 85114752, 19.99, 1, 1, 1);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180002, 25411251, 1280.00, 1, 1, 0);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180003, 62145412, 288.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180004, 21456985, 399.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180005, 21457452, 1680.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180006, 65214789, 9999.00, 1, 1, 3);

--------------------------------------------------------------------------
--------------------------------------------------------------------------

--在所有数据库节点上创建数据库gupao,创建两张表
--全局表
CREATE TABLE `dict` (
  `id` int(11) DEFAULT NULL,
  `param_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `param_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 取模分片表
CREATE TABLE `student` (
  `sid` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `qq` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


--测试语句(在mycat连接中gupao数据库中执行)
truncate table dict;
truncate table student;

--测试全局表
INSERT INTO `dict` (`id`, `param_code`, `param_name`) VALUES (1, '0731', '长沙市');

--测试取模分片(在mycat连接中gupao数据库中执行)
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (1, '李大彪', '166669999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (4, '菜狗子', '655556666');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (2, '等候那場雪', '466669999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (5, '猫老公', '265286999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (3, 'tj-大白', '368828888');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (6, '大郎', '516895555');

--------------------------------------------------------------------------
--------------------------------------------------------------------------

-- 在第一个数据库节点(122)gupao数据库创建非分片表
CREATE TABLE `noshard` (
  `id` bigint(30) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

truncate table noshard;

--测试非分片表(在mycat连接中gupao数据库中执行)
INSERT INTO `noshard` (`id`, `name`) VALUES (1, '这是一条没有分片的数据');

-- 库内分表
-- 在第一个数据库节点(122)gupao数据库创建单库分片表
CREATE TABLE `fee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `fee20251`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);
CREATE TABLE `fee20252`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);
CREATE TABLE `fee20253`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);

truncate table fee;

-- 测试语句(在mycat连接中gupao数据库中执行)
INSERT INTO `fee` (`id`, `create_time`) VALUES (1, '2025-1-1 14:46:19');
INSERT INTO `fee` (`id`, `create_time`) VALUES (2, '2025-2-1 14:46:19');
INSERT INTO `fee` (`id`, `create_time`) VALUES (3, '2025-3-1 14:46:19');

七.具体分片规则

autopartition-long.txt

# range start-end ,data node index
# K=1000,M=10000.
# 未使用
0-10000=0
10001-20000=1
20001-30000=2

auto-sharding-long.txt

0-10000=0
10001-20000=1
20001-30000=2

auto-sharding-rang-mod.txt

800M1-1000M=6
600M1-800M=4
200M1-400M=1
0-200M=5
400M1-600M=4

partition-hash-int.txt

10000=0
10010=1

partition-range-mod.txt

# range start-end ,data node group size
0-200M=5
200M1-400M=1
400M1-600M=4
600M1-800M=4
800M1-1000M=6

sharding-by-enum.txt

800M1-1000M=6
600M1-800M=4
200M1-400M=1
0-200M=5
400M1-600M=4

八.navicat连接mycat

默认端口:8066

 九.操作mycat开发出来的数据库

truncate table customer;
truncate table order_info;
truncate table order_detail;

INSERT INTO `customer` (`id`, `name`) VALUES (6666, '赵先生');
INSERT INTO `customer` (`id`, `name`) VALUES (7777, '钱先生');
INSERT INTO `customer` (`id`, `name`) VALUES (16666, '孙先生');
INSERT INTO `customer` (`id`, `name`) VALUES (17777, '李先生');
INSERT INTO `customer` (`id`, `name`) VALUES (26666, '周先生');
INSERT INTO `customer` (`id`, `name`) VALUES (27777, '吴先生');


select * from customer ORDER BY id ;

-- 测试ER分片(在mycat连接中imall数据库中执行)
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (1, 1000001, 1, 2, '2025-9-23 14:35:37', '2025-9-23 14:35:37');
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (2, 1000002, 1, 2, '2025-9-24 14:35:37', '2025-9-24 14:35:37');
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (3, 1000003, 3, 1, '2025-9-25 11:35:49', '2025-9-25 11:35:49');

select * from order_info;

-- 测试ER分片(在mycat连接中imall数据库中执行)
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (3, 20180001, 85114752, 19.99, 1, 1, 1);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180002, 25411251, 1280.00, 1, 1, 0);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180003, 62145412, 288.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180004, 21456985, 399.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180005, 21457452, 1680.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180006, 65214789, 9999.00, 1, 1, 3);

explain select * from order_detail;