Mycat2 注釋配置

注釋使用SQL注釋方式表達(dá),可以用于動(dòng)態(tài)更新Mycat配置并且把配置持久化,它的設(shè)計(jì)目標(biāo)是為了動(dòng)態(tài)的更新mycat的配置。但是由于配置的屬性復(fù)雜,它不會(huì)自動(dòng)的更改真實(shí)數(shù)據(jù)庫(kù)的schema。

通過(guò)注解配置不會(huì)自動(dòng)創(chuàng)建物理庫(kù)物理表(與直接使用自動(dòng)化建表語(yǔ)句不同,它會(huì)自動(dòng)建物理庫(kù)物理表),所以要保證物理庫(kù)物理表的在真實(shí)數(shù)據(jù)庫(kù)上是與配置對(duì)應(yīng)的。一般來(lái)說(shuō),原型庫(kù)(prototype)上必須存在與邏輯庫(kù)邏輯表完全一致得物理庫(kù)物理表,以便mycat讀取表和字段信息。

如果搞不懂配置,可以嘗試使用自動(dòng)化建表語(yǔ)句創(chuàng)建測(cè)試的物理庫(kù)物理表,它會(huì)自動(dòng)生成配置文件,然后通過(guò)查看本地的配置文件,觀察它的屬性,就可以知道什么回事。因?yàn)樽詣?dòng)化建表語(yǔ)句過(guò)于簡(jiǎn)單,可能不適合公司的業(yè)務(wù),此時(shí)需要更改配置文件的屬性來(lái)調(diào)整。這種自己更改調(diào)整的屬性值不屬于mycat的開(kāi)發(fā)測(cè)試范疇之內(nèi),也不能受mycat為自動(dòng)化建表的測(cè)試保證。

重置配置

/*+ mycat:resetConfig{} */

創(chuàng)建用戶(hù)

/*+ mycat:createUser{
  "username":"user",
  "password":"",
  "ip":"127.0.0.1",
  "transactionType":"xa"
} */

刪除用戶(hù)

/*+ mycat:dropUser{
  "username":"user"} */

顯示用戶(hù)

/*+ mycat:showUsers */

修改表序列號(hào)為雪花算法

/*+ mycat:setSequence{"name":"db1_travelrecord","time":true} */;

創(chuàng)建數(shù)據(jù)源

/*+ mycat:createDataSource{
  "dbType":"mysql",
  "idleTimeout":60000,
  "initSqls":[],
  "initSqlsGetConnection":true,
  "instanceType":"READ_WRITE",
  "maxCon":1000,
  "maxConnectTimeout":3000,
  "maxRetryCount":5,
  "minCon":1,
  "name":"dr0",
  "password":"123456",
  "type":"JDBC",
  "url":"jdbc:mysql://127.0.0.1:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",
  "weight":0
} */;

刪除數(shù)據(jù)源

/*+ 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
} */;

顯示數(shù)據(jù)源

/*+ mycat:showDataSources{} */

創(chuàng)建集群

/*! mycat:createCluster{
  "clusterType":"MASTER_SLAVE",
  "heartbeat":{
    "heartbeatTimeout":1000,
    "maxRetry":3,
    "minSwitchTimeInterval":300,
    "slaveThreshold":0
  },
  "masters":[
    "dw0" //主節(jié)點(diǎn)
  ],
  "maxCon":2000,
  "name":"c0",
  "readBalanceType":"BALANCE_ALL",
  "replicas":[
    "dr0" //從節(jié)點(diǎn)
  ],
  "switchType":"SWITCH"
} */;

刪除集群

/*! mycat:dropCluster{
  "name":"testAddCluster"
} */;

顯示集群

/*+ mycat:showClusters{} */

創(chuàng)建Schema

確保原型庫(kù)上,存在test_add_Schema物理庫(kù),以下注解才能正常運(yùn)行.

/*+ mycat:createSchema{
  "customTables":{},
  "globalTables":{},
  "normalTables":{},
  "schemaName":"test_add_Schema",
  "shardingTables":{},
  "targetName":"prototype"
} */;

建表注釋可以參考庫(kù)(schema)配置 以下注解相當(dāng)于把配置推送到mycat中進(jìn)行更新

創(chuàng)建單表(用于讀寫(xiě)分離,映射物理表)

/*+ mycat:createTable{
  "normalTable":{
    "createTableSQL":"create table normal(id int)",
    "dataNode":{
      "schemaName":"testSchema", //物理庫(kù)
      "tableName":"normal", //物理表
      "targetName":"prototype" //目標(biāo)
    }
  },
  "schemaName":"testSchema",//邏輯庫(kù)
  "tableName":"normal" //邏輯表
} */;

1.18后

/*+ mycat:createTable{
  "normalTable":{
    "createTableSQL":"create table normal(id int)",
    "locality":{
      "schemaName":"testSchema", //物理庫(kù)
      "tableName":"normal", //物理表
      "targetName":"prototype" //目標(biāo)
    }
  },
  "schemaName":"testSchema",//邏輯庫(kù)
  "tableName":"normal" //邏輯表
} */;

當(dāng)目標(biāo)是集群的時(shí)候,自動(dòng)進(jìn)行讀寫(xiě)分離,根據(jù)集群配置把查詢(xún)sql根據(jù)事務(wù)狀態(tài)發(fā)送到從或主數(shù)據(jù)源,如果目標(biāo)是數(shù)據(jù)源,就直接發(fā)送sql到這個(gè)數(shù)據(jù)源.在Mycat2中,是否使用Mycat的集群配置應(yīng)該是整體的架構(gòu)選項(xiàng),只能選其一.當(dāng)全體目標(biāo)都是數(shù)據(jù)源,要么全體目標(biāo)都是集群。前者一般在數(shù)據(jù)庫(kù)集群前再部署一個(gè)SLB服務(wù),Mycat訪問(wèn)這個(gè)SLB服務(wù),這個(gè)SLB服務(wù)實(shí)現(xiàn)讀寫(xiě)分離和高可用。后者則是Mycat直接訪問(wèn)數(shù)據(jù)庫(kù),Mycat負(fù)責(zé)讀寫(xiě)分離和集群高可用.當(dāng)配置中出現(xiàn)集群和數(shù)據(jù)源的情況,盡量配置成他們的表的存儲(chǔ)節(jié)點(diǎn)在一個(gè)物理庫(kù)的實(shí)例中沒(méi)有交集,這樣可以避免因?yàn)槎嗍褂眠B接導(dǎo)致事務(wù)一致性和隔離級(jí)別破壞產(chǎn)生的問(wèn)題.

創(chuàng)建全局表

/*+ mycat:createTable{
  "globalTable":{
    "createTableSQL":"create table global(id int)",
    "dataNodes":[
      {
        "targetName":"prototype"
      }
    ]
  },
  "schemaName":"testSchema",
  "tableName":"global"
} */;

1.18后

/*+ mycat:createTable{
  "globalTable":{
    "createTableSQL":"create table global(id int)",
    "broadcast":[
      {
        "targetName":"prototype"
      }
    ]
  },
  "schemaName":"testSchema",
  "tableName":"global"
} */;

創(chuàng)建范圍分表

/*+ mycat:createTable{
  "schemaName":"testSchema",
  "shadingTable":{
    "createTableSQL":"create table sharding(id int)",
    "dataNode":{
      "schemaNames":"testSchema",
      "tableNames":"sharding",
      "targetNames":"prototype"
    },
    "function":{
      "clazz":"io.mycat.router.mycat1xfunction.PartitionConstant",
      "properties":{
        "defaultNode":"0",
        "columnName":"id"
      }
    }
  },
  "tableName":"sharding"
} */;

1.18后

/*+ mycat:createTable{
  "schemaName":"testSchema",
  "shadingTable":{
    "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"
} */;

顯示session引用的IO緩沖塊計(jì)數(shù)

/*+ mycat:showBufferUsage{}*/

顯示用戶(hù)

/*+ mycat:showUsers{}*/

顯示schema

/*+ mycat:showSchemas{}*/

顯示調(diào)度器

/*+ mycat:showSchedules{}*/

顯示心跳配置

/*+ mycat:showHeartbeats{}*/

顯示心跳狀態(tài)

/*+ mycat:showHeartbeatStatus{}*/

顯示實(shí)例狀態(tài)

/*+ mycat:showInstances{}*/

顯示Reactor狀態(tài)

/*+ mycat:showReactors{}*/

顯示線(xiàn)程池狀態(tài)

/*+ mycat:showThreadPools{}*/

顯示表

/*+ mycat:showTables{"schemaName":"mysql"}*/

顯示mycat連接

/*+ mycat:showConnections{}*/

顯示存儲(chǔ)節(jié)點(diǎn)

/*+ mycat:showDataNodes{//1.18前
  "schemaName":"db1",
  "tableName":"normal"
} */;
/*+ mycat:showTopology{//1.18后
  "schemaName":"db1",
  "tableName":"normal"
} */;