Writing in the front

Mycat is developed based on Alibaba’s open source Cobar product. Cobar’s stability, reliability, excellent architecture and performance as well as many mature use cases make Mycat have a good starting point from the very beginning. Standing on the shoulders of giants, we can see further. Excellent open source projects and innovative ideas in the industry are widely integrated into Mycat’s DNA, making Mycat in many ways ahead of other similar open source projects, and even beyond some commercial products. — from Mycat.

As the core developer of Mycat, how could you not publish a series of Mycat articles?

background

As one of the core developers of Mycat, today, finally arranged to Mycat series of articles. In the Mycat series of articles, we will start with a case study of using Mycat to implement a sub-database sub-table. Mycat will continue to be updated on the principles, architecture and underlying source code parsing. I hope Mycat series of articles can help friends to master Mycat thoroughly.

So, today, we will first come to a wave of Mycat implementation of MySQL sub-database sub-table article.

Note: In this case, MySQL Server is installed on CentOS6.8 Server and Mycat Server is installed on the local Windows system. It doesn’t matter which environment is installed. Here, I use VMWare VIRTUAL machine and install CentOS system. Therefore, Mycat Server is installed on the local Windows system.

Program planning

IP port service The user name password
192.168.81.131 3306 The MySQL database root root
192.168.81.132 3306 The MySQL database root root
192.168.81.133 3306 The MySQL database root root
192.168.81.130 8066/9066 Mycat Server admin admin123

As shown in the table above, among the 4 hosts on the LAN, hosts 131 — 133 each have a MySQL instance installed, and host 130, that is, the local host has Mycat Server installed.

Now assume that the database of the system is Messagedb, and there are only two tables, one is message table: message, and the other is dictionary table representing message source: source. This case implements the rule of natural monthly sharding, so the above three mysql instances need to create four databases respectively, namely

Database instance Stored database
192.168.81.131:3306 Message202001, Message202002, Message202003, message202004
192.168.81.132:3306 Message202005, Message202006, Message202007, message202008
192.168.81.133:3306 Message202009, Message202010, Message202011, message202012

Description: For those of you who are new to Mycat and don’t understand sharding, for Mycat, a sharding represents a database on a MySQL instance, namely schema@host, so when our original large table needs to be sharded, Mycat will follow the rules we set. To distribute the data in this large table to the shards, we need to create the same name database and the same structure table on each corresponding shard.

Environment to prepare

Note: HERE, I omit the MySQL installation process, you can install MySQL by yourself. I will also share enterprise-class MySQL installation, optimization, and deployment in a MySQL related topic.

Create a database and create tables to import data

Create all databases based on the database instance and stored database mapping table, and execute the following script in each database:

create table source (
        id int(11) not null auto_increment primary key comment 'pk',
        name varchar(10) default ' ' comment 'source name'
);
create table message (
        id int(11) not null auto_increment primary key comment 'pk',
        content varchar(255) default ' ' comment 'message content',
        create_time date default null,
        source_id int(11) not null.foreign key(source_id) references source(id)
);
insert into `source`(`id`,`name`) values(1.'weibo');
insert into `source`(`id`,`name`) values(2.'weixin');
insert into `source`(`id`,`name`) values(3.'qq');
insert into `source`(`id`,`name`) values(4.'email');
insert into `source`(`id`,`name`) values(5.'sms');
Copy the code

In the message table, there are four fields:

  • Id: the primary key
  • Content: Indicates the content of the message
  • Create_time: create_time, which is used by mycat for sharding
  • Source_id: foreign key of the source table

In addition, we inserted five records into the Source table for testing purposes. At this point, the back-end database environment is set up.

Install and configure Mycat

Install Mycat

The process of installing Mycat is relatively simple, and the installation package can be downloaded at github.com/MyCATApache… . After downloading, it will be decompressed to the corresponding directory of the system, which will not be detailed here.

Mycat installation package structure

Once installed, take a quick look at the mycat directory structure:

Start the Mycat

In WIndows, you need to open the cli as an administrator, CD to the bin directory of Mycat, or add the installation directory of Mycat to the path directory of the environment variable of the system, and enter Mycat install to install the Mycat service. Then enter the command mycat start to start mycat Server.

In Linux, go to the bin directory of Mycat and enter./ Mycat start to start Mycat Server.

Mycat provides two ports. Port 9066 is the management port, which provides system monitoring functions such as viewing the status of the current system node and reporting heartbeat status. Port 8066 is the data port, which is equivalent to the database access port. We can access these two ports using the mysql command

mysql -h[mycat_host] -u[mycat_user] -p[mycat_passwd] -P [8066|9066]
Copy the code

At the same time, we can also modify these two ports.

How to configure mycat_user and mycat_passwd? Here are three main mycat configuration files: server. XML, schema. XML and rule-xml.

server.xml

This configuration file is used to configure the system information for Mycat and has two main labels: system and User. The user in this case is the same user accessing the myCat service as described above, not the back-end database user. If we used the default configuration, server.xml would look something like this:


      
<! DOCTYPEmycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
                <property name="useSqlStat">0</property>  
                <property name="useGlobleTableCheck">0</property> 
                <property name="sequnceHandlerType">2</property>
                <property name="processorBufferPoolType">0</property>
                <property name="useOffHeapForMerge">1</property>
                <property name="memoryPageSize">1m</property>
                <property name="spillsFileBufferSize">1k</property>
                <property name="useStreamOutput">0</property>
                <property name="systemReserveMemorySize">384m</property>
        </system>
 
        <user name="admin">
                <property name="password">admin123</property>
                <property name="schemas">messagedb</property>
        </user>
</mycat:server>
Copy the code

The schemas attribute under the User label represents the databases that the user can access. Multiple databases can be defined, separated by commas. For databases defined by schemas, you must configure the logical library corresponding to the schema. XML file. Otherwise, a message is displayed indicating that the database cannot be accessed.

schema.xml

The schema configuration file is the most complex and critical one. It defines the logical libraries, logical tables, and sharding information in MyCAT. The configuration is as follows:


      
<! DOCTYPEmycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
        <schema name="messagedb" checkSQLschema="false" sqlMaxLimit="100">
                <table name="message" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" rule="sharding-by-month" />
                <table name="source" primaryKey="id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" />
        </schema>
        <dataNode name="dn1" dataHost="mysql-01" database="message202001" />
        <dataNode name="dn2" dataHost="mysql-01" database="message202002" />
        <dataNode name="dn3" dataHost="mysql-01" database="message202003" />
        <dataNode name="dn4" dataHost="mysql-01" database="message202004" />
        <dataNode name="dn5" dataHost="mysql-02" database="message202005" />
        <dataNode name="dn6" dataHost="mysql-02" database="message202006" />
        <dataNode name="dn7" dataHost="mysql-02" database="message202007" />
        <dataNode name="dn8" dataHost="mysql-02" database="message202008" />
        <dataNode name="dn9" dataHost="mysql-03" database="message202009" />
        <dataNode name="dn10" dataHost="mysql-03" database="message202010" />
        <dataNode name="dn11" dataHost="mysql-03" database="message202011" />
        <dataNode name="dn12" dataHost="mysql-03" database="message202012" />
 
        <dataHost name="mysql-01" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.81.131:3306" user="root"
                                   password="root">
                </writeHost>
        </dataHost>
 
        <dataHost name="mysql-02" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="192.168.81.132:3306" user="root"
                                   password="root">
                </writeHost>
        </dataHost>
 
        <dataHost name="mysql-03" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM3" url="192.168.81.133:3306" user="root"
                                   password="root">
                </writeHost>
        </dataHost>
</mycat:schema>
Copy the code

Here are a few points:

  • The datanode attribute defines which fragments the logical table should be distributed to. The rule attribute indicates which sharding rule to use. Here we choose Sharding-by-month. As long as it corresponds to rule-xml, which follows

  • The source table is a global table, defined with type= “global”, so that mycat can clone the same data on the specified shard, which is very good for join queries.

  • The datanode tag defines the sharding, dataHost is the host name, corresponding to the name attribute value of the dataHost tag, and database defines the specific database name of the host database instance.

  • The dataHost tag defines the database instance, and the Heartbeart tag indicates the method used for heartbeat detection. The writeHost tag defines the instance of write data, and the readHost tag defines the instance of read data. Therefore, the balance property needs to be set to 0

  • For regulatory and security reasons, it is best not to use the root user of the database, but to create a separate user for mycat access.

rule.xml

Rule. XML defines many sharding rules. For the algorithm of the rules, please refer to the official guide.


      
<! DOCTYPEmycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
	 <tableRule name="sharding-by-month">
                <rule>
                        <columns>create_time</columns>
                        <algorithm>partbymonth</algorithm>
                </rule>
        </tableRule>
		<function name="partbymonth"
                class="io.mycat.route.function.PartitionByMonth">
                <property name="dateFormat">yyyy-MM-dd</property>
                <property name="sBeginDate">2020-01-01</property>
        </function>
</mycat:rule>
Copy the code
  • The tableRule tag defines the sharding rule. The columns tag indicates which field in the database table to apply the rule to. The algorithm tag specifies the name of the implementation algorithm
  • The function tag defines the corresponding implementation class and parameters, including dateFormat and sBeginDate.

Description: The start date is used to calculate the shard position of the data. For example, a Message from January 2020 will find the first shard (Dn1), and a message from December 2020 will find the 12th shard (DN12), but if a message from January 2018 is present, Mycat will look for the 13th shard, but there is no configuration in the configuration file, so it will throw an error that the shard cannot be found.

To sum up: server. XML defines the user who accesses mycat service and the database (logical library) authorized by the user. Schema. XML defines the specific logical library, logical table, information about sharding and database instance, rule-xml sharding rules and implementation classes

test

Here we have the mycat configuration file, but before we rush to fill it with data, let’s visit the management port 9066 and see how it works:

C:\Users\binghe>mysql -uadmin -padmin123 -P9066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.629.-mycat1.6-RELEASE- 20161028204710. MyCat Server (monitor)

Copyright (c) 2000.2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show @@datanode;
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+----- ----------+
| NAME | DATHOST                | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+----- ----------+
| dn1  | mysql- 01/message202001 |     0 | mysql |      0 |    4 | 1000 |     412 |          0 |        0 |       0 |            - 1 |
| dn10 | mysql- 03/message202010 |     0 | mysql |      0 |    0 | 1000 |      11 |          0 |        0 |       0 |            - 1 |
| dn11 | mysql- 03/message202011 |     0 | mysql |      0 |    8 | 1000 |      16 |          0 |        0 |       0 |            - 1 |
| dn12 | mysql- 03/message202012 |     0 | mysql |      0 |    1 | 1000 |     412 |          0 |        0 |       0 |            - 1 |
| dn2  | mysql- 01/message202002 |     0 | mysql |      0 |    2 | 1000 |       9 |          0 |        0 |       0 |            - 1 |
| dn3  | mysql- 01/message202003 |     0 | mysql |      0 |    4 | 1000 |      11 |          0 |        0 |       0 |            - 1 |
| dn4  | mysql- 01/message202004 |     0 | mysql |      0 |    2 | 1000 |       9 |          0 |        0 |       0 |            - 1 |
| dn5  | mysql./message202005 |     0 | mysql |      0 |    4 | 1000 |     413 |          0 |        0 |       0 |            - 1 |
| dn6  | mysql./message202006 |     0 | mysql |      0 |    2 | 1000 |       9 |          0 |        0 |       0 |            - 1 |
| dn7  | mysql./message202007 |     0 | mysql |      0 |    4 | 1000 |      11 |          0 |        0 |       0 |            - 1 |
| dn8  | mysql./message202008 |     0 | mysql |      0 |    2 | 1000 |       9 |          0 |        0 |       0 |            - 1 |
| dn9  | mysql- 03/message202009 |     0 | mysql |      0 |    0 | 1000 |      11 |          0 |        0 |       0 |            - 1 |
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+----- ----------+
12 rows in set (0.00 sec)

mysql> show @@heartbeat;
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+----- --+
| NAME   | TYPE  | HOST           | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+----- --+
| hostM2 | mysql | 192.16881.132. | 3306 |       1 |     0 | idle   |       0 | 1.1.1        | 2020- 03- 04 14:22:59 | false |
| hostM1 | mysql | 192.16881.131. | 3306 |       1 |     0 | idle   |       0 | 1.1.1        | 2020- 03- 04 14:22:59 | false |
| hostM3 | mysql | 192.16881.133. | 3306 |       1 |     0 | idle   |       0 | 2.1.1        | 2020- 03- 04 14:22:59 | false |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+----- --+
3 rows in set (0.00 sec)

mysql>
Copy the code

If all nodes are present and the heartbeat status is RS_CODE=1, the connection to the back-end database is normal.

Now let’s batch insert 10 million data using JDBC:

package com.mycat.test;
 
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Random;
 
import org.junit.Test;
 
/** * test Mycat *@author binghe
 */
public class TestMycat {
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "JDBC: mysql: / / 127.0.0.1:8066 / messagedb? useServerPrepStmts=false&rewriteBatchedStatements=true";
	private static final String username = "admin";
	private static final String password = "admin123";
 
	@Test
	public void test(a) throws SQLException {
		Calendar calendar = Calendar.getInstance();
		Random random = new Random();
		calendar.set(2020.0.1.0.0.0);
	
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			Class.forName(driver);
			connection = (Connection) DriverManager.getConnection(url, username, password);
			connection.setAutoCommit(false);
			String sql = "insert into message(`content`, `create_time`, `source_id`) values(? ,? ,?) ";
			ps = connection.prepareStatement(sql);
			long start = System.currentTimeMillis();
			for (int i = 0; i < 10000000; i++) {
				ps.setString(1, System.currentTimeMillis() + "");
				long randomtime = calendar.getTimeInMillis() + (random.nextInt(365) + 1) * 86400 * 1000l;
				Date date = new Date(randomtime);
				int source_id = random.nextInt(5) + 1;
				ps.setDate(2, date);
				ps.setInt(3, source_id);
				ps.addBatch();
				if(i ! =0 && i % 10000= =0) {
					System.out.println("execute batch : " + i);
					ps.executeBatch();
				}
			}
			ps.executeBatch();
			connection.commit();
			System.out.println(System.currentTimeMillis() - start);
		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			if(ps ! =null)
				ps.close();
			if(connection ! =null) connection.close(); }}}Copy the code

Multi-statement transaction required more than ‘MAX_binlog_cache_size’ bytes of storage; The max_binlog_cache_size parameter in my.cnf can be appropriately increased.

validation

Finally, we verify the shard result, where the data in the message table is shard by month according to create_time value, while the data in the source table is global and appears on each shard

mysql -h192168.81.131. -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202002.message;" :+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2020.- 01       | 2020.- 28       |
+------------------+------------------+
 
mysql -h192168.81.132. -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202005.message;" :+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2020- 05- 01       | 2020- 05- 31       |
+------------------+------------------+
 
mysql -h192168.81.133. -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202009.message;" :+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2020- 09- 01       | 2020- 09- 30       |
+------------------+------------------+
Copy the code
mysql -h192168.81.131. -uroot -proot -P3306 -e "select * from message202001.source"
+----+--------+
| id | name   |
+----+--------+
|  1 | weibo  |
|  2 | weixin |
|  3 | qq     |
|  4 | email  |
|  5 | sms    |
+----+--------+
 
mysql -h192168.81.132. -uroot -proot -P3306 -e "select * from message202007.source"
+----+--------+
| id | name   |
+----+--------+
|  1 | weibo  |
|  2 | weixin |
|  3 | qq     |
|  4 | email  |
|  5 | sms    |
+----+--------+
 
mysql -h192168.81.133. -uroot -proot -P3306 -e "select * from message202011.source"
+----+--------+
| id | name   |
+----+--------+
|  1 | weibo  |
|  2 | weixin |
|  3 | qq     |
|  4 | email  |
|  5 | sms    |
+----+--------+
Copy the code

conclusion

This paper carries out a practical operation on the characteristics of MyCAT sharding, completes the deployment of MyCAT-Server and back-end mysql database, and carries on the relevant configuration according to the natural month sharding rule, and finally makes a small test to verify the correctness of the sharding function.

Well, today Mycat series of articles on the introduction of the case here, we have any good opinion or suggestions can be left a message at the end of the article, I am the glacier, we will see you next time!!

Big welfare

WeChat search the ice technology WeChat 】 the public, focus on the depth of programmers, daily reading of hard dry nuclear technology, the public, reply within [PDF] have I prepared a line companies interview data and my original super hardcore PDF technology document, and I prepared for you more than your resume template (update), I hope everyone can find the right job, Learning is a way of unhappy, sometimes laugh, come on. If you’ve worked your way into the company of your choice, don’t slack off. Career growth is like learning new technology. If lucky, we meet again in the river’s lake!

In addition, I open source each PDF, I will continue to update and maintain, thank you for your long-term support to glacier!!

Write in the last

If you think glacier wrote good, please search and pay attention to “glacier Technology” wechat public number, learn with glacier high concurrency, distributed, micro services, big data, Internet and cloud native technology, “glacier technology” wechat public number updated a large number of technical topics, each technical article is full of dry goods! Many readers have read the articles on the wechat public account of “Glacier Technology” and succeeded in job-hopping to big factories. There are also many readers to achieve a technological leap, become the company’s technical backbone! If you also want to like them to improve their ability to achieve a leap in technical ability, into the big factory, promotion and salary, then pay attention to the “Glacier Technology” wechat public account, update the super core technology every day dry goods, so that you no longer confused about how to improve technical ability!