Detailed explanation on the installation and use of mycat middleware for mysql

MyCAT is MySQLmiddleware, formerly known as Alibaba's famous Cobar. After Cobar was open sourced for a period of time, it stopped. So MyCAT took up this banner, and in the era of big data, its importance has become increasingly apparent. This article is mainly about the introductory deployment of MyCAT.

1. What is mycat

A completely open source, large database cluster for enterprise application development

Supports transactions, ACID, and can replace MySQL An enhanced version of the database

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

A fusion memorycachingtechnology, NoSQL technology, HDFS The new SQL Server for big data

A new generation of enterprise-level database product that combines traditional databases and new distributed data warehouses

A novel database middleware product

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

Second, mycat installation

1, download address mycat

2, install mycat

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

Three, configure mycat

1, configure server.xml

# vim /usr/local/mycat/conf/server.xml //添加以下内容

 <user name="user">  //mycat用户名
 <property name="password">user</property> //mycat密码
 <property name="schemas">mytest</property>  //mycat虚拟数据库名
 <property name="readOnly">true</property>  //只读
 </user>

 <user name="tankzhang">
 <property name="password">admin</property>
 <property name="schemas">mytest</property>
 </user>

It should be noted here that the default virtual data name is TESTDB , if testdb is not configured in schema.xml, then testdb must be changed to the virtual data name in schema.xml. The username, 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" />//定义虚拟数据库名mytest
<dataNode name="my1" dataHost="test1" database="test" /> //真实数据库名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" > //真实数据库的连接方式
 <readHost host="hostS1" url="192.168.5.214:3306" user="tank" password="123456" /> //同上
 </writeHost>
 </dataHost>

</mycat:schema>

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

a. balance AttributeLoad balancing type, there are currently 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, when the dual master dual slave mode (M1 ->S1, M2->S2 , and M1 and M2 are mutually active and backup). 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 writerHost for execution. writerHost does not bear the reading pressure. Note that balance=3 is only available in 1.4 and later versions, not 1.3.

b. writeType attribute

Load balancing type, there are currently 3 values:

1. writeType="0", all write operations are sent to the configured first For a writeHost, the first one hangs and is switched to the second

writeHost that is still alive. The one that has been switched after restarting shall prevail. The switching is recorded in the config 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, blog There are

4 in it, add real user

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

Add user on two 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. Pay attention to firewalls.

Four, start mycat

1, common parameters
./mycat start start
./mycat stop stop
./mycat console Run in the foreground
./mycat restart Restart the service
./mycat pause Pause
./mycat status View startup status

2, start , and check out mycat

# ./mycat start
Starting Mycat-server...

# netstat -tpnl |grep 8066
tcp 0 0 :::8066 :::* LISTEN 31728/java 

# ./mycat status
Mycat-server is running (31726).

Five, test read and write separation

# mysql -u tankzhang -p -P 8066 -h 127.0.0.1 //一定要带上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 |    //虚拟数据库
+----------+
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 '姓名',
 -> `create_time` int(10) NOT NULL DEFAULT '0' COMMENT '创建时间',
 -> 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;  //修改从数据库的user表中的name,会发现读是从从数据库读取的
+----+-----------+-------------+
| id | name | create_time |
+----+-----------+-------------+
| 1 | tankzhang | 0 |
+----+-----------+-------------+
1 row in set (0.01 sec)

Six, summary

mycat supports mysql points Tables, shards, etc., but their use is not recommended. Mycat does not support many clusters. It would be awesome if it can be used with mha.

The above is the detailed content of Detailed explanation on the installation and use of mycat middleware for mysql. For more information, please follow other related articles on the PHP Chinese website!