Mysql middleware mycat installation and usage example sharing

This article mainly shares with you examples of how to install and use the mysql middleware mycat. MyCAT is a MySQL middleware, 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 new SQL Server that integrates memory caching technology, NoSQL technology, and HDFS 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 the official description. 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

http://dl.mycat.io/

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 in schema.xml. name. 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 attribute load balancing type, there are currently 4 values:

1. balance="0", If the read-write separation mechanism is not enabled, 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 A writeHost, the first one hangs and the second one is still alive.

WriteHost, the one that has been switched after restarting shall prevail. The switching 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 and slave servers, I won’t talk about it here, there are

# in the blog ##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. Pay attention to firewalls.

Four, start mycat

1, common parameters

./mycat start start
./mycat stop stop
./mycat console front desk Run
./mycat restart Restart the service
./mycat pause Pause
./mycat status Check the startup status

2, start, and check 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 reading and writing 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 tables, sharding, etc., but its use is not recommended. Mycat does not support many clusters. It would be awesome if it can be used with mha.

Related recommendations:

Example of Mycat read-write separation based on MySQL master-slave replication

How to configure MyCat

MySQL distributed cluster MyCAT (1) Brief introduction

The above is the detailed content of Mysql middleware mycat installation and usage example sharing. For more information, please follow other related articles on the PHP Chinese website!