Mycat-02 命令详解+分片规则
1.Mycat 命令详解
mysql -uroot -p123456 -P9066 -h10.1.2.111
1.更新配置文件。例如更新schema.xml reload @@config; 2.开启关闭SQL监控分析功能 reload @@sqlstat=open/close; 3.设置慢SQL 时间阈值 reload @@sqlslow=10;
查看schema.xml中对应的database 和datanode
mysql> show @@database; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.00 sec) mysql> show @@datanode; +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ | NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME | +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ | dn1 | localhost1/db1 | 0 | mysql | 0 | 0 | 1000 | 23 | 0 | 0 | 0 | -1 | | dn2 | localhost1/db2 | 0 | mysql | 0 | 3 | 1000 | 18 | 0 | 0 | 0 | -1 | | dn3 | localhost1/db3 | 0 | mysql | 0 | 8 | 1000 | 25153 | 0 | 0 | 0 | -1 | +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
心跳状态
mysql> show @@heartbeat; +--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+ | NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP | +--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+ | hostM1 | mysql | 10.1.3.67 | 3606 | 1 | 0 | idle | 0 | 0,0,0 | 2018-03-29 15:47:30 | false | | hostS1 | mysql | 10.1.3.68 | 3606 | 1 | 0 | idle | 0 | 0,0,0 | 2018-03-29 15:47:30 | false | | hostS2 | mysql | 10.1.3.69 | 3606 | 1 | 0 | idle | 0 | 0,0,0 | 2018-03-29 15:47:30 | false | +--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
RS_CODE
1 --心跳正常
-1 --连接出错
-2 --连接超时
0 --初始化状态
若节点发生故障，会进行连续5次的周期检测，心跳连续失败后会变成-1.
查看mycat 前后端的连接
1.显示的是有哪些机器连接到了mycat 上
mysql> show @@connection;
+------------+------+------------+------+------------+------+--------+---------+--------+---------+---------------+
| PROCESSOR | ID | HOST | PORT | LOCAL_PORT | USER | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) |
+------------+------+------------+------+------------+------+--------+---------+--------+---------+---------------+
| Processor2 | 6 | 10.1.2.140 | 9066 | 19729 | root | NULL | utf8:33 | 1239 | 33169 | 3191 |
+------------+------+------------+------+------------+------+--------+---------+--------+---------+---------------+
强制关闭连接 kill @@connection 7;
2.查看mycat 有多少连接到后端的数据库上
mysql> mysql> show @@backend;
+------------+------+---------+-----------+------+--------+---------+---------+--------+--------+----------+------------+--------+
| processor | id | mysqlId | host | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema |
+------------+------+---------+-----------+------+--------+---------+---------+--------+--------+----------+------------+--------+
| Processor0 | 528 | 667 | 10.1.3.69 | 3606 | 10287 | 84836 | 20137 | 82174 | false | false | 0 | db3 |
| Processor0 | 736 | 744 | 10.1.3.67 | 3606 | 63074 | 18640 | 4459 | 17975 | false | false | 0 | db3 |
| Processor0 | 660 | 732 | 10.1.3.69 | 3606 | 52033 | 42732 | 10165 | 41372 | false | false | 0 | db3 |
| Processor3 | 735 | 770 | 10.1.3.69 | 3606 | 12023 | 18640 | 4459 | 17975 | false | false | 0 | db3 |
+------------+------+---------+-----------+------+--------+---------+---------+--------+--------+----------+------------+--------+
查看缓存
mysql> show @@cache; +---------------------------------------+-------+------+--------+------+------+---------------+---------------+ | CACHE | MAX | CUR | ACCESS | HIT | PUT | LAST_ACCESS | LAST_PUT | +---------------------------------------+-------+------+--------+------+------+---------------+---------------+ | ER_SQL2PARENTID | 1000 | 0 | 0 | 0 | 0 | 0 | 0 | | SQLRouteCache | 10000 | 1 | 16 | 2 | 1 | 1522311356967 | 1522311295461 | | TableID2DataNodeCache.TESTDB_ORDERS | 50000 | 0 | 0 | 0 | 0 | 0 | 0 | | TableID2DataNodeCache.TESTDB_EMPLOYEE | 10000 | 2 | 6 | 4 | 2 | 1522311356968 | 1522311354446 | +---------------------------------------+-------+------+--------+------+------+---------------+---------------+
各个字段的含义：
SQLRouteCache：SQL 语句路由缓存
TableID2DataNodeCache：缓存表主键和分片的对应关系
查看数据源状态，切换主从
mysql> show @@datasource; +----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+ | DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD | +----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+ | dn1 | hostM1 | mysql | 10.1.3.67 | 3606 | W | 0 | 10 | 1000 | 25631 | 53 | 12 | | dn1 | hostS1 | mysql | 10.1.3.68 | 3606 | W | 0 | 1 | 1000 | 25167 | 0 | 0 | | dn1 | hostS2 | mysql | 10.1.3.69 | 3606 | R | 0 | 10 | 1000 | 25561 | 0 | 0 | | dn3 | hostM1 | mysql | 10.1.3.67 | 3606 | W | 0 | 10 | 1000 | 25631 | 53 | 12 | | dn3 | hostS1 | mysql | 10.1.3.68 | 3606 | W | 0 | 1 | 1000 | 25167 | 0 | 0 | | dn3 | hostS2 | mysql | 10.1.3.69 | 3606 | R | 0 | 10 | 1000 | 25561 | 0 | 0 | | dn2 | hostM1 | mysql | 10.1.3.67 | 3606 | W | 0 | 10 | 1000 | 25631 | 53 | 12 | | dn2 | hostS1 | mysql | 10.1.3.68 | 3606 | W | 0 | 1 | 1000 | 25167 | 0 | 0 | | dn2 | hostS2 | mysql | 10.1.3.69 | 3606 | R | 0 | 10 | 1000 | 25561 | 0 | 0 | +----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
switch @@datasource localhost1:1 (writehost 的位标，默认从上到下0 开始)
这个命令会将原数据源的连接池中的连接关闭，并从新的数据源重新创建连接，此时mycat 不可用。
reload @@config 执行过程中mycat 服务也不可用。
查看系统日志
mysql> show @@syslog limit=10;
SQL 统计
show @@sql;
show @@sql.slow;
show @@sql.sum;
2.Mycat 分片规则
连续分片：自定义数字范围，按日期（天）分片，自然月分片
优点： 1.扩容无需迁移数据 2.范围条件查询消耗资源少 缺点： 1.存在热点数据的可能 2.并发访问的能力有可能受限于单个或少量的datanode
离散分片：取模分片，枚举分片，字符串hash，一致性hash
优点： 1.并发访问能力增强 2.范围查询性能提升 缺点 1.数据扩容涉及到数据迁移的问题 2.数据库连接消耗比较多
此规则优点在于扩容时迁移数据量比较少，前提分片节点比较多，虚拟节点分配多些。
虚拟节点少的缺点是会造成数据分布不够均匀
如果实际分片数量比较少，迁移量会比较多
Primary key 的特殊意义：
可以缓存id 的值跟datanode 的对应关系。下次查询时不必每个节点都搜索，直接路由到分片的节点上。
分片规则 = 分片字段+分片函数
tableRule标签
name: 指定分片唯一算法的名称
rule: 指定分片算法的具体内容包括columns 和 algorithm 两个属性
columns：指定对应的表中用于分片的字段
algorithm：对应function 标签的算法名称
Function 标签
Name: 分片函数名称，在该文件中唯一。
Class: 对应具体的分片算法，需要指定算法的具体类
Property：根据算法的要求指定
1.枚举分片
rule.xml
1. <tableRule name="sharding-by-intfile"> 2. <rule> 3. <columns>age</columns> 4. <algorithm>hash-int</algorithm> 5. </rule> 6. </tableRule> 7. <function name="hash-int" 8. class="io.mycat.route.function.PartitionByFileMap"> 9. <property name="mapFile">fun/partition-hash-int.txt</property> 10. <property name="type">0</property> 11. <property name="defaultNode">0</property> 12. </function>
type: 默认是0 表示integer 非零表示string
defaultNode: 如果遇到不识别的枚举值则 路由到0 节点。
schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> <!-- random sharding using sharding-by-intfile rule --> <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-intfile" /> </schema> <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="10.1.3.67:3606" user="mycat" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS1" url="10.1.3.68:3606" user="mycat" password="123456" /> <readHost host="hostS2" url="10.1.3.69:3606" user="mycat" password="123456" /> </writeHost> </dataHost>
规则文件信息 partition-hash-int.txt
100=0 200=1 300=2
datanode 的个数必须大于等于 partition-hash-int.tx 里面配置的个数
登录mycat 8066 端口
1.创建employee 表，会在三个datanode上都创建 CREATE TABLE `employee` ( `id` int(11) NOT NULL, `age` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2.插入数据 insert into employee values (1,100,database()); insert into employee values (2,200,database()); insert into employee values (3,300,database()); insert into employee values (4,400,database()); 3.查看数据 mysql> select * from employee; +----+------+------+ | id | age | name | +----+------+------+ | 1 | 100 | db1 | | 4 | 400 | db1 | | 2 | 200 | db2 | | 3 | 300 | db3 | +----+------+------+ 数据根据分片规则插入到三个datanode中。age = 400 不在枚举范围内则插入到默认节点0 中。
mysql> explain select * from employee; +-----------+----------------------------------+ | DATA_NODE | SQL | +-----------+----------------------------------+ | dn1 | SELECT * FROM employee LIMIT 100 | | dn2 | SELECT * FROM employee LIMIT 100 | | dn3 | SELECT * FROM employee LIMIT 100 | +-----------+----------------------------------+
也可以登录到写节点确认db1,db2,db3中的数据
浙公网安备 33010602011771号