MyCat分库分表的基本操作

最新推荐文章于 2023-10-19 19:35:12 发布

我是菜鸟131

最新推荐文章于 2023-10-19 19:35:12 发布

阅读量1.7k

点赞数 1

分类专栏：数据库文章标签：数据库 mongodb mysql

本文链接：https://blog.csdn.net/hahaxixi131/article/details/122204772

版权

数据库专栏收录该内容

20 篇文章 1 订阅

订阅专栏

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的规则
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qp9OHrNp-1640703171549)(E:/Typort/image-20211228215802386.png)]

因为主键自增长会和求模发生冲突，所以我们这里删除主键的设置
在这里插入图片描述

接下来查看他的规则详情，在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
在这里插入图片描述

我是菜鸟131

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MyCat分库分表的基本操作

文章目录MySQL的分库分表什么是分库分表分割方式：MyCAT介绍软件介绍：分片规则工作过程拓扑结构部署MyCat服务器安装软件软件目录结构修改配置文件**1.定义连接用户和逻辑库名****2.数据分片配置**3.配置数据库服务器（3台都要配）启动服务1.启动服务2.查看服务状态3.测试配置分片规则：sharding-by-intfile（枚举法）mod-long（求模法）不分片存储记录（type=global）在分片服务器上添加新库新表MySQL的分库分表什么是分库分表将存放在一台数据库服务器中的数
复制链接

扫一扫