2, Mycat global serial number
Mycat can guarantee id uniqueness in the following ways:
1) Local file mode
2) Database mode
3) Timestamp mode
4) ZKID generator
5) ZK increment ID
4 and 5 are recommended
In the above 5 methods, the global serial number configuration should be enabled in the server.xml file and the autoIncrement attribute of the logical table should be set to true in the schema.xml file (two required steps)
1) sequnceHandlerType sets the corresponding global serial number policy options (server.xml). In mycat, the corresponding source code is MyCATSequnceProcessor.java
<property name="sequnceHandlerType">0</property>
sequnceHandlerType can take the following values:
0: local file mode
1: database mode
2: timestamp method
3: ZKID generator
4: ZK increment ID
2) autoIncrement property is true (schema.xml)
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />
1. Local file mode
MYCAT source code used: io.mycat.route.sequence.handler.IncrSequenceHandler
Open the global serial number configuration in the server.xml file:
<property name="sequnceHandlerType">0</property>
Configuration file used: sequence_conf.properties
#default global sequence GLOBAL.HISIDS= GLOBAL.MINID=10001 GLOBAL.MAXID=20000 GLOBAL.CURID=10000 # self define sequence COMPANY.HISIDS= COMPANY.MINID=1001 COMPANY.MAXID=2000 COMPANY.CURID=1000 CUSTOMER.HISIDS= CUSTOMER.MINID=1001 CUSTOMER.MAXID=2000 CUSTOMER.CURID=1000 ORDER.HISIDS= ORDER.MINID=1001 ORDER.MAXID=2000 ORDER.CURID=1000 HOTNEWS.HISIDS= HOTNEWS.MINID=1001 HOTNEWS.MAXID=2000 HOTNEWS.CURID=1000
Take the configuration of HOTNEWS table to illustrate:
HOTNEWS.HISIDS= #The auto increment id used in the history of HOTNEWS is generally not configured HOTNEWS.MINID=1001 #HOTNEWS is the minimum self incrementing id used in this table HOTNEWS.MAXID=2000 #HOTNEWS is the maximum self incrementing id used in this table HOTNEWS.CURID=1000 #Auto increment id currently used in HOTNEWS table
Disadvantages: after Mycat is republished, the self incrementing ID is restored to the initial value. The reason is that sequnceHandlerType defines static variables and is not recommended
Example:
1.1 configure the master and slave of the logical table hotnews and mysql in the schema.xml file of mycat. Note that only autoIncrement="true" can use the globally unique id
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />
<dataNode name="dn1" dataHost="centos1" database="db1" />
<dataNode name="dn2" dataHost="centos1" database="db2" />
<dataNode name="dn3" dataHost="centos1" database="db3" />
<dataHost name="centos1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.152.130:3306" user="root" password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.152.131:3306" user="root" password="123456" />
</writeHost>
<!-- <writeHost host="hostS1" url="localhost:3316" user="root"
password="123456" />-->
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
1.2 create three databases db1, DB2 and db3 respectively in the main database (192.168.152.130), and then create the hotnews table
create database db1;
use db1;
create table hotnews(
id bigint(20) not null primary key auto_increment,
title varchar(50) default null
);
create database db2;
use db2;
create table hotnews(
id bigint(20) not null primary key auto_increment,
title varchar(50) default null
);
create database db3;
use db3;
create table hotnews(
id bigint(20) not null primary key auto_increment,
title varchar(50) default null
);
1.3 insert data
Before inserting data into hotnews, let's take a look at the configuration file sequence using local file mode_ Contents of conf.properties
cat sequence_conf.properties |grep HOTNEWS
Insert data into hotnews
insert into hotnews(id, title) values(next value for MYCATSEQ_HOTNEWS, 'test1'); insert into hotnews(id, title) values(next value for MYCATSEQ_HOTNEWS, 'test2'); insert into hotnews(id, title) values(next value for MYCATSEQ_HOTNEWS, 'test3');
1.4 viewing the results of hotnews table
select * from hotnews;
View the configuration file sequence again_ It is found that the content of conf.properties changes with the self increasing id of the inserted data
2. Database mode
This method is the same as that of local files, except that sequence_ The contents of conf.properties are managed by the database
MYCAT source code used: io.mycat.route.sequence.handler.IncrSequenceMySQLHandler
Here, take the hotnews table as an example
2.1 enable the global serial number configuration in the server.xml file:
<property name="sequnceHandlerType">1</property>
Configuration file used: sequence_db_conf.properties
vim sequence_db_conf.properties
#sequence stored in datanode GLOBAL=dn1 COMPANY=dn1 CUSTOMER=dn1 ORDERS=dn1
2.2 select one of the partitions and perform the following steps. For example, I create it in dn1, and the corresponding database name is db1 (why is datanode involved here, because the subsequent sequence_db_conf.properties file will be used). Note: it is created by logging in to the database, not in mycat
Step 1: create a SEQUENCE table to store serial numbers
use db1; DROP TABLE IF EXISTS MYCAT_SEQUENCE; CREATE TABLE MYCAT_SEQUENCE ( NAME VARCHAR (50) NOT NULL, /*Global SEQ name*/ current_value INT NOT NULL, /*Current sequence ID*/ increment INT NOT NULL DEFAULT 100, /*Initial sequence ID*/ PRIMARY KEY (NAME) ) ENGINE = INNODB ;
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 100000, 100);
Step 2: create SEQ function
-- Get current sequence Value of (return current value),Increment)
DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50))
RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval
FROM MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval ;
END
;;
DELIMITER ;
-- Get next sequence value
DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64)
CHARSET utf8
DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
;;
DELIMITER ;
-- set up sequence value
DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER)
RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
;;
DELIMITER ;
The strategy of inserting a table that needs self growth. This data is required by hotnews. name must be in uppercase, or an error will be reported
insert into MYCAT_SEQUENCE values('HOTNEWS','101','100');
Note: a sequence named HOTNEWS is inserted. The current value is 101 and the step size is 100. When the first piece of data is inserted, the id is 201, and each subsequent piece of data id is added by 1
Step 3: in sequence_ db_ The conf.properties file defines the sequence name of the hotnews table and the partition to which it can be used. This is defined on dn1
Name = slice 1 [, slice 2] [,...] [, slice N]
vim sequence_db_conf.properties
preservation:
:wq
2.3 restart mycat
[root@centos1 mycat]# ./bin/mycat restart;
2.4 connect mycat for data test
mysql -uroot -p123456 -P8066 -h192.168.152.128
insert data
insert into hotnews(id, title) values(next value for MYCATSEQ_HOTNEWS, 'test11111'); insert into hotnews(id, title) values(next value for MYCATSEQ_HOTNEWS, 'test11112'); insert into hotnews(id, title) values(next value for MYCATSEQ_HOTNEWS, 'test11113');
View results:
select * from hotnews;
Disadvantages: primary key conflicts may occur when mycat hangs. Not recommended
three Local timestamp mode
MYCAT source code used: io.mycat.route.sequence.handler.IncrSequenceTimeHandler
3.1 enable the global serial number configuration in the server.xml file:
<property name="sequnceHandlerType">2</property>
Profile used:
sequence_time_conf.properties
Workid = 01 (range 01-31)
DATAACENTERID=01 (range 01-31)
Example:
First, clear the data in the hotnews table to view the test results
delete from hotnews;
Insert test data
insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test11111'); insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test11112'); insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test11113');
View results:
select * from hotnews;
Advantages: there is no id duplication.
Disadvantages: the primary key is too long. The time may be reset. Scenarios without special requirements can be used
four Self growing primary key
Mode 1: different self growth initial values + the same step size
Method 2: refer to "database method"
five Distributed ZK ID generation (recommended)
mycat source code used:
io.mycat.route.sequence.handler.IncrSequenceZKHandler
io.mycat.route.sequence.handler.DistributedSequenceHandler
Environment preparation: first install zookeeper in the virtual machine 192.168.152.130. Refer to my article for details Build dubbo+zookeeper+dubboadmin distributed service framework (under windows platform)
5.1 enable the global serial number configuration in the server.xml file:
vim server.xml
<property name="sequnceHandlerType">3</property>
5.2 modify the following configuration file:
1)myid.properties
vim myid.properties
Profile description:
loadZK=true|false / / whether to use zk sequence generator
zkURL=xxx.xxx.xxx.xxx:2182,xxx.xxx.xxx.xxx:2182,xxx.xxx.xxx.xxx:2182
clusterId = cluster name
2)sequence_distributed_conf.properties
vim sequence_distributed_conf.properties
Profile description:
INSTANCEID=ZK // Change to "ZK" (default is 01)
CLUSTERID=01 // Cluster number
5.3 restart mycat
[root@centos1 mycat]# ./bin/mycat restart;
5.4 connect mycat for data test
mysql -uroot -p123456 -P8066 -h192.168.152.128
insert data
insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test00000001'); insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test00000002'); insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test00000003');
View results:
select * from hotnews;
6. ZK increment mode (recommended)
MYCAT source code used: io.mycat.route.sequence.handler.IncrSequenceZKHandler
6.1 enable the global serial number configuration in the server.xml file:
vim server.xml
<property name="sequnceHandlerType">4</property>
Profile used:
1) myid.properties refer to "5. Distributed ZK ID generator".
2) sequence_conf.properties
HOTNEWS.HISIDS= #The auto increment id used in the history of HOTNEWS is generally not configured HOTNEWS.MINID=1001 #The minimum value in the interval used by zk HOTNEWS.MAXID=2000 #The maximum value in the interval used by zk HOTNEWS.CURID=1000 #Current value in the interval used by zk
explain:
Take hotnews as an example, and then according to sequence_ The configuration of hotnews in conf.properties takes the offset of maximum and MINID (1000 here) as the incremental value of id. when inserting data, the 1000 values are used up. Continue to take the offset of maximum and MINID
6.2 restart mycat
[root@centos1 mycat]# ./bin/mycat restart;
six point three Connect mycat for data test
mysql -uroot -p123456 -P8066 -h192.168.152.128
insert data
insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test00000001'); insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test00000002'); insert into hotnews(id, title) values(next value for MYCATSEQ_GLOBAL, 'test00000003');
View results:
select * from hotnews;
