Mycat 实现 MySQL 读写分离

目前MyCat配置Mysql 读写分离的文章虽然很多，但是看起来比较杂乱，造成很多困扰，所以自己根据实践并采用新的Mycat 版本将搭建步骤自始至终重写一遍。

在根据本文档操作前，基于服务器的环境已经配置了Mysql 的主从复制模式，若没有配置参考如下文章：

https://www.cnblogs.com/ryxiong-blog/p/12513383.html

1.环境介绍

主服务器（master）
mycat服务器

IP：10.16.195.49
系统：Linux（redhat centos7 64）
mysql版本：8.0.17
同步数据库：AIDCDATA, AIHsMdlSrv
同步帐号：model_slave
同步密码：slave123

从服务器（slave）两台

IP：10.16.195.50/10.16.195.51
系统：Linux（redhat centos7 64）
mysql版本：8.0.17
同步数据库：AIDCDATA, AIHsMdlSrv

2.mycat安装

1.安装java环境

mycat依赖于java环境，下载jdk：http://dl.mycat.io/jdk-8u20-linux-x64.tar.gz

# 解压到/usr/local
tar -zxcf jdk-8u20-linux-x64.tar.gz /usr/local
mv jdk-8u20-linux-x64 jdk

添加环境变量

sudo vim /etc/profile
export JDK_HOME=/usr/local/jdk
export PATH=%PATH:$JDK_HOME/bin

# 保存退出
source /etc/profile

2.mycat安装

下载mycat安装包：http://www.mycat.io/

注意下载release版linux系统的安装包

# 解压到/usr/local
tar -zxcf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz /usr/local
# 解压后文件名就是mycat

配置环境变量

sudo vim /etc/profile
export MYCAT_HOME=/usr/local/mycat
export PATH=%PATH:$MYCAT_HOME/bin

# 保存退出
source /etc/profile

3.mycat配置详解

mycat核心配置文件有三个：

server.xml：定义用户以及系统相关变量，如端口等
Schema.xml：定义逻辑库，表、分片节点等内容
rule.xml：定义分片规则

server.xml配置详解

1.user标签--用户配置节点

mycat提供给用户登录的节点

<!-- 登陆用户名, 即MyCat连接用户名 -->

<!-- 写入帐号的配置 -->
<user name="mycat_writer">
    <!-- 登陆密码, 即MyCat连接密码 -->
	<property name="password">123456</property>
    <!-- 数据库名, 即MyCat的逻辑库, 此处会与schema.xml的配置存在关联 -->
	<property name="schemas">AIDCDATA,AIHsMdlSrv</property>
	
	<!-- 表级 DML 权限设置, 对数据库权限进行精细化管理 -->
    <!-- check表示是否开启权限检查, 默认为false, 表示不检查 -->
	<privileges check="false">
        <!-- 一个schema表示对一个数据库的权限管理, dml顺序:Insert, Update, Select, Delete -->
		<schema name="db_user" dml="0110" >
            <!-- 一个table表示对某张表进行权限管理 -->
			<table name="users" dml="1111"></table>
			<table name="useraddres" dml="1110"></table>
		</schema>
	</privileges>
</usr>
<user name="mycat_reader">
		<property name="password">123456</property>
		<property name="schemas">AIDCDATA,AIHsMdlSrv</property>
    <property name="readOnly">true</property>
</user>

2.system标签 -- 系统配置节点

默认端口8066，默认管理端口9066

<system>
    <!-- 字符集 -->
    <property name="charset">utf8</property> 
    <!-- 是否开启实时统计 1:开启, 0:关闭-->
	<property name="useSqlStat">0</property>
	<!-- 是否开启全局表一致性检测。1为开启；0为关闭 -->
	<property name="useGlobleTableCheck">0</property>
	<!-- mycat 模拟的 mysql 版本号，默认值为 5.6 版本，如非特需，不要修改这个值，目前支持设置 5.5,5.6,5.7 版本，其他版本可能会有问题 -->
    <property name="fakeMySQLVersion">5.6.20</property>
    <!-- 每次读取流的数量, 默认为4096 -->
	<property name="processorBufferChunk">40960</property>
    <!-- 处理线程数量, 默认为cpu核数 -->
	<property name="processors">1</property> 
    <!-- 创建共享buffer需要占用的总空间大小 -->
    <!-- 值计算方式: processors * processorBufferChunk * 100-->
    <property name="processorBufferPool">4096000</property>
	<!-- 服务线程池大小 -->
	<property name="processorExecutor">32</property> 
    <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
	<property name="processorBufferPoolType">0</property>
    <!-- 二级共享buffer是processorBufferPool的百分比，这里设置的是百分比 -->
    <property name="processorBufferLocalPercent">100</property>
    <!-- 清理 NIOProcessor 上前后端空闲、超时和关闭连接的间隔时间。默认是 1 秒，单位毫秒 -->
    <property name="processorCheckPeriod">1000</property>
    <!--默认是65535 64K 用于sql解析时最大文本长度 -->
	<property name="maxStringLiteralLength">65535</property>
    <!-- 全局ID生成方式。(0:为本地文件方式，1:为数据库方式；2:为时间戳序列方式；3:为ZK生成ID；4:为ZK递增ID生成-->
	<property name="sequnceHandlerType">0</property>
    <!-- 是否开启mysql压缩协议。1为开启，0为关闭，默认关闭 -->
    <property name="useCompression">1</property>
    <!-- 指定 Mysql 协议中的报文头长度。默认 4 -->
    <property name="packetHeaderSize">4</property> 
    <!-- 指定 Mysql 协议可以携带的数据最大长度。默认 16M -->
    <property name="maxPacketSize">16M</property> 
	<property name="backSocketNoDelay">1</property>
	<property name="frontSocketNoDelay">1</property>
	<property name="processorExecutor">16</property>
    <!-- 指定连接的空闲超时时间。某连接在发起空闲检查下，发现距离上次使用超过了空闲时间，那么这个连接会被回收，就是被直接的关闭掉。默认 30 分钟，单位毫秒 -->
	<property name="idleTimeout">1800000</property> 
    <!-- 前端连接的初始化事务隔离级别，只在初始化的时候使用，后续会根据客户端传递过来的属性对后端数据库连接进行同步。默认为 REPEATED_READ，设置值为数字默认 3 -->
    <!-- READ_UNCOMMITTED=1;READ_COMMITTED=2;REPEATED_READ=3;SERIALIZABLE=4; -->
    <property name="txIsolation">3</property>
    <!-- SQL 执行超时的时间，Mycat 会检查连接上最后一次执行 SQL 的时间，若超过这个时间则会直接关闭这连接。默认时间为 300 秒，单位秒 -->
    <property name="sqlExecuteTimeout">300</property>
	<!-- 对后端连接进行空闲、超时检查的时间间隔，默认是 300 秒，单位毫秒 -->
	<property name="dataNodeIdleCheckPeriod">300000</property>
	<!-- 对后端所有读、写库发起心跳的间隔时间，默认是 10 秒，单位毫秒 -->
	<property name="dataNodeHeartbeatPeriod">10000</property>
	<!-- mycat 服务监听的 IP 地址，默认值为 0.0.0.0 -->
    <property name="bindIp">0.0.0.0</property> 
	<!-- 定义 mycat 的使用端口，默认值为 8066 -->
	<property name="serverPort">8066</property> 
	<!-- 定义 mycat 的管理端口，默认值为 9066 -->
    <property name="managerPort">9066</property> 
	<property name="frontWriteQueueSize">4096</property> 
	<!-- 分布式事务开关。0为不过滤分布式事务；1为过滤分布式事务；2 为不过滤分布式事务,但是记录分布式事务日志 -->
	<property name="handleDistributedTransactions">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>
	<!-- 是否采用zookeeper协调切换 -->
	<property name="useZKSwitch">true</property>
</system>

3.firewall标签 -- 防火墙配置节点

<!-- 白名单和黑名单一般只对一个进行设置 -->
<firewall> 
	<!-- 白名单 -->
	<whitehost>
		<host host="127.0.0.1" user="mycat"/>
		<host host="127.0.0.2" user="mycat"/>
	</whitehost>
	<!-- 黑名单 -->
	<blacklist check="false">
	</blacklist>
</firewall>

4.本案例中server.xml配置文件

只修改用户配置部分，其他默认。

<user name="mycat_writer">
	<property name="password">123456</property>
	<property name="schemas">AIDCDATA,AIHsMdlSrv</property>
</usr>
<user name="mycat_reader">
		<property name="password">123456</property>
		<property name="schemas">AIDCDATA,AIHsMdlSrv</property>
    <property name="readOnly">true</property>
</user>

schema.xml配置详解

1.schema标签 -- mycat逻辑库相关配置

注意：

schema是mycat服务中会生成的逻辑库，是虚拟的。<table>是真实的表明。
一个逻辑库<schema>可以对应多个真实数据库，通过dataNode来指定，dn1,dn2,dn3。
name与server.xml中schemas要对应
如果<schema>中不指定<table>属性的话，需要指定dataNode属性，<schema name="db_store" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1,dn2" />

<!-- 数据库设置，此数据库为逻辑数据库，name与server.xml中schemas对应 -->
<!-- schema : 定义mycat实例中的逻辑库，也就是mycat服务中有数据库，mycat可以有多个逻辑库，每个逻辑库都有自己的相关配置 -->
<!-- name : 逻辑数据库名，与server.xml中的schemas对应-->
<!-- checkSQLschema	: 数据库前缀相关设置，当该值为true时，例如我们执行语句select * from db_store.company 。mycat会把语句修改为 select * from company 去掉db_store -->
<!-- sqlMaxLimit : 当该值设置为某个数值时，每条执行的sql语句，如果没有加上limit语句，Mycat会自动加上对应的值。不写的话，默认返回所有的值。
* 需要注意的是，如果运行的schema为非拆分库的，那么该属性不会生效。需要自己sql语句加limit -->
<!-- 如果schema中没有table配置，需要指定dataNode属性 -->
<schema name="db_store" checkSQLschema="false" sqlMaxLimit="100">
	<table name="store" dataNode="db_store_dataNode" primaryKey="storeID"/>
	<table name="employee" dataNode="db_store_dataNode" primaryKey="employeeID"/>
</schema>
<schema name="db_user" checkSQLschema="false" sqlMaxLimit="100">
	<table name="data_dictionary" type="global" dataNode="db_user_dataNode1,db_user_dataNode2" primaryKey="dataDictionaryID"/>
	<!-- name : 表名, 物理数据库中表名 -->
	<!-- dataNode : 表存在节点, 多个节点用","隔开, 连续节点通过"$"进行连接处理, 如下 -->
	<!-- primaryKey	: 主键字段名，自动生成主键时需要设置 -->
	<!-- autoIncrement : 是否自增
		* mysql对非自增长主键，使用last_insert_id() 是不会返回结果的，只会返回0.所以，只有定义了自增长主键的表，才可以用last_insert_id()返回主键值
		* mycat提供了自增长主键功能，但是对应的mysql节点上数据表，没有auto_increment,那么在mycat层调用last_insert_id()也是不会返回结果的 -->
	<!-- rule : 分片规则名, 具体在rule.xml解析 -->
	<!-- type : 该属性定义了逻辑表的类型，目前逻辑表只有全局表和普通表。全局表： global 普通表：无 -->
	<!-- needAddLimit : 指定表是否需要自动的在每个语句后面加上limit限制，由于使用了分库分表，数据量有时候会特别庞大，这时候执行查询语句，忘记加上limt就会等好久，所以mycat自动为我们加上了limit 100，这个属性默认为true，可以自己设置为false禁用。如果使用这个功能，最好配合使用数据库模式的全局序列 -->
	<!-- subTables : 分表, 分表目前不支持join -->
	<table name="users" dataNode="db_user_dataNode$1-2"  rule="mod-userID-long" primaryKey="userID">
		<!-- childTable : 定义ER分片的子表, 通过属性与父表进行关联 -->
		<!-- name : 子表(物理表)名称 -->
		<!-- joinKey : 子父表关联字段在子表的名称 -->
		<!-- parentKey : 子父表关联字段在父表的名称 -->
		<childTable name="user_address"  joinKey="userID" parentKey="userID" primaryKey="addressID"/>
	</table>
</schema>

2.dataNode标签：分片分库信息相关配置

<!-- 节点配置 : 分片信息，也就是分库相关配置-->
<!-- dataNode : 定义了mycat中的数据节点，也就是我们所说的数据分片。一个datanode标签就是一个独立的数据分片
	* 如下表述的意思为，使用名字为db_storeHOST数据库实例上的db_store物理数据库，这就组成一个数据分片，最后我们用db_store_dataNode来标示这个分片-->
<!-- name : 定义数据节点的唯一名称, 在table标签上用这个名字建立表与分片对应的关系 -->
<!-- dataHost : 用于定义该分片属于哪个数据库实例，属性与datahost标签上定义的name对应 -->
<!-- database : 用于定义该分片属于数据库实例上的物理库，也就是真实的数据库名 -->
<dataNode name="db_store_dataNode" dataHost="db_storeHOST" database="db_store" />
<dataNode name="db_user_dataNode1" dataHost="db_userHOST1" database="db_user" />
<dataNode name="db_user_dataNode2" dataHost="db_userHOST2" database="db_user" />

3.dataHost标签：mysql物理数据库相关配置

<!-- 节点主机配置 : 物理数据库，真正存储数据的数据库 -->
<!-- 这个标签直接定义了具体数据库实例，读写分离配置和心跳语句 -->
<!-- name : 唯一标示dataHost标签，供上层dataNode使用 -->
<!-- maxCon : 指定每个读写实例连接池的最大连接 -->
<!-- minCon : 指定每个读写实例连接池的最小连接 -->
<!-- balance : 负载均称类型
	* 0 : 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
	* 1 : 全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式（M1-S1，M2-S2 并且M1 M2互为主备），正常情况下，M2,S1,S2都参与select语句的负载均衡
	* 2 : 所有读操作都随机的在writeHost、readHost上分发
	* 3 : 所有读请求随机的分发到writeHst对应的readHost执行，writeHost不负担读写压力 -->
<!-- writeType : 负载均衡类型
	* 0 : 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties
	* 1 : 所有写操作都随机的发送到配置的 writeHost。1.5以后版本废弃不推荐 -->
<!-- switchType	: 节点切换方式
	* -1 : 不自动切换
	*  1 : 默认值 自动切换
	*  2 : 基于MySql主从同步的状态决定是否切换心跳语句为 show slave status
	*  3 :  基于mysql galary cluster 的切换机制（适合集群）1.4.1 心跳语句为 show status like 'wsrep%'-->
<!-- dbType : 指定后端链接的数据库类型目前支持二进制的mysql协议，还有其他使用jdbc链接的数据库，例如：mongodb，oracle，spark等 -->
<!-- dbDriver : 指定连接后段数据库使用的driver，目前可选的值有native和JDBC。使用native的话，因为这个值执行的是二进制的mysql协议，所以可以使用mysql和maridb，其他类型的则需要使用JDBC驱动来支持; 如果使用JDBC的话需要符合JDBC4标准的驱动jar 放到mycat\lib目录下 -->
<!-- tempReadHostAvailable : 如果配置了这个属性 writeHost 下面的 readHost 仍旧可用，默认 0 可配置（0、1） -->
<dataHost name="db_storeHOST" maxCon="1000" minCon="10" balance="1"
		  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
	<!-- 指明用于和后端数据库进行心跳检查的语句 -->
	<!-- mysql : 可以用select user()-->
	<!-- oracle : 可以用select 1 from dual-->
	<heartbeat>select user()</heartbeat>
	<!-- readHost/writeHost : 这两个标签都指定后端数据库的相关配置，用于实例化后端连接池。唯一不同的是，writeHost 指定写实例、readHost 指定读实例 -->
	<!-- host : 标识不同的示例, 一般 writeHost 我们使用*M1，readHost 我们用*S1 -->
	<!-- url : 后端实例连接地址。
		* Native：地址：端口
		* JDBC：jdbc的url -->
	<!-- user : 后端存储实例需要的用户名称 -->
	<!-- password : 后端存储实例需要的密码 -->
	<!-- weight : 权重, 配置在 readhost 中作为读节点的权重 -->
	<!-- usingDecrypt : 是否对密码加密，默认0(不加密)。具体加密方法看官方文档 -->
	<writeHost host="hostM1" url="192.168.8.137:3306" user="root"  password="123456">
		<readHost host="hostS1" url="192.168.8.101:3306" user="root" password="123456" />
	</writeHost>
</dataHost>
<dataHost name="db_userHOST1" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<writeHost host="userHost1" url="192.168.8.137:3306" user="root"  password="123456">
	</writeHost>
</dataHost>
<dataHost name="db_userHOST2" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<writeHost host="userHost2" url="192.168.8.101:3306" user="root"  password="123456">
	</writeHost>
</dataHost>

4.该案例中schema.xml配置

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

<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="AIDCDATA" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" />
    <schema name="AIHsMdlSrv" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2" />

    <dataNode name="dn1" dataHost="localhost" database="backend" />
    <dataNode name="dn2" dataHost="localhost" database="AIHsMdlSrv" />

    <dataHost name="localhost" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- 添加一个写入库配置 -->
        <writeHost host="hostM1" url="10.16.195.49:3306" user="root" password="mysql123">
        <!-- 添加两个只读库配置-->
        <readHost host="hostS1" url="10.16.195.50:3306" user="root" password="mysql123" />
        <readHost host="hostS2" url="10.16.195.51:3306" user="root" password="mysql123" />
        </writeHost>

    </dataHost>
     
</mycat:schema>

rule.xml配置详解

1.tableRule标签 -- 表拆分规则

<!-- 定义表拆分规则 -->
<!-- name : 属性指定唯一的名字，用于标识不同的表规则, 与scheme.xml中的rule属性对应 -->
<tableRule name="sharding-by-intfile">
	<rule>
		<!-- 指定要拆分的列名字 -->
		<columns>sharding_id</columns>
		<!-- 表拆分算法, 与function的name对应; 连接表规则和具体路由算法 -->
		<algorithm>hash-int</algorithm>
	</rule>
</tableRule>

2.function标签 -- 分片规则

<!-- name : 指定算法名称 -->
<!-- class : 指定算法具体实现的类名称 -->
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
	<!-- 具体算法需要的属性文件, 该文件在mycat文件的conf路径下 -->
	<property name="mapFile">partition-hash-int.txt</property>
</function>

3.分片规则种类

连续分片之自定义数字范围分片，0-2标识节点为数据库节点，取在schema中的节点配置顺序；该配置表示按照索引进行数据分片，索引0-5000000分配在0节点，5000000-10000000分配到1节点，以此类推

<function name="rang-long"
		class="io.mycat.route.function.AutoPartitionByLong">
	<property name="mapFile">autopartition-long.txt</property>
    <!-- 超过范围后的默认节点 -->
	<property name="defaultNode">0</property> 
</function>

2018120922271111

连续分片之按时间（月，天，小时）分片

<!-- 按日期（天）分片： 从开始日期算起，按照天数来分片 -->
<function name=“sharding-by-date” class=“io.mycat.route.function.PartitionByDate">
	<property name=“dateFormat”>yyyy-MM-dd</property>      <!—日期格式-->
	<property name=“sBeginDate”>2014-01-01</property>            <!—开始日期-->
	<property name=“sPartionDay”>10</property> <!—每分片天数-->
</function>
 
<!-- 按日期（自然月）分片： 从开始日期算起，按照自然月来分片 -->
<function name=“sharding-by-month” class=“io.mycat.route.function.PartitionByMonth">
	<property name=“dateFormat”>yyyy-MM-dd</property>        <!—日期格式-->
	<property name=“sBeginDate”>2014-01-01</property>            <!—开始日期-->
</function>
 
<!-- 按月小时进行分片, 最多24个分片, 最少1个分片, 一个月完成后从头开始循环, 每个月月尾, 需要手动清理备份数据 -->
<function name="sharding-by-hour" class=“io.mycat.route.function.LatestMonthPartion"> 
	<property name=“splitOneDay”>24</property> <!-- 将一天的数据拆解成几个分片-->
</function>

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

<function name="hash-int" class=“io.mycat.route.function.PartitionByFileMap">
    <!-- 配置文件名称 -->
 	<property name="mapFile">partition-hash-int.txt</property> 
    <!-- type默认值为0（0表示Integer，非零表示String） -->
	<property name="type">0</property> 
    <!-- 默认节点的作用：枚举分片时，如果碰到不识别的枚举值，就让它路由到默认节点 -->
	<property name="defaultNode">0</property> 
</function>

20181209223640111

十进制求模分片：规则为对分片字段十进制取模运算，数据分布最均匀

<function name="mod-long" class=“io.mycat.route.function.PartitionByMod"> 
	<!-- 参与分片的节点数量  --> 
	<property name="count">3</property> 
</function>

离散分片之应用指定分片 -- 规则为对分片字段进行字符串截取，获取的字符串即指定分片

例如 id=05-100000002 在此配置中代表根据 id 中从startIndex=0 开始，截取 size=2 位数字即 05，05 就是获取的分区，如果没传默认分配到 defaultPartition

<function name="sharding-by-substring“ class="io.mycat.route.function.PartitionDirectBySubString">
    <!-- 开始截取的位置 -->
	<property name="startIndex">0</property>
    <!-- 截取的长度 -->
	<property name="size">2</property>
    <!-- 分片数量 -->
	<property name="partitionCount">8</property>
    <!-- 默认分片 -->
	<property name="defaultPartition">0</property>
</function>

离散分片之字符串截取数字hash分片 -- 此规则是截取字符串中的int数值hash分片

<function name="sharding-by-stringhash" class=“io.mycat.route.function.PartitionByString"> 
    <!-- 字符串hash求模基数, count表示分片数量  lenth * count = 1024 -->
	<property name=length>512</property>
	<property name="count">2</property> 
    <!--hash预算位, 没搞懂...-->
	<property name="hashSlice">0:2</property>
</function>

离散分片之一致性Hash分片 -- 此规则优点在于扩容时迁移数据量比较少

<function name="murmur" class=“io.mycat.route.function.PartitionByMurmurHash"> 
    <!-- 创建hash对象的种子，默认0--> 
	<property name=“seed”>0</property>
    <!-- 要分片的数据库节点数量，必须指定，否则没法分片--> 
	<property name="count">2</property>
    <!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
	<property name="virtualBucketTimes">160</property>
</ function>

综合分片之范围求模分片
- 先进行范围分片计算出分片组，组内再求模，可以进行范围分片到0-200M，再组内分片到5个分片其中之一
- 可以避免扩容时的数据迁移，又可以一定程度上避免范围分片的热点问题
- 分片组内使用求模可以保证组内数据比较均匀，分片组之间是范围分片可以兼顾范围查询

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

以下配置一个范围代表一个分片组，=号后面的数字代表该分片组所拥有的分片的数量

综合分片之取模范围约束分片 -- 对指定分片列进行取模后再由配置决定数据的节点分布
- 1-32 即代表id%256后分布的范围，=后面的数字表示分配的节点
- 如果id非数字，则分配在defaoultNode默认节点

<function name="sharding-by-pattern" class=“io.mycat.route.function.PartitionByPattern">
    <!-- 求模基数 -->
 	<property name="patternValue">256</property>
 	<property name="defaultNode">2</property> 
	<property name="mapFile">partition-pattern.txt</property> 
 </function>

20181209225242710

4.该案例中没有涉及拆分表的操作，没有配置rule.xml

4.Mycat启动

主从mysql数据库中都需要新建scheme.xml配置中的数据库用户。案例使用的是root。

1.启动mycat服务

/usr/local/mycat/bin/mycat start
# 配置了环境变量
mycat start

2.查看错误日志

cat /usr/local/mycat/logs/wrapper.log

看到这个表示成功启动

3.mycat登录

如果在server.xml中没有配置serverPort，默认连接端口为8066

# 登录mycat服务
mysql -h 127.0.0.1 -umycat_writer -pmycat_writer -P8066

可以在mysql控制台执行sql命令

5.Mycat 管理命令与监控

管理命令

1.登录方式

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

8066 数据端口默认
9066 管理端口默认

命令行的登陆是通过 9066 管理端口来操作,登录方式类似于 mysql 的服务端登陆。

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

管理端口用于执行管理命令：

mysql -h127.0.0.1 -umycat_writer -pmycat_writer -P9066

命令端口用户执行增删改查等 SQL 语句：

mysql -h127.0.0.1 -umycat_writer -pmycat_writer -P8066

2.管理命令

从9066 管理端口登陆后，执行 show @@help 可以查看到所有命令

mysql> show @@help;
+-------------------------------------+--------------------------------------------+
| STATEMENT                           | DESCRIPTION                                |
+-------------------------------------+--------------------------------------------+
| show @@time.current                 | Report current timestamp                   |
| show @@time.startup                 | Report startup timestamp                   |
														·
														·
| online                              | Change MyCat status to ON                  |
| clear @@slow where schema = ?       | Clear slow sql by schema                   |
| clear @@slow where datanode = ?     | Clear slow sql by datanode                 |
+-------------------------------------+--------------------------------------------+
58 rows in set (0.00 sec)

3.查看mycat版本

mysql> show @@version;
+-----------------------------------------+
| VERSION                                 |
+-----------------------------------------+
| 5.6.29-mycat-1.6-RELEASE-20161028204710 |
+-----------------------------------------+
1 row in set (0.00 sec)

4.查看当前的库

mysql> show @@database;
+----------+
| DATABASE |
+----------+
| testdb   |
+----------+
1 row in set (0.00 sec)

5.查看 MyCAT 的数据节点的列表,对应 schema.xml 配置文件的 dataNode 节点:

mysql> show @@datanode;
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST    | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | vm3306/db1 |     0 | mysql |      0 |    8 | 1000 |     244 |          0 |        0 |       0 |            -1 |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
1 row in set (0.00 sec)

其中,“NAME”表示 dataNode 的名称;“dataHost”表示对应 dataHost 属性的值,即数据主机; “ACTIVE”表示活跃连接数;“IDLE”表示闲置连接数;“SIZE”对应总连接数量。

这里有 8 个空闲连接，去主从节点用 netstat -ntp 命令看看建立的连接情况：

master:

[root@vm1 ~]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 10.16.195.49            192.168.0.104:60060         ESTABLISHED 1492/sshd
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.121:58636  ESTABLISHED 1414/mysqld
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.121:58640  ESTABLISHED 1414/mysqld
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.121:58582  ESTABLISHED 1414/mysqld
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.121:58644  ESTABLISHED 1414/mysqld
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.121:58646  ESTABLISHED 1414/mysqld
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.121:58641  ESTABLISHED 1414/mysqld
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.121:58635  ESTABLISHED 1414/mysqld
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.121:58632  ESTABLISHED 1414/mysqld
tcp        0      0 ::ffff:10.16.195.49:3306   ::ffff:192.168.0.122:48205  ESTABLISHED 1414/mysqld

slave:

[root@vm3 ~]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 10.16.195.50:48205         192.168.0.120:3306          ESTABLISHED 1607/mysqld
tcp        0      0 10.16.195.50:22            192.168.0.104:60102         ESTABLISHED 1196/sshd
tcp        0      0 ::ffff:10.16.195.50:3306   ::ffff:192.168.0.121:45593  ESTABLISHED 1607/mysqld
tcp        0      0 ::ffff:10.16.195.50:3306   ::ffff:192.168.0.121:45591  ESTABLISHED 1607/mysqld
tcp        0      0 ::ffff:10.16.195.50:3306   ::ffff:192.168.0.121:45583  ESTABLISHED 1607/mysqld
tcp        0      0 ::ffff:10.16.195.50:3306   ::ffff:192.168.0.121:45589  ESTABLISHED 1607/mysqld
tcp        0      0 ::ffff:10.16.195.50:3306   ::ffff:192.168.0.121:45579  ESTABLISHED 1607/mysqld
tcp        0      0 ::ffff:10.16.195.50:3306   ::ffff:192.168.0.121:45580  ESTABLISHED 1607/mysqld
tcp        0      0 ::ffff:10.16.195.50:3306   ::ffff:192.168.0.121:45588  ESTABLISHED 1607/mysqld
tcp        0      0 ::ffff:10.16.195.50:3306   ::ffff:192.168.0.121:45577  ESTABLISHED 1607/mysqld

可看到有很多从 mycat 服务器发起数据库连接（主有9个连接，从有8个连接）。

6.查看心跳报告：

mysql> show @@heartbeat;
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 10.16.195.49 | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2020-02-18 06:44:38 | false |
| hostS1 | mysql | 10.16.195.50 | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2020-03-18 06:44:38 | false |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
2 rows in set (0.00 sec)

该命令用于报告心跳状态

RS_CODE 状态:
    OK_STATUS = 1;正常状态
    ERROR_STATUS = -1; 连接出错
    TIMEOUT_STATUS = -2; 连接超时
    INIT_STATUS = 0; 初始化状态

若节点故障,会连续默认 5 个周期检测,心跳连续失败,就会变成-1,节点故障确认,然后可能发生切换

7.查看 Mycat 的前端连接状态，即应用与 mycat 的连接：

mysql> show @@connection\G
*************************** 1. row ***************************
    PROCESSOR: Processor0
           ID: 1
         HOST: 127.0.0.1
         PORT: 9066
   LOCAL_PORT: 50317
       SCHEMA: NULL
      CHARSET: latin1:8
       NET_IN: 257
      NET_OUT: 6343
ALIVE_TIME(S): 1264
  RECV_BUFFER: 4096
   SEND_QUEUE: 0
      txlevel:
   autocommit:
1 row in set (0.00 sec)

从上面获取到的连接 ID 属性，可以手动杀掉某个连接。

kill @@connection id,id,id

8.显示后端连接状态：

mysql> show @@backend\G
...
...
...
*************************** 16. row ***************************
 processor: Processor0
        id: 4
   mysqlId: 8
      host: 192.168.0.122
      port: 3306
    l_port: 45583
    net_in: 7018
   net_out: 1646
      life: 6287
    closed: false
  borrowed: false
SEND_QUEUE: 0
    schema: db1
   charset: utf8:33
   txlevel: 3
autocommit: true
16 rows in set (0.00 sec)

一共有16个后端连接，这里截取最后一个。

9.显示数据源：

mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST          | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.16.195.49 | 3306 | W    |      0 |    8 | 1000 |     231 |         0 |          2 |
| dn1      | hostS1 | mysql | 10.16.195.50 | 3306 | R    |      0 |    8 | 1000 |     211 |         8 |          0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)

可以看到主从信息。

执行SQL语句

登录数据管理端口

mysql -h127.0.0.1 -utest -ptest -P8066

创建 users表:

mysql> show databases;
+------------+
| DATABASE   |
+------------+
| AIDCDATA   |
| AIHsMdlSrv |
+------------+
1 row in set (0.00 sec)

mysql> use AIDCDATA;create table users (id INT, name VARCHAR(20));
Database changed
Query OK, 0 rows affected (0.25 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| users         |
+---------------+
1 row in set (0.01 sec)

插入两条数据：

mysql> insert into users values (1, 'guli'), (2, 'xie');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看一下插入结果：

mysql> select * from users;
+------+------+
| id   | name |
+------+------+
|    1 | guli |
|    2 | xie  |
+------+------+
2 rows in set (0.00 sec)

没问题。

再分别到主从节点看数据插入没有：

master:

mysql> use AIDCDATA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_AIDCDATA |
+--------------------+
| users              |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from users;
+------+------+
| id   | name |
+------+------+
|    1 | guli |
|    2 | xie  |
+------+------+
2 rows in set (0.00 sec)

slave:

mysql> use AIDCDATA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1           |
+---------------+
1 row in set (0.00 sec)

mysql> select * from users;
+------+------+
| id   | name |
+------+------+
|    1 | guli |
|    2 | xie  |
+------+------+
2 rows in set (0.00 sec)

好，可以看到 OK 了。

查看刚才执行过的 sql 语句：

mysql> show @@sql;
+------+------+---------------+--------------+-------------------+
| ID   | USER | START_TIME    | EXECUTE_TIME | SQL               |
+------+------+---------------+--------------+-------------------+
|    1 | mycat_writer | 1485212346188 |            1 | select * from AIDCDATA |
|    2 | mycat_writer | 1485212040101 |            1 | select * from AIDCDATA |
|    3 | mycat_writer | 1485211834831 |            1 | select * from AIDCDATA |
|    4 | mycat_writer | 1485211803688 |            1 | select * from AIDCDATA |
|    5 | mycat_writer | 1485209518691 |            2 | select * from AIDCDATA |
+------+------+---------------+--------------+-------------------+
5 rows in set (0.00 sec)

遇到的问题：

似乎无法统计 insert 语句，不知为什么。

查看统计数据：

mysql> show @@sql.sum;
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
| ID   | USER | R    | W    | R%   | MAX  | NET_IN | NET_OUT | TIME_COUNT   | TTL_COUNT    | LAST_TIME     |
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
|    1 | mycat_writer |    5 |    0 | 1.00 | 1    |     85 |     709 | [5, 0, 0, 0] | [5, 0, 0, 0] | 1485212346189 |
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
1 row in set (0.00 sec)

端口号: 该命令工作在 9066 端口,用来记录用户通过本地 8066 端口向 Mycat-Server 发送的 SQL 请求执行
信息。信息包括有 ID 值,执行 SQL 语句的用户名称,执行的 SQL 语句,命令执行的起始时间,命令执行消耗时间

查看慢查询语句：

设置慢查询阈值为0：reload @@sqlslow=0;

mysql> reload @@sqlslow=0;
Query OK, 1 row affected (0.00 sec)
Reset show  @@sql.slow time success

在8066端口执行查询：select * from users;

mysql> select * from users;
+------+-------+
| id   | name  |
+------+-------+
|    1 | guli  |
|    2 | xie   |
|    3 | xu    |
|    4 | he    |
|    5 | huang |
|    6 | ma    |
|    7 | liu   |
|    8 | zeng  |
+------+-------+
8 rows in set (0.00 sec)

在 9066 端口执行 show @@sql.slow 查看抓取的慢查询SQL语句：

mysql> show @@sql.slow;
+------+------------+---------------+--------------+-------------------+
| USER | DATASOURCE | START_TIME    | EXECUTE_TIME | SQL               |
+------+------------+---------------+--------------+-------------------+
| test | NULL       | 1485213017329 |            1 | select * from tb1 |
+------+------------+---------------+--------------+-------------------+
1 row in set (0.00 sec)

验证读写分离是否已经成功

使用mysql客户端连接9066管理端口，执行 show @@datasource 可以观察到 READ_LOAD，WRITE_LOAD 两个统计参数的变化：

这里显示 hostM1 为写节点，hostS1、hostS2 为读节点：

hostM1 的 WRITE_LOAD = 2

hostS1 的 READ_LOAD = 3

hostS2 的 READ_LOAD = 5

mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST          | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.16.195.49 | 3306 | W    |      0 |    8 | 1000 |     287 |         0 |          2 |
| dn1      | hostS1 | mysql | 10.16.195.50 | 3306 | R    |      0 |    8 | 1000 |     271 |         3 |          0 |
| dn1      | hostS1 | mysql | 10.16.195.51 | 3306 | R    |      0 |    8 | 1000 |     271 |         5 |          0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)

使用mysql客户端连接8066管理端口，执行查询，插入语句，同时使用mysql客户端连接 9066 端口观察一下读写统计参数的变化：

8066：执行查询 select * from users;

mysql> select * from users;
+------+------+
| id   | name |
+------+------+
|    1 | guli |
|    2 | xie  |
|    3 | xu   |
|    4 | he   |
+------+------+
4 rows in set (0.00 sec)

9066：

mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST          | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.16.195.49 | 3306 | W    |      0 |    8 | 1000 |     287 |         0 |          2 |
| dn1      | hostS1 | mysql | 10.16.195.50 | 3306 | R    |      0 |    8 | 1000 |     271 |         4 |          0 |
| dn1      | hostS1 | mysql | 10.16.195.51 | 3306 | R    |      0 |    8 | 1000 |     271 |         5 |          0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)

读节点的读计数加1，

hostM1 的 WRITE_LOAD = 2
hostS1 的 READ_LOAD = 4
hostS2 的 READ_LOAD = 5

8066：执行插入操作 insert into users values (5,'huang');

mysql> insert into users values (5,'huang');
Query OK, 1 row affected (0.02 sec)

9066：

mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST          | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.16.105.49 | 3306 | W    |      0 |    8 | 1000 |     332 |         0 |          4 |
| dn1      | hostS1 | mysql | 10.16.105.52 | 3306 | R    |      0 |    8 | 1000 |     315 |        13 |          0 |
| dn1      | hostS2 | mysql | 10.16.105.51 | 3306 | R    |      0 |    8 | 1000 |     315 |        13 |          0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)

写节点的读计数加1

hostM1 的 WRITE_LOAD = 3
hostS1 的 READ_LOAD = 4
hostS2 的 READ_LOAD = 5

由此可见读写分离是成功的。可以看到数据也成功写入数据库：

mysql> select * from users;
+------+-------+
| id   | name  |
+------+-------+
|    1 | guli  |
|    2 | xie   |
|    3 | xu    |
|    4 | he    |
|    5 | huang |
+------+-------+
5 rows in set (0.00 sec)

到此基本演示了 mycat 的主从读写分离功能，配置的前提是已经有一个配置好的 mysql 主从复制架构，mycat 工作于 mysql 主从架构的前端，负责 SQL 语句的分发。

6.Mycat服务踩坑

1.错误现象

查看mycat日志，一直显示连不上服务器

cat /usr/local/mycat/logs/mycat.log

错误内容

2020-03-18 17:52:34.787 INFO [$_NIOREACTOR-7-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql: select user()
2020-03-18 17:52:34.787 ERROR [$_NIOREACTOR-7-RW] (io.mycat.backend.heartbeat.MySQLHeartbeat.nextDecter(MySQLHeartbeat.jave:215)) - set Error 3 DBHsotConfig [hostName=hostS1, url=10.16.195.50:3306]
2020-03-18 17:02:03.787 INFO [S_NIOREACTOR-7-RW]  (io.mycat.net.AbstractConnection.close (Abstractconnection.java:520)) - close connection, reason:stream closed,MySQLConnection[id=165, LastTime=1584522123784, user=root, schema=AIHsMdl
Srv, old shema=AIHsMdlSrv, borrowed=false, fromSlaveDB=true, threadId=0, charset=utf8, txIsoation=3, autocommit=true, attachment=null, respHandler=null, host=10.16.195.50, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-03-18 17:52:34.787 INFO [$_NIOREACTOR-7-RW] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionError(PhysicalDatasource.java508))

遇到这个错误，去网上搜罗了很多解决方法，最主要的说法是：

检查schema.xml中读写用户的帐号和密码，是否和数据库配置的一致。

我确认了几次，确实是一致的。

2.错误现象2

然后偶然通过navicate连接mysql服务的时候，遇到错误：

1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

查到解决办法：https://www.cnblogs.com/ryxiong-blog/p/12518573.html

修改用户密码的加密规则，降到原来的规则。

alter user 'root'@'%' identified with mysql_native_password by "123456";
flush privileges;

重新连接就可以。

3.解决方法

那么是不是mycat也有这种问题呢，导致连接不上mysql服务器。

于是将3台mysql服务器的密码加密规则全部退回mysql_native_password。

4.重启mysql服务和mycat服务

service mysql restart
mycat restart

posted @ 2020-03-18 20:32 ryxiong728 阅读(416) 评论(0) 编辑收藏举报

刷新页面返回顶部

何处繁花笙歌落

路漫漫其修远兮，吾将上下而求索

Mycat 实现 MySQL 读写分离

Mycat 实现 MySQL 读写分离

1.环境介绍

2.mycat安装

3.mycat配置详解

server.xml配置详解

schema.xml配置详解

rule.xml配置详解

4.Mycat启动

5.Mycat 管理命令与监控

管理命令

执行SQL语句

验证读写分离是否已经成功

6.Mycat服务踩坑

公告