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

This article will explain in detail how to deploy and use Mycat. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

MyCAT can be regarded as the enterprise database of "MySQL" cluster, which is used to replace the expensive Oracle cluster. Behind it is Ali's well-known open source product Cobar. The goal of MyCAT is to smoothly migrate existing stand-alone databases and applications to the "cloud" at low cost, so as to solve the data bottleneck in the case of rapid growth of data storage and business scale.

1 Application scenario

Mycat has a lot of data sub-database rules, the next few articles on the relevant feel commonly used rules for trial and summary.

Generally speaking, the rules of data slicing by natural month or date are more suitable for mall order query, similar to the data in the last week, 2 weeks and 3 months. Or report class application.

Putting such data in one area saves the time of data consolidation.

Of course, the monthly data volume should not be too large for OK.

I. detailed explanation of the deployment steps

(1) use command line tool or graphical client to connect to MYSQL and create three sharded databases used by DEMO.

CREATE SCHEMA `range_db_ 01` DEFAULT CHARACTER SET utf8; CREATE SCHEMA `range_db_ 02` DEFAULT CHARACTER SET utf8; CREATE SCHEMA `range_db_ 03` DEFAULT CHARACTER SET utf8; CREATE SCHEMA `range_db_ 04` DEFAULT CHARACTER SET utf8

(2) modify the configuration my.cnf to add the following statement, which is usually placed in / etc/my.cnf or / etc/mysql/my.cnf, and is set to Mysql table name case-insensitive, otherwise the table may not be found.

Lower_case_table_names = 1

(3) decompress Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz to the installation directory and generate a mycat directory

(4) install jdk-7u79-linux-x64

(5) modify mycat/conf/wrap.conf and modify wrapper.Java.command=java to the storage path of the previous step

Wrapper.java.command=/usr/java/jdk1.7.0_79/

(6) create a mycat user and change the directory permission to mycat

Useradd mycatchown-R mycat.mycat mycat

(7) change the user's password

Passwd mycat input:

(8) modify mycat/conf/schema.xml,URL, user name and password, but the rest remain unchanged

2. Detailed explanation of the operation steps

(1) enter mycat/bin (default data port is 8066, management port is 9066)

Execute. / mycat start

(2) enter the logs directory and view the log. If wrapper.log reports an error, java.NET.BindException: Address already in use kills the relevant java process that is being executed.

Ps-ef | grep javakill-9 xxx

Third, the detailed explanation of the use steps

(1) enter the mysql bin directory mysql/bin/

(2) Log in to mysql and execute the following command

. / mysql-utest-ptest-h292.168.184.56-P8066-DTESTDB

(the user account and authorization information of mycat are configured in the conf/server.xml file)

(3) Table creation test:

Mysql > create table employee (id int not null primary key,name varchar, sharding_id int not null); Query OK, 0 rows affected (0.30 sec) mysql > explain create table employee (id int not null primary key,name varchar, sharding_id int not null) +-+-+ | DATA_NODE | SQL | | +-+- -- + | dn1 | create table employee (id int not null primary key Name varchar, sharding_id int not null) | | dn2 | create table employee (id int not null primary key,name varchar) Sharding_id int not null) | +-+-+ 2 rows in set (0.04 sec)

(4) use of client software: navicat

Create a new mycat connection: ip:192.168.184.56, user name: test, password: test, port: 8066

You can see the created table under the TESTDB database: employee

Open the db1,db2 database and you can see that the created table employee

(5) insert data test

Mysql > insert into employee (id,name,sharding_id) values (1 dagger leader us',10000); ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 6Current database: TESTDBQuery OK, 1 row affected (0.03 sec) mysql > explain insert into employee (id,name,sharding_id) values (1 heroine leader us',10000) +-+-+ | DATA_NODE | SQL | | +-+-+ | dn1 | insert into employee (id) | Name,sharding_id) values (1 sec leader us',10000) | +-+-+ 1 row in set (0.00 sec)

(6) perform fragmentation testing according to the rule auto-sharding-long (primary key range)

Mysql > explain create table company (id int not null primary key,name varchar) +-+-+ | DATA_NODE | SQL | | +-+-+ | dn1 | create table company (id int not null primary key) | Name varchar (100) | | dn2 | create table company (id int not null primary key,name varchar) | | dn3 | create table company (id int not null primary key) Name varchar) | +-+-+ 3 rows in set (0.01sec)

(7) three pieces of data are inserted into the three slices.

Mysql > explain insert into company (id,name) values (1pcmp') +-+ | DATA_NODE | SQL | + -- + | dn1 | insert into company (id Name) values (1jinghehp') | | dn2 | insert into company (id,name) values (1djinhp') | | dn3 | insert into company (id,name) values (1recoveryhp') | +-+-+ 3 rows in set (0.00 sec)

(8) confirm that it is sharded storage.

Mysql > select * from employee +-+ | id | name | sharding_id | +-- + | 2 | me | 10010 | 4 | mydog | 10010 | | 1 | leader us | 10000 | | 3 | mycat | 10000 | +-+ 4 rows in set (0.01sec) mysql > explain select * from employee +-+-+ | DATA_NODE | SQL | +-+-+ | dn1 | SELECT * FROM employee LIMIT 100 | | dn2 | SELECT * FROM employee LIMIT 100 | +-+-- + 2 rows in set (0.00 sec) on "how to deploy and use Mycat" ends here 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.