Mycat中如何配置schmea.xml

这篇文章主要为大家展示了“Mycat中如何配置schmea.xml”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“Mycat中如何配置schmea.xml”这篇文章吧。

长白网站制作公司哪家好,找成都创新互联!从网页设计、网站建设、微信开发、APP开发、响应式网站等网站项目制作,到程序开发,运营维护。成都创新互联从2013年创立到现在10年的时间,我们拥有了丰富的建站经验和运维经验,来保证我们的工作的顺利进行。专注于网站建设就选成都创新互联

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.         dataNode="dn1">     ---默认数据节点,若新建一张表company2未在配置文件中配置,则默认建立在dn1数据节点上(私有表)

  2.                 

  3.                 

      ---分片表,根据对应的分片规则 分片到各个物理节点上

  4.                 

  5.                 

  6.    ---全局表,每个节点上都有的表

  7.                                   ---等同于company

  8.                                             ----等同于company2

  9.                 

  10.                 

  11.                 

  12.                            rule="mod-long" />

  13.                 

  14.                 

  15.                            rule="sharding-by-intfile" />

  16.                 

  17.                            rule="sharding-by-intfile">

  18.                         

  19.                                                 parentKey="id">

  20.                                 

  21.                                                         parentKey="id" />

  22.                         

  23.                         

  24.                                                 parentKey="id" />

  25.                 

  26.                 

  27.         

  28.         


    1.                      ----数据节点对应的localhost以及真实的数据库

    2.         

    3.         

    4.         balance="2"           ------localhosts对应的连接配置信息

    5.                           writeType="0"dbType="MySQL" dbDriver="native" switchType="1" slaveThreshold="100">

    6.                 select user()

    7.                 

    8.                 

    9.                                    password="ESBecs00">

    10.                         

    11.                 

    12.         

    13.         

    14.         

    15.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    16.                 select user()

    17.                 

    18.                 

    19.                                    password="ESBecs00">

    20.                         

    21.                 

    22.         

    23.         

    24.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    25.                 select user()

    26.                 

    27.                 

    28.                                    password="ESBecs00">

    29.                         

    30.                 

    31.         

    32. ......未完待续


    全局表的查询有负载均衡的作用

    1. mysql> select * from company;   ----本应该是相同的数据,改为不同的数据是为了展示负载均衡的效果

    2. +----+-------------+

    3. | id | name |

    4. +----+-------------+

    5. | 1 | this is 119 |

    6. +----+-------------+

    7. 1 row in set (0.01 sec)

    8. mysql> select * from company;

    9. +----+-------------+

    10. | id | name |

    11. +----+-------------+

    12. | 1 | this is 120 |

    13. +----+-------------+

    14. 1 row in set (0.01 sec)

    15. mysql> select * from company;

    16. +----+-------------+

    17. | id | name |

    18. +----+-------------+

    19. | 1 | this is 121 |

    20. +----+-------------+

    21. 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)

    10. mysql> select * from tt;    ---私有表 节点1上

    11. +------+------------+

    12. | id | name |

    13. +------+------------+

    14. | 2 | zhangsanli |

    15. | 1 | 12314 |

    16. +------+------------+

    17. 2 rows in set (0.00 sec)

    18. mysql> select a.*,b.* from tt a,order2 b where a.id=b.ID and b.id=2;   -----2和私有表不在一个节点上,查不出来

    19. Empty set (0.00 sec)

    20. mysql> select a.*,b.* from tt a,order2 b where a.id=b.ID and b.id=1;    -----1和私有表在一个节点上,所以查的出来

    21. +------+-------+----+----------+---------+---------------------+

    22. | id | name | ID | PROVINCE | SN | CREATE_TIME |

    23. +------+-------+----+----------+---------+---------------------+

    24. | 1 | 12314 | 1 | beijing | 2BJ0001 | 2017-04-23 21:48:08 |

    25. +------+-------+----+----------+---------+---------------------+

    26. 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. +---------------------+----+---------+---------------------+

以上是“Mycat中如何配置schmea.xml”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!


新闻名称:Mycat中如何配置schmea.xml
分享URL:http://scyanting.com/article/gdesjd.html