ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 【Mycat】schmea.xml配置文件学习

【Mycat】schmea.xml配置文件学习

原创 MySQL 作者：小亮520cl 时间：2017-04-17 11:32:47 0 删除 编辑

1.基本环境

dn1 localhost1 192.168.6.121:3306---writehost
192.168.6.121:3307---readhost
192.168.6.121:3308
dn2 locahost2 192.168.6.120:3306---writehost
192.168.6.120:3307---readhost
192.168.6.121:3308
dn3 localhost3 192.168.6.119:3306---writehost
192.168.6.119:3307---readhost
192.168.6.119:3308


2.schemal配置
  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">

  5.         <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">     ---默认数据节点，若新建一张表company2未在配置文件中配置，则默认建立在dn1数据节点上（私有表）
  6.                 <!-- auto sharding by id (long) -->
  7.                 <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />   ---分片表，根据对应的分片规则 分片到各个物理节点上

  9.                 <!-- global table is auto cloned to all defined data nodes ,so can join
  10.                         with any table whose sharding node is in the same data node -->

  11.
  12.                 <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />    ---全局表,每个节点上都有的表
  13.                                   ---等同于company
                                                ----等同于company2
  14.                 <table name="goods" primaryKey="ID" type="global" dataNode="dn1，dn2,dn3" />
  15.                 <!-- random sharding using mod sharind rule -->
  16.                 <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
  17.                            rule="mod-long" />
  18.                 <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
  19.                         needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
  20.                         rule="mod-long" /> -->
  21.                 <table name="employee" primaryKey="ID" dataNode="dn1,dn2"
  22.                            rule="sharding-by-intfile" />
  23.                 <table name="customer" primaryKey="ID" dataNode="dn1,dn2"
  24.                            rule="sharding-by-intfile">
  25.                         <childTable name="orders" primaryKey="ID" joinKey="customer_id"
  26.                                                 parentKey="id">
  27.                                 <childTable name="order_items" joinKey="order_id"
  28.                                                         parentKey="id" />
  29.                         </childTable>
  30.                         <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
  31.                                                 parentKey="id" />
  32.                 </table>
  33.                 <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
  34.                         /> -->
  35.         </schema>
  36.         <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
  37.                 /> -->

    38.
    1.         <dataNode name="dn1" dataHost="localhost1" database="examdb" />             ----数据节点对应的localhost以及真实的数据库
    2.         <dataNode name="dn2" dataHost="localhost2" database="examdb" />
    3.         <dataNode name="dn3" dataHost="localhost3" database="examdb" />



    7.         <dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"            ------localhosts对应的连接配置信息
    8.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    9.                 <heartbeat>select user()</heartbeat>
    10.                 <!-- can have multi write hosts -->
    11.                 <writeHost host="hostM1" url="192.168.6.121:3306" user="root"
    12.                                    password="ESBecs00">
    13.                         <!-- can have multi read hosts -->
    14.                 </writeHost>

    16.         </dataHost>
    17.         

    19.         <dataHost name="localhost2" maxCon="1000" minCon="10" balance="2"
    20.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    21.                 <heartbeat>select user()</heartbeat>
    22.                 <!-- can have multi write hosts -->
    23.                 <writeHost host="hostM1" url="192.168.6.120:3306" user="root"
    24.                                    password="ESBecs00">
    25.                         <!-- can have multi read hosts -->
    26.                 </writeHost>

    28.         </dataHost>

    30.         <dataHost name="localhost3" maxCon="1000" minCon="10" balance="2"
    31.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    32.                 <heartbeat>select user()</heartbeat>
    33.                 <!-- can have multi write hosts -->
    34.                 <writeHost host="hostM1" url="192.168.6.119:3306" user="root"
    35.                                    password="ESBecs00">
    36.                         <!-- can have multi read hosts -->
    37.                 </writeHost>

    39.         </dataHost>
    40. </mycat:schema>
    41. ......未完待续

    全局表的查询有负载均衡的作用
    1. mysql> select * from company;   ----本应该是相同的数据，改为不同的数据是为了展示负载均衡的效果
    2. +----+-------------+
    3. | id | name |
    4. +----+-------------+
    5. | 1 | this is 119 |
    6. +----+-------------+
    7. 1 row in set (0.01 sec)

    9. mysql> select * from company;
    10. +----+-------------+
    11. | id | name |
    12. +----+-------------+
    13. | 1 | this is 120 |
    14. +----+-------------+
    15. 1 row in set (0.01 sec)

    17. mysql> select * from company;
    18. +----+-------------+
    19. | id | name |
    20. +----+-------------+
    21. | 1 | this is 121 |
    22. +----+-------------+
    23. 1 row in set (0.00 sec)


    分片表和私有表如果不在一哥节点上就联合查不到了！

    uc分片表，4101在第二个节点上
    act为私有表，4101在第一个节点上
    select * from uc_coupon where COUPON_ID=4101 ;   --单独查，是有的
    select * from act_vote_info where id=4101;       ---单独查，是有的
    select a.*,b.* from uc_coupon a,act_vote_info b where a.COUPON_ID=b.id and b.id=4101 limit 1;  --联合查，查不到了，因为跨节点了！
    举例：
    1. mysql> select * from order2;   ---分片表
    2. +----+----------+---------+---------------------+
    3. | ID | PROVINCE | SN | CREATE_TIME |
    4. +----+----------+---------+---------------------+
    5. | 2 | shanghai | 2BJ0001 | 2017-05-09 15:01:33 |    ---节点2上
    6. | 1 | beijing | 2BJ0001 | 2017-04-23 21:48:08 |     ---节点1上
    7. | 3 | tianjin | 2BJ0001 | 2017-05-09 15:01:45 |
    8. +----+----------+---------+---------------------+ 
    9. 3 rows in set (0.00 sec)

    11. mysql> select * from tt;    ---私有表 节点1上
    12. +------+------------+
    13. | id | name |
    14. +------+------------+
    15. | 2 | zhangsanli |
    16. | 1 | 12314 |
    17. +------+------------+
    18. 2 rows in set (0.00 sec)

    20. mysql> select a.*,b.* from tt a,order2 b where a.id=b.ID and b.id=2;   -----2和私有表不在一个节点上，查不出来
    21. Empty set (0.00 sec)

    23. mysql> select a.*,b.* from tt a,order2 b where a.id=b.ID and b.id=1;    -----1和私有表在一个节点上，所以查的出来
    24. +------+-------+----+----------+---------+---------------------+
    25. | id | name | ID | PROVINCE | SN | CREATE_TIME |
    26. +------+-------+----+----------+---------+---------------------+
    27. | 1 | 12314 | 1 | beijing | 2BJ0001 | 2017-04-23 21:48:08 |
    28. +------+-------+----+----------+---------+---------------------+
    29. 1 row in set (0.00 sec)

    同理：

    分片表和分片表条件数据如果不在一个节点上就联合查不到了！
    1. mysql> select a.* ,b.* from order2 a,order3 b where a.id=b.id and a.id=2;    --单独都是有数据的，但是id相等的分在不同的节点上，还是不能跨节点
    2. Empty set (0.00 sec)
    mysql> select * from order2;
    +----+----------+---------+---------------------+
    | ID | PROVINCE | SN      | CREATE_TIME         |
    +----+----------+---------+---------------------+
    |  2 | shanghai | 2BJ0001 | 2017-05-09 15:01:33 |
    |  1 | beijing  | 2BJ0001 | 2017-04-23 21:48:08 |
    |  3 | tianjing | 2BJ0001 | 2017-05-15 14:52:17 |
    +----+----------+---------+---------------------+
    3 rows in set (0.00 sec)


    mysql> select * from order3;
    +----+----------+---------+---------------------+
    | ID | PROVINCE | SN      | CREATE_TIME         |
    +----+----------+---------+---------------------+
    |  2 | beijing  | 2BJ0001 | 2017-05-15 14:56:27 |
    |  1 | tianjing | 2BJ0001 | 2017-05-15 14:56:35 |
    |  3 | shanghai | 2BJ0001 | 2017-05-15 14:56:17 |
    +----+----------+---------+---------------------+
    3 rows in set (0.00 sec)


    解决办法：注解，详细用法见文档

    1. mysql> /*!mycat:catlet=demo.catlets.ShareJoin */ select b.sn,b.CREATE_TIME,a.CREATE_TIME from order2 a,order3 b where a.id=b.id;
    2. +---------------------+----+---------+---------------------+
    3. | CREATE_TIME | id | sn | CREATE_TIME |
    4. +---------------------+----+---------+---------------------+
    5. | 2017-04-23 21:48:08 | 1 | 2BJ0001 | 2017-05-15 14:56:35 |
    6. | 2017-05-09 15:01:33 | 2 | 2BJ0001 | 2017-05-15 14:56:27 |
    7. | 2017-05-15 14:52:17 | 3 | 2BJ0001 | 2017-05-15 14:56:17 |
    8. +---------------------+----+---------+---------------------+




来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29096438/viewspace-2137390/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【Python】python-django上传下载功能
下一篇： 【Mycat】Mycat读写分离与负载均衡2
请登录后发表评论 登录
全部评论
小亮520cl
专家姓名：陈亮
毕业以后专业任职数据库工程师职位，itpub一直作为自己的笔记记录的地方,blog写的不详细，草书笔记，仅供参考！

注册时间：2013-09-12

  • 博文量
    529
  • 访问量
    1126962

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593