MyCat:对MySQL数据库进行分库分表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MyCat:对MySQL数据库进行分库分表,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9014字,纯文字阅读大概需要13分钟。
内容图文
本篇前提:
mycat配置正确,且能正常启动。
1、schema.xml
<table>
标签:
dataNode -- 分片节点指定(取值:dataNode中的name属性值)rule ------ 分片规则选择(取值:rule标签中的name属性值)
- 1
- 2
- 3
[root@dras-test conf]# vim schema.xml 1 <?xml version="1.0"?> 2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 3 <mycat:schemaxmlns:mycat="http://io.mycat/"> 4 <schemaname="mycatdb"checkSQLschema="false"sqlMaxLimit="100"> 5 <!-- auto sharding by id (long) --> 6 <tablename="t_person"dataNode="dn1,dn2"rule="mod-long" /> 7 <tablename="t_user"primaryKey="id"dataNode="dn1,dn2"rule="sharding-by-murmur" /><!-- 全局表 --> 8 <!-- table name="province" type="global" dataNode="dn1,dn2,dn3" /> 9 10 <table name="student" dataNode="dn1,dn2" rule="auto-sharding-long-sharejoin" /> 11 <table name="score" dataNode="dn2,dn3" rule="auto-sharding-long-sharejoin" /> 12 <table name="score" dataNode="dn1,dn2" rule="auto-sharding-long-sharejoin" /> 13 <!-- ER分片 --> 14 <tablename="customer"dataNode="dn1,dn2,dn3"rule="auto-sharding-long-customer"> 15 <childTablename="orders"joinKey="customer_id"parentKey="id"/> 16 </table--> 17 18 <tablename="user"primaryKey="id"dataNode="dn1,dn2"rule="mod-long-test"> 19 <childTablename="cell"joinKey="user_id"parentKey="id"/> 20 <childTablename="note"joinKey="user_id"parentKey="id"/> 21 <childTablename="lit"joinKey="user_id"parentKey="id"/> 22 <childTablename="lit_usr"joinKey="user_id"parentKey="id"/> 23 </table> 24 25 </schema> 26 27 <dataNodename="dn1"dataHost="localhost1"database="db1" /> 28 <dataNodename="dn2"dataHost="localhost1"database="db2" /> 29 <dataNodename="dn3"dataHost="localhost1"database="db3" /> 30 31 <dataHostname="localhost1"maxCon="500"minCon="100"balance="2"32writeType="1"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"> 33 <heartbeat>select user()</heartbeat> 34 35 <writeHosthost="hostM1"url="localhost:3306"user="root"36password="" > 37 </writeHost> 38 39 </dataHost> 40 </mycat:schema>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
2、rule.xml
<tablerule>
标签
columns—— 指定分片列的列名;
algorithm—- 选择分片算法(function标签中的name属性) <function>
标签
定义算法,class–分片算法类名及路径; <count>
分片数,需要分成多少片; <mapFile>
范围分片时使用的规则; <type>
默认值是0,表示分片列的值是整数,非0表示是字符串。
[root@dras-test conf]# vim rule.xml <?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mycat:rule SYSTEM "rule.dtd"><mycat:rulexmlns:mycat="http://io.mycat/"><tableRulename="mod-long"><rule><columns>person_id</columns><algorithm>mod-long</algorithm></rule></tableRule><tableRulename="mod-long-test"><rule><columns>id</columns><algorithm>mod-long</algorithm></rule></tableRule><tableRulename="auto-sharding-long-customer"><rule><columns>id</columns><algorithm>auto-sharding-long-customer</algorithm></rule></tableRule><tableRulename="auto-sharding-long-sharejoin"><rule><columns>id</columns><algorithm>auto-sharding-long-sharejoin</algorithm></rule></tableRule><tableRulename="sharding-by-murmur"><rule><columns>uuid</columns><algorithm>murmur</algorithm></rule></tableRule><functionname="mod-long"class="io.mycat.route.function.PartitionByMod"><!-- how many data nodes --><propertyname="count">2</property></function><functionname="auto-sharding-long-customer"class="io.mycat.route.function.AutoPartitionByLong"><propertyname="mapFile">autopartition-long.txt</property></function><functionname="auto-sharding-long-sharejoin"class="io.mycat.route.function.AutoPartitionByLong"><propertyname="mapFile">autopartition-long-sharejoin.txt</property></function><functionname="murmur"class="io.mycat.route.function.PartitionByMurmurHash"><propertyname="seed">0</property><!-- 默认是0 --><propertyname="type">1</property><!-- 默认是0, 表示integer, 非0表示string--><propertyname="count">2</property><!-- 要分片的数据库节点数量,必须指定,否则没法分片 --><propertyname="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就是虚拟节点数是物理节点数的160倍 --><!-- <property name="weightMapFile">weightMapFile</property> 节点的权重,没有指定权重的节点默认是1。以properties文件的格式填写,以从0开始到count-1的整数值也就是节点索引为key,以节点权重值为值。>所有权重值必须是正整数,否则以1代替 --><propertyname="bucketMapPath">/usr/local/mycat/logs/bucketMapPath</property><!-- 用于测试时观察各物理节点与虚拟节点的分布情况,如果指定了这个属性,会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件,没有默认值,如果不指定,就不会输出任何东西 --></function></mycat:rule>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
3、说明
对于以上配置文件,选择一个来说明,其他类推。
对t_person表:
在sechma.xml中:
<table name="t_person" dataNode="dn1,dn2" rule="mod-long" />
说明,将其分别存在分片节点dn1和dn2上, 分别对应实际MySQL数据库的db1和db2:
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
数据库db1和db2又在分片主机localhost1上,localhost1是连接的实际MySQL服务器,
<writeHost host="hostM1" url="localhost:3306" user="root" password="" >
因此,t_person表会被按照rule=’mod-long’被分别存储在实际MySQL服务器的db1和db2中。
在rule.xml中,
mod-long算法指定其分片里是id,分片算法是mod-long,对id列进行取模。
count=2,说明对2取模,
取模后值为0,存入dn1,取模后值为1,存入dn2.
4、验证
在mycat数据库中创建含id列的t_person表,插入5条数据:
[root@dras-test ~]# mysql -uroot -p123456 -h127.0.0.1 -P8066Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> show databases;+----------+| DATABASE |+----------+| mycatdb |+----------+1 row in set (0.00 sec)mysql> use mycatdb;Database changedmysql> mysql> create table t_person(id int(11) primary key, name varchar(32));Query OK, 0 rows affected (0.04 sec)mysql> desc t_person;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | varchar(32) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> mysql> insert into t_person(id,name) values(1,"Moxiao1"),(2,"Moxiao2"),(3,"Moxiao3"),(4,"Moxiao4"),(5,"Moxiao5");Query OK, 5 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> mysql> select * from t_person;+----+---------+| id | name |+----+---------+| 2 | Moxiao2 || 4 | Moxiao4 || 1 | Moxiao1 || 3 | Moxiao3 || 5 | Moxiao5 |+----+---------+5 rows in set (0.04 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
在实际的物理MySQL服务器中,查看:
[root@dras-test conf]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 522063Server version: 5.1.71-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || db1 || db2 || db3 || estudy || mysql || test || yundras |+--------------------+8 rows in set (0.00 sec)mysql> select * from db1.t_person;+----+---------+| id | name |+----+---------+| 2 | Moxiao2 || 4 | Moxiao4 |+----+---------+2 rows in set (0.00 sec)mysql> select * from db2.t_person;+----+---------+| id | name |+----+---------+| 1 | Moxiao1 || 3 | Moxiao3 || 5 | Moxiao5 |+----+---------+3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
t_person表成功被mycat自动分散到db1和db2两个库的t_person中。
解决单表数据量大的问题。在以分片列为条件进行查询时,会先查找其所在的分片,缩小查找范围。
mysql> explain select * from t_person where id=3;+-----------+-----------------------------------------------+
| DATA_NODE | SQL |+-----------+-----------------------------------------------+
| dn2 | SELECT * FROM t_person WHERE id = 3 LIMIT 100 |+-----------+-----------------------------------------------+1 row in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
原文:https://www.cnblogs.com/jpfss/p/8205385.html
内容总结
以上是互联网集市为您收集整理的MyCat:对MySQL数据库进行分库分表全部内容,希望文章能够帮你解决MyCat:对MySQL数据库进行分库分表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。