MYCAT primary Key database self-increment mode

Create a person table, the primary key is Id,hash mode Shard, the primary key is self-increment (adopt database mode)

#person表结构如下Id，主键，Mycat自增主键name,字符串，16字节最长school,毕业学校，数字，1-1000范围，是学校编号age，年龄，18-60addr,地址，32字节，建议为 gz-tianhe（城市-地区两级 枚举的仿真数据）zcode,邮编，birth，生日，为日期类型，  1980到2010年之间随机的日期score，得分，0-100分

1. Machine Environment

192.168.2.136   mycat1192.168.2.135   mysql192.168.2.134   test

2. mycat parameter file configuration

Edit Server.xml

[[email protected] conf]# more /usr/local/mycat/conf/server.xml |grep sequnceHandlerType                <!--<property name="sequnceHandlerType">0</property>-->#0 代表文件方式，#1 代表数据库方式#2 代表本地时间戳方式

Restart Mycat Effective Login Management Port authentication

[[email protected] conf]# mycat restartStopping Mycat-server...Stopped Mycat-server.Starting Mycat-server...

3. Database configuration, creating sequence related tables and function

#所有操作都在 DB1 # Create Mycat_sequence table drop tables IF EXISTS mycat_sequence;–name SEQUENCE name –current_value current value–increment   Growth Step! Mycat how many SEQUENCE CREATE TABLE mycat_sequence (name VARCHAR () not null,current_value Intnot INT in the database are read at a time Not NULL DEFAULT N, PRIMARY KEY (name)) Engine=innodb; #插入一条 Sequenceinsert into Mycat_sequence (Name,current_value, Increment) VALUES (' person ', 100000, +); #创建相关 function# Gets the value of the current sequence (return current value, increment) DROP function IF EXISTS Mycat_seq_curr Val;delimiter;; CREATE definer= ' root ' @ '% ' FUNCTION ' mycat_seq_currval ' (seq_name varchar) RETURNS varchar CharSet Utf8deterministicbegindeclare retval VARCHAR (64); SET retval= " -999999999,null"; Select CONCAT (CAST (Current_value as CHAR), ",", cast (increment Aschar)) into Retvalfrom mycat_sequence WHERE NAME = Seq_nam E RETURN retval; END;;D elimiter; #设置 sequence value drop FUNCTION IF EXISTS mycat_seq_setval;delimiter;; CREATE definer= ' root ' @ '% ' FUNCTION mycat_seq_setval (Seq_name VARCHAR (), value INTEGER) returNS varchar (CHARSET) utf8deterministicbeginupdate mycat_sequenceset current_value = valuewhere name = Seq_name; RETURN Mycat_seq_currval (seq_name); END;;D elimiter; #获取下一个 sequence value drop FUNCTION IF EXISTS ' mycat_seq_nextval ';D elimiter;; CREATE definer= ' root ' @ '% ' FUNCTION ' mycat_seq_nextval ' (seq_name varchar) RETURNS varchar CHARSET Utf8deterministicbeginupdate mycat_sequence SET current_value = current_value + incrementwhere NAME = seq_name; RETURN Mycat_seq_currval (seq_name); END;;D Elimiter;

4. Modify MYCAT Related configuration

Sequence_db_conf.properties

[[email protected] conf]# vi sequence_db_conf.properties#sequence stored in datanodeGLOBAL=dn1COMPANY=dn1CUSTOMER=dn1ORDERS=dn1PERSON=dn1

Configure Schema.xml

<! DOCTYPE mycat:schema SYSTEM "schema.dtd" ><mycat:schema xmlns:mycat= "http://org.opencloudb/" > <schema Name= "TESTDB" checksqlschema= "false" sqlmaxlimit= "> <table name=" person "primarykey=" id "autoincremen T= "true" datanode= "Dn1,dn2,dn3" rule= "Sharding-by-murmurperson"/> </schema> < DataNode name= "dn1" datahost= "MySQLServer" database= "db1"/> <datanode name= "dn2" datahost= "MySQLServer" Databas E= "DB2"/> <datanode name= "dn3" datahost= "MySQLServer" database= "db3"/> <!--######### TESTDB ########- <datahost name= "MySQLServer" maxcon= "" "mincon=" "balance=" 0 "writetype=" 0 "dbtype=" MySQL "DbD            river= "native" switchtype= "1" slavethreshold= ">" 

configuration Rule.xml


I D
Murmur



 <function name="murmur" class="org.opencloudb.route.function.PartitionByMurmurHash"> <property name="seed">0</property><!-- 默认是0 --> <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 --> <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 --> <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 --> <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 --> </function>

Restart Mycat

[[email protected] conf]# mycat restartstopping mycat-server ... Mycat-server is not running. [[email protected] ~]$ mysql-utest-ptest-h192.168.2.136-p8066warning:using a password on the command line inte Rface can be insecure. Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 4Server version:5.5.8-mycat-1.5.1-release-20161130213509 mycat Server (OPENCLOUNDDB) Co Pyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> mysql> mysql> use TESTDB; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> show tables;+------------------+| Tables in TESTDB |+------------------+| Person |+------------------+1 row in Set (0.00 sec) 

Create a person table in 5 mycat and insert data validation

#创建 person 表:CREATE TABLE person(id INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(16),school INT,              age INT, addr VARCHAR(32),zcode VARCHAR(6),birth DATETIME,score INT) ;#插入数据 insert into person(id,name, school, age, addr, zcode, birth, score)        values(NEXT VALUE FOR MYCATSEQ_PERSON,'xiaoxu', 100, 30,       'shanghai-jiuting','072450','1987-02-01',100);

Insert Success!!!

MYCAT primary Key database self-increment mode