A simple introduction to database middleware Mycat

When the MySQL database in the project becomes a bottleneck, we generally use the master-slave replication, sub-Library table to improve the response speed of the database, such as MySQL master-slave replication, in the absence of database middleware, we can only be controlled by the development engineer in the program, This is for a project that is running on the line, the amount of code that needs to be changed is very large, and it is not easy to expand, for example, I would like to add another slave, the following mycat get started

First, detailed deployment steps

(1) Using command line tool or graphical client, connect MySQL, create three shard database of demo;
CREATE database db1;
CREATE database DB2;
CREATE database db3;

(2)/etc/my.cnf, set to MySQL case is not sensitive, otherwise the table may not be found problems.
Lower_case_table_names = 1

(3) Unzip mycat-server-1.3.0.2-20150105144205-linux.tar.gz to/usr/local/mycat

(4) Extract jdk-7u65-linux-i586.gz, copy jdk1.7.0_65 to/usr/local/mycat/

(5) Modify/usr/local/conf/wrap.conf modify Wrapper.java.command=java to store the path in the previous step
Wrapper.java.command=/usr/local/mycat/jdk1.7.0/bin/java

(6) Create Mycat user, change directory permission to Mycat
Useradd Mycat
Chown-r Mycat.mycat/usr/local/mycat

(7) Modify user password
passwd Mycat
Input:

(8) Modify the/usr/local/conf/schema.xml,url, user name, password modification, the remaining unchanged
<writehost host= "hostM1" url= "192.168.0.4:3306" user= "root"
password= "Admin" >

Second, detailed operation steps

(1) Enter/usr/local/mycat/bin (default data port is 8066, management port is 9066)
Execute./mycat start

Third, the use of detailed steps
(1) Enter the MySQL bin directory/usr/local/mysql/bin/

(2) Log in to MySQL to execute the following command
./mysql-utest-ptest-h10.1.176.104-p8066-dtestdb

(Mycat user account and authorization information is 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) Client software use: Navicat

Create mycat New connection: ip:10.1.176.104, user name: Test, Password: Test, port: 8066
You can see that the table was created under the TestDB database: Employee

Open the DB1,DB2 database you can also see that tables have been created employee

(5) Insert data test
Mysql> INSERT into employee (ID,NAME,SHARDING_ID) VALUES (1, ' leader us ', 10000);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection Id:6
Current Database:testdb

Query OK, 1 row affected (0.03 sec)

Mysql> explain INSERT into employee (ID,NAME,SHARDING_ID) VALUES (1, ' leader us ', 10000);
+-----------+-----------------------------------------------------------------------+
| Data_node | SQL |
+-----------+-----------------------------------------------------------------------+
| DN1 | INSERT into employee (ID,NAME,SHARDING_ID) VALUES (1, ' leader us ', 10000) |
+-----------+-----------------------------------------------------------------------+
1 row in Set (0.00 sec)

(6) Shard test According to the rule Auto-sharding-long (primary key range)
Mysql> Explain CREATE TABLE company (ID int. NOT NULL primary key,name varchar (100));
+-----------+---------------------------------------------------------------------+
| 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 (100)) |
| DN3 | CREATE TABLE company (ID int. NOT NULL primary key,name varchar (100)) |
+-----------+---------------------------------------------------------------------+
3 rows in Set (0.01 sec)

  (7) Three shards are inserted with 3 data
 mysql> explain insert INTO company (Id,name) VALUES (1, ' hp ');
 +-----------+---------------------------------------------+
 | Data_node | sql                                           |
 +-----------+---------------------------------------------+
 | dn1        | INSERT INTO company (Id,name) VALUES (1, ' hp ') | 
 | dn2       | INSERT INTO Company (Id,name) VALUES (1, ' hp ') | 
 | dn3       | insert in company (ID, Name) VALUES (1, ' hp ') | 
 +-----------+---------------------------------------------+
 3 rows In Set (0.00 sec)

  (8) Confirm that it is a shard store
 
 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.01 sec)

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)

A simple introduction to database middleware Mycat