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

This article is to share with you about how to use mycat middleware in mysql, the editor thinks it is very practical, so I share it with you to learn. 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 mycat

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 / / define virtual database name mytest / / real database name test select user () / / connection method of 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:

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 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, M2Magi S1 and S2 participate in the load balancing of select statements.

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 read pressure. Note that balance=3 is only available in 1.4 and later versions, but not in 1.3.

B. writeType attribute

There are three types of load balancer:

WriteType= "0", all write operations are sent to the first writeHost of the configuration, and the first one hangs the second writeHost that is still alive. After restarting, the switch is recorded in the configuration file: dnindex.properties.

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

WriteType= "2", not realized.

Specific parameters

3. Configure master and slave servers

4, add real users

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

Add users to two machines in 213214.

5. Test the real user connection

Make sure that the real users 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 run. / mycat restart restart service. / mycat pause 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).

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.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' 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 ('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 | +-+-- -+ 1 row in set (0.01sec) is how to use mycat middleware in mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.