一什么是分库分表
将存放在一个数据库的(主机)中的数据,按照特定方式进行拆分,分散存放到多个数据库库(主机)中,以达到分散单台设备负载的效果
二数据的分片方式 :
分库/分表 水平分割 垂直分割
三 配置数据片
3.1 环境准备
3.2 配置mycat服务器------192.168.4.56
3.2.1 配置分片服务机器(192.168.4.56)
1 装包
检查环境 :
[root@host56 ~]# which java;
/usr/bin/java
[root@host56 ~]# java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)
[root@host56 ~]# rpm -qa | grep -i openjdk
java-1.8.0-openjdk-1.8.0.131-11.b12.el7.x86_64
java-1.8.0-openjdk-headless-1.8.0.131-11.b12.el7.x86_64
[root@host56 ~]# tar -zxvf Mycat-server-1.4-beta-20150604171601-linux.tar.gz
[root@host56 ~]# mv mycat/ /usr/local/
[root@host56 ~]# ls /usr/local/mycat/
bin catlet conf lib logs version.txt
2 目录结构
--bin //mycat 命令 如 启动 停止
---catlet //扩展功能
--- conf //配置文件
---lib //mycat 使用的jar
---log //mycat 服务启动的日志
--wrapper.log //mycat 服务启动的日志
--mycat.log 记录sol脚本执行后的报错内容
3 重要配置文件说明
rule.xml 分片规则
schema.xml 设置数据分片
server.xml 设置连接Mycat帐号
[root@host56 conf]# ls /usr/local/mycat/conf/ *.txt 记录分片规则的文本
4 修改配置文件
4.1 设置连接帐号
]# vim /usr/local/mycat/conf/server.xml
<user name="root"> 具有读写权限
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user"> 只有读
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
:wq
4.2配置数据的分片
]# cp /usr/local/mycat/conf/schema.xml /root/
]# sed -i '56,77d' /usr/local/mycat/conf/schema.xml //只是删除注释
]# sed -i '16,18d' /usr/local/mycat/conf/schema.xml 只是删除注释
]# sed -i '36,39d' /usr/local/mycat/conf/schema.xml //只是删除注释
]# vim /usr/local/mycat/conf/schema.xml
<schema ...> </schema ...>
<dataNode ..... />
<dataHost> </dataHost>
3.2.2 配置数据库服务器 (192.168.4.53/54/55)
1.创建存储数据库的库
主机53 mysql> create database db1
主机54mysql> create database db2
主机55mysql> create database db3
2 用户授权
主机53 mysql>grant all on *.* to jingyaya@"%" identified by "123qqq...A";
主机54mysql>grant all on *.* to jingyaya@"%" identified by "123qqq...A";
主机55mysql>grant all on *.* to jingyaya@"%" identified by "123qqq...A";
3.3 启动服务 ------- 192.168.4.56
]# /usr/local/mycat/bin/mycat status 查看状态
]# /usr/local/mycat/bin/mycat start //启动服务
]# netstat -utnlp | grep :8066 查看服务是否有起来i
tail -f /usr/local/mycat/logs/wrapper.log # 如果没有起来查看报错信息
3.4 客户端的测试 --192.168.4.50
]#mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> show databases;
mysql> use TESTDB;
mysql> show tables;
mysql> desc company;
3.5 根据分片配置,建表 然后存储数据
3.5.1 sharding-by-intfile 枚举规则
主机56 ]# vim /usr/local/mycat/conf/partition-hash-int.txt # 主机56 操作
10000=0 # 表示主机53 db1
10010=1 # 表示主机54 db2
10020=2 # 表示主机55 db3
:wq
]#/usr/local/mycat/bin/mycat stop
]#/usr/local/mycat/bin/mycat start
]#netstat -utnlp | grep :8066
50]#mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> use TESTDB;
mysql> create table employee(ID int primary key auto_increment,
-> sharding_id int , name char(15),age tinyint );
mysql> desc employee ;
mysql> insert into employee(sharding_id,name,age)
-> values
-> (10000,"bob",21),
-> (10010,"lucy",18),
-> (10020,"jerry",29),
-> (10020,"jack",34);
mysql> select * from employee;
53]# select * from employee; # 在主机53查看
54]# select * from employee; # 在主机54查看
55]# select * from employee; # 在主机55查看
3.5.2 求模法分片规则 mod-long
56 ]# vim /usr/local/mycat/conf/schema.xml
<table name="hotnews" dataNode="dn1,dn2,dn3"
rule="mod-long" />
:wq
56 ]# /usr/local/mycat/conf/rule.xml
<tableRule name="mod-long">
<rule>
<columns>num</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
56]# /usr/local/mycat/bin/mycat stop
56]# /usr/local/mycat/bin/mycat start
50]# mysql -h192.168.4.56 -uroot -p123456
mysql> use TESTDB;
mysql> create table hotnews(
num int, title char(50),
title char(50),
comment char(150),
worker char(30)
);
mysql> desc hotnews;
mysql> insert into hotnews(num,title,comment,worker)
values (99,"linux","cdls","nb"),
(23,"shell","whilefor","wk"),
(30,"ope","nginxtomcat","dmy");
mysql> insert into hotnews(num,title,comment,worker)
values(10,"mysql","xxxx","plj");
50mysql> select * from hotnews;
53mysql> select * from hotnews;
54mysql> select * from hotnews;
55mysql> select * from hotnews;
[root@host56 ~]# vim /usr/local/mycat/conf/rule.xml # 规则
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property> # 表示 num%3
3.5.3 数据存储不分片 type=global
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
50]# mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> use TESTDB;
mysql> create table company (
ID int primary key auto_increment ,
cname char(20),address char(50),
tel char(8),worker char(20));
mysql> insert into company(cname,address,tel,worker)
values
("tarena","beijing","666888","hsy"),
("tarena","beijing","666888","hsy");
53mysql> select * from db1.company;
54mysql> select * from db2.company;
55mysql> select * from db3.company;
3.6 添加新库/新表
3.6.1 添加新库
]# vim /usr/local/mycat/conf/server.xml
<user name="root">
.....
<property name="schemas">TESTDB,BBSDB</property>
:wq
3.6.2 添加新表
]# vim /usr/local/mycat/conf/schema.xml
<schema name="BBSDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="user" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<table name="pay" dataNode="dn1,dn2,dn3"
rule="mod-long" />
<table name="gz" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
</schema>
:wq
3.6.3 重启mycat服务
198 /usr/local/mycat/bin/mycat stop
199 /usr/local/mycat/bin/mycat start
200 netstat -utnlp | grep :8066
3.6.4 测试配置
50]# mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> show databases;
mysql> use BBSDB;
mysql> show tables;
mysql> create table pay( num int ,name char(15),money float(7,2));
mysql> insert into pay(num,name,money)
values(21,"bob",28000),(33,"tom",35000),(30,"ab",15);
mysql> insert into pay(num,name,money)
values(10,"bob2",28000),(34,"tom2",35000);
mysql> select * from pay #主机 53 测试 21%3 33%3 30%3 取余 0 所以只有在53主机上有
-> ;
+------+------+----------+
| num | name | money |
+------+------+----------+
| 21 | bob | 28000.00 |
| 33 | tom | 35000.00 |
| 30 | ab | 15000.00 |
+------+------+----------+
mysql> select * from pay; #在主机54测试 10%3 34%3 取余为1 所以只有在54主机上有
+------+------+----------+
| num | name | money |
+------+------+----------+
| 10 | xx | 28000.00 |
| 34 | tom2 | 35000.00 |
+------+------+----------+
2 rows in set (0.00 sec)