随着时间的推移,数据的产生越来越多,软件系统对庞大数据的各种操作已经显得愈发困难,传统的关系型数据库已经无法满足快速查询与插入数据的需求。NoSQL通过降低数据的安全性,减少对事务的支持,减少对复杂查询的支持,以此来获取性能上的提升,暂时缓解了这一危机。但是,有些数据操作场景是绝对需要有事务和安全性指标的,此时NoSQL是无法满足的,依然需要使用关系型数据库。 为了提高关系型数据库的查询性能,于是将一个数据库的数据分散到不同的数据库中存储,以抵抗高访问量高并发对数据库的压力,相当于对数据进行拆分操作并且做数据库集群。 如何拆分数据,同时让程序和数据库解耦,使数据的分片存储对于开发者来说完全透明,数据库中间件油然而生。
数据库中间件对比
Mycat 背后是阿里曾经开源的知名产品——Cobar。Cobar 的核心功能和优势是 MySQL 数据库分片,此产品曾经广为流传,据说最早的发起者对 Mysql 很精通,后来从阿里跳槽了。 阿里随后开源 Cobar,并维持到 2013 年年初,然后,就没有然后了。Cobar 的思路和实现路径的确不错。基于 Java 开发的,实现了 MySQL 公开的二进制传输协议,巧妙地将自己伪装成一个 MySQL Server,目前市面上绝大多数 MySQL 客户端工具和应用都能兼容。比自己实现一个新的数据库协议要明智的多,因为生态环境在哪里摆着。 Mycat 是基于cobar演变而来,对cobar的代码进行了彻底的重构,使用 NIO 重构了网络模块,并且优化了 Buffer 内核,增强了聚合,Join 等基本特性,同时兼容绝大多数数据库成为通用的数据库中间件。 简单的说,MyCAT就是:一个新颖的数据库中间件产品支持mysql集群,提供高可用性数据分片集群。你可以像使用mysql一样使用mycat。对于开发人员来说根本感觉不到mycat的存在。
读写分离
数据分片
垂直拆分(分库)、水平拆分(分表)、垂直+水平拆分(分库分表)
多数据整合
Mycat的原理中最重要的一个动词是“拦截”,它拦截了用户发送过来的SQL语句,首先对SQL语句做了一些特定的分析:如分片分析、路由分析、读写分离分析、缓存分析等,然后将此SQL发往后端的真实数据库,并将返回的结果做适当的处理,最终再返回给用户。如下图所示:
上述图片里,Orders表被分为三个分片datanode(简称dn),这三个分片是分布在两台MySQL Server上(DataHost),即datanode=database@datahost方式,当Mycat收到一个SQL时,会先解析这个SQL,查找涉及到的表,然后看此表的定义,如果有分片规则,则获取到SQL里分片字段的值,并匹配分片函数,得到该SQL对应的分片列表,然后将SQL发往这些分片去执行,最后收集和处理所有分片返回的结果数据,并输出到客户端。
以select * from orders where prov=?
语句为例,查到prov=zhejiang
,按照分片函数,zhejiang返回dn1,于是SQL就发给了MySQL1,去取DB1上的查询结果,并返回给用户。
从GitHub(https://github.com/MyCATApache/Mycat-download)上下载MyCat,可以使用1.6这个稳定版本,解压缩文件拷贝到linux下/usr/local/
,解压完成后即可使用。
目录说明:
配置文件介绍:
log4j2.xml
:该文件是mycat日志配置文件,在该文件中可以定义mycat日志的级别。
server.xml
:server.xml几乎保存了所有mycat需要的系统配置信息。最常用的是在此配置用户名、密码及权限。
<user name="root"> <!-- 配置用户名 -->
<property name="password">123456</property> <!-- 配置密码 -->
<property name="schemas">TESTDB</property> <!-- 配置该用户可以访问的逻辑数据库 -->
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false"> 配置权限信息,check为false表示不进行语法检查
<schema name="TESTDB" dml="0110" >
insert,update,select,delete权限信息 1:表示拥有该权限,0:不具有该权限
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
schema.xml
:schema.xml作为MyCat中重要的配置文件之一,管理着MyCat的逻辑库、表、分片节点以及节点主机。弄懂这些配置,是正确使用MyCat的前提。这里就一层层对该文件进行解析。
简易的schema.xml配置文件如下所示:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="autosharding-long" />
</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="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
<readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
</writeHost>
<writeHost host="hostS1" url="localhost:3316" user="root" password="123456" />
</dataHost>
</mycat:schema>
schema标签用于定义MyCat实例中的逻辑库 table标签定义了MyCat中的逻辑表 dataNode标签定义了MyCat中的数据节点,也就是我们通常说所的数据分片 dataHost标签在mycat逻辑库中也是作为最底层的标签存在,直接定义了具体的数据库实例、读写分离配置和心跳语句
rule.xml
:rule.xml里面就定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法,或者对表使用相同的算法但具体的参数不同。一个规则的定义由一对tableRule和function这两个标签完成。
<tableRule name="auto-sharding-long"> <!-- 定义分片规则 -->
<rule>
<columns>id</columns> <!-- 分片字段 -->
<algorithm>rang-long</algorithm> <!-- 分片算法 -->
</rule>
</tableRule>
<!-- 分片算法对应的具体的执行函数 -->
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
在具体使用过程中我们也可以按照需求添加tableRule和function。 auto-sharding-long这种分片算法是按照数据范围进行分片,具体哪些范围的需要分配到一个数据库中是在autopartition-long.txt文件中定义的。autopartition-long.txt具体的定义如下所示:
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0 # 第一个分片
500M-1000M=1 # 第二个分片
1000M-1500M=2 # 第三个分片
1.修改配置文件server.xml
修改用户信息,与MySQL区分,如下:
…
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
…
2.修改配置文件schema.xml
删除schema标签间的表信息,dataNode标签只留一个,dataHost标签只留一个,writeHost和readHost只留一对
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="testdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.140.128:3306" user="root" password="123123">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.140.127:3306" user="root" password="123123" />
</writeHost>
</dataHost>
</mycat:schema>
3.验证数据库访问情况
Mycat作为数据库中间件要和数据库部署在不同机器上,所以要验证远程访问情况。
mysql -uroot -p123123 -h 192.168.140.128 -P 3306
mysql -uroot -p123123 -h 192.168.140.127 -P 3306
#如远程访问报错,请建对应用户
#grant all privileges on *.* to root@'缺少的host' identified by '123123';
4.启动程序
mycat/bin
目录下执行./mycat console
mycat/bin
目录下执行./mycat start
为了能第一时间看到启动日志,方便定位问题,我们选择控制台启动 。
5.启动时可能出现报错
如果操作系统是CentOS 6.8可能会出现域名解析失败错误,如下图
可以按照以下步骤解决
/etc/hosts
文件 在 127.0.0.1 后面增加你的机器名
1.登录后台管理窗口
此登录方式用于管理维护Mycat
mysql -umycat -p123456 -P 9066 -h 192.168.140.128
#常用命令如下
show database
show @@help
2.登录数据窗口 此登录方式用于通过Mycat查询数据 ,我们选择这种方式访问Mycat
mysql -umycat -p123456 -P 8066 -h 192.168.140.128
我们通过 Mycat 和 MySQL 的主从复制配合搭建数据库的读写分离,实现 MySQL 的高可用性。我们将搭建:一主一从、双主双从两种读写分离模式。
一个主机用于处理所有写请求,一台从机负责所有读请求,架构图如下
1.MySQL主从复制原理
2.主机配置(host79),修改配置文件:vim /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
binlog 日志三种格式
time=now()
,造成从机执行的时候数据不一致@@host name
3.从机配置(host80),修改配置文件:vim /etc/my.cnf
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
4.主机、从机重启MySQL服务
# 1.启动
service mysqld start
/etc/init.d/mysqld start
# 2.停止
service mysqld stop
/etc/init.d/mysqld stop
# 3.重启
service mysqld restart
/etc/init.d/mysqld restart
5.主机从机都关闭防火墙
# 1.查看防火状态
systemctl status firewalld
service iptables status
# 2.暂时关闭防火墙
systemctl stop firewalld
service iptables stop
# 3.永久关闭防火墙
systemctl disable firewalld
chkconfig iptables off
# 4.重启防火墙
systemctl enable firewalld
service iptables restart
6.在主机上建立帐户并授权slave
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
#查询master的状态
show master status;
#记录下File和Position的值
#执行完此步骤后不要再操作主服务器MySQL,防止主服务器状态值变化
7.在从机上配置需要复制的主机
#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;
#启动从服务器复制功能
start slave;
#查看从服务器状态
show slave status\G;
#下面两个参数都是Yes,则说明主从配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
8.主机新建库、新建表、insert记录,从机复制:主机128,从机127
9.停止从服务复制功能
stop slave;
10.重新配置主从
stop slave;
reset master;
1.修改Mycat的配置文件schema.xml
修改dataHost的balance属性,通过此属性配置读写分离的类型,负载均衡类型,目前的取值有4 种:
为了能看到读写分离的效果,把balance设置成2,会在两个主机间切换查询
<dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
2.验证读写分离
在写主机数据库表mytbl中插入带系统变量数据,造成主从数据不一致INSERT INTO mytbl VALUES(2,@@hostname);
在Mycat里查询mytbl表,可以看到查询语句在主从两个主机间切换
一个主机 m1 用于处理所有写请求,它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后,m2 主机负责写请求,m1、m2 互为备机。架构图如下
编号 | 角色 | IP地址 | 机器名 |
---|---|---|---|
1 | Master1 | 192.168.140.128 | host79.atguigu |
2 | Slave1 | 192.168.140.127 | host80.atguigu |
3 | Master2 | 192.168.140.126 | host81.atguigu |
4 | Slave2 | 192.168.140.125 | host82.atguigu |
1.双主机配置
修改Master1配置文件:vim /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=1
修改Master2配置文件:vim /etc/my.cnf
#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=2
2.双从机配置
修改Slave1配置文件:vim /etc/my.cnf
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
修改Slave2配置文件:vim /etc/my.cnf
#从服务器唯一ID
server-id=4
#启用中继日志
relay-log=mysql-relay
3.双主机、双从机重启 mysql 服务
4.主机从机都关闭防火墙
5.在两台主机上建立帐户并授权 slave
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
#查询Master1的状态
show master status;
#查询Master2的状态
show master status;
分别记录下File和Position的值 执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
6.在从机上配置需要复制的主机
Slava1 复制 Master1,Slava2 复制 Master2
#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;
Slava1的复制命令
Slava2的复制命令
#启动两台从服务器复制功能
start slave;
#查看从服务器状态
show slave status\G;
# 下面两个参数都是Yes,则说明主从配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
Slava1的复制Master1
Slava2的复制Master2
7.两个主机互相复制
Master2 复制 Master1,Master1 复制 Master2
Master2的复制命令
Master1的复制命令
8.Master1 主机新建库、新建表、insert 记录,Master2 和从机复制
1.修改Mycat的配置文件schema.xml
<dataNode name="dn1" dataHost="host1" database="testdb" />
<!-- balance="1": 全部的readHost与stand by writeHost参与select语句的负载均衡。-->
<!-- writeType="0": 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个 -->
<!-- writeType="1",所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐 -->
<!-- writeHost,重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties -->
<!-- switchType="1": 1 默认值,自动切换; -1 表示不自动切换; 2 基于MySQL主从同步的状态决定是否切换。 -->
<dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" >
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.140.128:3306" user="root" password="123123">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.140.127:3306" user="root" password="123123" />
</writeHost>
<writeHost host="hostM2" url="192.168.140.126:3306" user="root" password="123123">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.140.125:3306" user="root" password="123123" />
</writeHost>
</dataHost>
2.验证读写分离
在写主机Master1数据库表mytbl中插入带系统变量数据,造成主从数据不一致 INSERT INTO mytbl VALUES(3,@@hostname);
在Mycat里查询mytbl表,可以看到查询语句在Master2(host81)、Slava1(host80)、Slava2(host82)主从三个主机间切换
3.抗风险能力
停止数据库Master1
在Mycat里插入数据依然成功,Master2自动切换为写主机INSERT INTO mytbl VALUES(3,@@hostname);
启动数据库Master1
在Mycat里查询mytbl表,可以看到查询语句在Master1(host79)、Slava1(host80)、Slava2(host82)主从三个主机间切换
Master1、Master2 互做备机,负责写的主机宕机,备机切换负责写操作,保证数据库读写分离高可用性。
一个数据库由很多表的构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类,分布到不同 的数据库上面,这样也就将数据或者说压力分担到不同的库上面,如下图:
系统被切分成了,用户,订单交易,支付几个模块。
一个问题:在两台主机上的两个数据库中的表,能否关联查询? 答案:不可以关联查询。 分库的原则:有紧密关联关系的表应该在一个库里,相互没有关联关系的表可以分到不同的库里。
#客户表 rows:20万
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
#订单详细表 rows:600万
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
#订单状态字典表 rows:20
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);
以上四个表如何分库?客户表分在一个数据库,另外三张都需要关联查询,分在另外一个数据库。同一个服务的不同数据库可以join查询,如果所属不同服务的表,是无法join关联查询的。
1.修改 schema.xml
配置文件
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="orders" />
<dataNode name="dn2" dataHost="host2" database="orders" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.140.128:3306" user="root" password="123123">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="192.168.140.127:3306" user="root" password="123123">
</writeHost>
</dataHost>
如下图:
2.新增两个空白库
分库操作不是在原来的老数据库上进行操作,需要准备两台 机器分别安装新的数据库
#在数据节点 dn1、dn2 上分别创建数据库 orders
CREATE DATABASE orders;
3.启动 Mycat
./mycat console
4.在Mycat上创建表
#访问 Mycat
mysql -umycat -p123456 -h 192.168.140.128 -P 8066
#切换到 TESTDB
#创建 4 张表
#查看表信息,可以看到成功分库
相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中,如图:
1.选择要拆分的表
MySQL 单表存储数据条数是有瓶颈的,单表达到 1000 万条数据就达到了瓶颈,会影响查询效率,需要进行水平拆分(分表)进行优化。 例如:例子中的 orders、orders_detail 都已经达到 600 万行数据,需要进行分表优化。
2.分表字段
以 orders 表为例,可以根据不同自字段进行分表
编号 | 分表字段 | 效果 |
---|---|---|
1 | id(主键、或创建时间) | 查询订单注重时效,历史订单被查询的次数少,如此分片会造成一个节点访问多,一个访问少,不平均。 |
2 | customer_id(客户 id) | 根据客户 id 去分,两个节点访问平均,一个客户的所有订单都在同一个节点。 |
3.修改配置文件 schema.xml
为 orders 表设置数据节点为 dn1、dn2,并指定分片规则为 mod_rule(自定义的名字)
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
4.修改配置文件 rule.xml
在 rule 配置文件里新增分片规则 mod_rule,并指定规则适用字段为 customer_id
,
还有选择分片算法 mod-long
(对字段求模运算),customer_id
对两个节点求模,根据结果分片
配置算法 mod-long 参数 count 为 2,两个节点
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
…
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
5.在数据节点 dn2 上建 orders 表
6.重启Mycat,让配置生效
7.访问 Mycat 实现分片
在 mycat 里向 orders 表插入数据,INSERT 字段不能省略
INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);
在mycat、dn1、dn2中查看orders表数据,分表成功
Orders 订单表已经进行分表操作了,和它关联的 orders_detail 订单详情表如何进行 join 查询。 我们要对 orders_detail 也要进行分片操作。Join 的原理如下图:
Mycat 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路,Foundation DB 创新性的提出了 Table Group 的概念,其将子表的存储位置依赖于主表,并且物理上紧邻存放,因此彻底解决了JOIN 的效率和性能问 题,根据这一思路,提出了基于 E-R 关系的数据分片策略,子表的记录与所关联的父表记录存放在同一个数据分片上。
修改 schema.xml
配置文件
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
在 dn2 创建 orders_detail 表
重启 Mycat
访问 Mycat 向 orders_detail 表插入数据
INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);
在mycat、dn1、dn2中运行两个表join语句
select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;
在分片的情况下,当业务表因为规模而进行分片以后,业务表与这些附属的字典表之间的关联,就成了比较 棘手的问题,考虑到字典表具有以下几个特性:
鉴于此,Mycat 定义了一种特殊的表,称之为“全局表”,全局表具有以下特性:
将字典表或者符合字典表特性的一些表定义为全局表,则从另外一个方面,很好的解决了数据JOIN 的难题。通过全局表加基于 E-R 关系的分片策略,Mycat 可以满足 80%以上的企业应用开发
修改 schema.xml
配置文件:
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
<!-- 全局表 -->
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
在 dn2 创建 dict_order_type 表
重启 Mycat
访问 Mycat 向 dict_order_type 表插入数据
INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');
在Mycat、dn1、dn2中查询表数据
此规则为对分片字段求摸运算。也是水平分表最常用规则。5.1 配置分表中 orders 表采用了此规则。
通过在配置文件中配置可能的枚举 id,自己配置分片,本规则适用于特定的场景,比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的,这类业务使用本条规则。
1.修改schema.xml
配置文件
<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>
2.修改rule.xml
配置文件
<tableRule name="sharding_by_intfile">
<rule>
<!-- columns:分片字段 -->
<columns>areacode</columns>
<!-- algorithm:分片函数 -->
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
…
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<!-- mapFile:标识配置文件名称 -->
<property name="mapFile">partition-hash-int.txt</property>
<!-- type:0为int型、非0为String -->
<property name="type">1</property>
<!-- defaultNode:默认节点:小于0表示不设置默认节点,大于等于0表示设置默认节点,设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错 -->
<property name="defaultNode">0</property>
</function>
3.修改partition-hash-int.txt
配置文件
110=0
120=1
4.重启 Mycat
5.访问 Mycat,创建订单归属区域信息表
CREATE TABLE orders_ware_info
(
`id` INT AUTO_INCREMENT comment '编号',
`order_id` INT comment '订单编号',
`address` VARCHAR(200) comment '地址',
`areacode` VARCHAR(20) comment '区域编号',
PRIMARY KEY(id)
);
6.插入数据
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1,1,'北京','110');
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2,2,'天津','120');
7.查询Mycat、dn1、dn2可以看到数据分片效果
此分片适用于,提前规划好分片字段某个范围属于哪个分片。
1.修改schema.xml
配置文件
<table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>
2.修改rule.xml
配置文件
<tableRule name="auto_sharding_long">
<rule>
<!-- columns:分片字段 -->
<columns>order_id</columns>
<!-- algorithm:分片函数 -->
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
…
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
<!-- mapFile:标识配置文件名称 -->
<property name="mapFile">autopartition-long.txt</property>
<!-- defaultNode:默认节点:小于0表示不设置默认节点,大于等于0表示设置默认节点,设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错 -->
<property name="defaultNode">0</property>
</function>
3.修改autopartition-long.txt
配置文件
0-102=0
103-200=1
4.重启 Mycat
5.访问 Mycat,创建支付信息表
CREATE TABLE payment_info
(
`id` INT AUTO_INCREMENT comment '编号',
`order_id` INT comment '订单编号',
`payment_status` INT comment '支付状态',
PRIMARY KEY(id)
);
6.插入数据
INSERT INTO payment_info (id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info (id,order_id ,payment_status) VALUES (3,103,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (4,104,1);
7.查询Mycat、dn1、dn2可以看到数据分片效果
此规则为按天分片。设定时间格式、范围。
1.修改schema.xml
配置文件
<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>
2.修改rule.xml
配置文件
<tableRule name="sharding_by_date">
<rule>
<!-- columns:分片字段 -->
<columns>login_date</columns>
<!-- algorithm:分片函数 -->
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
…
<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
<!-- dateFormat:日期格式 -->
<property name="dateFormat">yyyy-MM-dd</property>
<!-- sBeginDate:开始日期 -->
<property name="sBeginDate">2019-01-01</property>
<!-- sEndDate:结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入 -->
<property name="sEndDate">2019-01-04</property>
<!-- sPartionDay:分区天数,即默认从开始日期算起,分隔2天一个分区 -->
<property name="sPartionDay">2</property>
</function>
3.重启 Mycat
4.访问 Mycat,创建用户信息表
CREATE TABLE login_info
(
`id` INT AUTO_INCREMENT comment '编号',
`user_id` INT comment '用户编号',
`login_date` date comment '登录日期',
PRIMARY KEY(id)
);
5.插入数据
INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,'2019-01-01');
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,'2019-01-02');
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,'2019-01-03');
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,'2019-01-04');
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,'2019-01-05');
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,'2019-01-06');
6.查询Mycat、dn1、dn2可以看到数据分片效果
在实现分库分表的情况下,数据库自增主键已无法保证自增主键的全局唯一。为此,Mycat 提供了全局 sequence,并且提供了包含本地配置和数据库配置等多种实现方式。
此方式 Mycat 将 sequence 配置到文件中,当使用到 sequence 中的配置后,Mycat 会更新 classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。
利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库,这样效率太低。 Mycat 会预加载一部分号段到 Mycat 的内存中,这样大部分读写序列都是在内存中完成的。 如果内存中的号段用完了 Mycat 会再向数据库要一次。 问:那如果 Mycat 崩溃了 ,那内存中的序列岂不是都没了? 是的。如果是这样,那么 Mycat 启动后会向数据库申请新的号段,原有号段会弃用。 也就是说如果 Mycat 重启,那么损失是当前的号段没用完的号码,但是不会因此出现主键重复。
建库序列脚本
在dn1上创建全局序列表
CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT
NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;
创建全局序列所需函数(官方提供)
DELIMITER $$
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS
VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;
DELIMITER ;DELIMITER
DELIMITER ;
初始化序列表记录
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,100);
修改 Mycat 配置
修改sequence_db_conf.properties
,vim sequence_db_conf.properties
意思是ORDERS
这个序列在dn1这个节点上,具体dn1节点是哪台机子,请参考schema.xml
修改server.xml
,vim server.xml
全局序列类型:0-本地文件,1-数据库方式,2-时间戳方式。此处应该修改成1。
重启Mycat
验证全局序列
登录 Mycat,插入数据insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);
查询数据
重启Mycat后,再次插入数据,再查询
全局序列 ID = 64 位二进制 42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加) 换算成十进制为 18 位数的long 类型,每毫秒可以并发 12 位二进制的累加。
可在 java 项目里自己生成全局序列:
在实际项目中,Mycat 服务也需要考虑高可用性,如果 Mycat 所在服务器出现宕机,或 Mycat 服务故障,需要有备机提供服务,需要考虑 Mycat 集群。
我们可以使用 HAProxy + Keepalived 配合两台 Mycat 搭起 Mycat 集群,实现高可用性。HAProxy 实现了 MyCat 多节点的集群高可用和负载均衡,而 HAProxy 自身的高可用则可以通过 Keepalived 来实现。
编号 | 角色 | IP地址 | 机器名 |
---|---|---|---|
1 | Mycat1 | 192.168.140.128 | host79.atguigu |
2 | Mycat2 | 192.168.140.127 | host80.atguigu |
3 | HAProxy(master) | 192.168.140.126 | host81.atguigu |
4 | Keepalived(master) | 192.168.140.126 | host81.atguigu |
5 | HAProxy(backup) | 192.168.140.125 | host82.atguigu |
6 | Keepalived(backup) | 192.168.140.125 | host82.atguigu |
准备好HAProxy安装包,传到/opt
目录下
解压到/usr/local/src
,tar -zxvf haproxy-1.5.18.tar.gz -C /usr/local/src
进入解压后的目录,查看内核版本,进行编译
cd /usr/local/src/haproxy-1.5.18
uname -r #查看系统内核,如:3.10.0-514.el7,此时ARGET参数就为linux310
# 内核版本:ARGET=linux310
# 系统位数:ARCH=x86_64
# haprpxy安装路径:PREFIX=/usr/local/haprpxy
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64
编译完成后,进行安装
make install PREFIX=/usr/local/haproxy
安装完成后,创建目录、创建HAProxy配置文件
mkdir -p /usr/data/haproxy/
vim /usr/local/haproxy/haproxy.conf
向配置文件中插入以下配置信息并保存
global
log 127.0.0.1 local0
#log 127.0.0.1 local1 notice
#log loghost local0 info
maxconn 4096
chroot /usr/local/haproxy
pidfile /usr/data/haproxy/haproxy.pid
uid 99
gid 99
daemon
#debug
#quiet
defaults
log global
mode tcp
option abortonclose
option redispatch
retries 3
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
listen proxy_status
bind :48066
mode tcp
balance roundrobin
server mycat_1 192.168.140.128:8066 check inter 10s
server mycat_2 192.168.140.127:8066 check inter 10s
frontend admin_stats
bind :7777
mode http
stats enable
option httplog
maxconn 10
stats refresh 30s
stats uri /admin
stats auth admin:123123
stats hide-version
stats admin if TRUE
启动HAProxy
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
查看HAProxy进程
ps -ef|grep haproxy
打开浏览器访问 http://192.168.140.125:7777/admin,在弹出框输入用户名:admin密码:123123,如果Mycat主备机均已启动,则可以看到如下图:
验证负载均衡,通过HAProxy访问Mycat
mysql -umycat -p123456 -h 192.168.140.126 -P 48066
准备好Keepalived安装包,传到/opt
目录下
解压到/usr/local/src
tar -zxvf keepalived-1.4.2.tar.gz -C /usr/local/src
安装依赖插件
yum install -y gcc openssl-devel popt-devel
进入解压后的目录,进行配置,进行编译
cd /usr/local/src/keepalived-1.4.2
./configure --prefix=/usr/local/keepalived
进行编译,完成后进行安装
make && make install
运行前配置
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
修改配置文件vim /etc/keepalived/keepalived.conf
,修改内容如下:
! Configuration File for keepalived
global_defs {
notification_email {
xlcocoon@foxmail.com
}
notification_email_from keepalived@showjoy.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
#主机配MASTER,备机配BACKUP
state MASTER
#所在机器网卡
interface ens33
virtual_router_id 51
#数值越大优先级越高
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
#虚拟IP
192.168.140.200
}
}
virtual_server 192.168.140.200 48066 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.140.125 48066 {
weight 1
TCP_CHECK {
connect_timeout 3
retry 3
delay_before_retry 3
}
}
real_server 192.168.140.126 48600 {
weight 1
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
启动验证
service keepalived start
登录验证
mysql -umycat -p123456 -h 192.168.140.200 -P 48066
关闭mycat
通过虚拟ip查询数据
mysql -umycat -p123456 -h 192.168.140.200 -P 48066
目前 Mycat 对于中间件的连接控制并没有做太复杂的控制,目前只做了中间件逻辑库级别的读写权限控制。是通过 server.xml
的 user 标签进行配置。
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
配置说明
标签属性 | 说明 |
---|---|
name | 应用连接中间件逻辑库的用户名 |
password | 该用户对应的密码 |
TESTDB | 应用当前连接的逻辑库中所对应的逻辑表。schemas 中可以配置一个或多个 |
readOnly | 应用连接中间件逻辑库所具有的权限。true 为只读,false 为读写都有,默认为 false |
测试案例一
使用user用户,权限为只读(readOnly:true) 验证是否可以查询出数据,验证是否可以写入数据
用user用户登录,运行命令:
mysql -uuser -puser -h 192.168.140.128 -P8066
切换到TESTDB数据库,查询orders表数据:
use TESTDB;
select * from orders;
执行插入数据sql:
insert into orders(id,order_type,customer_id,amount) values(7,101,101,10000);
可看到运行结果,插入失败,只有只读权限
测试案例二
使用mycat用户,权限为可读写(readOnly:false) 验证是否可以查询出数据,验证是否可以写入数据
用mycat用户登录,运行命令:
mysql -umycat -p123456 -h 192.168.140.128 -P8066
切换到TESTDB数据库,查询orders表数据:
use TESTDB;
select * from orders;
执行插入数据sql:
insert into orders(id,order_type,customer_id,amount) values(7,101,101,10000);
可看到运行结果,插入成功
在 user 标签下的 privileges 标签可以对逻辑库(schema)、表(table)进行精细化的 DML 权限控制。 privileges 标签下的 check 属性,如为 true 开启权限检查,为 false 不开启,默认为 false。 由于 Mycat 一个用户的 schemas 属性可配置多个逻辑库(schema) ,所以 privileges 的下级节点 schema 节点同样可配置多个,对多库多表进行细粒度的 DML 权限控制。
server.xml
配置文件privileges部分,配置orders表没有增删改查权限:
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<!-- 表级 DML 权限设置 -->
<privileges check="true">
<schema name="TESTDB" dml="1111" >
<table name="orders" dml="0000"></table>
<!--<table name="tb02" dml="1111"></table>-->
</schema>
</privileges>
</user>
配置说明
DML 权限 | 增加(insert) | 更新(update) | 查询(select) | 删除(select) |
---|---|---|---|---|
0000 | 禁止 | 禁止 | 禁止 | 禁止 |
0010 | 禁止 | 禁止 | 可以 | 禁止 |
1110 | 可以 | 可以 | 可以 | 禁止 |
1111 | 可以 | 可以 | 可以 | 可以 |
测试案例一
使用mycat用户,privileges配置orders表权限为禁止增删改查(dml=”0000”) 验证是否可以查询出数据,验证是否可以写入数据
重启mycat,用mycat用户登录,运行命令:
mysql -umycat -p123456 -h 192.168.140.128 -P8066
切换到TESTDB数据库,查询orders表数据:
use TESTDB;
select * from orders;
禁止该用户查询数据
执行插入数据sql:
insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);
可看到运行结果,禁止该用户插入数据
测试案例二
使用mycat用户,privileges配置orders表权限为可以增删改查(dml=”1111”) 验证是否可以查询出数据,验证是否可以写入数据
用mycat用户登录,运行命令:
mysql -umycat -p123456 -h 192.168.140.128 -P8066
切换到TESTDB数据库,查询orders表数据:
use TESTDB;
select * from orders;
可以查询到数据
执行插入数据sql:
insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);
可看到运行结果,插入成功
执行删除数据sql:
delete from orders where id in (7,8);
firewall标签用来定义防火墙; firewall 下 whitehost 标签用来定义 IP 白名单 blacklist 用来定义SQL 黑名单。
可以通过设置白名单 实现 某主机某用户可以访问 Mycat ,而其他主机用户禁止访问。
修改配置文件server.xml
配置firewall标签,配置只有 192.168.140.128 主机 可以通过 mycat 用户访问:
<firewall>
<whitehost>
<host host="192.168.140.128" user="mycat"/>
</whitehost>
</firewall>
重启Mycat后,192.168.140.128主机使用mycat用户访问
mysql -umycat -p123456 -h 192.168.140.128 -P 8066
可以正常访问,如下图
在此主机换user用户访问,禁止访问
在192.168.140.127主机用mycat用户访问,禁止访问
可以通过设置黑名单,实现 Mycat 对具体 SQL 操作的拦截,如增删改查等操作的拦截。
修改配置文件server.xml
配置firewall标签,配置禁止mycat用户进行删除操作:
<firewall>
<whitehost>
<host host="192.168.140.128" user="mycat"/>
</whitehost>
<blacklist check="true">
<property name="deleteAllow">false</property>
</blacklist>
</firewall>
重启Mycat后,192.168.140.128主机使用mycat用户访问
mysql -umycat -p123456 -h 192.168.140.128 -P 8066
可以正常访问,如下图
切换TESTDB数据库后,执行删除数据语句
delete from orders where id=7;
运行后发现已禁止删除数据,如下图
可以设置的黑名单 SQL 拦截功能列表
配置项 | 缺省值 | 描述 |
---|---|---|
selelctAllow | true | 是否允许执行 SELECT 语句 |
deleteAllow | true | 是否允许执行 DELETE 语句 |
updateAllow | true | 是否允许执行 UPDATE 语句 |
insertAllow | true | 是否允许执行 INSERT 语句 |
createTableAllow | true | 是否允许创建表 |
setAllow | true | 是否允许使用 SET 语法 |
alterTableAllow | true | 是否允许执行 Alter Table 语句 |
dropTableAllow | true | 是否允许修改表 |
commitAllow | true | 是否允许执行 commit 操作 |
rollbackAllow | true | 是否允许执行 rollback 操作 |
Mycat-web 是 Mycat 可视化运维的管理和监控平台,弥补了 Mycat 在监控上的空白。帮 Mycat 分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心,可以管理多个节点。 Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等,具备 IP 白名单、邮件告警等模块,还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。
tar -zxvf zookeeper-3.4.11.tar.gz
cp zoo_sample.cfg zoo.cfg
./zkServer.sh start
netstat -ant | grep 2181
下载安装包 http:// www.mycat.io/
安装包拷贝到 Linux 系统 opt 目录下 ,并解压:tar -zxvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
拷贝mycat-web文件夹到/usr/local目录下:cp -r mycat-web /usr/local
进入mycat-web的目录下运行启动命令:
cd /usr/local/mycat-web/
./start.sh &
Mycat-web服务端口为8082,查看服务是否已经启动:netstat -ant | grep 8082
通过地址访问服务:http://192.168.140.127:8082/mycat
在 Mycat-web 上可以进行 Mycat 性能监控,例如:内存分享、流量分析、连接分析、活动线程分析等等。