文章目录
MySQL的分库分表
什么是分库分表
将存放在一台数据库服务器中的数据,按照特定方式进行拆分,分散存放到多台数据库服务器中,以达到分散单台服务器负载的效果
分割方式:
- 垂直分隔
- 纵向切分
- 将单个数据库的多个表按业务类型分类,分散存储到不同的数据库
- 纵向切分
-
水平分隔
-
横向切分
- 按照表中指定字段的切片规则,将表记录按行切分,分散存储到多个数据库中
- 按照表中指定字段的切片规则,将表记录按行切分,分散存储到多个数据库中
-
MyCAT介绍
软件介绍:
mycat是基于Java的分布式数据库系统中间件,为高并发环境的分布式存储提供解决方案
- 适合数据大量写入的存储需求
- 支持MySQL、Oracle、SQL server、mongodb等
- 提供数据读写分离服务
- 提供数据分片服务
- 基于阿里巴巴cobar进行研发的开源软件
分片规则
mycat支持提供10种分片规则
- 枚举法(sharding-by-intfile)
- 固定分片(rule1)
- 范围约定(auto-sharding-long)
- 求模法(mod-long)
- 日期列分区法(sharding-by-date)
- 通配取模(sharding-by-patterh)
- ASCII码求模通配(sharding-by-prefixpattern)
- 编程指定(sharding-by-substring)
- 字符串拆分hash解析(sharding-by-stringhash)
- 一致性hash(sharding-by-murmur)
工作过程
当Mycat收到一个SQL命令时
- 解析SQL命令涉及到的表
- 然后看对表的配置,如果有分片规则,则获取SQL命令里分片字段的值,并匹配分片函数,获得分片列表
- 然后将SQL命令发往对应的分片服务器去执行
- 最后收集和处理所有分片结果数据,并返回到客户端
拓扑结构
数据分片拓扑结构
主机名 | 角色 | 数据库 | IP地址 |
---|---|---|---|
client50 | 客户端 | 无 | 192.168.4.50/24 |
mycat56 | 分片服务器 | 无 | 192.168.4.56/24 |
mysql53 | 数据库服务器 | db1 | 192.168.4.53/24 |
mysql54 | 数据库服务器 | db2 | 192.168.4.54/24 |
mysql55 | 数据库服务器 | db3 | 192.168.4.55/24 |
在53,54,55还原为初始独立数据库服务器,停止上面的主从结构(如果有配置主从请停止,如果没有请忽略)
部署MyCat服务器
安装软件
安装jdk,系统自带的openjdk,或者去Java官网下载,使用mycat,Java环境版本不能低于1.8.0
yum -y install jdk-8u144-linux-x64.rpm #这里使用我自己下载的jdk包,没有使用系统自带的
java -version
安装mycat服务软件包
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mv mycat /usr/local/
可以在这个下载
链接:https://pan.baidu.com/s/1zz8-fTlhnX_WWgVt6n-jYQ
提取码:123a
软件目录结构
ls /usr/local/mycat/
bin #mycat命令
catlet #扩展功能
conf #配置文件
lib #mycat使用的jar包
logs #mycat服务启动日志
wrapper.log #mycat服务启动日志
mycat.log #记录SQL脚本执行后的报错内容
重要的配置文件
conf/rule.xml #分片规则
conf/schema.xml #设置数据分片
conf/server.xml #设置连接账号及逻辑库
其他文件 #函数调用文件
分片规则配置文件以 .txt 或者以 .properties结尾
修改配置文件
1.定义连接用户和逻辑库名
vim /usr/local/mycat/conf/server.xml #使用默认配置就行
2.数据分片配置
注:下面的注释不能出现在配置文件
定义分片的表
<schema>.....</schema> #定义分片信息
<table>.....</table> #定义表
name #逻辑库名或者逻辑表名
dataNode #指定数据库节点
rule #指定使用的分片规则
type=global #数据不分片存储
定义数据节点
<dataNode 选项=值,.../> #定义数据节点
name #数据节点名
datahost #数据库服务器主机名
database #数据库名
定义数据库服务器IP地址及端口
<datahost 选项=值,..>....</datahost>
name #主机名(与datahost对应的主机名)
host #主机名(与IP地址对应的主机名)
url #数据库服务器IP地址及端口号
user #数据库服务器授权用户
password #授权用户密码
#由于该配置文件较大,在学习的时候可以先进行备份,以防修改错误导致服务不能启动。
#在进行下面的操作之前可以,可以适当删除一些无用的配置,因为mycat也可以提供读写分离的服务,但本次实验只使用它分库分表的功能,所以在这里我删除了他的读写分离的配置
# sed -i '56,77d' schema.xml
# sed -i '39,42d' schema.xml
# sed -i '16,18d' schema.xml
#注:版本不同,请不要使用上述命令,或者不用删除多余配置
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="库名",这个库名要和server.xml 定义的库名一致
#因为我们这次实验使用了三台数据库服务器所以在dataNode后给没有dn3的,加上dn3
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
rule="mod-long" />
<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile">
<childTable name="orders" primaryKey="ID" joinKey="customer_id"
parentKey="id">
<childTable name="order_items" joinKey="order_id"
parentKey="id" />
</childTable>
<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
parentKey="id" />
</table>
</schema>
#指定dn1的主机和数据库,如:dn1的主机为sql53,在其上面分库的数据库为db1
<dataNode name="dn1" dataHost="sql53" database="db1" />
<dataNode name="dn2" dataHost="sql54" database="db2" />
<dataNode name="dn3" dataHost="sql55" database="db3" />
#定义sql53的详细信息,主要修改dataHost name,url="主机IP:3306",user="用户",mycat通过哪个用户来连接数据库,建议不要使用root,password="密码",用户的密码,writeHost host="名字",主机名,这个名字只要和其他数据库主机名不一致就行
<dataHost name="sql53" 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.4.53:3306" user="test"
password="123456">
</writeHost>
</dataHost>
#因为dataHost的配置只有一份,所以剩下的两份需要我们自行添加
<dataHost name="sql54" 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.4.54:3306" user="test"
password="123456">
</writeHost>
</dataHost>
<dataHost name="sql55" 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="hostM3" url="192.168.4.55:3306" user="test"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
3.配置数据库服务器(3台都要配)
根据分片配置做相应的设置
- 创建存储数据的库
- 添加mycat的连接用户
#192.168.4.53
mysql -uroot -p123456 -e "create database db1"
mysql -uroot -p123456 -e 'grant all on *.* to test@"%" identified by "123456"'
#192.168.4.54
mysql -uroot -p123456 -e "create database db2"
mysql -uroot -p123456 -e 'grant all on *.* to test@"%" identified by "123456"'
#192.168.4.55
mysql -uroot -p123456 -e "create database db3"
mysql -uroot -p123456 -e 'grant all on *.* to test@"%" identified by "123456"'
启动服务
1.启动服务
为了方便我们使用可以先做一个软连接,如果不做软连接就需要输入绝对路径
ln -s /usr/local/mycat/bin/mycat /usr/bin
mycat status #查看状态
mycat start
#如果启动失败,可以查看目录下的日志,wrapper.log
出现问题时,可以先检查自己的环境,比如jdk是否安装或者查看版本是否高于1.80
2.查看服务状态
ss -nultp | grep 8066 #mycat默认端口
3.测试配置
客户端连接mycat服务器访问数据
mysql -h 192.168.4.56 -P8066 -uroot -p123456
show databases; #这里的数据库为mycat 的测试数据库,在上面的配置文件中可以看到
use TESTDB;
show tables; #这里出现的表,在上面分片的配置文件中能看到,这些表为虚拟表
desc company; #可以看出这张表并没有表结构
接下来我们创建表,创建的时候要符合表的分片规则。
分片规则:
下面介绍几种常见的分片规则
sharding-by-intfile(枚举法)
- 字段值必须在列举的范围内选择
打开配置文件/schema.xml 可以看出employee使用的分片规则是sharding-by-intfile,接下来打开rule.xml查看详细规则,可以看出sharding-by-intfile分片规则采用了hash-int算法,由图三可以看出hash-int算法的配置文件存储在partition-hash-int.txt 中
由上面三图可以得出employee表要有一个ID字段作为主键,要有一个字段名为sharding_id,分片规则就是使用sharding_id做分片,分片算法使用hash-int
我们打开partition-hash-int.txt
vim /usr/local/mycat/conf/partition-hash-int.txt
10000=0
10010=1
10020=2
#定义分片字段枚举的值,默认只有两个,因为我们的数据库有三个,所以需要我们添加一行枚举,当值为10000,把数据写入第一台服务器
#当值为10010时,把数据写入第二台数据服务器,当值为10020时,把数据写入第三台
重启服务
mycat restart
ss -nultp | grep 8066
根据分片规则和配置进行建表
接下来在客户端连接数据库
mysql -h 192.168.4.56 -P8066 -uroot -p123456
use TESTDB;
create table employee ( ID int primary key auto_increment, sharding_id int , name char(15), home char(30), sex enum("man","woman") );
desc employee;
然后我们在其余的数据库中就可以查到相应的数据
55服务器
54服务器
53服务器
ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0
创建表时可能会出错,出现这个错误,请大家仔细检查自己的配置文件和用户,或者去数据库看自己的表是否创建成功或者创建正确
出现问题可以去日志文件查看那里出现问题了
接下来我们插入数据
在客户端插入数据
insert into employee(sharding_id,name,home,sex) values(10000,"tom","usa","man");
#sharing_id为10000,把这条数据插入到db1,
insert into employee(sharding_id,name,home,sex) values(10010,"tom1","usa","man");
insert into employee(sharding_id,name,home,sex) values(10020,"tom2","usa","man");
insert into employee(sharding_id,name,home,sex) values(10030,"tom3","usa","man");
#在这里输入sharing_id为300时,因为配置文件中并没有定义这个数据源,所以会失败
查询数据是否成功添加到相应的分片数据库上
55服务器
54服务器
53服务器
添加成功
mod-long(求模法)
- 根据字段值与设定的数字求模结果存储数据
接下来我们查看这个数据库的那张表使用了mod-long的规则
因为主键自增长会和求模发生冲突,所以我们这里删除主键的设置
接下来查看他的规则详情,在rule.xml中
这个代表了要使用表中的id字段去取余,所以我们的表中就需要有一个id字段,取余对应得算法也为mod-long(algorithm 对应的为算法名)。
接下来查看算法的详情
表中的id字段默认与3取余,这里的值可以修改,根据实际情况修改,当值为3时,取余结果只能为0、1、2,与4取余时,结果为0、1、2、3。这个数值一般为数据库服务器的个数
接下来重启服务
mycat restart
ss -nultp | grep 8066 #有端口说明配置文件修改没有错误
在客户端进行测试
mysql -h 192.168.4.56 -P8066 -uroot -p123456
use TESTDB;
create table hotnews ( id int , title char(15), comment char(50), worker char(20),uptime datetime);
desc hotnews;
在客户端查看表结构
55
54
53
在客户端插入记录
insert into hotnews(id,title,comment,worker,uptime) values(7,"linux","nginx","test",now());
#7和3取余为1,存储在第二台服务器
insert into hotnews(id,title,comment,worker,uptime) values(8,"math","Lobita","Lobita",now());
#8和3取余为2,存储在第三台服务器
insert into hotnews(id,title,comment,worker,uptime) values(9,"Language","Shout","Lu Xun",now());
#9和3取余为0,存储在第一台服务器
在数据库查看插入情况
53
54
55
不分片存储记录(type=global)
接下来我们查看这个数据库的那张表使用了不分片的规则
客户端创建表
mysql -h 192.168.4.56 -P8066 -uroot -p123456
use TESTDB;
create table company ( ID int primary key auto_increment, name char(15), addr char(100));
insert into company(name,addr) values ("QQ","shenzheng"),("ali","hangzhou");
接下来我们在数据库服务器查看,因为没有分片存储,所以三台服务器都有该表该数据
55
54
53
在分片服务器上添加新库新表
....
<property name="schemas">TESTDB,gamedb</property>
....
#使用只读用户也能访问新库新表,也需要添加这个新库名
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB,gamedb</property>
<property name="readOnly">true</property>
</user>
..
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="gamedb" checkSQLschema="false" sqlMaxLimit="100" >
<table name="user" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table name="game_pople" dataNode="dn1,dn2,dn3" rule="mod-long" />
</schema>
...
mycat restart
ss -nultp | grep 8066 #查看是否启动成功
接下来去客户端查看新添加的库表,并添加数据测试
mysql -h 192.168.4.56 -P8066 -uroot -p123456
use gamedb;
show tables;
create table user ( ID int primary key auto_increment, name char(15), host char(100));
insert into user(name,host) values ("tom","usa"),("bob","china");
create table game_pople ( id int ,equipment char(15), comment char(50) ,uptime datetime);
insert into game_pople(id,equipment,comment,uptime) values(7,"A","harm",now());
#7和3取余为1,存储在第二台服务器
insert into game_pople(id,equipment,comment,uptime) values(8,"B","defense",now());
在客户端查看
在数据库服务器查看
55
54
53