1.写在最前
本文主要介绍数据库中间件Mycat,适用于初次接触Mycat的朋友。为避免篇幅过长,本文分为上下两篇。上篇主要介绍Mycat的安装、读写分离、分库分表的实现。通过阅读此文,读者可以快速搭建Mycat,实现简单的分库分表。下篇主要介绍如何搭建高可用Mycat集群以及MyCat集群的运维管理,感兴趣的朋友可以关注下。
2.环境准备
2.1 虚拟机
条件有限的朋友可以通过虚拟机来模拟不同的机器,我使用的是Orcale公司轻量级的Virtualbox软件,管兴趣的朋友可以自行了解下。当初在虚拟机的安装上也花费了不少功夫,有需要的朋友,可以留言,我写篇详细的安装步骤。
2.2 mysql
2.2.1 安装mysql
配置mysql扩展源
rpm -ivh http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
yum安装mysql
yum install mysql-community-server -y
启动mysql
systemctl start mysqld (启动mysql)
systemctl enable mysqld (加入开机自启)
使用初始密码登录mysql
mysql -uroot -p$(awk '/temporary password/{print $NF}' /var/log/mysqld.log)
修改数据库密码规则(简化密码)
set global validate_password_policy=0;
set global validate_password_length=1;
修改密码
set password for root@localhost = password('123456');
登录测试
mysql -uroot -p123456
2.2.2 开启远程登录
记得重启mysql;
use mysql;
select host,user from user; //查看用户权限
update user set host = '%' where user = 'root';
select host, user from user;
systemctl stop mysqld;(停止mysql)
2.2.3 其他命令
mysql status mysqld; //查看mysql状态 ,绿色的active (running)表示正在运行
2.2.4 目录结构
目录 | 说明 |
---|---|
/var/lib/mysql | mysql数据文件存放路径,可自定义 |
/etc/my.cnf | mysql配置文件路径 |
/usr/lib64/mysql | mysql库文件路径 |
/var/log/mysqld.log | mysql日志文件路径 |
2.3 mycat
2.3.1 安装mycat
下载安装包
链接:https://pan.baidu.com/s/1HOlzsm2whh9PgMQU0go_pw
提取码:miwj
解压安装包
# 我将Mycat安装在/usr/local/目录下
tar -zxvf mycat.gz -C /usr/local/
2.3.2 配置mycat
cd /usr/local/mycat/conf
//1. 修改server.xml(系统配置)
# 用户名mycat,密码:123456,逻辑库:TESTDB,可随意定义,最好与Mysql的用户区分开,避免混淆。
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
//2. 修改schemal.xml(逻辑库、表、分片节点)
# 删除schema下的测试表,指定数据节点dn1
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
# 删除多余的数据节点dn2和dn3,只保留dn1,database为数据库dn1节点上的数据库名
<dataNode name="dn1" dataHost="host1" 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>
## 写主机
<writeHost host="M1" url="10.254.124.246:3306" user="root"
password="123456">
</writeHost>
</dataHost>
//3. 修改rule.xml(定义分片规则)
#后续有介绍
//4. 确保mycat这台机器可以连接数Mysql主机(m1)
mysql -u用户名 -p密码 -h IP -P 3306
2.3.3 启动mycat
方式一:控制台启动
#刚开始推荐使用该启动方式,可以看到Mycat对应的日志输出
./mycat console
方式二:后台启动
./mycat start
2.3.4 登录mycat
管理窗口登录:(运维,9066)
mysql -umycat -p123456 -h 10.254.124.188 -P 9066
数据窗口登录:(推荐,8066)
mysql -umycat -p123456 -h 10.254.124.188 -P 8066
2.3 机器名称
Mysql在主从同步状态下,数据是一致的;为了使主从数据不一致,我们使用系统变量@@hostname(主机名)来区分数据,将系统名改为易识别的有助于后续观察读写分离;
## 修改主机名
hostnamectl set-hostname 主机名;
hostname //查看主机名
3.读写分离
3.1 一主一从
mycat实现读写分离需要依赖于mysql的主从复制
3.1.1 主从复制
mycat只支持单主机,不同于redis,mycat从接入点开始复制(redis从头开始复制)
3.1.2 修改主机配置文件
mysql配置文件:my.cnf
修改配置文件
#服务器唯一Id(唯一Id)
server-id=1
#启用binlog
log-bin=mysql-bin
#设置不要复制的数据库(系统库)
binlog-ignore-db=mysql
#需要复制的数据(其余就是不需要复制的数据库)
binlog-do-db=需要复制的数据库 [注意:该表还没建!!!]
#设置binlog格式
binlof-format=STATEMENT
binlog格式
- STATEMENT: 将非查询语句发给从机,缺陷:如果数据库中存在now()会导致主从不一致
- ROW:将数据变化发给从机,缺陷:全表更新时,会产生大量的记录
- MIXED:混合模式,sql中没有会造成主从复制不一致的函数时,使用STATEMENT模式;存在会造成主从复制不一致的函数时,使用ROW模式;缺陷:系统变量(eg:@@host name 主机名称)会导致主从不一致
3.1.3 修改从机配置文件
#服务器唯一Id(唯一Id)
server-id=2
#启用中继日志
relay-log=mysql-relay
3.1.4 其余配置
- 重启mysql
systemctl restart mysqld
- 关闭防火墙
systemctl status firewalld //查看防火墙状态
systemctl stop firewalld //关闭防火墙
- 主机操作
## 为从机授权
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
## 查看主机状态
show master status; 【file:binLog文件名,position:接入位点】
- 从机操作
#重置主从服务(如果之前进行过主从复制)
stop slave;
reset master;
#复制主机的命令
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; [Slave_IO_Running、Slave_SQL_Running=yes则启动成功]
3.1.5主从复制测试
##主机创建数据库
create database db;//Binlog_Do_DB指定的数据库
##从机查看数据库是否生成
show databases;
##主机创建表
use db; //先切库
create table user(id int,name varchar(20));
insert into user values(1,'sp');
## 从机查看数据是否同步
select * from user;
# 重启mycat
./mycat stop;
./mycat start;
use TESTDB;
select * from user;
3.1.6 读写分离测试
如何制造不一致的数据? 系统变量
#主机插入
insert into usr values (2,@@hostname);
# 查看主从对应的数据
select * from usr ;
# mycat查数据
name = master
mycat每次查找的数据都是主机的数据。(原因见下小节)
3.1.7 balance 设置
的balance属性配置读写分离的类型
类型 | 描述 |
---|---|
0 | 不开启读写分离机制,所有读操作都发送到当前可用的 写主机 上 |
1 | 全部的 读主机 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从 模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。 |
2 | 所有读操作都随机的在 writeHost、readhost 上分发。 |
3 | 所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力 |
生产中:如果是一主一从,使用类型3,如果是多主多从,使用模式1;
测试:使用类型2,观察数据切换。
3.2 双主双从
3.2.1 主备架构
主从架构虽然实现了读写分离,但是不能保证高可用性。不同于Redis中从机可以充当备机,Mycat中的备机和从机不同,正常情况下,M1负责写请求,S1、M2、S2负责读请求;M1宕机后,M2负责写请求,S1、S2负责读请求,M1恢复后,作为M2的备机。
3.2.2 主从配置
tip: 注意删除一主一从时的数据库以及重置主从关系。
3.2.2.1 双主机配置
- master1:
#修改配置文件: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:
修改配置文件: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
3.2.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.2.2.3 主从复制、主备复制
- 修改配置文件后,重启mysql
systemctl restart mysqld
- 确认所有服务器都关闭了防火墙
systemctl stop firewalld
- 确保mycat可以连接到其他服务器
mysql -u -p -h -P
- 在两台主机上建立slave用户
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
- 查询主机状态
记录下file和postion的值后就不要再操作;
#查询Master1的状态
show master status;
- 从机复制主机
#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;
#启动主从复制
start slave;
#查看主从复制
show slave status\G;
#成功标识
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 主备复制
现在S1复制M1,S2复制M2;我们还需要让M1和M2相互复制。
操作步骤同上(从机复制主机)
- 主从测试
//M1
create database;
create table;
insert into table;
//M2、S1、S2
观察对应的数据库、表、数据是否存在;
同理,在M2上操作,M1、S1、S2也会同步数据;
S1、S2上也可以插入数据,但不会被同步;
- 主从连接失败时,更新位点
stop slave;
change master to master_log_file='mysql-bin.006731', master_log_pos=4;
start slave;
3.2.3 配置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" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="mycat" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="M1" url="10.254.124.191:3306" user="root"
password="123456">
<readHost host="S1" url="10.254.125.2:3306" user="root" password="123456" />
</writeHost>
<writeHost host="M2" url="10.254.125.14:3306" user="root"
password="123456">
<readHost host="S2" url="10.254.125.5:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
- 负载均衡配置:
负载均衡类型取值,有4 种:
(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 上分发。
(4)balance="3",所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力
- writeType:
(1)writeType="0": 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个
(2)writeType="1": 1.5版本后已经废弃
- switchType:
(1)switchType="1" 默认值,自动切换。
(2)switchType="-1" 表示不自动切换
(3)switchType="2" 基于 MySQL 主从同步的状态决定是否切换。
3.2.4 读写验证
读写分离:
//mycat
insert into user(1,@@hostname);
select * from user; //M2、S1、S2随机读
主备切换:
//M1上执行
systemctl stop mysqld;
//mycat
insert into user; //M2变为主机
select * from user; //S1、S2随机读,M1上线后变为备机
4.垂直拆分
垂直拆分即分库操作,通常一个数据库中包含多张表,垂直拆分就是将这些表根据业务逻辑的不同拆分开来,然后分到不同的数据库中;
4.1 如何拆分
注意:不同机器上的表之间,不能进行关联查询。
分库原则:有关联关系的表要放在同一个库中
假如我们现在有四张表,如下,该如何拆分?
#客户表 rows:2万
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)
);
结果: 客户表一个库,订单表、订单明细表、订单状态字典表一个库。(拆分依据的是业务逻辑 --------订单表、订单明细表、订单状态字典表在业务上是耦合的,不能拆分到不同的数据库中)
4.2 分库操作
4.2.1 Mycat配置
- 修改schema配置文件
<mycat:schema xmlns:mycat="http://io.mycat/">
<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>
<writeHost host="M1" url="10.254.124.199:3306" user="root"
password="123456">
</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>
<writeHost host="M1" url="10.254.125.14:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
- 新增两个空白库
//M1 M2上操作
create database orders;
- 重启mycat
./mycat start
4.2.2 分库操作
//Mycat上操作
mysql -u用户名 -p密码 -h 机器Ip -P 8066 //连接mycat
依次创建用户表、订单表、订单明细表、订单枚举表 //建表语句见上
//M1
use orders;show tables; //orders、order_detail、dict_order_type
//M2
use orders;show tables; //customer
5.水平拆分
5.1 分表
Mysql单表存储数据是有瓶颈的,一般单表数据量超过1千万时,查询就会受限。
5.1.1 订单表
5.1.1.1 选字段
如果我们要对订单表进行水平拆分,该根据哪个字段进行拆分。
- Id: ①按范围拆(0-100,101-200),这样拆分容易导致新旧数据分布不均,而且人们通常很少会查询很久之前的订单,这样会造成,一台机器访问压力大,另一台却闲置,不能充分利用资源。 ②取模(比如Id对2取模,0,2,4在一台机器上,1,3,5在另一台机器上),这样同一个用户的订单可能会被拆分到不同机器上,查询某个用户订单时,需要从多台机器取数据。
- 创建时间:如果按照创建时间分表(假设订单表有create_time),那么请问哪台机器处理双十一的数据?显然这样是不合理的
- 客户Id(customer_id): 对客户Id取模拆分,这样不仅可以使数据分布比较均匀,而且能保证同一客户的订单数据保存在同一台机器上,是比较合理的拆分。
5.1.1.2 改配置
- schema.xml
#为 orders 表设置数据节点为 dn1、dn2,并指定分片规则为 mod_rule(自定义的名字)
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
- rule.xml
# 在 rule 配置文件里新增分片规则 mod_rule,并指定规则适用字段为 customer_id,
#选择分片算法 mod-long(对字段求模运算),customer_id 对两个节点求模,根据结果分片
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
# 配置算法 mod-long 参数 count 为 2,两个节点
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">2</property>
</function>
5.1.1.3 分表
- 我们在之前进行分库操作时,dn2(10.254.125.14)机器上并没有订单表,所有我们需要先在dn2上建订单表。
- 重启mycat,让配置生效
# mycat的bin目录下
./mycat restart
- 在mycat中插入数据
注意:插入语句表后面括号中的列名,不能省略;
不能写为:INSERT INTO orders VALUES (1,101,100,100100);因为mycat不知道哪个字段代表customer_id;
# 在 mycat 里向 orders 表插入数据,INSERT 字段不能省略
INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,102,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,103,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,102,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,103,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,102,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,103,100020);
# 分别在M1、M2上查看数据分布
select * from orders;
M1:
M2:
5.1.2 订单明细表
上面我们已经将订单表拆分到不同的数据库中,那么订单明细表该如何拆分。注意:订单表和订单明细表是要进行join查询的。
5.1.2.1 ER表
Mycat 借鉴了 Foundation DB 的设计思路,将子表的存储位置依赖于主表,并且物理上紧邻存放,因此彻底解决了 JION 的效率和性能问 题,根据这一思路,提出了基于 E-R 关系的数据分片策略,子表的记录与所 关联的父表记录存放在同一个数据分片上。
5.1.2.2 修改配置
- schema.xml
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
# primarykey:主键 joinKey:外键 parentKey:父表主键
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
5.1.2.3 分表
- 同理,我们需要现在dn2上创建订单明细表
- 重启mycat
- 插入数据
INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail2',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail3',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail4',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail6',6);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail5',5);
- mycat
select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id; //6条数据
- M1 M2
select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id; //3条数据
select * from orders_detail;//3条数据
# 订单表和其对应的订单详情存放在同一台机器上。
5.1.3 订单状态字典表
5.1.3.1 全局表
我们再来思考下,诸如订单状态字典表这样的字典表该如何存储,很明显不同机器都需要用到字典表,一般字典表都具有以下特征:
- 变动不频繁
- 数据量总体变化不大
- 数据规模不大
针对这样的表,mycat提供了一种特殊的表—全局表,全局表具有以下特征: - 全局表的插入、更新操作会实时在所有节点上执行,保持各个分片的数据一致性
- 全局表的查询操作,只从一个节点获取
- 全局表可以跟任何一个表进行 JOIN 操作
5.1.3.2 修改配置
- schema.xml
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
5.1.3.3 分表
- 同理,我们需要现在dn2上创建字典表
- 重启mycat
- 插入数据
# mycat操作
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
select * from dict_order_type; //2条数据
5.2 常用分片规则
5.2.1 取模分片
取模分片也是最常用的分片规则,5.1.1中订单表就是按照这种规则分片的。
5.2.2 枚举分片
通过在配置文件中配置可能的结果,自己配置分片,适用于特定的场景,比如有些业务
需要按照省份或区县来做保存,而全国省份区县固定的。
比如表:orders_ware_info
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)
);
5.2.2.1 配置
- schema.xml
<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>
- rule.xml
<tableRule name="sharding_by_intfile">
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
# columns:分片字段,algorithm:分片函数
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
<property name="defaultNode">0</property>
</function>
# mapFile:标识配置文件名称,type:0为int型、非0为String,
#defaultNode:默认节点:小于 0 表示不设置默认节点,大于等于 0 表示设置默认节点,
# 设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错
- partition-hash-int.txt
110=0 //北京-110,北京的路由到第一台机器
120=1 //天津-120,天津的路由到第二台机器
5.2.2.2 分表
- 重启 Mycat
- 访问Mycat创建表
- Mycat插入数据
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');
- 分片校验
select * from orders_ware_info;//mycat两条记录,M1、M2各一条。
5.2.3 范围分片
将分片字段划分为不同的范围,归属到不同的分片;
例如:我们将成绩合格的数据(score>s=60)放到同一个库,不合格的数据放到另外一个库中;
CREATE TABLE student_score
(
`id` INT AUTO_INCREMENT comment '学号',
`score` INT comment '成绩',
PRIMARY KEY(id)
);
5.2.3.1 配置
- schema.xml
<table name="student_score" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>
- rule.xml
<tableRule name="auto_sharding_long">
<rule>
<columns>score</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
# columns:分片字段,algorithm:分片函数
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
# mapFile:标识配置文件名称
# defaultNode:默认节点:小于 0 表示不设置默认节点,大于等于 0 表示设置默认节点,
# 设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错
- autopartition-long.txt
0-59=0 //成绩不合格的路由到第一台机器
60-100=1 //成绩合格的路由到第二台机器
5.2.3.2 分表
- 重启 Mycat
- 访问Mycat创建表
- Mycat插入数据
INSERT INTO student_score (id,score) VALUES (1,40);
INSERT INTO student_score (id,score) VALUES (2,80);
INSER INTO student_score (id,score) VALUES (3,58);
INSER INTO student_score (id,score) VALUES (4,98);
- 分片校验
select * from student_score;//mycat4条记录,M1、M2各2条。
5.2.4 日期分片
按日期将数据分到不同的数据库中;
#用户登陆信息表
CREATE TABLE login_info
(
`id` INT AUTO_INCREMENT comment '编号',
`user_id` INT comment '用户编号',
`login_date` date comment '登录日期',
PRIMARY KEY(id)
);
5.2.4.1 配置
- schema.xml
<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>
- rule.xml
<tableRule name="sharding_by_date">
<rule>
<columns>login_date</columns>
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
# columns:分片字段,algorithm:分片函数
<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2019-01-01</property>
<property name="sEndDate">2019-01-04</property>
<property name="sPartionDay">2</property>
</function>
#dateFormat :日期格式
#sBeginDate :开始日期
#sEndDate:结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入
#sPartionDay :分区天数,即默认从开始日期算起,分隔 2 天一个分区
必须设置结束日期,结束日期是循环的标记,如上,1月1、1月2分配到第一个节点上,1月3、1月4分配到第二个节点上,如果不配置结束日期,那么1月5、1月6将会分配到第三个节点上(并不存在),设置后,会从1月4日开启循环,1月5、1月6分配到第一个节点上,依次类推。
5.2.4.2 分表
- 重启 Mycat
- 访问Mycat创建表
- Mycat插入数据
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');
- 分片校验
select * from login_info;//mycat 6条,M1 4条,M2 2条
5.3 全局序列
我们一般设计表时,id通常采用自增策略;但是在分库分表的情况下,数据的自增主键已经无法保证全局唯一性了。为了解决该问题,Mycat为我们提供了全局序列,并提供了多种实现方式,推荐使用数据库方式;
5.3.1 本地文件
mycat会在配置文件中记录当前序列值,然后拦截insert语句,根据配置文件中的值设置ID,同时更新配置文件中的值。
缺点:抗风险能力差,Mycat主机宕机后,即使有从机,也没法读取主机的配置文件。
5.3.2 数据库方式
利用数据库的一个表来进行累加计数,为了提高效率,mycat会一次取出多个号段,然后存放在内存中。
如果Mycat宕机重启,那么会废弃原有号段,重新取一次号段;这样虽然会损失部分号段,但是不会重复。
5.3.2.1 设置全局序列
- 创建全局序列表
假设,我们在M1上创建全局序列表;
########################## M1 ##########################
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 ;
########################## 初始化全局序列表 ########################
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 1000,100);//从1000开始,步长100
- 修改Mycat配置
sequence_db_conf.properties
ORDERS=dn1 # 确保该值和节点对应
server.xml
# 全局序列类型:0-本地文件,1-数据库方式,2-时间戳方式
<property name="sequnceHandlerType">1</property>
- 重启mycat
mycat restart
- 验证全局序列
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);
# 重启Mycat后再插入
5.3.3 时间戳方式
全局序列ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加) 换算成十进制为 18 位数的 long 类型,每毫秒可以并发 12 位二进制的累加。
- ① 优点:配置简单
- ② 缺点:18 位 ID 过长
5.3.4 自主生成全局序列
可在 java 项目里自己生成全局序列,如下:
- ① 根据业务逻辑组合
- ② 可以利用 redis 的单线程原子性 incr 来生成序列