Mycat 学习笔记 番外篇之系统命令 —— reload config

1）基础说明

Mycat （1.5版本）默认开通2个端口，可以在server.xml中进行修改。

8066 数据访问端口，即进行 DML 和 DDL 操作。

9066 数据库管理端口，即 mycat 服务管理控制功能。

Mac 环境验证不通过，mysql 命令连接不到 mycat 服务端。

 

2）在 schema.xml 文件中增加一个新的数据表配置，下面红字标识

<schema name="TRDB" checkSQLschema="false" sqlMaxLimit="100">

　　<table name="t_traveldata" dataNode="dn3,dn4" rule="auto-sharding-long" />

　　<table name="t_vote" dataNode="dn3,dn4" rule="ruleProvince" /> 
</schema>

 

3）通过mysql 连接9066 端口，执行  reload @@config

D:\bin\mysql\MySQL_3308\bin>mysql -u test -ptest -P 9066
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-mycat-1.5-beta-20160111170158 MyCat Server (monitor)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> reload @@config ;
Query OK, 1 row affected (0.08 sec)
Reload config success

 

4）查看Mycat.log文件，确认数据加载成功

01/22 14:25:22.548 DEBUG [$_NIOREACTOR-0-RW] (ManagerQueryHandler.java:65) -[thread=$_NIOREACTOR-0-RW,class=ManagerConnection,id=2,host=0:0:0:0:0:0:0:1,port=9066,schema=null]reload @@config
01/22 14:25:22.618 INFO [BusinessExecutor0] (CacheService.java:187) -clear all cache pool
01/22 14:25:22.618 INFO [BusinessExecutor0] (EnchachePool.java:85) -clear cache SQLRouteCache
01/22 14:25:22.622 INFO [BusinessExecutor0] (DefaultLayedCachePool.java:100) -clear cache
01/22 14:25:22.622 INFO [BusinessExecutor0] (EnchachePool.java:85) -clear cache TableID2DataNodeCache.TESTDB_ORDERS
01/22 14:25:22.623 INFO [BusinessExecutor0] (EnchachePool.java:85) -clear cache ER_SQL2PARENTID
01/22 14:25:22.624 WARN [BusinessExecutor1] (ReloadConfig.java:168) -send ok package to client [thread=BusinessExecutor1,class=ManagerConnection,id=2,host=0:0:0:0:0:0:0:1,port=9066,schema=null]

 

5）Mysql 命令行验证

数据分片规则，hash-int ，默认2个数据结点。

根据province 字段分片，如果值为 10010 进入 datanode2 ，其他数据进行 datanode1 。

即对应  partition-hash-int.txt  中设置的结点编号 0 、1 。

<tableRule name="ruleProvince">
　　<rule>
　　　　<columns>province</columns>
　　　　<algorithm>hash-int</algorithm>
　　</rule>
</tableRule>

<function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap">
　　<property name="mapFile">partition-hash-int.txt</property>
　　<property name="type">0</property>
　　<property name="defaultNode">0</property>
</function>

 

partition-hash-int.txt

10000=0
10010=1
DEFAULT_NODE=1

 

mysql> CREATE TABLE `t_vote` ( `vid` INT NOT NULL, `province` INT NULL, `data
` VARCHAR(45) NULL, PRIMARY KEY (`vid`));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_vote (vid,province,data) values (1,10010,'row data 10010');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_vote (vid,province,data) values (2,10011,'row data 10011');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_vote (vid,province,data) values (3,10012,'row data 10012');

Query OK, 1 row affected (0.01 sec)

 

mysql> explain select * from t_vote;
+-----------+--------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------+
| dn1 | SELECT * FROM t_vote LIMIT 100 |
| dn2 | SELECT * FROM t_vote LIMIT 100 |
+-----------+--------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t_vote;
+-----+----------+----------------+
| vid | province | data |
+-----+----------+----------------+
| 1 | 10000 | row data 10000 |     ----> dn1
| 2 | 10011 | row data 10011 |     ----> dn1
| 3 | 10012 | row data 10012 |     ----> dn1
| 5 | 10001 | row data 10001 |     ----> dn1
| 1 | 10010 | row data 10010 |     ----> dn2
+-----+----------+----------------+
5 rows in set (0.01 sec)

 

6）reload @@config 和  reload @@config_all 差异

reload @@config 会加载schema.xml配置的调整。

reload @@config_all 会加载全局配置文件，包含各类分片规则配置。