分库分表

                                                          ql-day10     分库分表

          

mysql>  insert into employee(id,name,sharding_id)values(

       1,"jingjing",10000),(2,"niuge",10010),(3,"tao",10000),(4,"bai",10010);    

                                       　

•什么是分库分表

–通过某种特定条件,将存放在一个数据库(主机)中的

数据,分散存放到多个数据库(主机)中。

–已达到分散单台设备负载的效果,即分库分表

–数据的切分根据其切分规则的类型,分为2种切分模式

–垂直分割(纵向)和水平分割(横向)

垂直分割

 

•纵向切分

–把单一的表,拆分成多个表,并分散到不同的数据库(主机)上。

–一个数据库由多个表构成,每个表对应不同的业务,可以按照业务对表进行分类,将其分布到不同的数据库(主机)上,实现专库专用,让不同的库(主机)分担不同的业务。

水平分割

 

•横向切分

–按照表中某个字段的某种规则,把向表中写入的记录分散到多个库(主机)中。

–简单来说,就是按照数据行切分,将表中的某些行存储到指定的数据库(主机)中。

• 软件介绍

•Mycat是基于Java的分布式数据库系统中间层,为高并发下的分布式提供解决方案

–支持JDBC形式连接

–支持MySQL、Oracle、Sqlserver、Mongodb等

–提供数据读写分离服务

–可以实现数据库服务器的高可用

–提供数据分片服务

–基于阿里巴巴Cobar进行研发的开源软件

–适合数据大量写入数据的存储需求

• 分片规则

•mycat服务提供10种分片规则。

–1枚举法sharding-by-intfile

–2固定分片rule1

–3范围约定auto-sharding-long

–4求模法mod-long

–5日期列分区法sharding-by-date

–6通配取模sharding-by-pattern

–7ASCII码求模通配sharding-by-prefixpattern

–8编程指定sharding-by-substring

–9字符串拆分hash解析sharding-by-stringhash

–10一致性hash sharding-by-murmur

• 工作过程

 

• 工作过程(续1)分片算法

•每种分片规则对应一种算法。

–1枚举法sharding-by-intfile->hash-int

–2固定分片rule1->func1

–3范围约定auto-sharding-long->rang-long

–4求模法mod-long->mod-long

–5日期列分区法sharding-by-date->sharding-by-date

•每种分片规则对应一种算法。

–6通配取模sharding-by-pattern->sharding-by-pattern

–7ASCII码求模通配sharding-by-prefixpattern

->sharding-by-prefixpattern

–8编程指定sharding-by-substring->sharding-by-substring

–9字符串拆分hash解析sharding-by-stringhash->sharding-by-stringhash

–10一致性hash sharding-by-murmur->murmur

•当Mycat收到一个SQL时

–会先解析这个SQL查找涉及到的表,然后看此表的定

–如果有分片规则,则获取到SQL里分片字段的值,并匹配分片函数,得到该QL对应的分片列表

–然后将SQL发往这些分片去执行,最后收集和处理所有分片返回的结果数据,并输出到客户端以select*from Orders where prov=?语句为例,查到prov=wuhan,按照分片函数,wuhan返回dn1,于是SQL就发给了MySQL1,去取DB1上的查询结果,并返回给用户。

-如果上述SQL改为elect*from Orders where prov in(‘wuhan’,‘beijing’),那么,SQL就会发给ySQL1与MySQL2去执行,然后结果集合并后输出给用户。但通常业务中我们的SQL会有OrderBy以及Limit翻页语法,此时就涉及到结果集在Mycat端的二次处理。

• IP规划

•具体配置如下:

拓扑名称主机名角色数据库名IP地址

hostA client客户端无192.168.4.254/24

hostB Mycat Mycat服务器无名192.168.4.56/24

hostC c1数据库服务器db1 192.168.4.55/24

hostD c2数据库服务器db2 192.168.4.54/24

1)装包

•安装JDK

[root@localhost~]#rpm- qa |grep -i  jdk#安装系统自带的即可

java-1.8.0-openjdk-1.8.0.65-3.b17.el7.x86_64

java-1.8.0-openjdk-headless-1.8.0.65-3.b17.el7.x86_64

•安装mycat服务软件包

[root@localhost~]#tar -zxf Mycat-server-1.4-beta-

20150604171601-linux.tar.gz#免安装,解压后即可使用

[root@localhost~]#mv mycat/ /usr/local/

[root@localhost~]#ls /usr/local/mycat/

bin catlet conf lib logs version.txt

2)修改配置文件

•目录结构说明

–bin mycat命令如启动停止等

–catlet扩展功能

–conf配置文件

–lib mycat使用的jar包mycat是java开发的

–log mycat启动日志和运行日志

–wrapper.log mycat服务启动日志,启动有问题可以看这个日志的内容

–mycat.log记录sql脚本执行后的具体报错内容

3)修改配置文件(续1)

•重要配置文件说明

设置连接mycat服务的账号、密码等

–server.xml

–schema.xml配置mycat使用的真实数据库和表

–rule.xml

定义mycat分片规则

•配置标签说明

–<user>.......</user>定义连接mycat服务时使用的用户和密码

及逻辑库的名字

–<datanode>......</datanode>指定数据节点(物理库的主机名和

存储分片数据的数据库名)

–<datahost>......</datahost>指定数据库服务器的IP地址及连

接数据库时使用的授权用户名及密码

4)修改配置文件(续2)

•修改配置文件/usr/local/mycat/conf/server.xml

<user name="test">#连接mycat服务时使用的用户名test

<property name="password">test</property>#使用

test用户连接mycat用户时使用的密码

<property name="schemas">TESTDB</property>#连接上mycat服务后,可以看到的库名多个时,使用逗号分隔(是逻辑上的库名)

</user>

<user name="user">

<property name="password">user</property>

<property name="schemas">TESTDB</property>

<property name="readOnly">true</property>#定义只读权限,使用定义的user用户连接mycat服务后只有读记录的权限

</user>

5)修改配置文件(续4）

•修改配置文件/usr/local/mycat/conf/schema.xml

–定义分片信息

   

6)修改配置文件(续4)

•修改配置文件/usr/local/mycat/conf/schema.xml

–定义分片信息

 

7)修改配置文件(续5)54,55设置

•修改数据库服务器配置文件

–添加对应设置后重启mysqld服务

–添加授权用户

Vim/etc/my.cnf

[mysqld]

......

lower_case_table_names=1#表名不区分字母大小写

[root@localhost~]#systemctl restart mysqld

//mycat连接数据库服务器用户

u 54建库db2,授权

MySQL>create database db2;

MySQL>grant all on*.*to admin@"%"identified by"123456";

u 55建库db1,授权

MySQL>create database db1;

MySQL>grant all on*.*to admin@"%"identified by"123456";

7）起服务查端口：如果有问题重起电脑，显示内存不足

 

[root@host57~]#/usr/local/mycat/bin/mycat start

[root@host57~]#/usr/local/mycat/bin/mycat status

[root@host57~]#ps -p 1503

[root@host57~]#netstat -anput | grep :8066

8)测试配置

•在客户端连接mycat服务器

–mysql–hmycat服务器ip地址–P端口–u用户–p密码

 

[root@room9pc17~]#mysql -h192.168.4.56 -P8066 -utest -p123456

MySQL[(none)]>show databases;

MySQL[(none)]>show tables;

[root@host57 conf]#cat partition-hash-int.txt

10000=0

10010=1

1.1建表 host57

MySQL[TESTDB]>create table employee(id int not null primary key,name     varchar(100),sharding_id int not null);

MySQL[TESTDB]>insert into employee(id,name,sharding_id)values(1,"jingjing",10000),(2,"niuge",10010),(3,"tao",10000),(4,"bai",10010);

MySQL[TESTDB]>desc employee;

1.2查看host55

mysql>use db1;

mysql>show tables;

mysql>select * from employee;

1.3查看host54

mysql>use db2;

mysql>show tables;

mysql>select*from employee;