本文共 7847 字,大约阅读时间需要 26 分钟。
将存放在一个数据库中的数据,按照特定方式进行拆分,分散存放到多个数据库(主机)中,以达到分散单台设备负载的效果
垂直分割(纵向切分) 将单个表拆分成多个表,分散到不同的数据库;将单个数据库的多个表进行分类,按业务类别分散到不同的数据库上
水平分割(横向切分)按照表中的某个字段的某中规则,把表中的许多按行切分,分散到多个数据库中+
依赖jave环境
支持JDBC形式连接,支持My'SQL,Oracle,Sqlserver,Mongodb
提供数据读写分离,实现数据库服务器的高可用
提供数据分片服务,适合数据大量写入数据的存储需求
1.枚举法 sharding
2.固定分片 rule1
3.范围约定 auto_sharding-long
4.求模法 mod-long
5.日期列分区法 sharding-by-date
6.通配取模 sharding-by-pattern
7.ASCII码求模通配 sharding-by-prefixpattern
8.编程指定 sharding-by-substring
9.字符串拆分hash解析 sharding-by-stringhash
10.一致性hash sharding-by-murmur
过程
当mycat收到SQL查询时,先解析这个SQL查找涉及到的表,然后看此表的定义,如有分片规则,则获取SQL里分片字段的值并匹配分片函数来获得分片列表,然后将SQL发往这些分片去执行,最后收集和处理所有分片结果数据,返回到客户端
[192.168.4.50] 客户端
[192.168.4.56] mycat
[192.168.4.53] 独立数据库服务器
[192.168.4.54] 独立数据库服务器
[192.168.4.55] 独立数据库服务器
[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 java tzdata-java-2017b-1.el7.noarchjavapackages-tools-3.4.1-11.el7.noarch*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 python-javapackages-3.4.1-11.el7.noarchtar -xf Mycat-server-1.4-beta-20150604171601-linux.tar.gz
[root@host56 ~]# mv mycat/ /usr/local/
[root@host56 ~]# ls /usr/local/bin catlet conf lib logs version.txt
[root@host56 mycat]# ./bin/mycat --help
Usage: ./bin/mycat { console | start | stop | restart | status | dump }bin:命令、lib:使用的jar、conf:配置、log:日志、catlet:扩展功能、wrapper.log:启动日志、mycat.log:报错内容
server.xml 设置连mycat的帐号信息
schema.xml 配置mycat的真实库表
*.properties结尾的文件不能动
rule.xml 分片规则(该文件不需配置)
<!-- - - --> 注释
<user>..</user> 定义连mycat用户信息
<datanode>..</datanode> 指定数据节点
<datahost>..</datahost> 指定数据库地址及用户信息
每个规则对应一个算法rule规则
conf/*.txt 算法调用配置文件
文件看看就好,按大写G到底端
"TESTDB"与server.xml配置一致不需改,数据节点对应的服务器 name=不可与其他重复,type=global全局,只改dataNode=dn1,dn2.dn3
dn1,2,3分别指定一个<datahost>
[192.168.4.53-55]mysql> grant all on *.* to jingyaya@'%' identified by "888888";
[192.168.4.53]创建与56配置文件写的的库一致 create database db1;
[192.168.4.54] ----> create database db2;
[192.168.4.55] ----> create database db3;
]# /usr/local/mycat/bin/mycat status 查看状态
]# /usr/local/mycat/bin/mycat start 启用
]# tailf /usr/local/mycat/logs/wrapper.log 查看有没有报错信息
]# /usr/local/mycat/bin/mycat status
Mycat-server is running (1778).
]# netstat -nutlp | grep :8066 #查看是否启了
排错日志: /usr/local/mycat/logs/wrapper.log
1.56配置文件写错字符
2.内存不足 error=‘Cannot allocate memory’
3. 56配置文件的库名对不上 <dataNode name="dn1" dataHost="localhost1" database="db3" />
4.暂时还没有补充,靠你来占位了~
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> show databases; #虚拟库 +----------+ | DATABASE | +----------+ | TESTDB | +----------+ mysql> use TESTDB;mysql> show tables; #逻辑表(虚拟表)
+------------------+ | Tables in TESTDB | +------------------+ | company | | customer | | customer_addr | | employee | | goods | | hotnews | | orders | | order_items | | travelrecord | +------------------+mysql>desc company; ERROR 1146 (42S02): Table 'db3.company' doesn't exist常用分片规则 | 说明 |
枚举法 sharding-by-intfile | 通过在配置文件中配置可能的枚举 id,自己配置分片 |
固定分片 rule1 | 类似于十进制的求模运算,区别在于是二进制的操作,是取 id 的二进制低 10 位,即 id 二进制 &1111111111。 |
范围约定 auto-sharding-long | 提前规划好分片字段某个范围属于哪个分片 |
求模法 mod-long | 对分片字段求摸运算 |
日期列分区法 sharding-by-date | 按天分片 |
通配取模 sharding-by-pattern | 取模运算与范围约束的结合,主要为了后续数据迁移做准备,即可以自主决定取模后数据的节点分布 |
ASCII码求模通配 sharding-by-prefixparrern | 类似于取模范围约束,此规则支持数据符号字母取模 |
编程指定 sharding-by-substring | 在运行阶段有应用自主决定路由到那个分片 |
字符串拆分hash解析 sharding-by-stringhash | 截取字符串中的 int 数值 hash 分片 |
一致性hash sharding-by-murmur | 一致性 hash 预算有效解决了分布式数据的扩容问题 |
详情请查阅--->
[56]# 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>
]# 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 # 删除多余的注释,非必要操作
1.
[56]# cat /usr/local/mycat/conf/schema.xml
<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
(分片规则)rule="sharding-by-intfile" />[56]# cat /usr/local/mycat/conf/rule.xml
<tableRule name="sharding-by-intfile">
<rule> (字段名) <columns>sharding_id</columns> (算法)<algorithm>hash-int</algorithm> </rule> </tableRule>sharding-by-intfile 枚举规则
[56]#vim /usr/local/mycat/conf/partition-hash-int.txt
10000=0
10010=110020=2
]#/usr/local/mycat/bin/mycat stop
]#/usr/local/mycat/bin/mycat start
]# netstat -nutlp | 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;
54]# select * from employee;
55]# select * from employee;
2.
[56]# cat /usr/local/mycat/conf/schema.xml
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
rule="mod-long" />
[56]# cat /usr/local/mycat/conf/rule.xml
<tableRule name="mod-long">
<rule> <columns>num</columns> 原字段id与上面重复了,修改为num <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;
<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;
]# vim /usr/local/mycat/conf/server.xml
<user name="root">
.....
<property name="schemas">TESTDB,BBSDB</property>
]# 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>
]# /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> 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);
注:
<schema>... </schema> 一个库
<table /> 一个表
一一得一,一二得二,三八二十四,八五四十.........
转载地址:http://veiqi.baihongyu.com/