Shulou(Shulou.com)06/01 Report--

Mycat Learning practice-Mycat Global Primary key

@ (learning) [mycat, mysql]

Mycat Learning practice-Mycat Global Primary key

1. Introduction to Mycat global primary key

2. Mycat global primary key mode

2.1 Local file mode

2.2 Local timestamp method

2.3 Database mode

2.4 zookeeper mode

1. Introduction to Mycat global primary key

In the case of sub-database and sub-table, the database self-increasing primary key can not guarantee the global uniqueness of the self-increasing primary key.

The syntax of the global serial number conforms to the standard SQL specification and has the following format:

Next value for MYCATSEQ_XXX

MYCATSEQ_XXX is the name of the serial number. MyCAT automatically creates a new serial number, eliminating the complexity of development. In addition, MyCAT also provides a global serial number, named MYCATSEQ_GLOBAL.

Note that MYCATSEQ_ must be capitalized to be recognized correctly.

MyCAT warm Tip: in practice, it is recommended that each table use its own serial number, and the naming of the serial number is recommended as MYCATSEQ _ tableName_ID_SEQ.

Instructions for use in SQL

The identity of the custom serial number is: MYCATSEQ_XXX, where XXX is the name of the specific defined sequence. Examples of application are as follows:

Use the default global sequence:

Insert into tb1 (id,name) values (next value for MYCATSEQ_GLOBAL,'tb1')

Use a custom sequence:

Insert into tb2 (id,name) values (next value for MYCATSEQ_MY1,'tb2')

Get the latest value

Select next value for MYCATSEQ_xxx

2. Mycat global primary key mode

The global primary key provided by Mycat is as follows:

Local file mode: how to use server local disk files

Database mode: the way to use the database

Local timestamp method: use timestamp method

Distributed zookeeper generates ID

2.1 Local file mode

Vim conf/server.xml

0

Vim conf/sequence_conf.properties

# default global sequenceGLOBAL.HISIDS=GLOBAL.MINID=10001GLOBAL.MAXID=20000GLOBAL.CURID=10000# self define sequenceID_LOCAL_FILE.HISIDS=ID_LOCAL_FILE.MINID=1001ID_LOCAL_FILE.MAXID=2000ID_LOCAL_FILE.CURID=1000

In the above configuration file, custom table names must be written in uppercase

HISIDS: indicates the historical segment used (general

No special needs but no configuration)

MINID: minimum ID value

MAXID: indicates the maximum ID value

CURID represents the current ID value.

When the configuration name of sequence_conf.properties matches the table name, sql may not contain the ID field (here the table name is id_local_file)

Vim conf/schema.xml

Select 1

Experimental verification:

[root@testA conf] # mysql-uroot-p123456-P8066-h 127.0.0.1 testmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 3Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > CREATE TABLE `id_local_ file` (`id` varchar (20) NOT NULL, `nm` varchar (60) NULL, PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.06 sec) mysql > insert into id_local_file (id,nm) values (next value for MYCATSEQ_GLOBAL,'id_local_file'); Query OK, 1 row affected (0.03 sec) mysql > insert into id_local_file (nm) values ('id_local_file') / * there is no self-increasing ID field in the inserted sql statement * / Query OK, 1 row affected (0.01 sec) mysql > select * from id_local_file +-+-+ | id | nm | +-+-+ | 10001 | id_local_file | | 1001 | id_local_file | +-+-+ 2 rows in set (sec) mysql > select next value for MYCATSEQ_GLOBAL +-+ | 10002 | +-+ | 10002 | +-+ 1 row in set (10002 sec) mysql >

Advantages: local loading, fast reading speed and simple configuration

Disadvantages: when mycat is rereleased, the seq file needs to be replaced, and cluster deployment cannot be used in this way. Routing to different mycat does not guarantee that the id is unique, making mycat a stateful middleware.

2.2 Local timestamp method

Vim conf/server.xml

two

Vim conf/sequence_time_conf.properties

# sequence depend on TIMEWORKID=01DATAACENTERID=01

The two attribute values are: 0-31 any integer

Vim conf/schema.xml

Select 1

Experimental verification:

[root@testA conf] # mysql-uroot-p123456-P8066-h 127.0.0.1 testmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > CREATE TABLE `id_local_ time` (`id` varchar (20) NOT NULL, `nm` varchar (60) NULL, PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.02 sec) mysql > insert into id_local_time (id,nm) values (next value for MYCATSEQ_GLOBAL,'id_local_time'); Query OK, 1 row affected (0.06 sec) mysql > insert into id_local_time (nm) values ('id_local_time') / * there is no self-increasing ID field in the inserted sql statement * / Query OK, 1 row affected (0.01 sec) mysql > select * from id_local_time +-+ | id | nm | +-+-+ | 922641363168792576 | id_local_time | | 922641424359493632 | id_local_time | +- -+-+ 2 rows in set (0.06 sec) mysql > select next value for MYCATSEQ_GLOBAL +-+ | 922641542101995520 | +-+ | 922641542101995520 | +-+ 1 row in set (0.00 sec)

Local timestamp calculation method

ID= 64-bit binary (42 (milliseconds) + 5 (machine ID) + 5 (business code) + 12 (repeat accumulation)

The length is 18 bits, so the following tips are very important.

Be careful

Table field length must be greater than or equal to 18 bits

Pros: there is no problem with mycat rerelease affecting seq

Cons: the field length is 18 bits.

2.3 Database mode

Vim conf/server.xml

one

Vim conf/sequence_db_conf.properties

# sequence stored in datanodeGLOBAL=test1ID_DB=test1

Add functions and tables to the local database of the test1 node, as follows: sql:

DROP TABLE IF EXISTS mycat_sequence;CREATE TABLE mycat_sequence (NAME VARCHAR (50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100 primary KEY (NAME)) ENGINE = INNODB; INSERT INTO mycat_sequence (name,current_value,increment) VALUES ('GLOBAL', 100000, 100); DROP FUNCTION IF EXISTS `mycat_seq_ currval`; DELIMITER;; CREATE FUNCTION `mycat_seq_ currval` (seq_name VARCHAR (50)) RETURNS varchar (64) CHARSET utf8 DETERMINISTICBEGIN DECLARE retval VARCHAR (64) SET retval= "- 9999999999"; SELECT concat (CAST (current_value AS CHAR), ",", CAST (increment AS CHAR)) INTO retval FROM mycat_sequence WHERE name = seq_name; RETURN retval; END;;DELIMITER; DROP FUNCTION IF EXISTS `mycat_seq_ nextval`; DELIMITER CREATE FUNCTION `mycat_seq_ nextval` (seq_name VARCHAR (50)) RETURNS varchar (64) CHARSET utf8 DETERMINISTICBEGIN UPDATE mycat_sequence SET current_value = current_value + increment WHERE name = seq_name; RETURN mycat_seq_currval (seq_name); END;;DELIMITER; DROP FUNCTION IF EXISTS `mycat_seq_ setval`; DELIMITER CREATE FUNCTION `mycat_seq_ setval` (seq_name VARCHAR (50), value INTEGER) RETURNS varchar (64) CHARSET utf8 DETERMINISTICBEGIN UPDATE mycat_sequence SET current_value = value WHERE name = seq_name; RETURN mycat_seq_currval (seq_name); END;;DELIMITER

Add process:

[root@testA mycat] # mysql-uroot-p123456 testmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 102Server version: 5.7.19-log Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'C'to clear the current input statement.mysql > DROP TABLE IF EXISTS mycat_sequence;Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > CREATE TABLE mycat_sequence (- > NAME VARCHAR (50) NOT NULL,-> current_value INT NOT NULL,-> increment INT NOT NULL DEFAULT 100,-> PRIMARY KEY (NAME)->) ENGINE = INNODB; INSERT INTO mycat_sequence (name,current_value,increment) VALUES ('GLOBAL', 100000, 100) Query OK, 0 rows affected (0.11 sec) mysql > INSERT INTO mycat_sequence (name,current_value,increment) VALUES ('GLOBAL', 100000, 100); Query OK, 1 row affected (0.00 sec) mysql > DROP FUNCTION IF EXISTS `mycat_seq_ currval`; Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER Mysql > CREATE FUNCTION `mycat_seq_ currval` (seq_name VARCHAR (50))-> RETURNS varchar (64) CHARSET utf8-> DETERMINISTIC-> BEGIN-> DECLARE retval VARCHAR (64);-> SET retval= "- 99999999999 INTO retval";-> SELECT concat (CAST (current_value AS CHAR), ",", CAST (increment AS CHAR)) INTO retval-> FROM mycat_sequence WHERE name = seq_name -> RETURN retval;-> END->; Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER; mysql > DROP FUNCTION IF EXISTS `mycat_seq_ nextval`; DELIMITER;; Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER Mysql > 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-> Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER; mysql > DROP FUNCTION IF EXISTS `mycat_seq_ setval`; Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER Mysql > 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-> Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER; mysql >

The following steps are critical to get the id_db table to support database serial numbers as well.

Mysql > INSERT INTO mycat_sequence ('ID_DB', 1,100); mysql > select * from mycat_sequence +-+ | NAME | current_value | increment | +-+ | GLOBAL | 100200 | 100 | ID_DB | 301 | 100 | +- +-+-+ 2 rows in set (0.00 sec)

Vim conf/schema.xml

Select 1

Be careful

Release the mycat_sequence table, too, and pay attention to case (the database is case-sensitive by default)

Experimental verification:

[root@testA mycat] # mysql-uroot-p123456-P8066-h227.0.0.1 testmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > show databases;+-+ | DATABASE | +-+ | test | +-+ 1 row in set (0.00 sec) mysql > show tables +-+ | Tables in test | +-+ | id_db | | mycat_sequence | +-+ 2 rows in set (0.00 sec) mysql > drop id_db;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id_db' at line 1mysql > drop table id_db Query OK, 0 rows affected (0.08 sec) mysql > CREATE TABLE `id_ db` (`id` int NOT NULL, `nm` varchar (60) NULL, PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.02 sec) mysql > insert into id_db (id,nm) values (next value for MYCATSEQ_GLOBAL,'id_db'); Query OK, 1 row affected (0.10 sec) mysql > insert into id_db (nm) values ('db'); Query OK, 1 row affected (0.00 sec) mysql > select next value for MYCATSEQ_GLOBAL +-+ | 100201 | +-+ | 100201 | +-+ 1 row in set (sec) mysql > select * from id_db +-+-+ | id | nm | +-+-+ | 100200 | db | 100200 | id_db | +-+-+ 2 rows in set (0.00 sec) mysql > insert into id_db (nm) values ('db'); Query OK, 1 row affected (0.01 sec) mysql > select * from id_db +-+-+ | id | nm | +-+-+ | 303 | db | 304 | db | | 100200 | id_db | +-+-+ 3 rows in set (0.00 sec) mysql >

Pros: redeploying mycat will not be affected

Disadvantages: when the deployment of the configuration node is master-slave replication, there will be repetition when the master is suspended.

Be careful

If the node is master-slave switch, the data id may be abnormal (duplicate)

2.4 zookeeper mode

Vim conf/server.xml

three

Vim conf/sequence_distributed_conf.properties

INSTANCEID=01CLUSTERID=01

Schema's table adds attributes autoIncrement= "true" and primaryKey= "id"

Distributed ID generator based on ZK and local configuration (unique InstanceID of cluster (computer room) can be obtained through ZK, InstanceID can also be configured through configuration file) ID structure: long 64-bit, ID can occupy up to 63 bits

Current time millis (38-bit microsecond timestamp, can be used for 17 years)

InstanceId (instance ID, which can be obtained through ZK or configuration file, 5 bits, that is, decimal 0-31)

ThreadId (thread ID,9 bit)

Increment (self-increasing, 6 digits)

A total of 63 bits, which can withstand the concurrency of 1000 * (2 ^ 6) = 640000 in a single machine room and single machine.

Advantages: no pessimistic lock, no strong competition, higher throughput

Cons: increased requirements for zookeeper clusters.

Reference:

[1] http://mycat.io/

[2] "distributed database architecture and enterprise practice-- based on Mycat middleware"

[3] long GE official course courseware, blog

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.