1 Mycat简介

1.1 Mycat

Mycat是数据库中间件。
官网：http://mycat.org.cn/

1、数据库中间件

中间件：一类连接软件组件和应用的计算机软件，以便于软件各部件之间的沟通。
数据库中间件：连接Java应用程序和数据库的中间件。

2、数据库中间件对比

Cobar：阿里团队开发，已多年无维护更新。
Mycat：基于Cobar二次开发，开源社区维护。
OneProxy：不开源的商业中间件。
kingshard：Go语言开发，在不断完善。
Vitess：YouTube生产在使用，不支持MySQL原生协议。
Atlas：360团队基于MySQLProxy改写，高并发下不稳定。
MaxScale：MariaDB研发的中间件。
MySQLRoute：MySQL官方发布的中间件。

1.2 功能

1、读写分离

2、数据分片

垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）。

点击查看源网页

3、多数据源整合

MySQL、Oracle、Redis等。

1.3 原理

Mycat的原理中最重要的一个动词是“拦截”。
它拦截了用户发送过来的SQL语句：
1. 对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等。
2. 将此SQL发往后端的真实数据库，并将返回的结果做适当的处理。
3. 返回给用户。
这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用Mycat还是MySQL。

2 安装Mycat（Linux）

2.1 下载

百度云：https://pan.baidu.com/s/1dLfhYuLi4b5gLyiLBfPgJg
提取码：f1hp

2.2 上传服务器

将Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz上传至服务器/opt目录。

2.3 安装

# 1、切换到/opt/目录
cd /opt
# 2、解压文件
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
# 3、将解压后文件拷贝到/usr/local/目录
cp -r mycat /usr/local/
# 4、进入/usr/local/mycat/目录
cd /usr/local/mycat/

图片说明

2.4 核心配置文件

1、`schema.xml`

schema.xml：定义逻辑库、表、分片节点等内容。

这里使用本机两个mysql服务（端口为3306和330）进行模拟（database=testdb指定为实际数据库）：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <dataNode name="dn1" dataHost="host1" database="testdb" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root"
                   password="root">
            <!-- can have multi read hosts -->
            <readHost host="hostS1" url="127.0.0.1:3307" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

2、`rule.xml`

rule.xml：定义分片规则。

3、`server.xml`

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>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

        <property name="sequnceHandlerType">2</property>
      <!--  <property name="u***pression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
    <!-- <property name="processorBufferChunk">40960</property> -->
    <!-- 
    <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协调切换  -->
        <property name="useZKSwitch">true</property>
    </system>
    <!-- 修改此处 -->
    <user name="mycat">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
    </user>
    <user name="user">
        <property name="password">user</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>

图片说明

2.5 启动

图片说明

1、控制台启动

./mycat console

2、后台启动

./mycat start

2.6 登录

前提：已经启动Mycat服务。

1、登录后台管理窗口

此方式用于管理维护Mycat（运维人员）：
```
mysql -umycat -p123456 -P 9066 -h 127.0.0.1
```
用户名、密码在server.xml中配置。

端口：9066为维护端口。

主机：Mycat启动所在主机，当前模拟为本机。

2、登录数据窗口

此方式用于使用Mycat获取数据（开发人员）：
```
mysql -umycat -p123456 -P 8066 -h 127.0.0.1
```
用户名、密码在server.xml中配置。

端口：9066为维护端口。

主机：Mycat启动所在主机。

3 搭建读写分离

搭建MySQL主从复制。
搭建MyCat读写分离。

3.1 一主一从

1、MySQL的`my.ini`配置文件

MySQL主机：

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

# 配置MySQL主机
server-id=1    # 主服务器唯一id
log-bin=mysql-bin    # 启动二进制日志
# 设置不要复制的数据库（可设置多个）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 设置需要复制的数据库
binlog-do-db=testdb
# 设置login格式
binlog_format=STATEMENT

MySQL从机：

[mysql]
default-character-set=utf8

[client]
port=3307

[mysqld]
port=3307

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

# 配置从机
server-id=2    # 从机id
relay-log=mysql-relay    # 启用中继日志

注意：由于此处实验直接复制mysql安装包，需要删除mysql安装目录/data/auto.cnf文件，去除旧的UUID信息。
配置完成，重启服务器。

2、MySQL主机授权

# 授权
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

其他命令：

# 停止主机服务，重置主机
reset master;
# 查看主机状态
SHOW MASTER STATUS;

3、MySQL从机连接

CHANGE MASTER TO MASTER_HOST='127.0.0.1',    # Master服务器IP
                 MASTER_PORT=3306,    # Master服务器端口
                 MASTER_USER='slave',    # Master服务器授权同步的用户名，或者为root用户名
                 MASTER_PASSWORD='123123',    # Master授权同步的密码，或者为root密码
                 MASTER_LOG_FILE='mysql-bin.000001',    # Master服务器产生的日志
                 MASTER_LOG_POS=1361;    # 指定请求同步Master的bin-log的哪一行之后的内容

启动从机服务：
```
start slave;
```

其他命令：

# 停止从机服务
stop slave;
# 查看从机状态：如果Slave_IO_Running和Slave_SQL_Running都为Yes，说明配置成功
SHOW SLAVE STATUS;

4、MyCat配置逻辑库：`schema.xml`

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <dataNode name="dn1" dataHost="host1" database="testdb" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root"
                   password="root">
            <!-- can have multi read hosts -->
            <readHost host="hostS1" url="127.0.0.1:3307" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

<dataHost balance="参数">
- 0：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
- 1：全部的readHost与stand by writeHost参与的select语句的负载均衡。即，当双主双从模式（M1->S1，M2->S2，并且M1与M2互为主备），正常情况下，M2、S1、S2都参与select语句的负载均衡。
- 2：所有读操作都随机的在writeHost、readHost上分发。
- 3：所有读请求随机的分发到readHost上执行，writeHost不负担读压力。
一主一从设置为3，双主双从设置为1。

5、启动Mycat服务、登录后台管理

启动Mycat服务：
```
mycat console
```

登录后台管理：

mysql -umycat -p123456 -P 8066 -h 127.0.0.1

详细参看【安装Mycat（Linux）】。

3.2 双主双从

一个主机M1用于处理所有写请求，它的从机S1和另一台主机M2还有它的从机S2负责所有读请求。当M1主机宕机后，M2主机负责写请求，即M1、M2互为备机。

1、MySQL的`my.ini`配置

M1：

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

# 配置M1
# 主服务器唯一id
server-id=1
# 启动二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库（可设置多个）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 设置需要复制的数据库
binlog-do-db=testdb
# 设置login格式
binlog_format=STATEMENT
# 以下设置M1
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 表示自增长字段每次递增的量，指自增字段的起始值
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少
auto-increment-offset=1

M2：

[mysql]
default-character-set=utf8

[client]
port=3308

[mysqld]
port=3308

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

# 配置M2
server-id=2
relay-log=mysql-relay    # 启用中继日志
# 启动二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库（可设置多个）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 设置需要复制的数据库
binlog-do-db=testdb
# 设置login格式
binlog_format=STATEMENT
# 以下设置M1
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 表示自增长字段每次递增的量，指自增字段的起始值
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少
auto-increment-offset=2

S1：

[mysql]
default-character-set=utf8

[client]
port=3307

[mysqld]
port=3307

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

# 配置S1
server-id=3    # 从机id
relay-log=mysql-relay    # 启用中继日志

S2：

[mysql]
default-character-set=utf8

[client]
port=3309

[mysqld]
port=3309

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

# 配置S2
server-id=4    # 从机id
relay-log=mysql-relay    # 启用中继日志

配置完重启MySQL服务。

2、M1、M2授权

分别在M1、M2的mysql连接中进行授权：

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

查看Master状态：
```
show master status;
```

3、S1、S2连接

根据Master中的状态，填写连接信息。

S1：

CHANGE MASTER TO MASTER_HOST='127.0.0.1',    # Master服务器IP
                 MASTER_PORT=3306,    # Master服务器端口
                 MASTER_USER='slave',    # Master服务器授权同步的用户名，或者为root用户名
                 MASTER_PASSWORD='123123',    # Master授权同步的密码，或者为root密码
                 MASTER_LOG_FILE='mysql-bin.000001',    # Master服务器产生的日志
                 MASTER_LOG_POS=1361;    # 指定请求同步Master的bin-log的哪一行之后的内容

S2：

CHANGE MASTER TO MASTER_HOST='127.0.0.1',    # Master服务器IP
                 MASTER_PORT=3308,    # Master服务器端口
                 MASTER_USER='slave',    # Master服务器授权同步的用户名，或者为root用户名
                 MASTER_PASSWORD='123123',    # Master授权同步的密码，或者为root密码
                 MASTER_LOG_FILE='mysql-bin.000001',    # Master服务器产生的日志
                 MASTER_LOG_POS=120;    # 指定请求同步Master的bin-log的哪一行之后的内容

启动从机服务：
```
start slave;
```
查看连接信息，两个都为Yes表示配置成功：
```
show slave status;
```

4、M1、M2互相连接

M1：

CHANGE MASTER TO MASTER_HOST='127.0.0.1',    # Master服务器IP
                 MASTER_PORT=3308,    # Master服务器端口
                 MASTER_USER='slave',    # Master服务器授权同步的用户名，或者为root用户名
                 MASTER_PASSWORD='123123',    # Master授权同步的密码，或者为root密码
                 MASTER_LOG_FILE='mysql-bin.000001',    # Master服务器产生的日志
                 MASTER_LOG_POS=120;    # 指定请求同步Master的bin-log的哪一行之后的内容

M2：

CHANGE MASTER TO MASTER_HOST='127.0.0.1',    # Master服务器IP
                 MASTER_PORT=3306,    # Master服务器端口
                 MASTER_USER='slave',    # Master服务器授权同步的用户名，或者为root用户名
                 MASTER_PASSWORD='123123',    # Master授权同步的密码，或者为root密码
                 MASTER_LOG_FILE='mysql-bin.000001',    # Master服务器产生的日志
                 MASTER_LOG_POS=120;    # 指定请求同步Master的bin-log的哪一行之后的内容

分别开启M1、M2的从机服务：
```
start slave;
```
分别查看连接状态，两个都为Yes表示配置成功：
```
show slave status;
```

5、配置Mycat的`schema.xml`

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <dataNode name="dn1" dataHost="host1" database="testdb" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root"
                   password="root">
            <readHost host="hostS1" url="127.0.0.1:3307" user="root" password="root" />
        </writeHost>
        <writeHost host="hostM2" url="127.0.0.1:3308" user="root"
                   password="root">
            <readHost host="hostS2" url="127.0.0.1:3309" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

balance="1"：全部的readHost与stand by writeHost参与select语句的负载均衡。
writeType="参数"：
- 0：所有写操作发送到配置的第一个writeHost，第一个挂了切换到还生存的第二个writeHost。
- 1：所有写操作都随机的发送到配置的writeHost，1.5以后废弃不推荐。
switchType="参数"：
- 1：默认值。重启后writeHost自动切换。
- -1：不自动切换。
- 2：基于MySQL主从同步的状态决定是否切换。

6、启动Mycat服务、登录后台管理

启动Mycat服务：
```
mycat console
```

登录后台管理：

mysql -umycat -p123456 -P 8066 -h 127.0.0.1

详细参看【安装Mycat（Linux）】。

7、测试

往测试表中添加端口信息：

insert into mytbl values (null, @@port);

从测试表中获取信息：
可以看到，三次分别从端口3309、3307、3308处获取。

4 垂直拆分——分库

一个数据库由很多表构成，每个表对应着不同的业务。
垂直拆分是指按照业务将表进行分类，分到不同的数据库上面，这样也就将数据（读写压力）分担到不同的库上面。
分库：将一个数据库中的不同表，拆分到不同的数据库中。

4.1 如何划分表

分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。

4.2 配置分库

1、新增mysql服务

复制mysql安装目录，删除/data/auto.cnf文件，修改my.ini：

[mysql]
default-character-set=utf8

[client]
port=3310

[mysqld]
port=3310

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

进入/bin/目录，安装服务：
```
mysqld -install mysql3310
```
启动mysql3310服务，发现报错：
卸载服务：
```
sc delete mysql3310
```
配置环境变量path。
重新安装服务，并重启，成功。

2、配置Mycat的`schema.xml`

在之前双主双从的基础之上，增加一个mysql服务，端口为3310，存放customer表。
根据schema.xml修改数据库，创建orders数据库。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="orders" />
    <dataNode name="dn2" dataHost="host2" database="orders" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root"
                   password="root">
            <readHost host="hostS1" url="127.0.0.1:3307" user="root" password="root" />
        </writeHost>
        <writeHost host="hostM2" url="127.0.0.1:3308" user="root"
                   password="root">
            <readHost host="hostS2" url="127.0.0.1:3309" user="root" password="root" />
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3310" user="root"
                   password="root">
        </writeHost>
    </dataHost>
</mycat:schema>

3、使用Mycat建库、建表

配置完成后，根据配置创建orders数据库。
启动Mycat服务：
```
mycat console
```

登录Mycat管理窗口：

mysql -umycat -p123456 -P 8066 -h 127.0.0.1

选择数据库：
```
use TESTDB;
```

创建表：

create table customer (
    id int auto_increment,
    name varchar(200),
    primary key(id)
);
create table orders (
    id int auto_increment,
    order_type int,
    customer_id int,
    amount decimal(10,2),
    primary key(id)
);
create table orders_detail (
    id int auto_increment,
    detail varchar(2000),
    order_id int,
    primary key(id)
);
create table dict_order_type (
    id int auto_increment,
    order_type varchar(200),
    primary key(id)
);

执行成功后，发现只在3310端口上创建出customer表，而在3306端口上会创建其他三张表。由于之前配置主从复制时，配置了binlog-do-db=testdb，因此orders数据库不会进行主从复制。

所以，此时的schema.xml不用配置主从信息，修改如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="orders" />
    <dataNode name="dn2" dataHost="host2" database="orders" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root"
                   password="root">
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3310" user="root"
                   password="root">
        </writeHost>
    </dataHost>
</mycat:schema>

5 水平拆分——分表

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的种类规则来分散到多个数据库之中，每个表中包含一部分数据。
简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

5.1 配置分表

1、选择要拆分的表

MySQL单表存储数据条数是有瓶颈的，单表达到1000万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。
示例：orders、orders_detail都已经达到了600万条数据，需要进行分表优化。

2、分表字段

以orders表为例，可以根据不同字段进行分表：
id（主键、或创建时间）：查询订单注重实效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。
customer_id（客户id）：根据客户id去分，两个节点访问访问平均，一个客户的所有订单都在同一个节点。

3、修改配置文件`schema.xml`

为orders表设置数据节点为dn1、dn2，并指定分片规则为mod_rule（自定义的名字）：

<table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>

完整schema.xml如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
    <table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="orders" />
    <dataNode name="dn2" dataHost="host2" database="orders" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root"
                   password="root">
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3310" user="root"
                   password="root">
        </writeHost>
    </dataHost>
</mycat:schema>

4、修改配置文件`rule.xml`

新增分片规则mod_rule，并指定规则适用字段为customer_id。选择分片算法mod-long（对字段求模运算，内置的算法），对customer_id进行求模，根据结果分片：

<tableRule name="mod_rule">
    <rule>
        <columns>customer_id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>

往下翻，修改mod-long参数：

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">2</property>
</function>

在3310端口数据库上手动创建orders表。

5、测试

重启Mycat服务、重新登录Mycat数据管理窗口。
通过Mycat，往orders表中插入数据（注意：由于进行分片，插入必须指定字段）：

insert into orders (id, order_type, customer_id, amount)
values 
(1, 101, 100, 100100), 
(2, 101, 100, 100100),
(3, 101, 101, 100100),
(4, 101, 101, 100100),
(5, 101, 102, 100100), 
(6, 101, 103, 100100);

3306：
3310：
可见Mycat对插入数据进行了分片，即完成了分表配置。
使用Mycat查询orders：

图片说明

查询结果的顺序是在3306和3310数据库结果上拼接成的。
由此可见，Mycat会先根据分片查询不同数据库，再将查询结果合并，最后发送到应用。
要使结果按id排序（即与我们在单个数据库上查询一致），可使用order by：

图片说明

5.2 Mycat的分片——`join`

order订单表已经进行分表操作了，和它关联的orders_detail订单详情表如何进行join查询？
我们也需要对orders_detail表进行分片操作。

1、ER表

Mycat借鉴了NewSQL领域的新秀Foundation DB的设计思路。
Foundation DB创新性提出了Table Group的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了join的效率和性能问题。
根据这一思路，提出了E-R关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。
修改schema.xml配置文件的表配置：

<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
  <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>

完整schema.xml如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
    <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
      <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
    </table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="orders" />
    <dataNode name="dn2" dataHost="host2" database="orders" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root"
                   password="root">
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3310" user="root"
                   password="root">
        </writeHost>
    </dataHost>
</mycat:schema>

在3310端口数据库上手动创建orders_detail表。
重启Mycat服务器、数据控制窗口。
通过Mycat向orders_detail表插入数据（注意：子表不允许multi insert）：

insert into orders_detail (id, detail, order_id) values (1, 'detail', 1);
insert into orders_detail (id, detail, order_id) values (2, 'detail', 2);
insert into orders_detail (id, detail, order_id) values (3, 'detail', 3);
insert into orders_detail (id, detail, order_id) values (4, 'detail', 4);
insert into orders_detail (id, detail, order_id) values (5, 'detail', 5);
insert into orders_detail (id, detail, order_id) values (6, 'detail', 6);

3306：

图片说明

3310：

图片说明

可见，order_id根据order表的id进行了分片，配置成功。
通过Mycat，在3306、3310数据库中运行join语句（成功关联6条数据）：

select o.*, od.detail from orders o inner join orders_detail od on o.id=od.order_id;

图片说明

2、全局表

在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题。
考虑到字典表具有以下几个特性：
1. 变动不频繁。
2. 数据量总体变化不大。
3. 数据规模不大，很少有超过数十万条记录。
因此，Mycat定义了一种特殊的表，称为“全局表”。全局表具有以下特点：
- 全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性。
- 全局表的查询，只从一个节点获取。
- 全局表可以跟任何一个表进行join操作。
将字典表或者符合字典表特性的一些表定义为全局表，从另外一个方面，很好的解决了数据join的难题。
通过全局表 + 基于E-R关系的分片策略，Mycat可以满足80%以上的企业应用开发。
修改schema.xml配置文件，新增全局表配置：

<table name="dict_order_type" dataNode="dn1,dn2" rype="global"></table>

全部schema.xml配置信息如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
    <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
      <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
    </table>
    <table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="orders" />
    <dataNode name="dn2" dataHost="host2" database="orders" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root"
                   password="root">
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="127.0.0.1:3310" user="root"
                   password="root">
        </writeHost>
    </dataHost>
</mycat:schema>

在3310端口数据库上手动创建dict_order_type表。
重启Mycat服务器、数据控制窗口。
通过Mycat，向dict_order_type表插入数据：

insert into dict_order_type (id, order_type)
values
(101, 'type1'),
(102, 'type2');

3306、3310中都有相同的数据，说明全局表配置成功：

图片说明

5.3 常用分片规则

1、取模

对分片字段求模，是水平分表最常用的规则。
详细参看【5.1 配置分表】。

2、分片枚举

通过在配置文件中配置可能的枚举id，自己配置分片。
适用于特定的场景，如有些业务需要按照省份或区县来保存，而全国省份区县是固定的，这类业务使用分片枚举规则。

1）`schema.xml`

新增<table>，指定表信息，配置rule分片规则：

<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding-by-intfile"></table>

2）`rule.xml`

修改<tableRule name="sharding-by-intfile">：

<tableRule name="sharding-by-intfile">
    <rule>
        <columns>areacode</columns>
        <algorithm>hash-int</algorithm>
    </rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hash-int.txt</property>
    <property name="type">1</property>
    <property name="defaultNode">0</property>
</function>

参数：
- columns：分片字段。
- algorithm：分片函数。
- mapFile：表示配置文件名称。
- type：0（int型），非0（String型）。
- defaultNode：默认节点。小于0表示不设置默认节点，大于等于0表示设置默认节点（设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点。如果不设置，不识别时就会报错）。

3）`partition-hash-int.txt`

110=0
120=1

4）测试

重启Mycat，登录Mycat数据控制窗口。
通过Mycat，创建订单归属区域信息表orders_ware_info（结果：3306、3310数据库中都成功创建该表）：

create table orders_ware_info (
    id int auto_increment comment '编号',
    order_id int comment '订单编号',
    address varchar(200) comment '地址',
    areacode varchar(20) comment '区域编号',
    primary key (id)
);

通过Mycat，向orders_ware_info表中插入数据（结果：3306插入了北京，3310插入了天津，说明配置成功）：

insert into orders_ware_info (id, order_id, address, areacode) values (1, 1, '北京', '110');
insert into orders_ware_info (id, order_id, address, areacode) values (1, 1, '天津', '120');

3、范围约定

提前规划好分片字段某个范围属于哪个分片。

1）`schema.xml`

<table name="payment_info" dataNode="dn1,dn2" rule="auto-sharding-long"></table>

2）`rule.xml`

<tableRule name="auto-sharding-long">
    <rule>
        <columns>order_id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
    <property name="defaultNode">0</property>
</function>

3）`autopartition-long.txt`

0-102-0
103-200=1

4）测试

重启Mycat，登录数据控制窗口。
通过Mycat，创建payment_info表（结果：3306、3310数据库中都创建出该表）：

create table payment_info (
    id int auto_increment,
    order_id int,
    payment_status int,
    primary key (id)
);

通过Mycat，插入数据：

insert into payment_info (id, order_id, payment_status) values (1, 101, 0);
insert into payment_info (id, order_id, payment_status) values (2, 102, 0);
insert into payment_info (id, order_id, payment_status) values (3, 103, 0);
insert into payment_info (id, order_id, payment_status) values (4, 104, 0);

3306：

图片说明

3310：

图片说明

4、按日期（天）分片

1）`schema.xml`

<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date"></table>

2）`rule.xml`

<tableRule name="sharding_by_date">
    <rule>
        <columns>login_date</columns>
        <algorithm>shardingByDate</algorithm>
    </rule>
</tableRule>
<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2019-01-01</property>
    <property name="sEndDate">2019-01-04</property>
    <property name="sPartionDay">2</property>
</function>

参数：
- columns：分片字段。
- algorithm：分片函数。
- sBeginDate：开始日期。
- sEndDate：结束日期，代表数据达到了这个日期的分片后循环从开始分片插入。
- sPartionDay：分区天数，即默认从开始日期算起，每隔2天一个分区。

3）测试

重启Mycat服务器，控制窗口。
通过Mycat，创建用户信息表login_info（结果：两个数据库都创建出该表）：

create table login_info (
    id int auto_increment,
    user_id int,
    login_date date,
    primary key (id)
);

通过Mycat，插入数据：

insert into login_info (id, user_id, login_date) values (1, 101, '2019-01-01');
insert into login_info (id, user_id, login_date) values (2, 102, '2019-01-02');
insert into login_info (id, user_id, login_date) values (3, 103, '2019-01-03');
insert into login_info (id, user_id, login_date) values (4, 104, '2019-01-04');
insert into login_info (id, user_id, login_date) values (5, 105, '2019-01-05');
insert into login_info (id, user_id, login_date) values (6, 106, '2019-01-06');

3306：

图片说明

3310：

图片说明

5.4 全局序列

在实现分库分表的情况下，数据库自增主键已经无法保证自增主键的全局唯一。
为此，Mycat提供了全局sequence，并且提供了包含本底配置和数据库配置等多种实现方式。

1、本地文件

将sequence配置到文件中。
优点：本地加载，读取速度较快。
缺点：抗风险能力差，Mycat所在主机宕机后，无法读取本地文件。

2、数据库方式（多数使用）

利用数据库的一个表来进行计数累加。
但是并不是每次生成序列都读写数据库，这样效率太低。Mycat会预加载一部分号段到Mycat的内存中，这样大部分读写序列都是在内存中完成的。如果内存中的号段用完了，Mycat会再向数据库要一次。
如果Mycat崩溃了，那内存中的序列岂不是都没了？
- 是的。如果是这样，那么Mycat启动后会向数据库申请新的号段，原有号段会弃用。也就是说，如果Mycat重启，那么损失的是当前的号段没用完的号码，但是不会因此出现主键重复的问题。

1）创建序列脚本

在3306数据库上创建全局序列表：

create table mycat_sequence(
    name varchar(50) not null,
    current_value int not null,
    increment int not null default 100,
    primary key (name)
) engine=innodb;

在3306数据库上创建全局序列所需函数：

delimiter $$
create function mycat_seq_currval(seq_name varchar(50))
returns varchar(64)
deterministic
begin
declare retval varchar(64);
set retval="-999999999,null";
select concat(cast(current_value as char),",",cast(increment as char)) into retval from mycat_sequence where name = seq_name;
return retval;
end $$
delimiter ;

delimiter $$
create function mycat_seq_setval(seq_name varchar(50), value integer)
returns varchar(64)
deterministic
begin
update mycat_sequence set current_value= value where name = seq_name;
return mycat_seq_currval(seq_name);
end $$
delimiter ;

delimiter $$
create function mycat_seq_nextval(seq_name varchar(50))
returns varchar(64)
deterministic
begin
update mycat_sequence set current_value=current_value+increment where name = seq_name;
return mycat_seq_currval(seq_name);
end $$
delimiter ;

在3306数据库上初始化序列表记录（当前序号为400000，每次Mycat从数据库中取100个值）：

insert into mycat_sequence(name, current_value, increment) values ('orders', 400000, 100);

2）修改Mycat配置

修改Mycat的sequence_db_conf.properties，指定orders的序列在dn1，即3306数据库中：

ORDERS=dn1

修改Mycat的server.xml，指定全局序列类型（0：本地文件，1：数据库方式，2：时间戳方式）：

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

3）测试

重启Mycat，使用Mycat插入数据（注意：MYCATSEQ_ORDERS需要大写）：

insert into orders (id, amount, customer_id, order_type) values (next value for MYCATSEQ_ORDERS, 1000, 101, 102);

插入多次：

图片说明

3、时间戳

全局序列ID=64位二进制（42（毫秒）+5（机器ID）+5（业务编码）+12（重复累加）），换算成十进制为18位数的long类型，每毫秒可以并发12位二进制的累加。
优点：配置简单。
缺点：18位id过长。

4、自主生成全局序列

在Java项目中自己生成全局序列：
- 根据业务逻辑组合。
- 利用Redis的单线程原子性incr来生成序列。

6 基于HA机制的Mycat高可用

在实际项目中，Mycat服务也需要考虑高可用性。如果Mycat所在服务器出现宕机，或者Mycat服务故障，需要有备机提供服务，需要考虑Mycat集群。

6.1 高可用方案

我们可以使用HAProxy+Keepalived配合两台Mycat搭建Mycat集群，实现高可用性。
HAProxy实现了Mycat多节点的集群高可用和负载均衡，而HAProxy自身的高可用则可以通过Keepalived来实现。

6.2 安装配置HAProxy

# 1、准备好HAProxy安装包，上传到/opt目录下
# 2、解压到/usr/local/src
tar -zxvf haproxy-1.5.18.tar.gz -C /usr/local/src
# 3、进入解压后目录
cd /usr/local/src/haproxy-1.5.18
# 4、查看内核版本
uname -r
# 5、编译，以下为示例：TARGET为内核版本（3.10.0-514.el7时为linux310），PREFIX为HAProxy安装路径，ARCH为系统位数
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64
# 6、编译完成后，进行安装
make install PREFIX=/usr/local/haproxy
# 7、安装完成后，创建目录、HAProxy配置文件
mkdir -p /usr/data/haproxy/
vim /usr/local/haproxy/haproxy.conf
# 8、向配置文件中插入配置信息，并保存

未完待续……

7 Mycat安全设置

7.1 权限配置

1、user标签权限控制

目前Mycat对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库级别的读写权限。
通过server.xml的user标签进行配置：

<user name="mycat">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
</user>
<user name="user">
    <proprety name="password">user</proprety>
    <property name="schemas">TESTDB</property>
    <property name="readOnly">true</property>
</user>

说明：
- name：应用连接中间件逻辑库的用户名。
- password：该用户对应的密码。
- TESTDB：应用当前连接的逻辑库中所对应的逻辑表。schemas中可以配置一个或多个。
- readOnly：应用连接中间件逻辑库所具有的权限，默认为false。

2、privileges标签权限控制

在user标签下的privileges标签可以对逻辑库（schema）、表（table）进行精细化的DML权限控制。
privileges标签下的check属性，如为true开启权限检查，为false不开启，默认为false。
由于Mycat一个用户的schemas属性可配置多个逻辑库（schema），所以privileges的下级节点schema节点同样可配置多个，对多库多表进行细粒度的DML权限控制。

<user name="mycat">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
    <privileges check="true">
        <schama name="TESTDB" dml="1111">
            <table name="orders" dml="0000"></table>
        </schama>
    </privileges>
</user>

dml：
- 0000——禁止insert、update、select、delete。
- 0010——禁止insert、update、delete，可以select。
- 1110——禁止update、select、delete，可以insert。
- 1111——都可以。

7.2 SQL拦截

server.xml中的firewall标签用来定义防火墙：firewall下的whitehost标签用来定义IP白名单，blacklist用来定义SQL黑名单。

1、白名单

可以通过设置白名单，实现某主机某用户可以访问Mycat，而其他主机用户禁止访问。

<firewall>
    <whitehost>
        <host host="192.168.140.128" user="mycat"/>
    </whitehost>
</firewall>

2、黑名单

可以通过设置黑名单，实现Mycat对具体SQL操作的拦截，如增删改查等操作的拦截。

<firewall>
    <blacklist check="true">
        <property name="deleteAllow">false</property>
    </blacklist>
</firewall>

配置项：
- selectAllow：是否允许执行select语句，默认true。
- deleteAllow：是否允许执行delete语句，默认true。
- updateAllow：是否允许执行update语句，默认true。
- insertAllow：是否允许执行insert语句，默认true。
- createTableAllow：是否允许创建表，默认true。
- setAllow：是否允许使用set语句，默认true。
- alertTableAllow：是否允许执行alter table语句，默认true。
- dropTableAllow：是否允许删除表，默认true。
- commitAllow：是否允许执行commit操作，默认true。
- rollbackAllow：是否允许执行roll back操作，默认true。

8 Mycat监控工具

8.1 Mycat-web简介

Mycat-web是Mycat可视化运维的管理和监控平台，弥补了Mycat在监控上的空白，帮Mycat分担统计任务和配置管理任务。
Mycat-web引入了Zookeeper作为配置中心，可以管理多个节点。
Mycat-web主要管理和监控Mycat的流量、连接、活动线程和内存等，具备IP白名单、邮件警告等模块，还可以统计SQL并分析慢SQL和高频SQL等，为优化SQL提供依据。
未完待续……

Mycat

1 Mycat简介

1.1 Mycat

1、数据库中间件

2、数据库中间件对比

1.2 功能

1、读写分离

2、数据分片

3、多数据源整合

1.3 原理

2 安装Mycat（Linux）

2.1 下载

2.2 上传服务器

2.3 安装

2.4 核心配置文件

1、schema.xml

2、rule.xml

3、server.xml

2.5 启动

1、控制台启动

2、后台启动

2.6 登录

1、登录后台管理窗口

2、登录数据窗口

3 搭建读写分离

3.1 一主一从

1、MySQL的my.ini配置文件

2、MySQL主机授权

3、MySQL从机连接

4、MyCat配置逻辑库：schema.xml

5、启动Mycat服务、登录后台管理

3.2 双主双从

1、MySQL的my.ini配置

2、M1、M2授权

3、S1、S2连接

4、M1、M2互相连接

5、配置Mycat的schema.xml

6、启动Mycat服务、登录后台管理

7、测试

4 垂直拆分——分库

4.1 如何划分表

4.2 配置分库

1、新增mysql服务

2、配置Mycat的schema.xml

3、使用Mycat建库、建表

5 水平拆分——分表

5.1 配置分表

1、选择要拆分的表

2、分表字段

3、修改配置文件schema.xml

4、修改配置文件rule.xml

5、测试

5.2 Mycat的分片——join

1、ER表

2、全局表

5.3 常用分片规则

1、取模

2、分片枚举

1）schema.xml

2）rule.xml

3）partition-hash-int.txt

4）测试

3、范围约定

1）schema.xml

2）rule.xml

3）autopartition-long.txt

4）测试

4、按日期（天）分片

1）schema.xml

2）rule.xml

3）测试

5.4 全局序列

1、本地文件

2、数据库方式（多数使用）

1）创建序列脚本

2）修改Mycat配置

3）测试

3、时间戳

4、自主生成全局序列

6 基于HA机制的Mycat高可用

1、`schema.xml`

2、`rule.xml`

3、`server.xml`

1、MySQL的`my.ini`配置文件

4、MyCat配置逻辑库：`schema.xml`

1、MySQL的`my.ini`配置

5、配置Mycat的`schema.xml`

2、配置Mycat的`schema.xml`

3、修改配置文件`schema.xml`

4、修改配置文件`rule.xml`

5.2 Mycat的分片——`join`

1）`schema.xml`

2）`rule.xml`

3）`partition-hash-int.txt`

1）`schema.xml`

2）`rule.xml`

3）`autopartition-long.txt`

1）`schema.xml`

2）`rule.xml`