Installation and use of mysql mycat middleware

1. What is mycat

A completely open source large database cluster for enterprise application development

An enhanced database that supports transactions, ACID, and can replace MySQL

An enterprise-class database that can be viewed as a MySQL cluster to replace expensive Oracle clusters

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

A new generation of enterprise-level database products that combines traditional databases with new distributed data warehouses

A novel database middleware product

The above is the official statement. In fact, it is the database connection pool. MySQL proxy is also a connection pool, but its efficiency is very low.

2. Mycat installation

1. Download address mycat

http://dl.mycat.io/

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 content<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>

Please note that the default virtual database name is TESTDB. If testdb is not configured in schema.xml, you need to change testdb to the virtual database name in schema.xml. The username and password, and virtual database name defined here do not actually 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" /> //The real database name is 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" > //Real database connection method <readHost host="hostS1" url="192.168.5.214:3306" user="tank" password="123456" /> //Same as above</writeHost>
 </dataHost>

</mycat:schema>

There are quite a lot of mycat configuration parameters. Focus on balance="1" and writeType="0"

a. balance attribute load balancing type, currently there are 4 values:

1. balance="0", the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost.

2. balance="1", all readHost and stand by writeHost participate in the load balancing of the select statement. Simply put, in dual-master and dual-slave mode (M1 -> S1, M2-> S2, and M1 and M2 are mutually master and slave), under normal circumstances, M2, S1, and S2 all participate in the load balancing of the select statement.

3. balance="2", all read operations are randomly distributed on writeHost and readhost.

4. balance="3", all read requests are randomly distributed to the readhost corresponding to the writerHost for execution, and the writerHost does not bear the read pressure. Note that balance=3 is only available in 1.4 and later versions, not in 1.3.

b. writeType attribute

Load balancing type. Currently there are 3 values:

1. writeType="0", all write operations are sent to the first configured writeHost. If the first one fails, it will be switched to the second one that is still alive.

writeHost, after restart, the one that has been switched shall prevail, and the switch is recorded in the configuration file: dnindex.properties.

2. writeType="1", all write operations are randomly sent to the configured writeHost.

3. writeType="2", not implemented.

Specific parameters: http://mycat.io/document/Mycat_V1.6.0.pdf

3. Configure the master-slave server. I won’t talk about it here. There is a blog post about it.

4. Add real users

grant all privileges on test.* to tank@"192.168.%" identified by '123456';
flush privileges

Add users on machines 213 and 214.

5. Test the real user connection to ensure that the real user configured in schema.xml can connect to the real database. Be aware of firewalls.

Fourth, start mycat

1. Common parameters
./mycat start
./mycat stop
./mycat console front-end operation
./mycat restart Restart the service
./mycat pause
./mycat status View the 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 include 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; //Modify the name in the user table of the slave database, and you will find that the read is read from the slave database +----+-----------+-------------+
| id | name | create_time |
+----+-----------+-------------+
| 1 | tankzhang | 0 |
+----+-----------+-------------+
1 row in set (0.01 sec)

VI. Summary

mycat supports mysql table partitioning, sharding, etc., but it is not recommended. Mycat does not support many clusters, so it would be great if it could be used with mha.