mycat 表分库与读写分离

分库分表,读写分离都是数据库层面的负载均衡策略,由于单节点数据库存在单点故障和响应瓶颈,人们希望数据库以集群的形式对外提供服务,实现高可用,为了管理集群数据库,产生了Mycat这款中间件

Mycat是阿里开发的开源的数据库分库分表中间件,mycat管理多节点的mysql,请求访问数据时,由mycat处理这条请求应该发送到哪个或哪些节点上执行操作,对外界就像访问单节点的 mysql 一样

MyCat 安装及配置

安装

上传压缩包解压即可

配置

server.xml

设计 mycat 服务端口号(默认8066),登陆名密码等,用户操作权限

schema.xml

设计逻辑库,逻辑表与实际库的映射关系:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="student" dataNode="dn1,dn2,dn3" rule="crc32slot" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="master:3306" user="root"
password="root">
<readHost host="hostS1" url="slave:3306" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>

TESTDB 是逻辑库(schema),student 是逻辑表,将 student 表分片到三个 dataNode : dn1, dn2, dn3 中,对应三个实际库:db1, db2, db3,上图的配置中,三个实际库都在 dataHost : localhost1 中,对应主节点 master 及其从节点 slave。即在 mycat 中的 student 的数据实际上是存储在 master 节点的 mysql 中的三个库 db1, db2, db3 中的 student 表中,slave 节点为 master 的备份节点

rule.xml

1
2
3
<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
<property name="count">3</property><!-- 要分片的数据库节点数量,必须指定,否则没法分片 -->

启动和访问Mycat服务

将 mycat/bin 添加到环境变量,执行 mycat start 启动 mycat 服务,mycat status 查看服务状态,mysql -uroot -p123456 -hmaster -P8066 进入 mycat 的 DBMS,可以看打 mycat 中有一个库为 TESTDB,里面有一个表 student,而实际对应的mysql中并没有db1, db2, db3这三个库,需要手动创建

注:搭建 Mycat 分表和读写分离是建立在Mysql主备模式已经建立的基础上

分表

当一张表中字段数非常多时,会影响查询效率,我们希望将数据分散到多个表中,有两种方法:1. 可以放到一个库的不同表中,2. 也可以放到多个库的不同表中;放到多个库的不同表中可以令表名相同,方便管理,如下图:

手动建库

在 master 节点中创建库:create database db1, create database db2, create database db3

mycat中建表

虽然在 mycat 中能看到 TESTDB 库中已经有了 student 表,但这是在配置文件中读到的逻辑表,实际表没有创建,这里不需要到 master 中手动自己创建,在 mycat 中执行建表语句,会自动在对应的物理库中生成对应的表:

1
create table student(id int not null primary key, name varchar(24), age int);

1566188871014

新增的 _slot 字段用来保存 crc32slot 分片算法为每条记录计算结果,用来散列记录到某个表中,避免迁移时重新计算

分表结果

在 MySQL 实际表中查询结果:

实际表

在 Mycat 的逻辑表中查询结果:

逻辑表

读写分离

Mycat 将读请求发送给多个从库处理,增删改请求发送给主库,达到负载均衡的效果

在 schema.xml 中配置 dataHost 的属性:(官网文档抄的,我还没有配置主备HA)

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

或者:balance=”3” 表示:所有读请求随机的分发到 writerHost 对应的 readhost 执行,writerHost 不负担读压力

writeType=”0” 表示:所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个 writeHost

1
2
3
4
5
6
7
8
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="master:3306" user="root"
password="root">
<readHost host="hostS1" url="slave:3306" user="root" password="root" />
</writeHost>
</dataHost>

为了验证读到的是从节点的数据,可以手动修改一条记录,使其与主结点中对应记录不同,如果结果是修改过的说明是从节点中读的

遇到问题

无法将查询请求发送到从节点

实验中读到的是主节点中的值,通过 logs/mycat.log 中发现 mycat 无法连接从节点:io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:114)) - can't get connection for sql :select user(),即 mycat 通过schema.xml 中的 readHost中从节点的 url,用户名密码去访问被拒绝;在从节点的 mysql 库的 user 表中添加用户:

1
2
grant all privileges on *.* to 'username'@'ip' identified by 'password' with grant option;
flush privileges;

其中:username=root, password=root, 第一次我将 ip 设为 master,经测试 mycat 依旧无法连接,改为 ip 后连接成功,可以查询到从库的数据

注:从库是为了备份主库数据和响应读请求的,可以使用更适合读请求的 MyISAM 存储引擎,从库数据要与主库保持一致,自己手动修改只是为了测试读写分离,如果在从库中修改了数据主库不会同步,造成不一致

Reference

mycat中文指南手册

您的支持鼓励我继续创作!