Mysql database/table sharding Mycat

I have always been interested in Mysql database/table sharding, but I am still at the stage of interest and have not encountered any applicable scenarios. Life is short. Instead of waiting for an opportunity, you can create it yourself. After a little research, I chose an open-source product like Mycat. There is no special reason to discuss which excavator is strong, just to learn.

Local environment

Computer environment: Ubuntu 16.04
JDK: 1.8
Docker version 1.11.2
Mysql 5.7.13
Mycat 1.5-RELEASE
Navicat for Mysql
Install Mysql

For testing convenience, Mysql runs on Docker. For more information about installing the Docker environment, see Daocloud. After Docker is successfully installed, go to the DaoCloud image to download the Mysql Image docker pull daocloud. io/library/mysql: 5.7.13. After the download is complete, run the command line to execute sudo docker images. The output is as follows:

The two images shown in the preceding figure are Mysql and Daocloud toolkit respectively. Currently, no other images are available.

Because you must use multiple mysql instances, start two mysql services and test the environment:

Sudo docker run -- name mysql1-p 3306: 3306-e MYSQL_ROOT_PASSWORD = bboyjing-d daocloud. io/library/mysql: 5.7.13
Sudo docker run -- name mysql2-p 3316: 3306-e MYSQL_ROOT_PASSWORD = bboyjing-d daocloud. io/library/mysql: 5.7.13
Run the command line to enter sudo docker ps-a. The output is as follows:

Install Mysql client locally

The local machine only needs to connect to the Mysql server, so only one client can be installed. Run the command line to execute sudo apt-get install mysql-client. After the installation is complete, test, run mysql-h127.0.0.1-P3306-uroot-pbboyjing and mysql-h127.0.0.1-P3316-uroot-pbboyjing on the command line. If both of them are connected, the environment is normal.

Install Navicat

Graphic interface display may be convenient, so install a Navicat backup and download it directly from the official website. As for what to crack, you can do it on your own. After successful installation, connect to the ports 3306 and 3316 of The localhost respectively.

Install Mycat

Download the corresponding system version from Github and decompress it. The directory structure is as follows:

 

Bin directory: stores the script execution commands
Catlet Directory: custom partition rule storage path
Conf Directory: stores various configuration files
Lib Directory: stores some jar packages that Mycat depends on.
Logs Directory: log Directory
Next, run the startup script in the bin directory under the root directory of Mycat. Install jdk first. Run sudo./bin/mycat console on the console. The output is as follows, indicating that the instance is started normally.

If jdk has been installed, Unable to start JVM: No such file or directory may occur, which may be caused by the absence of JAVA environment variables under the root user. Try the following method, if it still fails, it should not.

Cd/opt
Sudo chmod-R 777 mycat
./Bin/mycat console

So far, the simple and runable Mycat environment has been set up, and a little bit of understanding is Mycat. Simply put, it is a proxy that intercepts the database, and the database access request goes through Mycat first, then, it is distributed to a specific host based on sharding rules to implement database/table sharding and other functions.

Let's take a look at several important concepts in Mycat.

User
Logical database (schema)
Logical table)
Partition node (dataNode)
Sharding rule (rule)
Global serial number (sequence)
Next we will talk about it one by one, think about a scenario, configure the configuration, and finally run it completely.

User

The user-related configuration is located in conf/server. xml, and server. xml contains the configuration of the entire Mycat Server. Here, we only take a look at the specific content.

<Username = "root">
<Propertyname = "password"> bboyjing </property>
<Propertyname = "schemas"> OrderDB </property>
<! -- <Property name = "readOnly"> true </property> -->
</User>
The above configuration defines the user name and password for connecting to the Mycat Server and the schema that can be accessed (schema is described below). If you are a read-only user, you can add the readOnly attribute to true. Modify the existing user label to the preceding content.

Logical database (schema)

The concept of schema is the same as that of DataBase in Mysql, which makes it easy to understand. The configuration is in conf/schema. xml, and the schemas attribute in the user tag points to the name attribute of the schema tag. The following describes the content of the schema tag:

<Schemaname = "OrderDB" checkSQLschema = "false" sqlMaxLimit = "100">
</Schema>
The schema tag has four attributes.

Name: The schema name is equivalent to the database name.
DataNode: The Shard where the schema is located (explained below) is actually a physical database. However, defining dataNode directly in the schema is slightly rough, and it may be more likely that each table will define its own sharding rules to route to the corresponding dataNode.
CheckSQLschema: when it is set to true, if you execute select * from OrderDB. order, Mycat will change the statement to select * from order, that is, to delete the characters indicating schema. It is better to disable this verification. Do not include schema when sending SQL statements.
SqlMaxLimit: when it is set to a value, the corresponding value is automatically added to Mycat even if no limit is added to the executed SQL statement. When limit is specified in an SQL statement, this value is not restricted. The official document also says that if the running schema is not a sharding database, this attribute will not take effect, so the SQL statements are strictly controlled.
Logical table)

The table is the table corresponding to the Mysql table. The table label is the sub-label of the schema, so the conf/schema. xml is still modified.

<Schemaname = "OrderDB" checkSQLschema = "false" sqlMaxLimit = "100">
<Tablename = "order" dataNode = "dn1, dn2" rule = "mod-long"/>
</Schema>
Table labels have nine attributes.

Name: corresponding to the table name in Mysql
DataNode: partition where the logical table is located. The attribute value must correspond to the name attribute of the dataNode tag. The dateNode tag will be described below.
Rule: name of the partition rule used by the logical table. The rule is configured in conf/rule. xml. The attribute value must correspond to the name attribute in the tableRule label.
RuleRequired: whether to bind the sharding rule. If it is true, you must configure rule.
PrimaryKey: the primary key of the logical table.
Type: logical table type, which can be a global table or a common table. This attribute is described in detail later in the example.
AutoIncrement: whether to enable the auto-increment primary key, which corresponds to the Mysql auto-increment primary key. It is disabled by default.
SubTable: table sharding, which will be supported later than 1.6
NeedAddLimit: whether to allow automatically adding the limit set in the schema tag. The default value is true.
There is a childTable sub-label under the table label, used to define the word table of the E-R partition, the so-called E-R partition is to route two related to the same partition, convenient join operation, the specific configuration will be shown later in the example.

Partition node (dataNode)

DataNode defines the data nodes in Mycat, which is usually referred to as data sharding. It actually points to the specific database address and name.

The dataNode label and schema are at the same level. The conf/schema. xml is still modified.

<DataNodename = "dn1" dataHost = "dockerhost1" database = "order_db"/>
<DataNodename = "dn2" dataHost = "dockerhost2" database = "order_db"/>
The dataNode tag has three attributes.

Name: partition name, globally unique
DataHost: the address of the database instance, which corresponds to the name attribute of the dataHost tag (as described below)
DataBase: the specific dataBase on the corresponding dataBase instance, which must be created in advance
Node host)

The dataHost tag defines specific database instances, read/write splitting configurations, and heartbeat statements.

At the same level as the schema, the dataHost tag still modifies conf/schema. xml.

<DataHostname = "dockerhost1" maxCon = "1000" minCon = "10" balance = "0"
WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1">
<Heartbeat> select user () <WriteHosthost = "hostM1" url = "localhost: 3306" user = "root" password = "bboyjing">
</WriteHost>
</DataHost>

<DataHostname = "dockerhost2" maxcon= "1000" minCon = "10" balance = "0"
WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1">
<Heartbeat> select user () <WriteHosthost = "hostM1" url = "localhost: 3316" user = "root" password = "bboyjing">
</WriteHost>
</DataHost>
The dataHost tag has nine attributes.

Name: Node host name
MaxCon: specifies the maximum connection pool for each read/write instance
MinCon: specifies the minimum connection for each read/write instance connection pool, that is, the size of the initial connection pool.
Balance: read operation load balancing type. Currently, there are three optional values: "0", "1", "2", and "3 ". Read/write splitting-related policies are described in detail later. "0" in the example indicates that read/write splitting is not enabled and read operations are all sent to the writeHost.
WriteType: write operation load balancing type. Currently, there are three optional values: "0", "1", and "2, in this example, "0" indicates that all write operations are sent to the first writeHost, and the first fails to the second writeHost that is still alive.
DbType: specifies the database type to connect to. Currently, the binary Mysql protocol and other databases connected to JDBC are supported. Such as mongodb, oracle, spark, etc.
DbDriver: specifies the driver used to connect to the database. Currently, it can be native or JDBC. Native supports mysql and mariadb, and other databases must be driven by JDBC.
SwitchType: automatic switch identifier. "1" indicates that automatic switch is disabled. Other options include "1" and "2".
TempReadHostAvailable: When the writeHost is out of connection, the readHost is still available. The default value is 0. Optional values: 0 and 1.
Sharding rule (rule)

The sharding rule is located in conf/rule. xml, which defines the rule definitions involved in table sharding,

<TableRulename = "mod-long">
<Rule>
<Columns> id </columns>
<Algorithm> mod-long </algorithm>
</Rule>
</TableRule>

<Functionname = "mod-long" class = "org. opencloudb. route. function. PartitionByMod">
<! -- How many data nodes -->
<Propertyname = "count"> 2 </property>
</Function>
TableRule label

Name attribute: specifies the globally unique name of a rule.
Columns label: name of the column to be split
Algorithm tag: indicates the name attribute of the function tag, indicating the specific algorithm.
Function tag

Name attribute: specify the algorithm name.
Class attribute: specifies the specific class name of the routing algorithm.
Property tag: defines the specific rules of an algorithm. This algorithm means to route data to two shards based on id mod2. Mycat defines some algorithm rules.

Start two Mysql containers in Docker

# Because the container has been started before, if the container has not been actively deleted, check its status first
Sudo docker ps-

# If the CONTAINER is in the Exited status, start it (in this example, your own container id)
Sudo docker start d1f418414648
Sudo docker start 9d9674cc4fa7
Connect two Mysql instances and create an order_db database respectively.

Mysql-h127.0.0.1-P3306-uroot-pbboyjing
Create database order_db;
Exit

Mysql-h127.0.0.1-P3316-uroot-pbboyjing
Create database order_db;
Exit
Start Mycat

# Go to the Mycat installation directory
Cd/opt/mycat
# Start Mycat
Bin/mycat start
Connect to Mycat through the Mysql client

# View the startup log (connected successfuly MySQLConnection indicates that the startup is successful)
Vim logs/mycat. log

# The log shows that Mycat ports are 8066 and 9066, of which 8066 is the data port and 9066 is the management port.
# Next we connect to Mycat through the user configured in server. xml. The mysql interactive interface indicates that the connection is successful.
Mysql-h127.0.0.1-P8066-uroot-pbboyjing

# View databases
Mysql> show databases;
+ ---------- +
| DATABASE |
+ ---------- +
| OrderDB |
+ ---------- +
# The logical database (schema) configured in server. xml is displayed)
This indicates that the Mycat service is started normally and can be connected through the Mysql client. The result of the last show databases step is shown in the figure below:

 

Design Order table

Design an order table in the order_db Database. The table structure is as follows:

CREATETABLE 'order '(
'Id' int (11) NOTNULL,
'Status' tinyint (4) NOTNULL,
'Price' int (11) NOTNULL,
'Create _ time' bigint (20) NOTNULL,
Primary key ('id ')
) ENGINE = innodbdefacharcharset = utf8
Based on the operations in the Mysql client in the previous step

# Switch to the OrderDB database
Mysql> use OrderDB;

# Run the simple table statement. After successful execution, the same order table will be created in both Mysql instances.
Mysql> create table 'order '(
-> 'Id' int (11) not null,
-> 'Status' tinyint (4) not null,
-> 'Price' int (11) not null,
-> 'Create _ time' bigint (20) not null,
-> Primary key ('id ')
->) ENGINE = InnoDB default charset = utf8;
The successful output is as follows: