MyCat分库分表的基本 *** 作

MyCat分库分表的基本 *** 作

文章目录

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

MySQL的分库分表 什么是分库分表

将存放在一台数据库服务器中的数据，按照特定方式进行拆分，分散存放到多台数据库服务器中，以达到分散单台服务器负载的效果

分割方式：

• 垂直分隔
  • 纵向切分
    • 将单个数据库的多个表按业务类型分类，分散存储到不同的数据库

• 水平分隔

  • 横向切分

    • 按照表中指定字段的切片规则，将表记录按行切分，分散存储到多个数据库中
      

MyCAT介绍 软件介绍：

mycat是基于Java的分布式数据库系统中间件，为高并发环境的分布式存储提供解决方案

• 适合数据大量写入的存储需求
• 支持MySQL、Oracle、SQL server、mongodb等
• 提供数据读写分离服务
• 提供数据分片服务
• 基于阿里巴巴cobar进行研发的开源软件

分片规则

mycat支持提供10种分片规则

1. 枚举法（sharding-by-intfile）
2. 固定分片（rule1）
3. 范围约定（auto-sharding-long）
4. 求模法(mod-long)
5. 日期列分区法(sharding-by-date)
6. 通配取模(sharding-by-patterh)
7. ASCII码求模通配(sharding-by-prefixpattern)
8. 编程指定(sharding-by-substring)
9. 字符串拆分hash解析(sharding-by-stringhash)
10. 一致性hash(sharding-by-murmur)

工作过程

当Mycat收到一个SQL命令时

1. 解析SQL命令涉及到的表
2. 然后看对表的配置，如果有分片规则，则获取SQL命令里分片字段的值，并匹配分片函数，获得分片列表
3. 然后将SQL命令发往对应的分片服务器去执行
4. 最后收集和处理所有分片结果数据，并返回到客户端

拓扑结构

数据分片拓扑结构

主机名角色数据库IP地址client50客户端无192.168.4.50/24mycat56分片服务器无192.168.4.56/24mysql53数据库服务器db1192.168.4.53/24mysql54数据库服务器db2192.168.4.54/24mysql55数据库服务器db3192.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.数据分片配置

注：下面的注释不能出现在配置文件

定义分片的表
.....    #定义分片信息
.....  #定义表
name  #逻辑库名或者逻辑表名
dataNode   #指定数据库节点
rule   #指定使用的分片规则
type=global   #数据不分片存储

定义数据节点
  #定义数据节点
name   #数据节点名
datahost   #数据库服务器主机名
database   #数据库名

定义数据库服务器IP地址及端口
....
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



       #schema name="库名"，这个库名要和server.xml 定义的库名一致
       #因为我们这次实验使用了三台数据库服务器所以在dataNode后给没有dn3的，加上dn3
        
                
                
                
                
                
                
                        
                                
                        
                        
                
        
        #指定dn1的主机和数据库，如：dn1的主机为sql53，在其上面分库的数据库为db1
        
        
        
        #定义sql53的详细信息，主要修改dataHost name，url="主机IP:3306"，user="用户"，mycat通过哪个用户来连接数据库,建议不要使用root，password="密码",用户的密码，writeHost host="名字"，主机名，这个名字只要和其他数据库主机名不一致就行
        
                select user()
                
                
                
 
  #因为dataHost的配置只有一份，所以剩下的两份需要我们自行添加
        
                select user()
                
                
                
        

        
                select user()
                
                
                
        

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

在分片服务器上添加新库新表

....
TESTDB,gamedb
....
#使用只读用户也能访问新库新表，也需要添加这个新库名
  
                user
                TESTDB,gamedb
                true
        
..

        
              
              
        
...

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