【MySQL | 运维篇】04、MySQL 分库分表之 MyCat 入门与配置

目录

一、MyCat入门

1.1 需求

​1.2 环境准备

1.3 配置

1). schema.xml

2). server.xml

1.4 测试

1 启动

2 连接MyCat

3 数据测试

二、MyCat配置

2.1 schema.xml

2.1.1 schema标签

1). schema 定义逻辑库 

2). schema 中的table定义逻辑表 

2.1.2 datanode标签 

2.1.3 datahost标签

2.2 rule.xml

2.3 server.xml

1). system标签 

2). user标签

---

 

一、MyCat入门

1.1 需求

        由于 tb_order 表中数据量很大，磁盘IO及容量都到达了瓶颈，现在需要对 tb_order 表进行数
据分片，分为三个数据节点，每一个节点主机位于不同的服务器上, 具体的结构，参考下图：

1.2 环境准备

准备3台服务器： 

• 192.168.78.139：MyCat中间件服务器，同时也是第一个分片服务器。
• 192.168.78.140：第二个分片服务器。
• 192.168.78.141：第三个分片服务器。

并且在上述3台数据库中创建数据库 db01 。

1.3 配置

1). schema.xml

在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置根据自己的实际情况修改：

[root@sql-master ~]# vim /usr/local/mycat/conf/schema.xml
 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
	<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>
 
	<dataNode name="dn1" dataHost="dhost1" database="db01" />
	<dataNode name="dn2" dataHost="dhost2" database="db01" />
	<dataNode name="dn3" dataHost="dhost3" database="db01" />
 
	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
 
		<writeHost host="master" url="jdbc:mysql://192.168.78.139:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="Qwe123456." />
	</dataHost>
	
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
 
		<writeHost host="master" url="jdbc:mysql://192.168.78.140:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="Qwe123456." />
	</dataHost>	
	
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
 
		<writeHost host="master" url="jdbc:mysql://192.168.78.141:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="Qwe123456." />
	</dataHost>
 
</mycat:schema>

2). server.xml

需要在server.xml中配置用户名、密码，以及用户的访问权限信息，在文件内容最下面找到，具体的配置如下： 

[root@sql-master ~]# vim /usr/local/mycat/conf/server.xml
 
	<user name="root" defaultAccount="true">
		<property name="password">Qwe123456.</property>
		<property name="schemas">DB01</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">123456</property>
		<property name="schemas">DB01</property>
		<property name="readOnly">true</property>
	</user>

设置 MyCat 管理员root 用户密码： 

        上述的配置表示，定义了两个用户 root 和 user ，这两个用户都可以访问 DB01 这个逻辑库，访问密码分别是Qwe123456. 和 123456，但是root用户访问DB01逻辑库，既可以读，又可以写，但是 user用户访问DB01逻辑库是只读的。 

1.4 测试

1 启动

        配置完毕后，先启动涉及到的3台分片服务器，然后启动MyCat服务器。切换到Mycat的安装目录，执行如下指令，启动Mycat：

# 启动
bin/mycat start
 
# 停止
bin/mycat stop

Mycat启动之后，占用端口号 8066。

启动完毕之后，可以查看logs目录下的启动日志wrapper.log，查看Mycat是否启动完成。

2 连接MyCat

通过如下指令，就可以连接并登陆MyCat。 

mysql -h 192.168.78.139 -P 8066 -uroot -pQwe123456.

        可以看到我们是通过MySQL的指令来连接的MyCat，因为MyCat在底层实际上是模拟了MySQL的协议。

3 数据测试

然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况。

CREATE TABLE TB_ORDER (
    id BIGINT(20) NOT NULL,
    title VARCHAR(100) NOT NULL ,
    PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
 
INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1');
INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2');
INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
 
INSERT INTO TB_ORDER(id,title) VALUES(5000000,'goods5000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');
INSERT INTO TB_ORDER(id,title) VALUES(15000000,'goods15000000');
INSERT INTO TB_ORDER(id,title) VALUES(15000001,'goods15000001');

经过测试，我们在数据库中发现，在往 TB_ORDER 表中插入数据时：

• 如果id的值在1-500w之间，数据将会存储在第一个分片数据库中。
• 如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中。
• 如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中。
• 如果id的值超出1500w，在插入数据时，将会报错。

        为什么会出现这种现象，数据到底落在哪一个分片服务器到底是如何决定的呢？ 这是由逻辑表配置时的一个参数 rule 决定的，而这个参数配置的就是分片规则。

二、MyCat配置

2.1 schema.xml

        schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库 、 逻辑表 、 分片规则、分片节点及数据源的配置。 

主要包含以下三组标签：

• schema 标签
• datanode 标签
• datahost 标签 

2.1.1 schema标签

1). schema 定义逻辑库 

        schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database概念, 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。

核心属性：

• name：指定自定义的逻辑库库名
• checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
• sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录 

2). schema 中的table定义逻辑表 

        table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义 。 

核心属性：

• name：定义逻辑表表名，在该逻辑库下唯一
• dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
• rule：分片规则的名字，分片规则名字是在rule.xml中定义的
• primaryKey：逻辑表对应真实表的主键
• type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为 global 

2.1.2 datanode标签 

核心属性：

• name：定义数据节点名称
• dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
• database：定义分片所属数据库 

2.1.3 datahost标签

 

        该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。

核心属性：

• name：唯一标识，供上层标签使用
• maxCon/minCon：最大连接数/最小连接数
• balance：负载均衡策略，取值 0,1,2,3
• writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
• dbDriver：数据库驱动，支持 native、jdbc 

2.2 rule.xml

        rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签：tableRule、Function。 

2.3 server.xml

server.xml配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。

1). system标签 

主要配置MyCat中的系统配置信息，对应的系统配置项及其含义，简单了解下：

2). user标签

        配置MyCat中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限描述方式及配置说明如下： 

        在测试权限操作时，我们只需要将 privileges 标签的注释放开。 在 privileges 下的schema标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性中配置逻辑表的权限。