Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the mysql middleware mycat. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

First, what is mycat?

A thoroughly open source large database cluster for enterprise application development

Enhanced database that supports transactions, ACID, and can replace MySQL

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

A new SQL Server that integrates 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

The above is an official statement. It is actually the connection pool of the database. Mysql proxy is also a kind of connection pooling, but it is inefficient.

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/

Third, configure mycat

1, configure server.xml

# vim / usr/local/mycat/conf/server.xml / / add the following / / mycat username user / / mycat password mytest / / mycat virtual database name true / / read-only admin mytest

Note here that the default virtual data name is TESTDB. If testdb is not configured in schema.xml, change testdb to the virtual data name in schema.xml. The user name and password defined here, the virtual database name, are not real in mysql.

2, configure schema.xml

# cat schema.xml// defines the virtual database name mytest / / the real database name testselect user () / the connection method of the real database / / ditto

Mycat configuration parameters, quite a lot. Focus on balance= "1" and writeType= "0"

A. Balance attribute load balancer 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 select statements. To put it simply, when the double master and double slave mode (M1-> S1, M2-> S2, and M1 and M2 are the master and standby of each other), under normal circumstances, M2M S1 and S2 participate in the load balancing of select statements.

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

4. Balance= "3". All read requests are randomly distributed to the corresponding readhost of wiriterHost for execution. WriterHost does not bear the read pressure. Note that balance=3 is only available in version 1.4 and later.

B. writeType attribute

There are three types of load balancer:

1. WriteType= "0". All write operations are sent to the first writeHost of the configuration, and the first one is hung up and cut to the second surviving one.

WriteHost, whichever has been switched after restart, 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 realized.

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

3. Configure the master-slave server, so I won't talk about it here.

4, add real users

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

Add users to two machines in 213214.

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 the firewall.

Fourth, start mycat

1, common parameters

. / mycat start start

. / mycat stop stop

. / mycat console foreground operation

. / mycat restart restart the service

. / mycat pause paused

. / mycat status to view startup status

2, start, and view mycat

#. / mycat startStarting Mycat-server...# netstat-tpnl | grep 8066tcp 00: 8066: * LISTEN 31728/java #. / mycat statusMycat-server is running (31726).

Fifth, test the separation of reading and writing

# mysql-u tankzhang-p-P 8066-h 127.0.0.1 / / be sure to bring 127.0.0.1Enter password:Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with-Amysql > 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 changedmysql > show tables +-+ | Tables_in_test | +-+ | user | +-+ 1 row in set (0.01 sec) mysql > INSERT INTO `user` (`id`, `name`) VALUES ('1cm,' tank'); Query OK, 1 row affected (0.00 sec) mysql > select * from user / / modify the name from the user table of the database You will find that the read is read from the database +-+ | id | name | create_time | +-+ | 1 | tankzhang | 0 | +-+-- This is the end of the article on "how to use mysql middleware mycat" by row in set (0.01sec). Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.