【Mycat】作为Mycat核心开发者，怎能不来一波Mycat系列文章？

淩亂°似流年 2021-09-02 05:27 310阅读 0赞

## 写在前面 ##

> Mycat是基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、优秀的架构和性能以及众多成熟的使用案例使得Mycat一开始就拥有一个很好的起点，站在巨人的肩膀上，我们能看到更远。业界优秀的开源项目和创新思路被广泛融入到Mycat的基因中，使得Mycat在很多方面都领先于目前其他一些同类的开源项目，甚至超越某些商业产品。——来自Mycat官网。
> 
> #  #
> 
> 作为Mycat的核心开发者，怎能不来一波Mycat系列文章呢？

## 背景介绍 ##

作为Mycat的核心开发者之一，今天，终于安排到Mycat系列文章了。在Mycat系列文章中，我们一起从一个利用Mycat实现分库分表的案例作为入门程序。后续会持续更新Mycat原理、架构和底层源码解析的文章。希望Mycat系列文章能够帮助小伙伴们彻底掌握Mycat。

那么，今天，我们就先来一波使用Mycat实现MySQL分库分表的文章。

注：案例中的MySQL服务器是安装在CentOS6.8服务器上，Mycat Server是安装在本机的Windows系统上，安装在什么环境上无所谓，这里，我用的是VMWare虚拟机，安装的CentOS系统，开启多个虚拟机，电脑实在是吃力，所以将Mycat Server装在了本机的Windows系统上。

## 方案规划 ##

<table> 
 <thead> 
  <tr> 
   <th>IP</th> 
   <th>端口</th> 
   <th>服务</th> 
   <th>用户名</th> 
   <th>密码</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>192.168.81.131</td> 
   <td>3306</td> 
   <td>MySQL数据库</td> 
   <td>root</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>192.168.81.132</td> 
   <td>3306</td> 
   <td>MySQL数据库</td> 
   <td>root</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>192.168.81.133</td> 
   <td>3306</td> 
   <td>MySQL数据库</td> 
   <td>root</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>192.168.81.130</td> 
   <td>8066/9066</td> 
   <td>Mycat Server</td> 
   <td>admin</td> 
   <td>admin123</td> 
  </tr> 
 </tbody> 
</table>

如上表所示，在局域网的4台主机中，131——133的主机各安装有一台MySQL实例，130主机，也就是本机安装了Mycat Server。

现在假设系统的数据库为messagedb，里面只有2张表，一张表为消息表：message，一张表示消息来源的字典表：source，本案例实现的是按自然月分片的规则，因此上述3个mysql实例各自需要创建4个数据库，即

<table> 
 <thead> 
  <tr> 
   <th>数据库实例</th> 
   <th>存储的数据库</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>192.168.81.131:3306</td> 
   <td>message202001、message202002、message202003、message202004</td> 
  </tr> 
  <tr> 
   <td>192.168.81.132:3306</td> 
   <td>message202005、message202006、message202007、message202008</td> 
  </tr> 
  <tr> 
   <td>192.168.81.133:3306</td> 
   <td>message202009、message202010、message202011、message202012</td> 
  </tr> 
 </tbody> 
</table>

说明：如果是刚接触Mycat的小伙伴对分片不太理解，简单地说，对于Mycat，一个分片表示某一个MySQL实例上的某一个数据库，即schema@host，于是当我们原先的一张大表需要分片的时候，mycat就会按照我们设定的规则，把这张大表中的数据分散到各个分片上，即所谓的分表分库，因此我们需要在每个对应的分片上创建相同名称的数据库，相同结构的表。

## 环境准备 ##

注意：这里，我就省略了MySQL的安装过程，小伙伴们可自行安装MySQL。我后续也会在MySQL相关的专题中给大家分享企业级MySQL安装、优化与部署过程。

### 创建数据库并建表导入数据 ###

根据数据库实例和存储的数据库对应关系表创建所有的数据库，并在每个数据库里执行如下脚本：

create table source (
            id int(11) not null auto_increment primary key comment 'pk',
            name varchar(10) default '' comment 'source name'
    );
    create table message (
            id int(11) not null auto_increment primary key comment 'pk',
            content varchar(255) default '' comment 'message content',
            create_time date default null,
            source_id int(11) not null,
            foreign key(source_id) references source(id)
    );
    insert into `source`(`id`,`name`) values(1,'weibo');
    insert into `source`(`id`,`name`) values(2,'weixin');
    insert into `source`(`id`,`name`) values(3,'qq');
    insert into `source`(`id`,`name`) values(4,'email');
    insert into `source`(`id`,`name`) values(5,'sms');

在message表中，总共有4个字段：

*  id：主键
 *  content：消息的内容
 *  create\_time：创建时间，这也是mycat进行分片时的参考字段
 *  source\_id：source表的外键

另外，我们在source表插入了5条记录，用于测试。到这里，后端数据库的环境就搭建完成了。

## 安装和配置Mycat ##

### 安装Mycat ###

安装Mycat的过程比较简单，在这个地址就可以下载安装包：[https://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE][https_github.com_MyCATApache_Mycat-download_tree_master_1.6-RELEASE]。下载完之后，就进行解压到系统相应目录，这里就不细说了。

### Mycat安装包结构 ###

安装完之后，简单地看一下mycat目录结构：

![在这里插入图片描述][2020111000313468.jpg_pic_center]

### 启动Mycat ###

WIndows下启动需要以管理员身份打开命令行窗口，cd 到Mycat的bin目录下，或者将Mycat的 安装目录加入系统的环境变量path目录里，首先输入命令mycat install进行mycat服务的安装操作，然后 输入命令mycat start 启动Mycat Server。

Linux下进入Mycat的bin目录直接输入./mycat start 启动Mycat Server。

Mycat提供了两个端口，其中，9066端口是管理端口，提供查看当前系统节点的情况，报告心跳状态等相关系统监控的功能，8066是数据端口，相当于数据库的访问端口。我们可以使用mysql命令访问这里两个端口

mysql -h[mycat_host] -u[mycat_user] -p[mycat_passwd] -P [8066|9066]

同时，我们也可以修改这两个端口。

那么mycat\_user和mycat\_passwd是如何配置呢，下面就需要介绍mycat中最主要的3个配置文件：server.xml，schema.xml和rule.xml。

### server.xml ###

该配置文件是用于配置mycat的系统信息，主要有两个标签：system和user。这里的user就是上述访问mycat服务的用户，不是后端数据库的用户。如果我们使用默认的配置，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="useSqlStat">0</property>  
                    <property name="useGlobleTableCheck">0</property> 
                    <property name="sequnceHandlerType">2</property>
                    <property name="processorBufferPoolType">0</property>
                    <property name="useOffHeapForMerge">1</property>
                    <property name="memoryPageSize">1m</property>
                    <property name="spillsFileBufferSize">1k</property>
                    <property name="useStreamOutput">0</property>
                    <property name="systemReserveMemorySize">384m</property>
            </system>
     
            <user name="admin">
                    <property name="password">admin123</property>
                    <property name="schemas">messagedb</property>
            </user>
    </mycat:server>

user标签下schemas属性表示该用户可以访问的数据库，可以定义多个数据库，用英文逗号隔开。schemas定义的数据库，一定要配置在后面的schema.xml文件对应的逻辑库，否则会提示无法访问。

### schema.xml ###

schema配置文件比较复杂，也是最关键的一个配置文件，定义了mycat中的逻辑库、逻辑表，和分片的相关信息。配置如下：

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
     
            <schema name="messagedb" checkSQLschema="false" sqlMaxLimit="100">
                    <table name="message" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" rule="sharding-by-month" />
                    <table name="source" primaryKey="id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" />
            </schema>
            <dataNode name="dn1" dataHost="mysql-01" database="message202001" />
            <dataNode name="dn2" dataHost="mysql-01" database="message202002" />
            <dataNode name="dn3" dataHost="mysql-01" database="message202003" />
            <dataNode name="dn4" dataHost="mysql-01" database="message202004" />
            <dataNode name="dn5" dataHost="mysql-02" database="message202005" />
            <dataNode name="dn6" dataHost="mysql-02" database="message202006" />
            <dataNode name="dn7" dataHost="mysql-02" database="message202007" />
            <dataNode name="dn8" dataHost="mysql-02" database="message202008" />
            <dataNode name="dn9" dataHost="mysql-03" database="message202009" />
            <dataNode name="dn10" dataHost="mysql-03" database="message202010" />
            <dataNode name="dn11" dataHost="mysql-03" database="message202011" />
            <dataNode name="dn12" dataHost="mysql-03" database="message202012" />
     
            <dataHost name="mysql-01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
                    <heartbeat>select user()</heartbeat>
                    <writeHost host="hostM1" url="192.168.81.131:3306" user="root" password="root">
                    </writeHost>
            </dataHost>
     
            <dataHost name="mysql-02" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
                    <heartbeat>select user()</heartbeat>
                    <writeHost host="hostM2" url="192.168.81.132:3306" user="root" password="root">
                    </writeHost>
            </dataHost>
     
            <dataHost name="mysql-03" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
                    <heartbeat>select user()</heartbeat>
                    <writeHost host="hostM3" url="192.168.81.133:3306" user="root" password="root">
                    </writeHost>
            </dataHost>
    </mycat:schema>

这里，有几点要说明一下：

*  schema标签定义逻辑库，其下table子标签定义逻辑表，datanode属性定义该逻辑表需要分布到哪几个分片上，rule属性表示使用何种分片规则，这里我们选择sharding-by-month，这个规则的名称是自定义的，只要和后面的rule.xml对应起来即可
 *  source表是一张全局表，这里需要使用type=”global”来定义，这样mycat就可以帮我们在指定的分片上克隆相同的数据，这对join查询是非常有好处的。
 *  datanode标签定义了分片，datahost是主机名，对应dataHost标签的name属性值，database定义该主机数据库实例上的具体数据库名。
 *  dataHost标签定义数据库实例，其下heartbeart标签表示心跳检测所使用的方法，writeHost标签定义写数据的实例，另外还有readHost标签可以定义读数据的实例，这里不考虑读写分离，仅使用写实例，因此需要把balance属性设置为0
 *  出于规范和安全考虑，最好不使用数据库的root用户，而是另外再创建一个用于mycat访问的用户。

### 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="sharding-by-month">
                    <rule>
                            <columns>create_time</columns>
                            <algorithm>partbymonth</algorithm>
                    </rule>
            </tableRule>
    		<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
                    <property name="dateFormat">yyyy-MM-dd</property>
                    <property name="sBeginDate">2020-01-01</property>
            </function>
    </mycat:rule>

*  tableRule标签定义分片规则的，其下columns标签表示对数据库表中的哪个字段应用规则，algorithm指定实现算法的名称，对应的是function标签中的name属性值
 *  function标签定义对应的实现类，以及参数，包括dateFormat（日期格式）和sBeginDate（起始日期）

说明：起始日期是用来计算数据所在的分片位置，例如2020年1月的message就会找到第1个分片，即dn1，2020年12月的message就会找到第12个分片，即dn12，但是如果出现了2018年1月的message，mycat就会去找第13个分片，但是配置文件中又没有对应的配置，那么就会抛出无法找到分片的错误。

综上：server.xml定义了访问mycat服务的用户，以及该用户授权的数据库（逻辑库），schema.xml定义了具体的逻辑库，逻辑表，以及分片和数据库实例的信息，rule.xml分片规则和实现类

## 测试 ##

到这里已经完成了mycat的配置文件，但先不急着往里面灌数据，我们先访问管理端口9066，看一下运行情况：

C:\Users\binghe>mysql -uadmin -padmin123 -P9066
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7
    Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)
    
    Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show @@datanode;
    +------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
    | NAME | DATHOST                | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
    +------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
    | dn1  | mysql-01/message202001 |     0 | mysql |      0 |    4 | 1000 |     412 |          0 |        0 |       0 |            -1 |
    | dn10 | mysql-03/message202010 |     0 | mysql |      0 |    0 | 1000 |      11 |          0 |        0 |       0 |            -1 |
    | dn11 | mysql-03/message202011 |     0 | mysql |      0 |    8 | 1000 |      16 |          0 |        0 |       0 |            -1 |
    | dn12 | mysql-03/message202012 |     0 | mysql |      0 |    1 | 1000 |     412 |          0 |        0 |       0 |            -1 |
    | dn2  | mysql-01/message202002 |     0 | mysql |      0 |    2 | 1000 |       9 |          0 |        0 |       0 |            -1 |
    | dn3  | mysql-01/message202003 |     0 | mysql |      0 |    4 | 1000 |      11 |          0 |        0 |       0 |            -1 |
    | dn4  | mysql-01/message202004 |     0 | mysql |      0 |    2 | 1000 |       9 |          0 |        0 |       0 |            -1 |
    | dn5  | mysql-02/message202005 |     0 | mysql |      0 |    4 | 1000 |     413 |          0 |        0 |       0 |            -1 |
    | dn6  | mysql-02/message202006 |     0 | mysql |      0 |    2 | 1000 |       9 |          0 |        0 |       0 |            -1 |
    | dn7  | mysql-02/message202007 |     0 | mysql |      0 |    4 | 1000 |      11 |          0 |        0 |       0 |            -1 |
    | dn8  | mysql-02/message202008 |     0 | mysql |      0 |    2 | 1000 |       9 |          0 |        0 |       0 |            -1 |
    | dn9  | mysql-03/message202009 |     0 | mysql |      0 |    0 | 1000 |      11 |          0 |        0 |       0 |            -1 |
    +------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
    12 rows in set (0.00 sec)
    
    mysql> show @@heartbeat;
    +--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
    | NAME   | TYPE  | HOST           | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
    +--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
    | hostM2 | mysql | 192.168.81.132 | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2020-03-04 14:22:59 | false |
    | hostM1 | mysql | 192.168.81.131 | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2020-03-04 14:22:59 | false |
    | hostM3 | mysql | 192.168.81.133 | 3306 |       1 |     0 | idle   |       0 | 2,1,1        | 2020-03-04 14:22:59 | false |
    +--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
    3 rows in set (0.00 sec)
    
    mysql>

如果看到各个节点都已经出现，并且心跳状态RS\_CODE=1，则表示后端数据库连接正常。

现在我们用JDBC的方式批量插入1000万数据：

package com.mycat.test;
     
    import java.sql.Connection;
    import java.sql.Date;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.Calendar;
    import java.util.Random;
     
    import org.junit.Test;
     
    /** * 测试Mycat * @author binghe */
    public class TestMycat { 
    	private static final String driver = "com.mysql.jdbc.Driver";
    	private static final String url = "jdbc:mysql://127.0.0.1:8066/messagedb?useServerPrepStmts=false&rewriteBatchedStatements=true";
    	private static final String username = "admin";
    	private static final String password = "admin123";
     
    	@Test
    	public void test() throws SQLException { 
    		Calendar calendar = Calendar.getInstance();
    		Random random = new Random();
    		calendar.set(2020, 0, 1, 0, 0, 0);
    	
    		Connection connection = null;
    		PreparedStatement ps = null;
    		try { 
    			Class.forName(driver);
    			connection = (Connection) DriverManager.getConnection(url, username, password);
    			connection.setAutoCommit(false);
    			String sql = "insert into message(`content`, `create_time`, `source_id`) values(?,?,?)";
    			ps = connection.prepareStatement(sql);
    			long start = System.currentTimeMillis();
    			for (int i = 0; i < 10000000; i++) { 
    				ps.setString(1, System.currentTimeMillis() + "");
    				long randomtime = calendar.getTimeInMillis() + (random.nextInt(365) + 1) * 86400 * 1000l;
    				Date date = new Date(randomtime);
    				int source_id = random.nextInt(5) + 1;
    				ps.setDate(2, date);
    				ps.setInt(3, source_id);
    				ps.addBatch();
    				if (i != 0 && i % 10000 == 0) { 
    					System.out.println("execute batch : " + i);
    					ps.executeBatch();
    				}
    			}
    			ps.executeBatch();
    			connection.commit();
    			System.out.println(System.currentTimeMillis() - start);
    		} catch (SQLException | ClassNotFoundException e) { 
    			e.printStackTrace();
    		} finally { 
    			if (ps != null)
    				ps.close();
    			if (connection != null)
    				connection.close();
    		}
    	}
     
    }

如果运行的时候报错： Multi-statement transaction required more than ‘max\_binlog\_cache\_size’ bytes of storage;，可以适当调大一下my.cnf下的max\_binlog\_cache\_size参数。

## 验证 ##

最后我们来检验一下分片的结果，其中message表中的数据根据create\_time的值按月进行了分片，而source表作为全局表，则其数据出现在了每个分片上，下面贴出部分结果

mysql -h192.168.81.131 -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202002.message;": 
    +------------------+------------------+
    | min(create_time) | max(create_time) |
    +------------------+------------------+
    | 2020-02-01       | 2020-02-28       |
    +------------------+------------------+
     
    mysql -h192.168.81.132 -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202005.message;": 
    +------------------+------------------+
    | min(create_time) | max(create_time) |
    +------------------+------------------+
    | 2020-05-01       | 2020-05-31       |
    +------------------+------------------+
     
    mysql -h192.168.81.133 -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202009.message;": 
    +------------------+------------------+
    | min(create_time) | max(create_time) |
    +------------------+------------------+
    | 2020-09-01       | 2020-09-30       |
    +------------------+------------------+

mysql -h192.168.81.131 -uroot -proot -P3306 -e "select * from message202001.source"
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | weibo  |
    |  2 | weixin |
    |  3 | qq     |
    |  4 | email  |
    |  5 | sms    |
    +----+--------+
     
    mysql -h192.168.81.132 -uroot -proot -P3306 -e "select * from message202007.source"
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | weibo  |
    |  2 | weixin |
    |  3 | qq     |
    |  4 | email  |
    |  5 | sms    |
    +----+--------+
     
    mysql -h192.168.81.133 -uroot -proot -P3306 -e "select * from message202011.source"
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | weibo  |
    |  2 | weixin |
    |  3 | qq     |
    |  4 | email  |
    |  5 | sms    |
    +----+--------+

## 总结 ##

本文就mycat分片的特性进行一次实战操作，完成了部署mycat-server以及后端mysql数据库，并以按自然月为分片规则进行了相关的配置，最后做了一个小的测试来验证分片功能的正确性。

**好了，今天Mycat系列文章的入门案例就到这儿吧，大家有啥好的意见或建议都可以在文末留言，我是冰河，我们下期见！！**

[https_github.com_MyCATApache_Mycat-download_tree_master_1.6-RELEASE]: https://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE
[2020111000313468.jpg_pic_center]: /images/20210901/1f457538c1a94694ae18f527205672cb.png