MyCat 配置分布式ID

[TOC] # 目录 ## MyCat中有两种方式 - 本地文件方式 (用于测试) - server.xml - sequence_conf.properties - schema.xml - 数据库方式 (用于生产) - server.xml - dbseq.sql - sequence_db_conf.properties - 时间方式 - 待续 ### 一、本地文件方式 server.xml ```xml [root@localhost conf]# vim /opt/mycat/conf/server.xml <?xml version="1.0" encoding="UTF-8"?>  <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="nonePasswordLogin">0</property>  <property name="useHandshakeV10">1</property> <property name="useSqlStat">0</property>  <property name="useGlobleTableCheck">0</property>  <property name="sqlExecuteTimeout">300</property>  <property name="sequnceHandlerType">0</property>   <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property> <property name="subqueryRelationshipCheck">false</property>         <property name="processorBufferPoolType">0</property>         <property name="handleDistributedTransactions">0</property>  <property name="useOffHeapForMerge">0</property>  <property name="memoryPageSize">64k</property>  <property name="spillsFileBufferSize">1k</property> <property name="useStreamOutput">0</property>  <property name="systemReserveMemorySize">384m</property>  <property name="useZKSwitch">false</property>      <property name="strictTxIsolation">false</property> <property name="useZKSwitch">true</property> </system>         <user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">user</property> </user> <user name="user"> <property name="password">user</property> <property name="schemas">user</property> <property name="readOnly">true</property> </user> </mycat:server> ``` > name="sequnceHandlerType" = 0 本地文件方式 sequence_conf.properties ```xml [root@localhost conf]# vim /opt/mycat/conf/sequence_conf.properties O_ORDER.HISIDS= O_ORDER.MINID=1001 O_ORDER.MAXID=20000000 # 最大自增值 O_ORDER.CURID=1000 ``` > 追加或者修改跟库一样名称的 order --> O_ORDER schema.xml ```xml [root@localhost conf]# vim /opt/mycat/conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/">  <schema name="user" checkSQLschema="false" sqlMaxLimit="100">  <table name="user" dataNode="dn246,dn247" rule="mod-long" /> <table name="province" dataNode="dn246,dn247" type="global"/>  <table name="o_order" autoIncrement="true" primaryKey="id" dataNode="dn246,dn247" rule="auto-sharding-long" >  <childTable name="order_item" joinKey="order_id" parentKey="id"/> </table>  </schema>  <dataNode name="dn246" dataHost="db246" database="user_246" /> <dataNode name="dn247" dataHost="db247" database="user_247" />   <dataHost name="db246" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat>  <writeHost host="M1" url="192.168.8.246:3306" user="eddie" password="Abc@123456"> <readHost host="S1" url="192.168.8.245:3306" user="eddie" password="Abc@123456" /> </writeHost>    </dataHost> <dataHost name="db247" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat>  <writeHost host="M1" url="192.168.8.247:3306" user="eddie" password="Abc@123456">   </writeHost>   </dataHost>   </mycat:schema> ``` > checkSQLschema="false", autoIncrement="true" primaryKey="id" #### 问题记录 ```sql -- > 2013 - Lost connection to MySQL server during query, is mycat bug -- > checkSQLschema属性设置false INSERT INTO o_order (id,total_amount,order_status) VALUES (next value FOR mycatseq_O_ORDER,89,4) 修改 checkSQLschema="false" -- > 1064 - bad insert sql (sharding column:ID not provided,INSERT INTO o_order (total_amount, order_status) VALUES (89, 4) -- > 没有提供分片列 INSERT INTO o_order (total_amount,order_status) VALUES (89,4) 添加 autoIncrement="true" primaryKey="id" -- > 修改后没有生效 mysql> reload @@config; Query OK, 1 row affected (0.02 sec) Reload config success ``` #### 效果图 ![图片.png](https://qiniuyun.eddilee.cn/%E5%9B%BE%E7%89%87_1605594689489.png?imageView2/0/q/75%7Cwatermark/2/text/YmxvZy5lZGRpbGVlLmNu/font/5a6L5L2T/fontsize/360/fill/IzAwMDAwMA==/dissolve/100/gravity/Center/dx/10/dy/10) ### 二、数据库方式 server.xml ```xml [root@localhost conf]# vim /opt/mycat/conf/server.xml  <property name="sequnceHandlerType">1</property> ``` dbseq.sql ```sql -- ---------------------------- -- cat /opt/mycat/conf/dbseq.sql -- 新建节点表，只能存放一个节点。为了保证唯一性 -- sz dbseq.sql 下载到本地，使用 Navicat 在 8.246 运行sql 导入（多了 MYCAT_SEQUENCE 表和四个函数） -- -- ---------------------------- DROP TABLE IF EXISTS MYCAT_SEQUENCE; CREATE TABLE MYCAT_SEQUENCE ( name VARCHAR(64) NOT NULL, current_value BIGINT(20) NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) ENGINE=InnoDB; -- ---------------------------- -- Function structure for `mycat_seq_currval` -- ---------------------------- DROP FUNCTION IF EXISTS `mycat_seq_currval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(64)) RETURNS varchar(64) CHARSET latin1 DETERMINISTIC BEGIN DECLARE retval VARCHAR(64); SET retval="-1,0"; SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name; RETURN retval ; END ;; DELIMITER ; -- ---------------------------- -- Function structure for `mycat_seq_nextval` -- ---------------------------- DROP FUNCTION IF EXISTS `mycat_seq_nextval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(64)) RETURNS varchar(64) CHARSET latin1 DETERMINISTIC BEGIN DECLARE retval VARCHAR(64); DECLARE val BIGINT; DECLARE inc INT; DECLARE seq_lock INT; set val = -1; set inc = 0; SET seq_lock = -1; SELECT GET_LOCK(seq_name, 15) into seq_lock; if seq_lock = 1 then SELECT current_value + increment, increment INTO val, inc FROM MYCAT_SEQUENCE WHERE name = seq_name for update; if val != -1 then UPDATE MYCAT_SEQUENCE SET current_value = val WHERE name = seq_name; end if; SELECT RELEASE_LOCK(seq_name) into seq_lock; end if; SELECT concat(CAST((val - inc + 1) as CHAR),",",CAST(inc as CHAR)) INTO retval; RETURN retval; END ;; DELIMITER ; -- ---------------------------- -- Function structure for `mycat_seq_setvals` -- ---------------------------- DROP FUNCTION IF EXISTS `mycat_seq_nextvals`; DELIMITER ;; CREATE FUNCTION `mycat_seq_nextvals`(seq_name VARCHAR(64), count INT) RETURNS VARCHAR(64) CHARSET latin1 DETERMINISTIC BEGIN DECLARE retval VARCHAR(64); DECLARE val BIGINT; DECLARE seq_lock INT; SET val = -1; SET seq_lock = -1; SELECT GET_LOCK(seq_name, 15) into seq_lock; if seq_lock = 1 then SELECT current_value + count INTO val FROM MYCAT_SEQUENCE WHERE name = seq_name for update; IF val != -1 THEN UPDATE MYCAT_SEQUENCE SET current_value = val WHERE name = seq_name; END IF; SELECT RELEASE_LOCK(seq_name) into seq_lock; end if; SELECT CONCAT(CAST((val - count + 1) as CHAR), ",", CAST(val as CHAR)) INTO retval; RETURN retval; END ;; DELIMITER ; -- ---------------------------- -- Function structure for `mycat_seq_setval` -- ---------------------------- DROP FUNCTION IF EXISTS `mycat_seq_setval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(64), value BIGINT) RETURNS varchar(64) CHARSET latin1 DETERMINISTIC BEGIN DECLARE retval VARCHAR(64); DECLARE inc INT; SET inc = 0; SELECT increment INTO inc FROM MYCAT_SEQUENCE WHERE name = seq_name; UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name; SELECT concat(CAST(value as CHAR),",",CAST(inc as CHAR)) INTO retval; RETURN retval; END ;; DELIMITER ; INSERT INTO MYCAT_SEQUENCE VALUES ('GLOBAL', 1, 1); ``` 在 MYCAT_SEQUENCE 插入一条数据：表名、起始值、每次追加1 ```sql INTO `user_246`.`MYCAT_SEQUENCE`(`name`, `current_value`, `increment`) VALUES ('O_ORDER', 10000, 1) ``` ```xml [root@localhost conf]# vim /opt/mycat/conf/sequence_db_conf.properties #sequence stored in datanode GLOBAL=dn1 COMPANY=dn1 CUSTOMER=dn1 ORDERS=dn1 O_ORDER=dn246 ``` > O_ORDER=dn246: schema.xml 选择创建表的节点 <dataHost name="db246" 插入测试数据后，MYCAT_SEQUENCE的current_value +1 ```sql INSERT INTO o_order (total_amount,order_status) VALUES (89,4) ```

分布式ID - Snowflake算法

MyCat HA Keepalived搭建热备架构