Simple understanding of MySQL and MYCAT Middleware

Time:2021-5-11

1、 What is MYCAT

A completely open source large database cluster for enterprise application development

It supports transaction, acid and can replace mysql

An enterprise level database that can be regarded as a MySQL Cluster is used to replace the expensive Oracle cluster

A new SQL Server integrating memory cache technology, NoSQL technology and HDFS big data

A new generation of enterprise database products combining traditional database and new distributed data warehouse

A novel database middleware product

These are the official instructions. It’s actually the connection pool of the database. MySQL proxy is also a connection pool, but its efficiency is very low.

2、 MYCAT installation

1. Download MYCAT

2. Install MYCAT


# tar zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/ 

3、 Configure MYCAT

1. Configure server.xml

#VIM / usr / local / MYCAT / conf / server.xml // add the following 
< user name = "user" > // MYCAT user name 
< property name = "password" > User < / property > // MYCAT password 
< property name = "schemas" > mytest < / property > // MYCAT virtual database name 
< property name = "readonly" > true < / property > // read only 
</user> 
<user name="tankzhang"> 
<property name="password">admin</property> 
<property name="schemas">mytest</property> 
</user>

Note that the default virtual data name is testdb. If testdb is not configured in schema.xml, then testdb should be changed to the virtual data name in schema.xml. The user name, password and virtual database name defined here do not really exist in MySQL.

2. Configure schema. XML

# cat schema.xml 
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
<mycat:schema xmlns:mycat="http://io.mycat/"> 
< schema name = mytest "checksqlschema = false" sqlmaxlimit = 100 "datanode = my1 / > // define the virtual database name mytest 
< datanode name = my1 "datahost = test1" database = Test / > // real database name: Test 
<dataHost name="test1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" > 
<heartbeat>select user()</heartbeat> 
< writehost host = hostm1 "url = 192.168.5.213:3306" user = tank "password = 123456" > // connection mode of real database 
< readhost host = hosts1 "url = 192.168.5.214:3306" user = tank "password = 123456 / > // ditto 
</writeHost> 
</dataHost> 
</mycat:schema>

MYCAT configuration parameters, quite a lot. Let’s focus on balance = 1 and writetype = 0

a. There are four types of load balancing values for the balance attribute

  • Balance = 0, the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writehost.
  • Balance = 1 “, all readhost and stand by writehost participate in the load balancing of the select statement. In short, when the dual master and dual slave mode (M1 – > S1, M2 – > S2, and M1 and M2 are primary and standby to each other), under normal circumstances, M2, S1 and S2 all participate in the load balancing of the select statement.
  • Balance = 2 “, all read operations are randomly distributed on writehost and readhost.
  • Balance = 3. All read requests are randomly distributed to the corresponding readhost of wiriterhost for execution. Writerhost does not bear the reading pressure. Note that balance = 3 is only available in version 1.4 and later, but not in version 1.3.

b. Writetype property

There are three types of load balancing

  • Writetype = 0 “, all write operations are sent to the first configured writehost, the first is hung and switched to the second surviving writehost. After restart, the one that has been switched shall prevail, and the switching is recorded in the configuration file: dnindex.properties
  • Writetype = 1. All write operations are randomly sent to the configured writehost.
  • Writetype = 2 “, not implemented.

Specific parameters

3. Configure master and slave servers

4. Add real users


grant all privileges on test.* to [email protected]"192.168.%" identified by '123456'; 
flush privileges 

Add users to 213214.

5. Test the real user connection

Ensure that the real users configured in schema. XML can connect to the real database. Pay attention to the firewall.

4、 Start MYCAT

1. Common parameters

. / MYCAT start 
. / MYCAT stop 
. / MYCAT console 
. / MYCAT restart service 
. / MYCAT pause 
. / MYCAT status view startup status

2, start and view MYCAT


# ./mycat start 
Starting Mycat-server... 
# netstat -tpnl |grep 8066 
tcp 0 0 :::8066 :::* LISTEN 31728/java 
# ./mycat status 
Mycat-server is running (31726). 

5、 Test read write separation

#MySQL - u tankzhang - P - P 8066 - H 127.0.0.1 // be sure to bring 127.0.0.1 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 1 
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) 

Copyright (c) 2000, 2016, 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 databases; 
+----------+ 
| DATABASE | 
+----------+ 
|Mytest | // virtual database 
+----------+ 
1 row in set (0.00 sec) 

mysql> use mytest; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 

mysql> CREATE TABLE IF NOT EXISTS `user` ( 
-> `id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'ID', 
->'name ` varchar (20) not null default' 'comment' name ', 
-> `create_ Time ` int (10) not null default '0' comment 'creation time', 
-> PRIMARY KEY (`id`) 
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 
Query OK, 0 rows affected (0.08 sec) 
Database changed 
mysql> show tables; 
+----------------+ 
| Tables_in_test | 
+----------------+ 
| user | 
+----------------+ 
1 row in set (0.01 sec) 
mysql> INSERT INTO `user` (`id` ,`name`)VALUES ('1', 'tank'); 
Query OK, 1 row affected (0.00 sec) 

mysql> select * from user; // If you modify the name in the user table of the slave database, you will find that the read is read from the database 
+----+-----------+-------------+ 
| id | name | create_time | 
+----+-----------+-------------+ 
| 1 | tankzhang | 0 | 
+----+-----------+-------------+ 
1 row in set (0.01 sec)

6、 Summary

MYCAT supports the table splitting and slicing of MySQL, but it is not recommended. MYCAT does not support many clusters. If it can be used with MHA, it will be better.

The above is the whole content of this article, I hope to help you learn, and I hope you can support developer more.

Recommended Today

Motion capture assists multi-source sensor information fusion navigation technology for unmanned vehicles

Unmanned vehicle is a kind of intelligent autonomous vehicle that can perform path planning and environment perception, and has become a popular development direction of current intelligent vehicles. Unmanned vehicles can identify the surrounding situation and their own status information through on-board sensors, and have the functions of navigation and positioning, and can complete the […]