当前位置：Gxlcms > 数据库问题 > mysql+mycat分片环境部署

mysql+mycat分片环境部署

时间：2021-07-01 10:21:17 帮助过：22人阅读

xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <table name="blog" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" /> </schema> <dataNode name="dn1" dataHost="server1" database="db1" /> <dataNode name="dn2" dataHost="server2" database="db2" /> <dataHost name="server1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="server_1" url="192.168.1.70:3306" user="root" password="123456" /> </dataHost> <dataHost name="server2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="server_2" url="192.168.1.71:3306" user="root" password="123456" /> </dataHost> </mycat:schema>

保存并退出

配置rule.xml

因为我们就两个mysql节点，且分片规则使用的是：rule="mod-long"，所以需要修改此文件内容，将mod-long默认的3个节点修改成2个，不然mycat无法启动。

既然启动失败，那么在后面的连接mycat的步骤会报以下错误：

ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘192.168.1.70‘ (111)

我曾在这里卡了一段时间，最终通过查看mycat的wrapper.log日志文件才找到原因的。

[root@localhost conf]# vi rule.xml

如下图：

技术分享

----------------------------------------------------------------------------------

下面这段是日志文件里的错误信息，可以不看，写出来的原因是方便自己以后理解

技术分享 
  1. STATUS | wrapper  | 2017/08/19 20:13:36 |<span style="color: #000000;"> TERM trapped.  Shutting down.
  2. STATUS </span>| wrapper  | 2017/08/19 20:13:37 | <--<span style="color: #000000;"> Wrapper Stopped
  3. STATUS </span>| wrapper  | 2017/08/19 20:13:41 | --><span style="color: #000000;"> Wrapper Started as Daemon
  4. STATUS </span>| wrapper  | 2017/08/19 20:13:41 |<span style="color: #000000;"> Launching a JVM...
  5. INFO   </span>| jvm 1    | 2017/08/19 20:13:41 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0<span style="color: #000000;">
  6. INFO   </span>| jvm 1    | 2017/08/19 20:13:41 | Wrapper (Version 3.2.3) http:<span style="color: #008000;">//</span><span style="color: #008000;">wrapper.tanukisoftware.org</span>
  7. INFO   | jvm 1    | 2017/08/19 20:13:41 |   Copyright 1999-2006<span style="color: #000000;"> Tanuki Software, Inc.  All Rights Reserved.
  8. INFO   </span>| jvm 1    | 2017/08/19 20:13:41 |<span style="color: #000000;"> 
  9. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |<span style="color: #000000;"> 
  10. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |<span style="color: #000000;"> WrapperSimpleApp: Encountered an error running main: java.lang.ExceptionInInitializerError
  11. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |<span style="color: #000000;"> java.lang.ExceptionInInitializerError
  12. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.MycatStartup.main(MycatStartup.java:53<span style="color: #000000;">)
  13. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |<span style="color: #000000;">     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  14. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62<span style="color: #000000;">)
  15. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43<span style="color: #000000;">)
  16. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at java.lang.reflect.Method.invoke(Method.java:498<span style="color: #000000;">)
  17. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:240<span style="color: #000000;">)
  18. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at java.lang.Thread.run(Thread.java:745<span style="color: #000000;">)
  19. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 | Caused by: io.mycat.config.util.ConfigException: Illegal table conf : table [ BLOG ] rule function [ mod-<span style="color: #0000ff;">long</span> ] partition size : 3 > table datanode size : 2, please make sure table datanode size =<span style="color: #000000;"> function partition size
  20. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.config.loader.xml.XMLSchemaLoader.checkRuleSuitTable(XMLSchemaLoader.java:563<span style="color: #000000;">)
  21. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.config.loader.xml.XMLSchemaLoader.loadTables(XMLSchemaLoader.java:396<span style="color: #000000;">)
  22. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.config.loader.xml.XMLSchemaLoader.loadSchemas(XMLSchemaLoader.java:173<span style="color: #000000;">)
  23. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:122<span style="color: #000000;">)
  24. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.config.loader.xml.XMLSchemaLoader.<init>(XMLSchemaLoader.java:83<span style="color: #000000;">)
  25. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.config.loader.xml.XMLSchemaLoader.<init>(XMLSchemaLoader.java:87<span style="color: #000000;">)
  26. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.config.ConfigInitializer.<init>(ConfigInitializer.java:74<span style="color: #000000;">)
  27. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.config.MycatConfig.<init>(MycatConfig.java:72<span style="color: #000000;">)
  28. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.MycatServer.<init>(MycatServer.java:144<span style="color: #000000;">)
  29. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     at io.mycat.MycatServer.<clinit>(MycatServer.java:96<span style="color: #000000;">)
  30. INFO   </span>| jvm 1    | 2017/08/19 20:13:42 |     ... 7<span style="color: #000000;"> more
  31. STATUS </span>| wrapper  | 2017/08/19 20:13:44 | <-- Wrapper Stopped
View Code 

创建数据库

192.168.1.70对应的mysql服务器创建数据库：db1

[root@localhost soft]# /usr/local/mysql/bin/mysql -uroot -p123456

mysql> create database db1;

----------------------------------------------------------------------------------

192.168.1.71对应的mysql服务器创建数据库：db2

[root@localhost soft]# /usr/local/mysql/bin/mysql -uroot -p123456

mysql> create database db2;

启动mycat

[root@localhost ~]# mycat start

连接mycat

[root@localhost mycat]# /usr/local/mysql/bin/mysql -u root -p -P 8066 -h 192.168.1.70

技术分享

如上图，表示逻辑数据库连接成功

说明：这里连接的就是逻辑数据库了

测试

1、 建表

选择逻辑数据库

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">use</span> TESTDB;
  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;"> blog ( 
  2. 　　id </span><span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  3. 　　title </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>) <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">, 
  4. 　　author </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">30</span><span style="color: #000000;">), 
  5. 　　</span><span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span><span style="color: #000000;"> (id) 
  6. ) ENGINE</span><span style="color: #808080;">=</span>MyISAM <span style="color: #0000ff;">DEFAULT</span> CHARSET<span style="color: #808080;">=</span>utf8;

执行完上面的sql语句，会看到

192.168.1.70 db1下有了blog表

192.168.1.71 db2下也有了blog表

2、插入数据

  1. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  2. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">2</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  3. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">3</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  4. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">4</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  5. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  6. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">6</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  7. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">7</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  8. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">8</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  9. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">9</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  10. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">10</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  11. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">11</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  12. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">12</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  13. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">13</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  14. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog(id,title) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">14</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span>);

3、查询

  1. <span style="color: #0000ff;">Select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> blog;

分别在server_1和server_2上执行查询语句

server_1返回的结果是：

技术分享

server_2返回的结果是：

技术分享

如果查询TESTDB逻辑数据库的话，出现的结果是：

技术分享

好，到此大功告成，作为入门篇非常简单。

远程连接mycat

开启端口8066

[root@localhost ~]# firewall-cmd --zone=public --add-port=8066/tcp --permanent

重启防火墙

[root@localhost ~]# firewall-cmd –reload

Navicat for MySQL连接

技术分享

技术分享

参考文献：

http://blog.csdn.net/testcs_dn/article/details/51438216  这篇主要参考环境配置

http://maosheng.iteye.com/blog/2306937 这篇文章主要参考它的简单配置schema.xml

http://www.jb51.net/article/114290.htm 这篇文章主要参考它的简单配置schema.xml

mysql+mycat分片环境部署

标签：add-port   64位   防火墙   pass   重启   部署安装   页面   ima   好的   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！