数据分片概述
分库/分表
- 什么是分库分表？
- 将存放在一台数据库服务器中的数据，按照特定的方式进行拆分，分散存储到多台数据库服务器中，以达到分散单台服务器负载的效果
水平分割
- 横向切分
- 按照表中指定字段的分片规则，将表记录按行切分，分散存储到多个数据库中
垂直分割
- 纵向切分
- 将单个数据库的多个表按照业务类型分类，分散存储到不同的数据库
MyCAT介绍
- 软件介绍：
- 开源软件，是基于Java的分布式数据库系统中间件，为高并发环境的分布式存储提供解决方案
- 适合数据大量写入的存储需求
- 支持MySQL、Oracle、Sqlserver、MongoDB等
- 提供数据读写分离服务
- 基于阿里巴巴Cobar进行研发的开源软件
- 分片规则
- mycat支持提供10种分片规则
- 枚举法-----------sharding-by-intfile
- 固定分片--------rule 1
- 范围约定--------auto-sharding-long
- 求模法------------mod-long
- 日期列分区法—sharding-by-date
- 通配取模----------sharding-by-pattern
- ASCII码求模通配----sharding-by-prefixpattern
- 编程指定----------sharding-by-substring
- 字符串拆分hash解析------sharding-by-stringhash
- 一致性hash-------sharding-by-murmur
- 工作过程
- 当mycat收到一个SQL命令时
- 解析SQL命令涉及到的表
- 然后看对表的配置，如果有分片规则，则获取SQL命令里的分片字段的值，并匹配分片函数，获得分片列表
- 然后将SQL命令往对应的数据库去执行
- 最后收集和处理所有的分片结果数据，并返回到客户端
部署MyCAT服务
环境部署
IP规划
拓扑结构
服务部署
步骤：
1.安装软件JDK（系统自带），安装mycat服务软件包软件包在此提取码：w4e7
2. 配置数据分片
3. 配置数据库服务器
4. 启动Mycat服务
1.在数据库服务器53、54、55主机中分别创建用于分片式存储的库db1、db2、db3
[root@host53 ~]# mysql -uroot -p123456
mysql> create database db1;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
[root@host54 ~]# mysql -uroot -p123456
mysql> create database db2;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
[root@host55 ~]# mysql -uroot -p123qqq...A
mysql> create database db3;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
2.在host56主机安装mycat软件以及Java软件
[root@host56 ~]# yum -y install java-1.8.0-openjdk.x86_64
[root@host56 ~]# java -version ##显示版本
openjdk version "1.8.0_161"
OpenJDK Runtime Environment (build 1.8.0_161-b14)
OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode)
[root@host56 ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz ##不需要安装，解压就可以使用
[root@host56 ~]# ls
mycat
Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@host56 ~]# ls mycat/
bin catlet conf lib logs version.txt
[root@host56 ~]# mv mycat/ /usr/local/
[root@host56 ~]# ls /usr/local/
bin etc games include lib lib64 libexec mycat sbin share src
[root@host56 ~]# cd /usr/local/mycat/
[root@host56 mycat]# ls lib/*.jar Java的打包压缩文件
###################################################################
目录结构
[root@host56 mycat]# ls
bin ##mycat命令
catlet ##扩展功能
conf ##配置文件
lib ##mycat使用的jar包
logs ##mycat启动日志和运行日志
version.txt
wrapper.log ##mycat服务启动日志，在服务启动后自动生成
mycat.log ##记录SQL脚本执行后的报错内容，在服务启动后自动生成
###################################################################
配置文件说明
server.xml ##设置连接账号及逻辑库
schema.xml ##配置数据分片存储的表
rule.xml ##分片规则
其他文件 ##分片规则配置文件
###################################################################
3.配置Mycat服务
创建客户端连接件使用的用户 密码 逻辑库 权限
[root@host56 mycat]# vim /usr/local/mycat/conf/server.xml （使用默认配置即可）
<user name="root"> //连接mycat服务时使用的用户名
<property name="password">123456</property> //用户连接mycat用户时使用的密码
<property name="schemas">TESTDB</property> //逻辑库名
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property> //只读权限，连接mycat服务后只有读记录的权限,不写这一行则是可读可写
</user>
定义数据分片存储的表
---------------------------------------------------
定义分片的表
<schema>......</schema> ##定义分片信息
<table>......</table> ##定义表
name ##逻辑库或逻辑表名
dataNode ##指定数据节点
rule ##指定使用的分片规则
type=global ##数据不分片存储
定义数据节点
<dataNode 选项=值,....../> ##定义数据节点
name ##数据节点明
datahost ##数据库服务器主机名
database ##数据库名
定义数据库服务器IP地址及端口
<datahost 选项=值,......>...</datahost> ##服务器主机名
name ##主机名（与datahost对应的主机名）
host ##主机名（与IP地址对应的主机名）
url ##数据库服务器IP地址及端口号
user ##数据库服务器授权用户
password ##授权用户密码
---------------------------------------------------
[root@mycat56 mycat]# wc -l schema.xml //删除前查看总行数
77 /root/schema.xml
[root@host56 mycat]# cp /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml.bak #先做一次备份！！！
[root@host56 mycat]# vim /usr/local/mycat/conf/schema.xml
为了看到更为清晰的配置文件，可以将注释行删掉，大批量的删除后再进入到文件内逐行删除注释行<!-- -->,最后的配置文件大约为38行左右
[root@host56 mycat]# sed -i '56,77d' /usr/local/mycat/conf/schema.xml
[root@host56 mycat]# sed -i '39,42d' /usr/local/mycat/conf/schema.xml
[root@host56 mycat]# wc -l /usr/local/mycat/conf/schema.xml
38 /usr/local/mycat/conf/schema.xml
[root@host56 mycat]# vim /usr/local/mycat/conf/schema.xml
1 <?xml version="1.0"?>
2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
3 <mycat:schema xmlns:mycat="http://io.mycat/">
4
5 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
6 <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding- long" />
7
8 <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2, dn3" />
9 <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn 3" />
10 <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="d n1,dn2,dn3"
11 rule="mod-long" />
12 <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
13 rule="sharding-by-intfile" />
14 <table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
15 rule="sharding-by-intfile">
16 <childTable name="orders" primaryKey="ID" joinKey="customer_id "
17 parentKey="id">
18 <childTable name="order_items" joinKey="order_id"
19 parentKey="id" />
20 </childTable>
21 <childTable name="customer_addr" primaryKey="ID" joinKey="cust omer_id"
22 parentKey="id" />
23 </table>
24 </schema>
##定义数据库主机名及存储数据的库
25 <dataNode name="dn1" dataHost="mysql53" database="db1" />
26 <dataNode name="dn2" dataHost="mysql54" database="db2" />
27 <dataNode name="dn3" dataHost="mysql55" database="db3" />
28
##定义mysql53主机名对应的数据库服务器IP地址
29 <dataHost name="mysql53" maxCon="1000" minCon="10" balance="0" 30 writeType="0" dbType="mysql" dbDriver="native" switchType="1 " slaveThreshold="100">
31 <heartbeat>select user()</heartbeat>
32 <writeHost host="hostM1" url="192.168.4.53:3306" user="tian"
33 password="123qqq...A">
34 </writeHost>
35 </dataHost>
36
##定义mysql54主机名对应的数据库服务器IP地址
37 <dataHost name="mysql54" maxCon="1000" minCon="10" balance="0"
38 writeType="0" dbType="mysql" dbDriver="native" switchType="1 " slaveThreshold="100">
39 <heartbeat>select user()</heartbeat>
40 <writeHost host="hostM2" url="192.168.4.54:3306" user="tian"
41 password="123qqq...A">
42 </writeHost>
43 </dataHost>
44
##定义mysql55主机名对应的数据库服务器IP地址
45 <dataHost name="mysql55" maxCon="1000" minCon="10" balance="0"
46 writeType="0" dbType="mysql" dbDriver="native" switchType="1 " slaveThreshold="100">
47 <heartbeat>select user()</heartbeat>
48 <writeHost host="hostM3" url="192.168.4.55:3306" user="tian"
49 password="123qqq...A">
50 </writeHost>
51 </dataHost>
52 </mycat:schema>
4.配置数据库服务器
首先确认53、54、55主机是否有相对应的库，授权给配置文件中的用户登录
53主机：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> grant all on *.* to tian@"%" identified by "123qqq...A"; ##添加授权用户
Query OK, 0 rows affected, 1 warning (0.10 sec)
54主机：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> grant all on *.* to tian@"%" identified by "123qqq...A"; ##添加授权用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
55主机：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> grant all on *.* to tian@"%" identified by "123qqq...A"; ##添加授权用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
5.启动Mycat服务
测试数据库的授权用户（记得要关防火墙）
[root@host50 ~]# mysql -h192.168.4.53 -utian -p123qqq...A
[root@host50 ~]# mysql -h192.168.4.54 -utian -p123qqq...A
[root@host50 ~]# mysql -h192.168.4.55 -utian -p123qqq...A
启动服务
[root@host56 mycat]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@host56 mycat]# netstat -nutlp | grep 8066 ##查看端口
tcp6 0 0 :::8066 :::* LISTEN 1951/java
[root@host56 mycat]# ps -C java ##查看进程
PID TTY TIME CMD
1951 ? 00:00:02 java
[root@host56 mycat]# ls /usr/local/mycat/logs/ #此时查看，目录下就有了错误日志等文件
mycat.log mycat.pid wrapper.log
查看报错动态输出：
[root@host56 mycat]# tail -f /usr/local/mycat/logs/wrapper.log
6.客户端访问分片服务器56
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456 #用server.xml中定义的用户名密码登录
MySQL [(none)]> show databases; #查看库，显示的是配置文件中设定的虚假的库
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> show tables; #查看表，这些表也是在schema.xml中定义的表
+------------------+
| Tables in TESTDB |
+------------------+
| company |
| customer |
| customer_addr |
| employee |
| goods |
| hotnews |
| orders |
| order_items |
| travelrecord |
+------------------+
9 rows in set (0.00 sec)
MySQL [TESTDB]> desc company; #查看表结构，此时会报错，因为是分片式服务器中的虚拟表，所以会报出db1、 db2、 db3表中没有这个表
ERROR 1146 (42S02): Table 'db3.company' doesn't exist
MySQL [TESTDB]> desc company;
ERROR 1146 (42S02): Table 'db2.company' doesn't exist
MySQL [TESTDB]> desc company;
ERROR 1146 (42S02): Table 'db1.company' doesn't exist
====================================================================
如果启动服务后查不到端口号，可以进行如下操作
[root@host56 ~]# cd /usr/local/mycat/logs/
[root@host56 logs]# ls
2020-02 mycat.log mycat.pid wrapper.log
[root@host56 logs]# rm -rf mycat.pid
[root@host56 logs]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
===================================================================
分片规则
- 枚举法（sharding-by-intfile）
- 字段值必须在规则文件定义的值里选择
1. 查看表使用的分片规则
[root@host56 mycat]# vim /usr/local/mycat/conf/schema.xml
<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">
2. 查看分片规则使用的函数
[root@host56 mycat]# vim /usr/local/mycat/conf/rule.xml
<tableRule name="sharding-by-intfile"> #分片规则
<rule>
<columns>sharding_id</columns> #分片字段名
<algorithm>hash-int</algorithm> #算法，函数名
</rule>
</tableRule>
3. 查看函数确定分片字段名称
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap"> #函数调用的配置文件，在lib里，不要动！！！！！
<property name="mapFile">partition-hash-int.txt</property> #分片字段值列表配置文件
</function>
定义分片字段值列表 :
[root@host56 conf]# vim /usr/local/mycat/conf/partition-hash-int.txt
10000=0 #0代表schema.xml中第一台数据库服务器 对应dn1
10010=1 #1代表schema.xml中第二胎数据库服务器 对应dn2
10020=2 #2代表schema.xml中第三台数据库服务器 对应dn3
[root@host56 conf]# /usr/local/mycat/bin/mycat restart #重启
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@host56 conf]# ss -nutlp | grep 8066 #可以再次查看到端口则说明重启成功
tcp LISTEN 0 100 :::8066 :::* users:(("java",pid=2236,fd=78))
4.建表（表的字段由第1 2 3步决定）
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456 ##客户端访问服务
MySQL [(none)]> use TESTDB; #进到TESTDB库
MySQL [TESTDB]> create table employee( #创建表结构
-> ID int primary key auto_increment ,
-> sharding_id int ,
-> name char(10) ,
-> addree varchar(50) ,
-> mail varchar(50)
-> );
Query OK, 0 rows affected (0.13 sec)
MySQL [TESTDB]> desc employee; ##查看表结构
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| sharding_id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| addree | varchar(50) | YES | | NULL | |
| mail | varchar(50) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)
5.插入记录
插入数据：
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10030 ,"bob","usa" ,"bob@111.com");
ERROR 1064 (HY000): can't find any valid datanode :EMPLOYEE -> SHARDING_ID -> 10030
#sharding_id不能找到有效的数据节点，只能存放在partition-hash-int.txt里面的数据节点
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10000 ,"bob","usa" ,"bob@111.com"); ##存储在53服务器的db1库里的employee表里
Query OK, 1 row affected (0.03 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10000 ,"aaa","usa" ,"bob@111.com");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10000 ,"bbb","usa" ,"bob@111.com");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10010 ,"sss","usa" ,"bob@111.com"); ##存储在54服务器的db2库里的employee表里
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10010 ,"ddd","usa" ,"bob@111.com");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10010 ,"fff","usa" ,"bob@111.com");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10010 ,"ggg","usa" ,"bob@111.com");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10020 ,"qqq","usa" ,"bob@111.com"); ##存储在55服务器的db3库里的employee表里
Query OK, 1 row affected (0.05 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10020 ,"www","usa" ,"bob@111.com");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10020 ,"eee","usa" ,"bob@111.com");
Query OK, 1 row affected (0.00 sec)
MySQL [TESTDB]> insert into employee(sharding_id ,name ,addree ,mail) values(10020 ,"rrr","usa" ,"bob@111.com");
Query OK, 1 row affected (0.01 sec)
6.在数据库服务器53/54/55主机查看数据是否成功分片存储到各个主机
[root@host53 ~]# mysql -uroot -p123456
mysql> select * from db1.employee;
+----+-------------+------+--------+-------------+
| ID | sharding_id | name | addree | mail |
+----+-------------+------+--------+-------------+
| 1 | 10000 | bob | usa | bob@111.com |
| 2 | 10000 | aaa | usa | bob@111.com |
| 3 | 10000 | bbb | usa | bob@111.com |
+----+-------------+------+--------+-------------+
3 rows in set (0.00 sec)
mysql> select * from db2.employee;
+----+-------------+------+--------+-------------+
| ID | sharding_id | name | addree | mail |
+----+-------------+------+--------+-------------+
| 1 | 10010 | sss | usa | bob@111.com |
| 2 | 10010 | ddd | usa | bob@111.com |
| 3 | 10010 | fff | usa | bob@111.com |
| 4 | 10010 | ggg | usa | bob@111.com |
+----+-------------+------+--------+-------------+
4 rows in set (0.00 sec)
[root@host55 ~]# mysql -uroot -p123qqq...A
mysql> select * from db3.employee;
+----+-------------+------+--------+-------------+
| ID | sharding_id | name | addree | mail |
+----+-------------+------+--------+-------------+
| 1 | 10020 | qqq | usa | bob@111.com |
| 2 | 10020 | www | usa | bob@111.com |
| 3 | 10020 | eee | usa | bob@111.com |
| 4 | 10020 | rrr | usa | bob@111.com |
+----+-------------+------+--------+-------------+
4 rows in set (0.00 sec)
MySQL [TESTDB]> select * from employee;
+----+-------------+------+--------+-------------+
| ID | sharding_id | name | addree | mail |
+----+-------------+------+--------+-------------+
| 1 | 10000 | bob | usa | bob@111.com |
| 2 | 10000 | aaa | usa | bob@111.com |
| 3 | 10000 | bbb | usa | bob@111.com |
| 1 | 10020 | qqq | usa | bob@111.com |
| 2 | 10020 | www | usa | bob@111.com |
| 3 | 10020 | eee | usa | bob@111.com |
| 4 | 10020 | rrr | usa | bob@111.com |
| 1 | 10010 | sss | usa | bob@111.com |
| 2 | 10010 | ddd | usa | bob@111.com |
| 3 | 10010 | fff | usa | bob@111.com |
| 4 | 10010 | ggg | usa | bob@111.com |
+----+-------------+------+--------+-------------+
11 rows in set (0.11 sec)
- 求模法
- 根据字段值与设定的数字求模结果存储数据
1.查看配置文件使用求模法规则的表名
[root@host56 logs]# vim /usr/local/mycat/conf/schema.xml
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
rule="mod-long" />
2.查看表的分片字段名及使用的函数配置
[root@host56 logs]# vim /usr/local/mycat/conf/rule.xml
<tableRule name="mod-long">
<rule>
<columns>x</columns> #求模字段名，可自定义
<algorithm>mod-long</algorithm> #算法
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property> #字段与之求模的数字
</function>
3.重启mycat服务
[root@host56 logs]# /usr/local/mycat/bin/mycat stop
Stopping Mycat-server...
Mycat-server was not running.
[root@host56 logs]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@host56 logs]# /usr/local/mycat/bin/mycat status
Mycat-server is running (1846).
4.客户端连接mycat服务建表存储数据
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| company |
| customer |
| customer_addr |
| employee |
| goods |
| hotnews |
| orders |
| order_items |
| travelrecord |
+------------------+
9 rows in set (0.00 sec)
MySQL [(none)]> create table TESTDB.hotnews; #在客户端不能用库名.表名创建，因为分片式服务器最终是写到相对应的数据库服务器上的，而数据库服务器中并没有TESTDB这个虚拟库，我们需要进到虚拟库中创建写入数据
ERROR 3000 (HY000): No MyCAT Database selected
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> create table hotnews(ID int primary key auto_increment , x int , title char(25) , comment varchar(150) );
Query OK, 0 rows affected (10.10 sec)
MySQL [TESTDB]> insert into hotnews(x , title , comment) values( 10, "dddd" , "yyyy"); #在这里虽然我们设置了自增模式，但是我们也需要写id号，否则会报错
ERROR 1264 (22003): Out of range value for column 'ID' at row 1
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 11, "dddd" , "yyyy"); ##插入记录，11和3取余，余2，记录存储在55服务器的db3库里
Query OK, 1 row affected (0.07 sec)
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 11, "dddd" , "yyyy");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 10, "dddd" , "yyyy"); ##插入记录，10和3取余，余1，记录存储在54服务器的db2库里
Query OK, 1 row affected (0.02 sec)
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 10, "dddd" , "yyyy");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 9, "dddd" , "yyyy"); ##插入记录，9和3取余，余0，存储在53服务器的db1库里
Query OK, 1 row affected (0.04 sec)
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 9, "dddd" , "yyyy");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 11, "dddd" , "yyyy");
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 10, "dddd" , "yyyy");
Query OK, 1 row affected (0.00 sec)
MySQL [TESTDB]> insert into hotnews(ID ,x , title , comment) values(null , 9, "dddd" , "yyyy");
Query OK, 1 row affected (0.00 sec)
5.在3台数据库服务器本机查看数据
[root@host53 ~]# mysql -uroot -p123456
mysql> select * from db1.hotnews;
+----+------+-------+---------+
| ID | x | title | comment |
+----+------+-------+---------+
| 1 | 9 | dddd | yyyy |
| 2 | 9 | dddd | yyyy |
| 3 | 9 | dddd | yyyy |
+----+------+-------+---------+
3 rows in set (0.00 sec)
[root@host54 ~]# mysql -uroot -p123456
mysql> select * from db2.hotnews;
+----+------+-------+---------+
| ID | x | title | comment |
+----+------+-------+---------+
| 1 | 10 | dddd | yyyy |
| 2 | 10 | dddd | yyyy |
| 3 | 10 | dddd | yyyy |
+----+------+-------+---------+
3 rows in set (0.00 sec)
[root@host55 ~]# mysql -uroot -p123qqq...A
mysql> select * from db3.hotnews;
+----+------+-------+---------+
| ID | x | title | comment |
+----+------+-------+---------+
| 1 | 11 | dddd | yyyy |
| 2 | 11 | dddd | yyyy |
| 3 | 11 | dddd | yyyy |
+----+------+-------+---------+
3 rows in set (0.00 sec)
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
MySQL [TESTDB]> select * from hotnews;
+----+------+-------+---------+
| ID | x | title | comment |
+----+------+-------+---------+
| 1 | 9 | dddd | yyyy |
| 2 | 9 | dddd | yyyy |
| 3 | 9 | dddd | yyyy |
| 1 | 10 | dddd | yyyy |
| 2 | 10 | dddd | yyyy |
| 3 | 10 | dddd | yyyy |
| 1 | 11 | dddd | yyyy |
| 2 | 11 | dddd | yyyy |
| 3 | 11 | dddd | yyyy |
+----+------+-------+---------+
9 rows in set (0.07 sec)
添加新库/新表
- 服务端配置
1.添加新库
[root@host56 logs]# vim /usr/local/mycat/conf/server.xml #添加新的库
82 <property name="schemas">TESTDB,GAME</property>
97 <property name="schemas">TESTDB,GAME</property>
2.添加新表
[root@host56 ~]# vim /usr/local/mycat/conf/schema.xml #添加以下几行，添加虚拟表
5 <schema name="GAME" checkSQLschema="false" sqlMaxLimit="100">
6 <table name="teacher" dataNode="dn1,dn2,dn3" rule="mod-long" />
7 <table name="class" dataNode="dn1,dn2,dn3" type="global" />
8 </schema>
3.重启mycat服务
[root@host56 logs]# /usr/local/mycat/bin/mycat stop
Stopping Mycat-server...
Mycat-server was not running.
[root@host56 logs]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@host56 logs]# ss -nutlp | grep 8066
tcp LISTEN 0 100 :::8066 :::* users:(("java",pid=2762,fd=77))
- 客户端访问
1.客户端连接mycat服务
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
2.创建新表存数据
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| GAME | ##逻辑库
| TESTDB |
+----------+
2 rows in set (0.01 sec)
MySQL [(none)]> use GAME;
MySQL [GAME]> create table class(name char(10) ,age int);
Query OK, 0 rows affected (0.04 sec)
MySQL [GAME]> insert into class values("yyy" ,19);
Query OK, 1 row affected (0.07 sec)
MySQL [GAME]> insert into class values("yyy" ,19);
Query OK, 1 row affected (0.03 sec)
MySQL [GAME]> insert into class values("yyy" ,19);
Query OK, 1 row affected (0.01 sec)
MySQL [GAME]> insert into class values("yyy" ,19);
Query OK, 1 row affected (0.01 sec)
MySQL [GAME]> insert into class values("zzz" ,20);
Query OK, 1 row affected (0.01 sec)
MySQL [GAME]> insert into class values("zzz" ,20);
Query OK, 1 row affected (0.00 sec)
MySQL [GAME]> insert into class values("zzz" ,20);
Query OK, 1 row affected (0.02 sec)
MySQL [GAME]> select * from class;
+------+------+
| name | age |
+------+------+
| yyy | 19 |
| yyy | 19 |
| yyy | 19 |
| yyy | 19 |
| zzz | 20 |
| zzz | 20 |
| zzz | 20 |
+------+------+
7 rows in set (0.05 sec)
3.在数据库服务器查看
[root@host53 ~]# mysql -uroot -p123456
mysql> select * from db1.class;
+------+------+
| name | age |
+------+------+
| yyy | 19 |
| yyy | 19 |
| yyy | 19 |
| yyy | 19 |
| zzz | 20 |
| zzz | 20 |
| zzz | 20 |
+------+------+
7 rows in set (0.00 sec)
[root@host54 ~]# mysql -uroot -p123456
mysql> select * from db2.class;
+------+------+
| name | age |
+------+------+
| yyy | 19 |
| yyy | 19 |
| yyy | 19 |
| yyy | 19 |
| zzz | 20 |
| zzz | 20 |
| zzz | 20 |
+------+------+
7 rows in set (0.01 sec)
[root@host55 ~]# mysql -uroot -p123qqq...A
mysql> select * from db3.class;
+------+------+
| name | age |
+------+------+
| yyy | 19 |
| yyy | 19 |
| yyy | 19 |
| yyy | 19 |
| zzz | 20 |
| zzz | 20 |
| zzz | 20 |
+------+------+
7 rows in set (0.00 sec)
总结
- mycat介绍：做数据分片存储的软件
- 10种分片规则：枚举法，求模法
- 配置文件：配置连接用户，逻辑库名，逻辑表，分片字段名
- 服务管理：启动，停止，查看状态，kill killall
- 分片规则的使用：水平分割 mod-long sharding-by-intfile
- 添加新库新表
- 客户端连接
3218
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
