Mycat数据库中间件系列（四）Mycat2的常用注解配置

在前面的mycat文章介绍里面我们使用了注解进行数据源的创建和查看数据源的状态信息，所以这里什么是注解呢？

在mycat下，注解其实就是相当于mycat支持的一些命令，当这些注解在客户端里面执行之后，服务端的mycat安装目录里面的${mycat}/conf目录下面的配置文件会发生变更。下面我们列举下常用的mycat注解配置。

序号	注解	示例
1	重置mycat的所有配置信息	/+ mycat:resetConfig{} /
2	创建一个mycat用户	/+ mycat:createUser{ "username":"${username}", "password":"${password}", "ip":"${ip}", "transactionType":"xa" } /
3	删除某个mycat用户	/+ mycat:dropUser{ "username":"user"} /
4	显示所有的mycat用户	/+ mycat:showUsers /
5	修改某张表的序列号为雪花算法	/+ mycat:setSequence{"name":"${db_name}_${table_name}","time":true} /;
6	创建数据源	/+ mycat:createDataSource{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ_WRITE", "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"${datasource_name}", "password":"${datasource_password}", "type":"JDBC", "url":"jdbc:mysql://127.0.0.1:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8", "user":"${datasource_username}", "weight":0 } /;
7	删除数据源	/+ mycat:dropDataSource{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ_WRITE", "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"newDs", "type":"JDBC", "weight":0 } /;
8	显示数据源	/+ mycat:showDataSources{} /
9	创建集群	/! mycat:createCluster{ "clusterType":"MASTER_SLAVE", "heartbeat":{ "heartbeatTimeout":1000, "maxRetry":3, "minSwitchTimeInterval":300, "slaveThreshold":0 }, "masters":[ "dw0" //主节点 ], "maxCon":2000, "name":"c0", "readBalanceType":"BALANCE_ALL", "replicas":[ "dr0" //从节点 ], "switchType":"SWITCH" } /;
10	删除集群	/! mycat:dropCluster{ "name":"testAddCluster" } /;
11	显示集群	/+ mycat:showClusters{} /
12	创建Schema (这里需要确保在物理库上已经存在这个如示例中的test_add_Schema库)	/+ mycat:createSchema{ "customTables":{}, "globalTables":{}, "normalTables":{}, "schemaName":"test_add_Schema", "shardingTables":{}, "targetName":"prototype" } /;
13	创建单表(用于读写分离,映射物理表)	/+ mycat:createTable{ "normalTable":{ "createTableSQL":"create table normal(id int)", "locality":{ "schemaName":"testSchema", //物理库 "tableName":"normal", //物理表 "targetName":"prototype" //目标 } }, "schemaName":"testSchema",//逻辑库 "tableName":"normal" //逻辑表 } /;
14	创建全局表	/+ mycat:createTable{ "globalTable":{ "createTableSQL":"create table global(id int)", "broadcast":[ { "targetName":"prototype" } ] }, "schemaName":"testSchema", "tableName":"global" } /;
15	创建范围分表	/+ mycat:createTable{ "schemaName":"testSchema", "shardingTable":{ "createTableSQL":"create table sharding(id int)", "partition":{ "schemaNames":"testSchema", "tableNames":"sharding", "targetNames":"prototype" }, "function":{ "clazz":"io.mycat.router.mycat1xfunction.PartitionConstant", "properties":{ "defaultNode":"0", "columnName":"id" } } }, "tableName":"sharding" } /;
16	显示session引用的IO缓冲块计数	/+ mycat:showBufferUsage{}/
17	显示用户	/+ mycat:showUsers{}/
18	显示schema	/+ mycat:showSchemas{}/
19	显示调度器	/+ mycat:showSchedules{}/
20	显示心跳配置	/+ mycat:showHeartbeats{}/
21	显示心跳状态	/+ mycat:showHeartbeatStatus{}/
22	显示实例状态	/+ mycat:showInstances{}/
23	显示Reactor状态	/+ mycat:showReactors{}/
24	显示线程池状态	/+ mycat:showThreadPools{}/
25	显示表	/+ mycat:showTables{"schemaName":"mysql"}/
26	显示mycat连接	/+ mycat:showConnections{}/
27	显示存储节点	/+ mycat:showTopology{//1.18后 "schemaName":"db1", "tableName":"normal" } /;
28	显示缓存的sql配置	/+ mycat:showSqlCaches /;
29	创建sql缓存	/+ mycat:createSqlCache{ "initialDelay":0, "name":"ping", "refreshInterval":60, "sql":"select 'X' ", "timeUnit":"SECONDS" } /; initialDelay:初始化时间 refreshInterval:刷新时间 timeUnit:时间单位,SECONDS,MINUTES,HOURS,DAYS sql:匹配的sql
30	删除缓存	/+ mycat:dropSqlCache{ "name":"ping" } /;
31	全量数据迁移 count:数据源读取的行数 username:连接数据源账户的名字 password:连接数据源账户的密码 name:是迁移任务名字,起区分作用 sql:读取结果集的sql url:JDBC连接字符串 batch:批量插入的批量 parallelism:用于查询,插入的线程数,这里不生效 schemaName:逻辑库名 tableName:逻辑表名	/+ mycat:MIGRATE{ "input":{ "count":64, "password":"123456", "properties":{}, "sql":"select from db1.input", "url":"jdbc:mysql://localhost:8066/mysql?username=root&password=123456&useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "username":"root" }, "name":"testNormalToNormal", "output":{ "batch":1000, "parallelism":1, "properties":{}, "schemaName":"db1", "tableName":"output" } } */;
32	查看数据迁移进度	/+ mycat:MIGRATE_LIST{} /;
33	停止数据迁移	/+ mycat:MIGRATE_STOP{ "id":"0dcfd5c4-450e-4379-b975-de802aee15a1" } /;
34	清理BINLOG同步任务	/+ mycat:BINLOG_CLEAR{ } /;
35	记录所有数据源的binlog文件以及位置并显示出来	/+ mycat:BINLOG_SNAPSHOT{ "name":"testBinlogSnapshot" } /;
36	binlog同步	/+ mycat:BINLOG_SYNC{ "connectTimeout":0,//binlog连接超时时间 "inputTableNames":[ "db1.travelrecord_input" ], "name":"test0", "outputTableNames":[ "db1.travelrecord_output" ] } /;
37	使用snapshotId查询mycat.ds_binlog得到快照信息,然后使用它的binlog文件和位置开始同步	/+ mycat:BINLOG_SYNC{ "connectTimeout":0, "inputTableNames":[ "db1.travelrecord_input" ], "name":"testBinlogGlobalToNormal", "outputTableNames":[ "db1.travelrecord_output" ], "snapshotId":"94516452-6151-433c-8890-071e43f618e0" } /;
38	停止binlog同步	/+ mycat:BINLOG_STOP{ "id":"ae1b8375-3e60-4152-be98-3792a59c5609" } /;
39	列出binlog同步任务	/+ mycat:BINLOG_LIST{ } /;
40	停止服务除了connectionIds中会话ID和当前连接的会话,其他会话在没有事务的情况下停止接收SQL,如果10秒内,所有会话无法停止接收SQL,则报错并恢复接收SQL 执行该注释后,如果成功,不能把上述排除停止接收sql的会话都关闭,否则,因为mycat不接收sql,可能没有会话可用.	/+ mycat:pauseServer{ "connectionIds":[1] } /;
41	恢复服务在执行pauseServer之后,执行此注释,开始恢复接收SQL	/+ mycat:resumeServer{ } /;
42	停止创建新的网络连接成功后,根据需求确保有会话可以操作mycat.如果没有会话可以操作mycat,就没法恢复允许创建新连接	/+ mycat:setAcceptConnect{ "value":1 //允许创建新的连接 } /; /+ mycat:setAcceptConnect{ "value":0 //禁止创建新的连接 } /;
43	会话自杀通知Mycat中所有连接在接收到此SQL的时候,关闭会话,sql一般是心跳语句。	/+ mycat:setReadyToCloseSQL{ "sql":"select 'x'" } /;