MySQL高可用架构-Mycat

一、Mycat介绍

1.1、简介

1）Mycat背后是阿里曾经开源的知名产品——Cobar，Cobar是阿里巴巴研发的关系型数据的分布式处理系统

2）从定义和分类来看，它是一个开源的分布式数据库系统，是一个实现了MySQL协议的的Server，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生（Native）协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里

1.2、mycat特性

mycat有如下特性：

  • 支持 SQL 92标准
  • 支持Mysql集群，可以作为Proxy使用
  • 支持JDBC连接多数据库
  • 支持各种数据库，包括Mysql 、mongodb、oracle、sqlserver 、hive 、db2 、 postgresql。
  • 支持galera for mysql集群，percona-cluster或者mariadb cluster，提供高可用性数据分片集群
  • 自动故障切换，高可用性
  • 支持读写分离，支持Mysql双主多从，以及一主多从的模式
  • 支持全局表，数据自动分片到多个节点，用于高效表关联查询
  • 支持独有的基于E-R 关系的分片策略，实现了高效的表关联查询
  • 支持一致性Hash分片，有效解决分片扩容难题
  • 多平台支持，部署和实施简单
  • 支持Catelet开发，类似数据库存储过程，用于跨分片复杂SQL的人工智能编码实现
  • 支持NIO与AIO两种网络通信机制，Windows下建议AIO，Linux下目前建议NIO
  • 支持Mysql存储过程调用
  • 以插件方式支持SQL拦截和改写
  • 支持自增长主键、支持Oracle的Sequence机制

1.3、mycat原理

Mycat的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

image

1）上述图片里，Orders表被分为三个分片datanode（简称dn)，这三个分片是分布在两台MySQL Server上(DataHost)，即datanode=database@datahost方式，因此你可以用一台到N台服务器来分片，分片规则为（sharding rule)典型的字符串枚举分片规则，一个规则的定义是分片字段（sharding column)+分片函数(rule function)，这里的分片字段为prov而分片函数为字符串枚举方式。

2）当Mycat收到一个SQL时，会先解析这个SQL，查找涉及到的表，然后看此表的定义，如果有分片规则，则获取到SQL里分片字段的值，并匹配分片函数，得到该SQL对应的分片列表，然后将SQL发往这些分片去执行，最后收集和处理所有分片返回的结果数据，并输出到客户端。以select * from Orders where prov=?语句为例，查到prov=wuhan，按照分片函数，wuhan返回dn1，于是SQL就发给了MySQL1，去取DB1上的查询结果，并返回给用户。

1.4、Mycat应用场景

Mycat几个典型的应用场景：

  • 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
  • 分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
  • 多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化
  • 报表系统，借助于Mycat的分表能力，处理大规模报表的统计

二、Mycat安装

2.1、环境准备

主机 IP地址
mycat 10.0.0.51
master01 10.0.0.201
master02 10.0.0.202
slave01 10.0.0.203
slave0210.0.0.204

host解析配置：

10.0.0.51 mycat
10.0.0.201 master01
10.0.0.202 master02
10.0.0.203 slave01
10.0.0.204 slave02

2.2、mycat安装

1）安装Java运行环境

[root@mycat ~]# yum install -y java
[root@mycat ~]# java -version
openjdk version "1.8.0_212"
OpenJDK Runtime Environment (build 1.8.0_212-b04)
OpenJDK 64-Bit Server VM (build 25.212-b04, mixed mode)

2）Mycat本机预先安装MySQL环境，但不需要启动MySQL实例

3）下载Mycat-server-xxxxx.linux.tar.gz软件包并解压：

软件下载地址：http://dl.mycat.io

[root@mycat local]# tar xf  Mycat-server-1.6.5-release-20180117003034-linux.tar.gz 
[root@mycat local]# cd mycat/
[root@mycat mycat]# ll
total 12
drwxr-xr-x 2 root root  190 Oct 11 21:02 bin
drwxrwxrwx 2 root root    6 Mar  1  2016 catlet
drwxrwxrwx 4 root root 4096 Oct 11 21:02 conf
drwxr-xr-x 2 root root 4096 Oct 11 21:02 lib
drwxrwxrwx 2 root root    6 Jan 17  2018 logs
-rwxrwxrwx 1 root root  219 Jan 17  2018 version.txt
-----------------------------------------------------------------------------------
#bin目录：
存放了window版本和linux版本，除了提供封装成服务的版本之外，也提供了nowrap的shell脚本命令 conf目录：存放配置文件，
	server.xml是Mycat服务器参数调整和用户授权的配置文件，
	schema.xml是逻辑库定义和表以及分片定义的配置文件，
	rule.xml是分片规则的配置文件，分片规则的具体一些参数信息单独存放为文件，也在这个目录下
配置文件修改，需要重启Mycat或者通过9066端口reload. 

#lib目录：
主要存放mycat依赖的一些jar文件. 

#日志存放在logs/mycat.log中，每天一个文件，日志的配置是在conf/log4j.xml中，根据自己的需要，可以调整输出级别为debug，debug级别下，会输出更多的信息，方便排查问题

注意：Linux下部署安装MySQL，默认不忽略表名大小写，需要手动到/etc/my.cnf 下配置 lower_case_table_names=1 使Linux环境下MySQL忽略表名大小写，否则使用MyCAT的时候会提示找不到表的错误

4）启动mycat

[root@mycat mycat]# bin/mycat start
Starting Mycat-server...
[root@mycat mycat]# ps -ef|grep mycat
[root@mycat ~]# netstat -lntup|grep java
tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      2592/java           
tcp6       0      0 :::37653                :::*                    LISTEN      2592/java           
tcp6       0      0 :::1984                 :::*                    LISTEN      2592/java           
tcp6       0      0 :::33984                :::*                    LISTEN      2592/java           
tcp6       0      0 :::8066                 :::*                    LISTEN      2592/java           
tcp6       0      0 :::9066                 :::*                    LISTEN      2592/java
#mycat有两个端口8066，9066，应用端可以连接8066，管理可以连接9066

5） 连接mycat

注意，需要在后端节点授权mycat的连接，否则以卡住：

master01-(none)]>grant all privileges on *.* to root@'10.0.0.%' identified by 'mysql';

[root@mycat ~]# mysql -uroot -p -P8066 -h 127.0.0.1
Enter password:   #默认密码是123456
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.6.29-mycat-1.6.5-release-20180117003034 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2017, 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 databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+

三、Mycat配置-schema.xml

1）Schema.xml作为MyCat中重要的配置文件之一，管理着MyCat的逻辑库、表、分片规则、DataNode以及DataHost

2）主要有三个标签，一是schema标签，二是dataNode标签，三是dataHost标签。如以下配置

[root@mycat conf]# cat 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="localhost1" database="test" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
	<heartbeat>select user()</heartbeat>
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql">
		<readHost host="slave01" url="10.0.0.203:3306" user="root" password="mysql" />
	</writeHost>
</dataHost>
</mycat:schema>

3.1、schema标签

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"></schema>

schema标签用于定义MyCat实例中的逻辑库，MyCat可以有多个逻辑库，每个逻辑库都有自己的相关配置。可以使用 schema 标签来划分这些不同的逻辑库。

如果不配置 schema 标签，所有的表配置，会属于同一个默认的逻辑库。

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> 
	<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"> 
	</table> 
</schema> 
<schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100"> 
	<table name="company" dataNode="dn10,dn11,dn12" rule="auto-sharding-long"> 
	</table> 
</schema>

如上所示的配置就配置了两个不同的逻辑库，逻辑库的概念和MYSQL数据库中Database的概念相同，我们在查询这两个不同的逻辑库中表的时候需要切换到该逻辑库下才可以查询到所需要的表。

3.1.1、dataNode属性

该属性用于绑定逻辑库到某个具体的数据节点上

<schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">	
	<!—配置需要分片的表--> 
	<table name=“tuser” dataNode=”dn1”/> 
</schema>

那么现在tuser就绑定到dn1所配置的具体database上，可以直接访问这个database,没有配置的表则会走默认节点dn2，这里注意没有配置在分片里面的表，工具查看无法显示，但是可以正常使用。

该属性的值需要和dataNode标签中name属性的值相互对应

3.1.2、checkSQLschema属性

1）当该值设置为true时，如果我们执行语句select * from TESTDB.travelrecord;则MyCat会把语句修改为select * from travelrecord;。即把表示schema的字符去掉，避免发送到后端数据库执行时报（ERROR 1146 (42S02): Table ‘testdb.travelrecord’ doesn’t exist）。不过，即使设置该值为 true ，如果语句所带的是并非是schema指定的名字，例如：select * from db1.travelrecord;那么MyCat并不会删除db1这个字段，如果没有定义该库的话则会报错，所以在提供SQL语句的最好是不带这个字段。

2）当设置为false时，则会把语句原封不动的发往最终的MySQL执行

#当checksqlschema=false时
mysql> use TESTDB;
mysql> select * from TESTDB.temp;
ERROR 1146 (42S02): Table 'TESTDB.temp' doesn't exist
mysql> select * from test.temp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+

#当checksqlschema=true时
mysql> select * from TESTDB.temp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+

mysql> select * from temp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+

3.1.3、sqlMaxLimit属性

①当该schema中有分片表（有table标签）时，才会生效

②当该值设置为某个数值时。每条执行的SQL语句，如果没有加上limit语句，MyCat也会自动的加上所对应的值。例如设置值为100，执行**select * from TESTDB.travelrecord;**的效果为和执行**select * from TESTDB.travelrecord limit 100;**相同。

3.2、table标签

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

Table 标签定义了MyCat中的逻辑表，所有需要拆分的表都需要在这个标签中定义。

3.2.1、rule属性

该属性用于指定逻辑表要使用的规则名字，规则名字在rule.xml中定义，必须与tableRule标签中name属性属性值一一对应。

3.2.2、type属性

该属性定义了逻辑表的类型，目前逻辑表只有“全局表”和”普通表”两种类型。对应的配置：

  • 全局表：global
  • 普通表：不指定该值为globla的所有表

3.3、dataNode标签

dataNode 标签定义了MyCat中的数据节点，用于绑定逻辑库到某个具体的database。

<dataNode name="dn1" dataHost="lch3307" database="db1" ></dataNode>

dataNode 标签定义了MyCat中的数据节点，也就是我们通常说所的数据分片。一个dataNode标签就是一个独立的数据分片。 例子中所表述的意思为：使用名字为lch3307数据库实例上的db1物理数据库，这就组成一个数据分片，最后，我们使用名字dn1标识这个分片。

3.3.1、name属性

定义数据节点的名字，这个名字需要是唯一的，我们需要在table标签上应用这个名字，来建立表与分片对应的关系

3.3.2、dataHost属性

该属性用于定义该分片属于哪个数据库实例的，属性值是引用dataHost标签上定义的name属性。

3.3.3、database属性

该属性用于定义该分片属性哪个具体数据库实例上的具体库，因为这里使用两个纬度来定义分片，就是：实例+具体的库。因为每个库上建立的表和表结构是一样的。所以这样做就可以轻松的对表进行水平拆分。

3.3.4、测试

#定义两个schema以及两个dataNode
[root@mycat conf]# cat schema.xml
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> 
</schema> 
<schema name="TESTDB2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"> 
</schema> 
<dataNode name="dn1" dataHost="node1" database="test" /> 
<dataNode name="dn2" dataHost="node1" database="test2" /> 
<dataHost name="node1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql"> 
	</writeHost> 
</dataHost> 
</mycat:schema>

#修改server.xml,否者只能看到一个逻辑库
[root@mycat conf]# vim server.xml 
<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">TESTDB,TESTDB2</property>  #添加TESTDB2

        <!-- 表级 DML 权限设置 -->
        <!--            
        <privileges check="false">
                <schema name="TESTDB" dml="0110" >
                        <table name="tb01" dml="0000"></table>
                        <table name="tb02" dml="1111"></table>
                </schema>
        </privileges>           
         -->
</user>

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

#重启mycat并登录
[root@mycat conf]# ../bin/mycat restart
[root@mycat conf]# mysql -uroot -p -P8066 -h 127.0.0.1
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
| TESTDB2  |
+----------+

3.4、dataHost标签

作为Schema.xml中最后的一个标签，该标签在mycat逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写分离配置和心跳语句，如：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
</schema> <schema name="TESTDB2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2">
</schema>
<dataNode name="dn1" dataHost="node1" database="test" />
<dataNode name="dn2" dataHost="node1" database="test2" />
<dataHost name="node1" maxCon="10" minCon="5" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">        
     <heartbeat>select user()</heartbeat>
     <writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql">
         <readHost host="slave01" url="10.0.0.203:3306" user="root" password="mysql" />
     </writeHost>
</dataHost>
</mycat:schema>

3.4.1、name属性

唯一标识dataHost标签，供上层的标签使用。

3.4.2、maxCon属性

指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最大连接数。

3.4.3、minCon属性

指定每个读写实例连接池的最小连接，初始化连接池的大小。

mysql> show full processlist;
+-----+------+------------------+-------+-------------+------+---------------------------------------------------------------+-----------------------+
| Id  | User | Host             | db    | Command     | Time | State                                                         | Info                  |
+-----+------+------------------+-------+-------------+------+---------------------------------------------------------------+-----------------------+
|  94 | root | localhost        | NULL  | Sleep       | 1684 |                                                               | NULL                  |
| 135 | repl | 10.0.0.203:52178 | NULL  | Binlog Dump | 7233 | Master has sent all binlog to slave; waiting for more updates | NULL                  |
| 324 | root | 10.0.0.51:52424  | test2 | Sleep       |   34 |                                                               | NULL                  |
| 325 | root | 10.0.0.51:52426  | test2 | Sleep       |    4 |                                                               | NULL                  |
| 326 | root | 10.0.0.51:52428  | test  | Sleep       |  144 |                                                               | NULL                  |
| 327 | root | 10.0.0.51:52430  | test2 | Sleep       |   54 |                                                               | NULL                  |
| 328 | root | 10.0.0.51:52432  | test  | Sleep       |   33 |                                                               | NULL                  |
| 329 | root | 10.0.0.51:52434  | test2 | Sleep       |   44 |                                                               | NULL                  |
| 330 | root | 10.0.0.51:52436  | test  | Sleep       |  144 |                                                               | NULL                  |
| 331 | root | 10.0.0.51:52438  | test2 | Sleep       |   14 |                                                               | NULL                  |
| 332 | root | 10.0.0.51:52440  | test  | Sleep       |  144 |                                                               | NULL                  |
| 333 | root | 10.0.0.51:52442  | test  | Query       |    0 | starting                                                      | show full processlist |
| 335 | root | 10.0.0.51:52450  | test2 | Sleep       |   24 |                                                               | NULL                  |
+-----+------+------------------+-------+-------------+------+---------------------------------------------------------------+-----------------------+

3.4.4、heartbeat标签

这个标签内指明用于和后端数据库进行心跳检查的语句。例如,MYSQL可以使用select user()，Oracle可以使用select 1 from dual等。

3.4.5、writeHost标签、readHost标签

这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。唯一不同的是，writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求

在一个dataHost内可以定义多个writeHost和readHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去。

3.4.6、host属性

用于标识不同实例，一般writeHost我们使用*M1，readHost我们用*S1

3.4.7、url属性

后端实例连接地址，如果是使用native的dbDriver，则一般为address:port这种形式。用JDBC或其他的dbDriver，则需要特殊指定。当使用JDBC时则可以这么写：jdbc:mysql://localhost:3306/。

3.4.8、user属性

后端存储实例需要的用户名字 password

3.4.9、password属性

后端存储实例需要的密码

3.4.10、weight 属性

权重 配置在readhost 中作为读节点的权重

3.4.11、balance属性

负载均衡类型，目前的取值有3种：

1） balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。

2）balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。

3）balance="2"，所有读操作都随机的在writeHost、readhost上分发。

3.4.12、writeType属性

负载均衡类型，目前的取值有2种：

1）writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties . （如果还是希望写第一个，需要删除该文件并重启mycat）

2） writeType=“1”，所有写操作都随机的发送到配置的writeHost，但不推荐使用

[root@mycat mycat]# cat conf/schema.xml
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> 
</schema> 
<schema name="TESTDB2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"> 
</schema> 
<dataNode name="dn1" dataHost="node1" database="test" /> 
<dataNode name="dn2" dataHost="node1" database="test2" /> 
<dataHost name="node1" maxCon="10" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="0"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql"> 
	</writeHost> 
	<writeHost host="slave01" url="10.0.0.203:3306" user="root" password="mysql"> 
	</writeHost> 
</dataHost> 
</mycat:schema>
------------------------------------------------------------------------------------------------
#当writetype=0时
mysql> select * from temp;
mysql> insert into temp values(1,'a');
mysql> insert into temp values(2,'b');
mysql> insert into temp values(3,'c');

#在master01上查看（可以关闭主从同步）
master01-test]>select * from temp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+

#当master01关闭时，再执行插入操作：
[root@master01 ~]# /etc/init.d/mysqld stop
mysql> insert into temp values(4,'d');
mysql> insert into temp values(5,'e');

#查看slave01
slave01-[test]>select * from temp;
+------+------+
| id   | name |
+------+------+
|    4 | d    |
|    5 | e    |
+------+------+
--------------------------------------------------------------------------------
#当writetype=1时（可以清除之前的数据测试）
mysql> insert into temp values(1,'a');
mysql> insert into temp values(2,'b');
mysql> insert into temp values(3,'c');
mysql> insert into temp values(4,'d');

#master01上查看
master01-test]>select * from temp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    4 | d    |
+------+------+

#在slave01上查看
slave01-[test]>select * from temp;
+------+------+
| id   | name |
+------+------+
|    3 | c    |
+------+------+

3.4.13、switchType属性

  • -1 表示不自动切换
  • 1 默认值，自动切换
  • 2 基于MySQL主从同步的状态决定是否切换 ，心跳语句为 show slave status
[root@mycat conf]# cat schema.xml
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> 
</schema> 
<schema name="TESTDB2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"> 
</schema> 
<dataNode name="dn1" dataHost="node1" database="test" /> 
<dataNode name="dn2" dataHost="node1" database="test2" /> 
<dataHost name="node1" maxCon="10" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql"> 
	</writeHost> 
	<writeHost host="slave01" url="10.0.0.203:3306" user="root" password="mysql"> 
	</writeHost> 
</dataHost> 
</mycat:schema>

#switchType=-1时 
mysql> insert into temp values(61,'a'); 
mysql> insert into temp values(62,'a'); 

#当master1数据库关闭时，执行结果报错： 
mysql> insert into temp values(63,'a'); 
ERROR 1184 (HY000): Connection refused 
mysql> insert into temp values(64,'a'); ERROR 1184 (HY000): Connection refused

#当switchType=1时 当master1数据库关闭时，数据依然能插入到slave1里
--------------------------------------------------------------------------------------------
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> 
</schema> 
<dataNode name="dn1" dataHost="node1" database="test" /> 
<dataHost name="node1" maxCon="10" minCon="5" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql"> 
		<readHost host="slave01" url="10.0.0.203:3306" user="root" password="mysql" /> 
	</writeHost> 
</dataHost> 
</mycat:schema>

#当switchtype=1时，由于没有第二个writehost，所以当master1崩溃时，读写都不成功 
mysql> insert into temp2 values(3,'c'); 
ERROR 1184 (HY000): Connection refused 
mysql> select * from temp2; 
ERROR 1184 (HY000): Connection refused

3.4.14、tempReadHostAvailable属性

如果配置了这个属性writeHost 下面的readHost仍旧可用，默认0,可配置（0、1）

<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> 
</schema> 
<dataNode name="dn1" dataHost="node1" database="test" /> 
<dataHost name="node1" maxCon="10" minCon="5" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" tempReadHostAvailable="1"> 	
	<heartbeat>select user()</heartbeat> 
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql"> 
		<readHost host="slave01" url="10.0.0.201:3306" user="root" password="mysql" /> 
	</writeHost> 
</dataHost> 
</mycat:schema>
--------------------------------------------------------------------------
#当master1关闭后，查询操作可以执行，但修改操作无法进行 
mysql> select * from temp2; #可以执行
mysql> insert into temp2 values(3,'c'); 
ERROR 1184 (HY000): Connection refused

四、Mycat配置-server.xml

4.1、相关配置

server.xml中重点需要关注的标签是用于定义登录mycat的用户和权限

<user name="test"> 
	<property name="password">test</property> 
	<property name="schemas">TESTDB</property> 
	<property name="readOnly">true</property> 
	<property name="benchmark">11111</property> 
	<property name="usingDecrypt">1</property> 
</user>

上面的例子中，我定义了一个用户，用户名为test、密码也为test，可访问的schema也只有TESTDB一个。 如果我在schema.xml中定义了多个schema，那么这个用户是无法访问其他的schema。在mysql客户端看来则是无法使用use切换到这个其他的数据库。如果使用了use命令，则mycat会报出这样的错误提示：

ERROR 1044 (HY000): Access denied for user 'test' to database 'xxx'

这个标签嵌套的property标签则是具体声明的属性值，正如上面的例子。我们可以:

  • 修改user标签的name属性来指定用户名；
  • 修改password内的文本来修改密码；
  • 修改readOnly为true 或false来限制用户是否只是可读的；
  • 修改schemas内的文本来控制用户可放问的schema；
  • 修改schemas内的文本来控制用户可访问的schema，同时访问多个schema的话使用逗号隔开， 例如: <property name="schemas">TESTDB,db1,db2</property>

目前Mycat对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库级别的读写权限控制。

<user name="mycat"> 
	<property name="password">mycat</property> 
	<property name="schemas">order</property> 
	<property name="readOnly">true</property> 
</user> 

<user name="mycat2"> 
	<property name="password">mycat</property> 
	<property name="schemas">order</property> 
</user>

配置说明： 配置中name是应用连接中间件逻辑库的用户名。 mycat 中password是应用连接中间件逻辑库的密码。 schemas是应用当前连接的逻辑库,schemas中可以配置一个或多个。 readOnly是应用连接中间件逻辑库所具有的权限。true为只读，false为读写都有，默认为false

4.2、相关属性

4.2.1、连接相关属性

初始化mysql前后端连接所涉及到的一些属性：

1）packetHeaderSize : 指定Mysql协议中的报文头长度。默认4。

2）maxPacketSize : 指定Mysql协议可以携带的数据最大长度。默认16M。

3）idleTimeout : 指定连接的空闲超时时间。某连接在发起空闲检查下，发现距离上次使用超过了空闲时间，那么这个连接会被回收，就是被直接的关闭掉。默认30分钟，单位毫秒。

4）charset : 连接的初始化字符集。默认为utf8。

5）txIsolation : 前端连接的初始化事务隔离级别，只在初始化的时候使用，后续会根据客户端传递过来的属性对后端数据库连接进行同步。默认为REPEATED_READ，设置值为数字默认3。

  • READ_UNCOMMITTED = 1;
  • READ_COMMITTED = 2;
  • REPEATED_READ = 3;
  • SERIALIZABLE = 4;

6）sqlExecuteTimeout:SQL执行超时的时间，Mycat会检查连接上最后一次执行SQL的时间，若超过这个时间则会直接关闭这连接。默认时间为300秒，单位秒。

4.2.2、服务相关的属性

1）bindIp : mycat服务监听的IP地址，默认值为0.0.0.0。

2）serverPort : 定义mycat的使用端口，默认值为8066

3）managerPort : 定义mycat的管理端口，默认值为9066。

[root@mycat conf]# cat server.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
	<property name="useHandshakeV10">1</property>
	<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
	<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

		<property name="sequnceHandlerType">2</property>
	<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
      <!--  <property name="useCompression">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 | type 2 NettyBufferPool -->
		<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">64k</property>

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

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

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


		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">false</property>

		<!-- XA Recovery Log日志路径 -->
		<!--<property name="XARecoveryLogBaseDir">./</property>-->

		<!-- XA Recovery Log日志名称 -->
		<!--<property name="XARecoveryLogBaseName">tmlog</property>-->

	</system>
	
	<!-- 全局SQL防火墙设置 -->
	<!--白名单可以使用通配符%或着*-->
	<!--例如<host host="127.0.0.*" user="root"/>-->
	<!--例如<host host="127.0.*" user="root"/>-->
	<!--例如<host host="127.*" user="root"/>-->
	<!--例如<host host="1*7.*" user="root"/>-->
	<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
	<!--
	<firewall>
	   <whitehost>
	      <host host="1*7.0.0.*" user="root"/>
	   </whitehost>
       <blacklist check="false">
       </blacklist>
	</firewall>
	-->

	<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">TESTDB</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>

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

</mycat:server>
[root@mycat conf]#

五、Mycat配置-rule.xml

rule.xml里面就定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法，或者对表使用相同的算法但具体的参数不同。这个文件里面主要有tableRule和function这两个标签。在具体使用过程中可以按照需求添加tableRule和function。

5.1、tableRule标签

这个标签定义表规则。 定义的表规则，在schema.xml：

<tableRule name="rule1"> 
	<rule> 
		<columns>id</columns> 
		<algorithm>hash-int</algorithm> 
	</rule> 
</tableRule>

name 属性指定唯一的名字，用于标识不同的表规则。
rule标签则指定对物理表中的哪一列进行拆分和使用什么路由算法。
columns 内指定要拆分的列名字。
algorithm 使用function标签中的name属性。连接表规则和具体路由算法。当然，多个表规则可以连接到同一个路由算法上。table标签内使用。让逻辑表使用这个规则进行分片。

5.2、function标签

<function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"> 
	<property name="mapFile">partition-hash-int.txt</property> 
</function> 

name 指定算法的名字。 
class 制定路由算法具体的类名字 
property 为具体算法需要用到的一些属性。

六、Mycat分片规则

在数据切分处理中，特别是水平切分中，中间件最终要的两个处理过程就是数据的切分、数据的聚合。选择合适的切分规则，至关重要，因为它决定了后续数据聚合的难易程度，甚至可以避免跨库的数据聚合处理。

6.1、Mycat全局表

1）如果你的业务中有些数据类似于数据字典，比如配置文件的配置，常用业务的配置或者数据量不大很少变动的表，这些表往往不是特别大，而且大部分的业务场景都会用到，那么这种表适合于Mycat全局表，无须对数据进行切分，只要在所有的分片上保存一份数据即可

2）Mycat 在Join操作中，业务表与全局表进行Join聚合会优先选择相同分片内的全局表join，避免跨库Join

3）在进行数据插入操作时，mycat将把数据分发到全局表对应的所有分片执行

4）在进行数据读取时候将会随机获取一个节点读取数据。

5）全局表的配置如下

<table name="t_area" primaryKey="id" type="global" dataNode="dn1,dn2" />

6）示例

[root@mycat conf]# cat schema.xml
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> 
	<table name="temp2" primaryKey="id" type="global" dataNode="dn1,dn2" /> 
</schema> 
<schema name="TESTDB2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"> 
</schema> 
<dataNode name="dn1" dataHost="node1" database="test" /> 
<dataNode name="dn2" dataHost="node1" database="test2" /> 
<dataHost name="node1" maxCon="10" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="-1"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql"> 
	</writeHost> 
	<writeHost host="slave011" url="10.0.0.203:3306" user="root" password="mysql"> 
	</writeHost> 
</dataHost> 
</mycat:schema>
-------------------------------------------------------------------------------------

事先要在相关数据库的各个节点上创建这个全局表
在Mycat上插入记录 
mysql> insert into temp2 values(100,'100'); ##writetype=0的情况下

当master1和slave1之间没有复制关系或复制停止时，数据仅插入到master1的test和test2库里
当master1和slave1之间有复制关系时，则数据插入到master1的test和test2库，并同步到slave1的test和test2库里

6.2、ER分片表

6.2.1、示例

有一类业务，例如订单（order）跟订单明细（order_detail）,明细表会依赖于订单，也就是说会存在表的主从关系，这类似业务的切分可以抽象出合适的切分规则，比如根据用户ID切分,其他相关的表都依赖于用户ID，再或者根据订单ID切分，总之部分业务总会可以抽象出父子关系的表。这类表适用于ER分片表，子表的记录与所关联的父表记录存放在同一个数据分片上，避免数据Join跨库操作。

<table name="orders" dataNode="dn1,dn2" rule="mod-long"> 
	<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="order_id" /> 
</table>

schema.xml中定义如下的分片配置,order,order_detail 根据order_id进行数据切分，保证相同order_id的数据分到同一个分片上，在进行数据插入操作时，Mycat会获取order所在的分片，然后将order_detail也插入到order所在的分片。

[root@mycat conf]# cat schema.xml
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> 
	<table name="orders" dataNode="dn1,dn2" rule="mod-long"> 
		<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="id" /> 
	</table> 
</schema> 
<schema name="TESTDB2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"> 
</schema> 
<dataNode name="dn1" dataHost="node1" database="test" /> 
<dataNode name="dn2" dataHost="node2" database="test" /> 
<dataHost name="node1" maxCon="10" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="-1"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql"> 
	</writeHost> 
</dataHost> 
<dataHost name="node2" maxCon="10" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="-1"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="slave01" url="10.0.0.203:3306" user="root" password="mysql"> 
	</writeHost> 
</dataHost> 
</mycat:schema>
--------------------------------------------------------------------------------------
#在dn1和dn2对应的MySQL数据库test上创建表
create table orders(id int primary key,name varchar(10));
create table order_detail(id int primary key,name varchar(10),order_id int ,foreign 

#mycat插入数据
mysql> insert into orders values(1,'a');
ERROR 1064 (HY000): partition table, insert must provide ColumnList
mysql> insert into orders(id,name) values(1,'a'),(2,'b'),(3,'c'),(4,'c'),(5,'c'); 
mysql> insert into order_detail(id,name,order_id) values(1,'a',1),(2,'b',2),(3,'c',3),(4,'c',4),(5,'c',5);
ERROR 1064 (HY000): ChildTable multi insert not provided
mysql> insert into order_detail(id,name,order_id) values(1,'a',1);
mysql> insert into order_detail(id,name,order_id) values(2,'b',2);
mysql> insert into order_detail(id,name,order_id) values(3,'c',3);
mysql> insert into order_detail(id,name,order_id) values(4,'d',4);
mysql> insert into order_detail(id,name,order_id) values(5,'e',5);

#查看
master01-test]>select * from orders;
+----+------+
| id | name |
+----+------+
|  2 | b    |
|  4 | c    |
+----+------+
master01-test]>select * from order_detail;
+----+------+----------+
| id | name | order_id |
+----+------+----------+
|  2 | b    |        2 |
|  4 | d    |        4 |
+----+------+----------+

slave01-[test]>select * from orders;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | c    |
|  5 | c    |
+----+------+
slave01-[test]>select * from order_detail;
+----+------+----------+
| id | name | order_id |
+----+------+----------+
|  1 | a    |        1 |
|  3 | c    |        3 |
|  5 | e    |        5 |
+----+------+----------+

6.2.2、选择分片字段的条件

选择分片字段的条件如下：

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

1）当你没有任何字段可以作为分片字段的时候，主键分片就是唯一选择，其优点是按照主键的查询最快

2）对于非主键分片的table，填写属性primaryKey，此时MyCAT会将你根据主键查询的SQL语句的第一次执行结果进行分析，确定该Table 的某个主键在什么分片上，并进行主键到分片ID的缓存。第二次或后续查询mycat会优先从缓存中查询是否有id–>node 即主键到分片的映射，如果有直接查询，通过此种方法提高了非主键分片的查询性能

6.3、相关概念

6.3.1、分片节点(dataNode)

数据切分后，一个大表被分到不同的分片数据库上面，每个表分片所在的数据库就是分片节点（dataNode）

6.3.2、节点主机(dataHost)

数据切分后，每个分片节点（dataNode）不一定都会独占一台机器，同一机器上面可以有多个分片数据库，这样一个或多个分片节点（dataNode）所在的机器就是节点主机（dataHost）,为了规避单节点主机并发数限制，尽量将读写压力高的分片节点（dataNode）均衡的放在不同的节点主机（dataHost）。

6.4、枚举分片

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

<tableRule name="sharding-by-intfile"> 
	<rule> 
		<columns>user_id</columns> 
		<algorithm>hash-int</algorithm> 
	</rule> 
</tableRule> 

<function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"> 
	<property name="mapFile">partition-hash-int.txt</property> 
	<property name="type">0</property> 
	<property name="defaultNode">0</property> 
</function>

partition-hash-int.txt 配置：

10000=0 
10010=1 DEFAULT_NODE=1 

columns 标识将要分片的表字段
algorithm 分片函数， 其中分片函数配置中，mapFile标识配置文件名称

6.5、范围分片

此分片适用于，提前规划好分片字段某个范围属于哪个分片

<tableRule name="auto-sharding-long"> 
	<rule> 
		<columns>user_id</columns> 
		<algorithm>rang-long</algorithm> 
	</rule> 
</tableRule> 

<function name="rang-long" class="org.opencloudb.route.function.AutoPartitionByLong"> 
	<property name="mapFile">autopartition-long.txt</property> 
	<property name="defaultNode">0</property> 
</function>

[root@mycat conf]# vim autopartition-long.txt 
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

columns 标识将要分片的表字段
algorithm 分片函数
rang-long 函数中mapFile代表配置文件路径 
defaultNode 超过范围后的默认节点。

测试：

[root@mycat conf]# cat schema.xml
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> 
	<table name="orders" dataNode="dn1,dn2" rule="auto-sharding-long"> 
	</table> 
</schema> 
<schema name="TESTDB2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"> 
</schema> 
<dataNode name="dn1" dataHost="node1" database="test" /> 
<dataNode name="dn2" dataHost="node2" database="test" /> 
<dataHost name="node1" maxCon="10" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="-1"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="master01" url="10.0.0.201:3306" user="root" password="mysql"> 
	</writeHost> 
</dataHost> 
<dataHost name="node2" maxCon="10" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="-1"> 
	<heartbeat>select user()</heartbeat> 
	<writeHost host="slave01" url="10.0.0.203:3306" user="root" password="mysql"> 
	</writeHost> 
</dataHost> 
</mycat:schema>

#配置分片范围
[root@mycat conf]# cat autopartition-long.txt 
# range start-end ,data node index
# K=1000,M=10000.
0-1000=0
1000-2000=1

#mycat插入数据测试
mysql> insert into orders(id,name) values(10,'a');
mysql> insert into orders(id,name) values(100,'a');
mysql> insert into orders(id,name) values(1000,'a');
mysql> insert into orders(id,name) values(10000,'a');
ERROR 1064 (HY000): can't find any valid datanode :ORDERS -> ID -> 10000
mysql> insert into orders(id,name) values(1001,'a');
mysql> insert into orders(id,name) values(1005,'a');
mysql> insert into orders(id,name) values(1020,'a')

#master上查看
master01-test]>select * from orders;
+------+------+
| id   | name |
+------+------+
|  100 | a    |
| 1000 | a    |
+------+------+

#slave上查看
slave01-[test]>select * from orders;
+------+------+
| id   | name |
+------+------+
| 1001 | a    |
| 1005 | a    |
| 1020 | a    |
+------+------+

6.6、取模分片

此规则为对分片字段求摸运算。

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

<function name="mod-long" class="org.opencloudb.route.function.PartitionByMod"> 
	<!-- how many data nodes --> 
	<property name="count">3</property> 
</function>
--------------------------------------------------------------------------------
columns 标识将要分片的表字段
algorithm 分片函数
此种配置非常明确即根据id进行十进制求模预算

6.7、自然月分片

按月份列分区 ，每个自然月一个分片，格式 between操作解析的范例

<tableRule name="sharding-by-month"> 
	<rule> 
		<columns>create_time</columns> 
		<algorithm>sharding-by-month</algorithm> 
	</rule> 
</tableRule> 

<function name="sharding-by-month" class="org.opencloudb.route.function.PartitionByMonth"> 
	<property name="dateFormat">yyyy-MM-dd</property> 
	<property name="sBeginDate">2014-01-01</property> 
</function> 

columns： 分片字段，字符串类型 
dateFormat ： 日期字符串格式 
sBeginDate ： 开始日期

6.8、Mycat分片原则

分表分库虽然能解决大表对数据库系统的压力，但它并不是万能的，也有一些不利之处，因此首要问题是，分不分库，分哪些库，什么规则分，分多少分片。

1）原则一：能不分就不分，1000万以内的表，不建议分片，通过合适的索引，读写分离等方式，可以很好的解决性能问题。

2）原则二：分片数量尽量少，分片尽量均匀分布在多个DataHost上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量。

3）原则三：分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容

4）原则四：尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题

5）原则五：查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会便利所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

七、Mycat通过日志查看语句路由

1）设置log4j2.xml文件

[root@mycat conf]# vim log4j2.xml
<asyncRoot level="debug" includeLocation="true">

2）重启mycat

[root@mycat conf]# ../bin/mycat restart

3）执行语句，查看mycat.log

image

八、Mycat架构部署

数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。 从数据库的角度来说，对于大多数应用来说，从集中到分布，最基本的一个需求不是数据存储的瓶颈，而是在于计算的瓶颈，即SQL查询的瓶颈，我们知道，正常情况下，Insert SQL就是几十个毫秒的时间内写入完成，而系统中的大多数Select SQL则要几秒到几分钟才能有结果，很多复杂的SQL，其消耗服务器CPU的能力超强，不亚于死循环的威力。在没有读写分离的系统上，很可能高峰时段的一些复杂SQL查询就导致数据库服务器CPU爆表，系统陷入瘫痪，严重情况下可能导致数据库崩溃。因此，从保护数据库的角度来说，我们应该尽量避免没有主从复制机制的单节点数据库。

1）对于MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取决于系统的压力，通常是1-3个读节点的配置。

image

2）MySQL支持更多的主从复制的拓扑关系，如下图所示，但通常我们不会采用双向主从同步以及环状的拓扑

image

3）常见架构-读写分离

image

MySQL节点开启主从复制的配置方案，并将主节点配置为Mycat的dataHost里的writeNode，从节点配置为readNode，同时Mycat内部定期对一个dataHost里的所有writeHost与readHost节点发起心跳检测

正常情况下，Mycat会将第一个writeHost作为写节点，所有的DML SQL会发送给此节点，若Mycat开启了读写分离，则查询节点会根据读写分离的策略发往readHost(+writeHost)执行

当一个dataHost里面配置了两个或多个writeHost的情况下，如果第一个writeHost宕机，则Mycat会在默认的3次心跳检查失败后，自动切换到下一个可用的writeHost执行DML SQL语句，并在conf/dnindex.properties文件里记录当前所用的writeHost的index（第一个为0，第二个为1，依次类推）

配置mysql端主从的数据自动同步，mycat不负责任何的数据同步问题。

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
     <heartbeat>select user()</heartbeat>
     <!-- can have multi write hosts -->
     <writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
         <!-- can have multi read hosts -->
         <readHost host="hostS1" url="localhost2:3306" user="root" password="123456" weight="1" />
     </writeHost>
</dataHost>

或者：

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
     <heartbeat>select user()</heartbeat>
     <!-- can have multi write hosts -->
     <writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
     </writeHost>
     <writeHost host="hostS1" url="localhost:3307" user="root" password="123456">
     </writeHost>
</dataHost>

以上两种：第一种当写挂了读不可用，第二种可以继续使用，事务内部的一切操作都会走写节点，所以读操作不要加事务

当你是1主3从的模式的时候，可以把第一个从节点配置为writeHost 2，第2个和第三个从节点则配置为writeHost 1的readHost，如下所示：

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native"> 
<heartbeat>select user()</heartbeat> 
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456" > 
	<readHost host="hostS2" url="localhost3:3306" user="root" password="123456" /> 
	<readHost host="hostS3" url="localhos4t:3306" user="root" password="123456" /> 
</writeHost> 
<writeHost host="hostS1" url="localhost2:3306" user="root" password="123456" /> 
</dataHost>

4）应用强制走写

一个查询SQL语句以/*balance*/注解来确定其是走读节点还是写节点。常用于异步复制 
1.6以后添加了强制走读走写处理： 

#强制走从： 
/*!mycat:db_type=slave*/ select * from travelrecord 
/*#mycat:db_type=slave*/ select * from travelrecord 

#强制走写： 
/*#mycat:db_type=master*/ select * from travelrecord 
/*!mycat:db_type=master*/ select * from travelrecord

5）主从延时切换

1.4开始支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

MyCAT心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与 slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制，Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延， 当Seconds_Behind_Master>slaveThreshold时，读写分离筛选器会过滤掉此Slave机器，防止读到很久之前的旧数据，而当主节点宕机后，切换逻辑会检查Slave上的Seconds_Behind_Master是否为0，为0时则表示主从同步，可以安全切换，否则不会切换。

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100"> 
	<heartbeat>show slave status </heartbeat> 
	<!-- can have multi write hosts --> 
	<writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> 
	</writeHost> 
	<writeHost host="hostS1" url="localhost:3316" user="root" password="123456"> 
	</writeHost> 
</dataHost>

6） 官方建议是采用基于硬件的负载均衡器或者软件方式的HAproxy，HAProxy相比LVS的使用要简单很多，功能方面也很丰富，免费开源，稳定性也是非常好，可以与LVS相媲美

image

7）如果还担心HAproxy的稳定性和单点问题，则可以用keepalived的VIP的浮动功能，加以强化

image

8）数据库集群架构

image

九、Mycat管理

1）MyCAT自身有类似其他数据库的管理监控方式，可以通过Mysql命令行，登录管理端口（9066）执行相应的SQL进行管理

2）登录：目前mycat有两个端口，8066 数据端口，9066 管理端口，命令行的登陆是通过9066 管理端口来操作，登录方式类似于mysql的服务端登陆。

mysql -h127.0.0.1 -utest -ptest -P9066 [-dmycat]

-h 后面是主机，即当前mycat按照的主机地址，本地可用127.0.0.1 远程需要远程ip
-u Mycat server.xml中配置的逻辑库用户
-p Mycat server.xml中配置的逻辑库密码
-P 后面是端口 默认9066，注意P 是大写
-d Mycat server.xml中配置的逻辑库

3） 通过show @@help;

可以查看所有的命令，如下

mysql> show @@help;
+------------------------------------------+--------------------------------------------+
| STATEMENT                                | DESCRIPTION                                |
+------------------------------------------+--------------------------------------------+
| show @@time.current                      | Report current timestamp                   |
| show @@time.startup                      | Report startup timestamp                   |
| show @@version                           | Report Mycat Server version                |
| show @@server                            | Report server status                       |
| show @@threadpool                        | Report threadPool status                   |
| show @@database                          | Report databases                           |
| show @@datanode                          | Report dataNodes                           |
| show @@datanode where schema = ?         | Report dataNodes                           |
| show @@datasource                        | Report dataSources                         |
| show @@datasource where dataNode = ?     | Report dataSources                         |
| show @@datasource.synstatus              | Report datasource data synchronous         |
| show @@datasource.syndetail where name=? | Report datasource data synchronous detail  |
| show @@datasource.cluster                | Report datasource galary cluster variables |
| show @@processor                         | Report processor status                    |
| show @@command                           | Report commands status                     |
| show @@connection                        | Report connection status                   |
| show @@cache                             | Report system cache usage                  |
| show @@backend                           | Report backend connection status           |
| show @@session                           | Report front session details               |
| show @@connection.sql                    | Report connection sql                      |
| show @@sql.execute                       | Report execute status                      |
| show @@sql.detail where id = ?           | Report execute detail status               |
| show @@sql                               | Report SQL list                            |
| show @@sql.high                          | Report Hight Frequency SQL                 |
| show @@sql.slow                          | Report slow SQL                            |
| show @@sql.resultset                     | Report BIG RESULTSET SQL                   |
| show @@sql.sum                           | Report  User RW Stat                       |
| show @@sql.sum.user                      | Report  User RW Stat                       |
| show @@sql.sum.table                     | Report  Table RW Stat                      |
| show @@parser                            | Report parser status                       |
| show @@router                            | Report router status                       |
| show @@heartbeat                         | Report heartbeat status                    |
| show @@heartbeat.detail where name=?     | Report heartbeat current detail            |
| show @@slow where schema = ?             | Report schema slow sql                     |
| show @@slow where datanode = ?           | Report datanode slow sql                   |
| show @@sysparam                          | Report system param                        |
| show @@syslog limit=?                    | Report system mycat.log                    |
| show @@white                             | show mycat white host                      |
| show @@white.set=?,?                     | set mycat white host,[ip,user]             |
| show @@directmemory=1 or 2               | show mycat direct memory usage             |
| switch @@datasource name:index           | Switch dataSource                          |
| kill @@connection id1,id2,...            | Kill the specified connections             |
| stop @@heartbeat name:time               | Pause dataNode heartbeat                   |
| reload @@config                          | Reload basic config from file              |
| reload @@config_all                      | Reload all config from file                |
| reload @@route                           | Reload route config from file              |
| reload @@user                            | Reload user config from file               |
| reload @@sqlslow=                        | Set Slow SQL Time(ms)                      |
| reload @@user_stat                       | Reset show @@sql  @@sql.sum @@sql.slow     |
| rollback @@config                        | Rollback all config from memory            |
| rollback @@route                         | Rollback route config from memory          |
| rollback @@user                          | Rollback user config from memory           |
| reload @@sqlstat=open                    | Open real-time sql stat analyzer           |
| reload @@sqlstat=close                   | Close real-time sql stat analyzer          |
| offline                                  | Change MyCat status to OFF                 |
| online                                   | Change MyCat status to ON                  |
| clear @@slow where schema = ?            | Clear slow sql by schema                   |
| clear @@slow where datanode = ?          | Clear slow sql by datanode                 |
+------------------------------------------+--------------------------------------------+

3） reload @@config_all;

该命令用于更新配置文件，例如更新schema.xml文件后在命令行窗口输入该命令，可不用重启即进行配置文件更新

mysql> reload @@config_all;
Query OK, 1 row affected (0.29 sec)
Reload config success

4）show @@database：

显示MyCAT的数据库的列表，对应schema.xml配置文件的schema子节点

mysql> show @@database;
+----------+
| DATABASE |
+----------+
| TESTDB   |
| TESTDB2  |
+----------+

5）show @@datanode：

显示MyCAT的数据节点的列表，对应schema.xml配置文件的dataNode节点

mysql> show @@datanode;
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST    | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | node1/test |     0 | mysql |      0 |    5 |   10 |      16 |          0 |        0 |       0 |            -1 |
| dn2  | node2/test |     0 | mysql |      0 |    5 |   10 |      16 |          0 |        0 |       0 |            -1 |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+

"NAME"表示dataNode的名称；
"dataHost"表示对应dataHost属性的值，即数据主机；
"ACTIVE"表示活跃连接数；
"IDLE"表示闲置连接数；
"SIZE"对应总连接数量。

6）show @@heartbeat：

命令用于报告心跳状态

mysql> show @@heartbeat;
+----------+-------+------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME     | TYPE  | HOST       | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+----------+-------+------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| master01 | mysql | 10.0.0.201 | 3306 |       1 |     0 | idle   |       0 | 4,3,3        | 2019-10-12 14:55:24 | false |
| slave01  | mysql | 10.0.0.203 | 3306 |       1 |     0 | idle   |       0 | 3,2,2        | 2019-10-12 14:55:24 | false |
+----------+-------+------------+------+---------+-------+--------+---------+--------------+---------------------+-------+

#RS_CODE 状态：
  OK_STATUS = 1;正常状态
  ERROR_STATUS = -1; 连接出错
  TIMEOUT_STATUS = -2;连接超时
  INIT_STATUS = 0; 初始化状态
#若节点故障，会连续默认5个周期检测，心跳连续失败，就会变成-1，节点故障确认，然后可能发生切换

7）show @@connection：

获取Mycat的前端连接状态，即应用与mycat的连接

mysql> show @@connection;
+------------+------+-----------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| PROCESSOR  | ID   | HOST      | PORT | LOCAL_PORT | USER | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit |
+------------+------+-----------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor0 |    2 | 127.0.0.1 | 9066 |      46632 | root | NULL   | utf8:33 |    309 |    6137 |           540 |        4096 |          0 |         |            |
+------------+------+-----------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+

8）kill @@connection id,id,id ：

用于杀掉连接

9）show @@cache;：

查看mycat缓存。

mysql> show @@cache;
+-------------------------------------+-------+------+--------+------+------+-------------+----------+
| CACHE                               | MAX   | CUR  | ACCESS | HIT  | PUT  | LAST_ACCESS | LAST_PUT |
+-------------------------------------+-------+------+--------+------+------+-------------+----------+
| ER_SQL2PARENTID                     |  1000 |    0 |      0 |    0 |    0 |           0 |        0 |
| SQLRouteCache                       | 10000 |    0 |      0 |    0 |    0 |           0 |        0 |
| TableID2DataNodeCache.TESTDB_ORDERS | 50000 |    0 |      0 |    0 |    0 |           0 |        0 |
+-------------------------------------+-------+------+--------+------+------+-------------+----------+

SQLRouteCache：sql路由缓存。
TableID2DataNodeCache ： 缓存表主键与分片对应关系。
ER_SQL2PARENTID ： 缓存ER分片中子表与父表关系。

10）show @@datasource;

查看数据源状态，如果配置了主从，或者多主可以切换。

mysql> show @@datasource; 
+----------+----------+-------+------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME     | TYPE  | HOST       | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+----------+-------+------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | master01 | mysql | 10.0.0.201 | 3306 | W    |      0 |    5 |   10 |      79 |         0 |          0 |
| dn2      | slave01  | mysql | 10.0.0.203 | 3306 | W    |      0 |    5 |   10 |      79 |         0 |          0 |
+----------+----------+-------+------------+------+------+--------+------+------+---------+-----------+------------+

11）switch @@datasource name:index

切换数据源，

  • name：schema中配置的dataHost 中name。
  • index：schema中配置的dataHost 的writeHost index 位标，即按照配置顺序从上到下的一次顺 序，从0开始。
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/hujinzhong/p/11656204.html