数据库应用_数据分片与mycat服务
1.数据分片; 2.部署mycat服务;3.基于mycat服务创建新库新表.
一,数据分片
1.数据分片,也叫分库分表,即将存放在一台数据库服务器中的数据,按照特定方式进行拆分,分散存放到其它多台服务器中,以分散单台服务器的负载.
2.垂直分割
将单个数据库中的多个表按业务分类,分散存储到不同的数据库中.
3.水平分割
按照表中指定字段的分片规则,将表记录按行切分,分散存储到多个数据库中.
4.Mycat软件介绍及作用
基于java的分布式数据库系统中间件,为高并发环境的分布式存储提供解决方案.mycat适合有大量数据写入的存储需求,支持mysql,Oracle,sqlserver,mongodb等软件,提供数据读写分离和分片服务,是基于阿里巴巴Cobar研发的开源软件.
5.分片规则
6.工作原理:mycat收到sql命令->解析命令涉及到的表->有分片规则->获取sql命令分片字段的值->匹配分片函数
->获取分片列表->转发sql命令到对应的分片服务器执行->收集和处理所有分片结果->响应客户端.
7.mycat目录下的文件结构
二,部署mycat
拓扑图:
192.168.4.50为客户端,在192.168.4.56上部署mycat分布式数据库服务,部署192.168.4.53,192.168.4.54,192.468.4.55作为逻辑库存储数据
在主机4.56上部署mycat:
1. 安装提供mycat服务的软件包
# yum -y install java-1.8.0-openjdk //安装jdk
# which java //查看生成的java目录
# java -version //查看版本
# tar -xvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz //解压
# mv mycat/ /usr/local/ //移动并改名
# ls /usr/local/mycat //查看生成的mycat目录
2. 修改配置文件
2.1 创建连接用户,定义客户端连接到mycat服务的用户
#vim/usr/local/mycat/conf/server.xml
<user name="root"> //连接mycat的用户名root
<property name="password">123456</property> //连接密码
<property name="schemas">TESTDB</property> //逻辑库名
</user>
<user name="user"> //第2个用户名user
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property> //只能读
</user>
2.2 定义数据分片的表table
# vim /usr/local/mycat/conf/schema.xml
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> //定义表格company的参数
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" /> //定义表格hostnew的参数
2.3 定义数据节点dn1,dn2,dn3
<dataNode name="dn1" dataHost="localhost53" database="db1" />
<dataNode name="dn2" dataHost="localhost54" database="db2" />
<dataNode name="dn3" dataHost="localhost55" database="db3" />
2.4 定义数据库服务器IP地址及端口
<dataHost name="localhost53" 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="hostM53" url="192.168.4.53:3306" user="admin"
password="123qqq...A">
</writeHost>
</dataHost>
3. 配置数据库服务器
3.1 创建存储的数据库
#mysql -uroot -p123qqq...A -e "create database db1" //4.53上建库db1
#mysql -uroot -p123qqq...A -e "create database db2" //4.54上建库db2
#mysql -uroot -p123qqq...A -e "create database db3" //4.55上建库db3
3.2添加授权用户
# mysql -uroot -p123qqq...A -e 'grant all on *.* to admin@"%" identified by "123qqq...A"' //分别在4.53,4.54,4.55上创建
# mysql -uroot -p123qqq...A -e 'select host,user from mysql.user' //查看创建的用户授权. //分别在4.53,4.54,4.55上查看
4.启动mycat服务,并查看服务信息
/usr/local/mycat/bin/mycat start
Netstat -apntul|grep :8806
5.客户端测试配置
5.1连接到mycat
# mysql -h192.168.4.56 -P8066 -uroot -p123456 //客户端4.50上测试
5.2选择库,查看表
mysql> show databases;
mysql> use TESTDB;
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| company |
| customer |
| customer_addr |
| employee |
| goods |
| hotnews |
| orders |
| order_items |
| travelrecord |
+------------------+
9 rows in set (0.00 sec)
5.3 测试mycat服务_枚举法
#vim /usr/local/mycat/conf/schema.xml (不做更改,只是查看该xml文件)
<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile" /> //以表格employee为例
#vim /usr/local/mycat/conf/rule.xml (不做更改,只是查看该xml文件)
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
# vim /usr/local/mycat/conf/partition-hash-int.txt
10000=0 (说明: 计算值为10000的时候,分片数据到第1台数据节点dn1存储数据)
10010=1 (说明: 计算值为10010的时候,分片数据到第2台数据节点dn2存储数据)
10020=2 (说明: 计算值为10020的时候,分片数据到第3台数据节点dn3存储数据) //新增
5.3.1 先停止服务,再重启服务,并查看端口
# /usr/local/mycat/bin/mycat stop
# /usr/local/mycat/bin/mycat start
# netstat -apntul|grep :8066
tcp6 0 0 :::8066 :::* LISTEN 1701/java
5.3.2 客户端连接到mycat,为表格employee创建表结构
# mysql -h192.168.4.56 -P8066 -uroot -p123456 //客户端登录到mycat服务器192.168.4.56
mysql> create table employee( //为employee创建表结构
-> ID int primary key, sharding_id int, name char(6),age int(8)
-> );
Query OK, 0 rows affected (1.23 sec)
mysql> desc employee;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | NULL | |
| sharding_id | int(11) | YES | | NULL | |
| name | char(6) | YES | | NULL | |
| age | int(8) | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)
5.3.3 在服务器4.53, 4.54, 4.55上查询表格结构是否生成
Mysql>show databases;desc db1; //在4.53上查看表结构
Mysql>show databases;desc db2; //在4.54上查看表结构
Mysql>show databases;desc db3; //在4.55上查看表结构
5.3.4 在表格employee里插入数据
mysql> insert into TESTDB.employee(ID,sharding_id,name,age) values(1,10000,"tom",18),(2,10010,"jack",19),(3,10020,"lucy",20);
Query OK, 3 rows affected (0.15 sec)
5.3.5 分别在4.53, 4.54, 4.55上查看插入的数据
mysql> select * from db1.employee; //ID为1的记录分配到4.53上存储
+----+-------------+------+------+
| ID | sharding_id | name | age |
+----+-------------+------+------+
| 1 | 10000 | tom | 18 |
+----+-------------+------+------+
1 row in set (0.00 sec)
mysql> select * from db2.employee; //ID为2的记录分配到4.54上存储
+----+-------------+------+------+
| ID | sharding_id | name | age |
+----+-------------+------+------+
| 2 | 10010 | jack | 19 |
+----+-------------+------+------+
1 row in set (0.00 sec)
mysql> select * from db3.employee; //ID为3的记录分配到4.55上存储
+----+-------------+------+------+
| ID | sharding_id | name | age |
+----+-------------+------+------+
| 3 | 10020 | lucy | 20 |
+----+-------------+------+------+
1 row in set (0.00 sec)
5.4 测试mycat服务_求模法
5.4.1 配置schema.xml
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3 rule="mod-long" /> //蓝色字体需要删除
5.4.2 配置rule.xml文件
<tableRule name="mod-long">
<rule>
<columns>num</columns> //定义字段名为num
<algorithm>mod-long</algorithm> //算法为求模
</rule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property> //定义服务器的数量,当前有3台,即4.53, 4.54, 4.55
</function>
5.4.3 停止服务,并重新启动,查看端口
# /usr/local/mycat/bin/mycat stop //停止mycat服务
# netstat -apntul|grep :8066 //查看端口停止状态
# /usr/local/mycat/bin/mycat start //启动mycat服务
# netstat -apntul|grep :8066 //查看端口启用状态
5.4.4 在客户端(4.50)验证求模分片规则
mysql> create table hotnews(
>num char(10),title char(15),comment char(20)
>);
Query OK, 0 rows affected (1.13 sec)
mysql> desc hotnews; //查看表结构
mysql> insert into hotnews(num,title,comment) values(3,"sc","xxxx");
Query OK, 1 row affected (0.16 sec) //插入数据
mysql> insert into hotnews(num,title,comment) values(10,"beijing","yyyy");
Query OK, 1 row affected (0.05 sec) //插入数据
mysql> insert into hotnews(num,title,comment) values(11,"shanghai","zzzz");
Query OK, 1 row affected (0.05 sec) //插入数据
mysql> select * from db1.hotnews; //num为3的记录,分配到4.53存储(算法:3/3,整除了)
+------+-------+---------+
| num | title | comment |
+------+-------+---------+
| 3 | sc | xxxx |
+------+-------+---------+
1 row in set (0.00 sec)
mysql> select * from db2.hotnews; //num为10的记录,分配到4.54存储(算法:10/3,余3)
+------+---------+---------+
| num | title | comment |
+------+---------+---------+
| 10 | beijing | yyyy |
+------+---------+---------+
1 row in set (0.00 sec)
mysql> select * from db3.hotnews; //num为11的记录,分配到4.55存储(算法:11/3,余6)
+------+----------+---------+
| num | title | comment |
+------+----------+---------+
| 11 | shanghai | zzzz |
+------+----------+---------+
1 row in set (0.00 sec)
三, 添加新库新表
#vim /usr/local/mycat/bin/mycat/user.xml //编辑用户文件user.xml
<user name="root">
<property name="password">123456</property>
<property name="schemas">TESTDB,NEWDB</property> //创建新库NEWDB
</user>
#vim /usr/local/mycat/bin/mycat/schema.xml //编辑概要文件schema.xml
<schema name="NEWDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> //为表格company添加一个全局类型
<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile" />
</schema>
# /usr/local/mycat/bin/mycat stop //停服务
# netstat -apntul|grep :8066 //查看端口
# /usr/local/mycat/bin/mycat start //启动服务
#]# mysql -h192.168.4.56 -P8066 -uroot -p123456 //客户端登录
mysql> create table company( id int,name char(10),address char(15)); //建表
Query OK, 0 rows affected (1.17 sec)
mysql> insert into company(id,name,address) values(1,"sina","beijing");
Query OK, 1 row affected (0.14 sec) //插入数据
mysql> insert into company(id,name,address) values(2,"baidu","beijing");
Query OK, 1 row affected (0.12 sec) //插入数据
mysql> insert into company(id,name,address) values(3,"jd","shenzheng");
Query OK, 1 row affected (0.25 sec) //插入数据
mysql> select * from company; //由于company是全局类型,故在3台服务器上都能查询到插入的信息
+------+-------+-----------+
| id | name | address |
+------+-------+-----------+
| 1 | sina | beijing |
| 2 | baidu | beijing |
| 3 | jd | shenzheng |
+------+-------+-----------+
3 rows in set (0.00 sec)
结束
