Mycat easy to get started

Mycat is MySQL middleware, formerly Ali famous Cobar,cobar in open source for a period of time, after nothing. So mycat carried up this banner, in the era of big data, its importance is increasingly manifested. This article is primarily about MYCAT deployment.

First, install Java

Because Mycat is developed in Java, it is necessary to install Java in the experimental environment, the official recommendation jdk1.7 and above version

The official Java Oracle is:

Http://www.oracle.com/technetwork/java/javase/archive-139210.html

Unzip the jdk-7u79-linux-x64.tar.gz file and configure the Java environment variables

# tar XVF jdk-7u79-linux-x64.tar.gz

# MV jdk1.7.0_79//usr/local/

Edit/etc/profile File

# Vim/etc/profile Add the following:

Export Java_home=/usr/local/jdk1.7.0_79export path= $JAVA _home/bin: $PATHexport classpath=.: $JAVA _home/lib/dt.jar:$ Java_home/lib/tools.jar

# Source/etc/profile--Make/etc/profile file effective

# java-version

Java version "1.7.0_79" Java (tm) SE Runtime Environment (build 1.7.0_79-b15) Java HotSpot (tm) 64-bit Server VM (Build 24.79- B02, Mixed mode)

Second, install MySQL

MySQL official as follows:

http://dev.mysql.com/downloads/mysql/

There are various versions, including RPM, binary, source package.

For the sake of convenience, I choose here is RPM package

# yum Install-y mysql-community-server-5.6.26-2.el5.x86_64.rpm

Start MySQL

#/etc/init.d/mysqld Start

Starting mysqld (via Systemctl):                           [  OK  ]

Create a password for the root account

# mysqladmin-u root password "123456"

Recommended for 123456, the following mycat configuration file Schema.xml will be used in the

Third, installation Mycat

Create a Mycat user and set a password

# Useradd Mycat

# passwd Mycat

Unzip the Mycat file

# tar XVF mycat-server-1.5-alpha-20151221110028-linux.tar.gz

# MV mycat//usr/local/

Set the owner and Mycat of the directory

# Chown-r mycat.mycat/usr/local/mycat/

Iv. Test Mycat

First create three databases on MySQL: db1,db2,db3.

mysql> CREATE DATABASE db1; Query OK, 1 row Affected (0.00 sec) mysql> CREATE database DB2; Query OK, 1 row Affected (0.00 sec) mysql> CREATE database db3; Query OK, 1 row Affected (0.00 sec)

Start the Mycat service

# cd/usr/local/mycat/bin/

#./mycat Start

Starting mycat-server ...

See if the Mycat service is started

# Ps-ef |grep Mycat

Root       9640   7257  0 22:47 pts/3    00:00:00 grep--color=auto mycat

And it didn't start.

View Logs

# cd/usr/local/mycat/logs/

# Cat Wrapper.log

STATUS | Wrapper | 2016/01/07 22:44:23 | --Wrapper Started as Daemonstatus | Wrapper | 2016/01/07 22:44:23 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:25 | JVM exited while loading the Application.info | JVM 1 | 2016/01/07 22:44:25 | Error:exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostExcept Ion:mysql-server1:mysql-server1:name or service not Knownstatus | Wrapper | 2016/01/07 22:44:29 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:29 | JVM exited while loading the Application.info | JVM 2 | 2016/01/07 22:44:29 | Error:exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostExcept Ion:mysql-server1:mysql-server1:name or service not Knownstatus | Wrapper | 2016/01/07 22:44:34 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:34 | JVM exited while loading the Application.info | JVM 3 | 2016/01/07 22:44:34 | Error:Exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostException:My Sql-server1:mysql-server1:name or service not Knownstatus | Wrapper | 2016/01/07 22:44:38 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:38 | JVM exited while loading the Application.info | JVM 4 | 2016/01/07 22:44:38 | Error:exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostExcept Ion:mysql-server1:mysql-server1:name or service not Knownstatus | Wrapper | 2016/01/07 22:44:42 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:43 | JVM exited while loading the Application.info | JVM 5 | 2016/01/07 22:44:43 | Error:exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostExcept Ion:mysql-server1:mysql-server1:name or service not Knownfatal | Wrapper | 2016/01/07 22:44:43 | There were 5 failed launches in a row, each LastiNg less than seconds. Giving up. FATAL | Wrapper |   2016/01/07 22:44:43 | There may a configuration problem:please check the logs. STATUS | Wrapper | 2016/01/07 22:44:43 | <--Wrapper Stopped

Based on the error message, the host name is suspected to be unbound

Modify Hosts file, bind host name

# vim/etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost Localhost.localdomain Localhost6 localhost6.localdomain6192.168.244.144 Mysql-server1

Restarting the Mycat service

# cd/usr/local/mycat/bin/

#./mycat Start

This mycat normal start.

# Ps-ef |grep Mycat

Root      10725      1  0 22:54?        00:00:00/usr/local/mycat/bin/./wrapper-linux-x86-64/usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident= Mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=true wrapper.lockfile=/var/lock/subsys/ Mycatroot      10881   7257  0 22:55 pts/3 00:00:00    grep--color=auto mycat

The following is an example of Travelrecord, for inserting, querying, routing analysis and other basic operations.

First connect MySQL database with Mycat

# MYSQL-UTEST-PTEST-H127.0.0.1-P8066-DTESTDB

8066 is the Mycat listening port, similar to the 3306 port of MySQL, where-u,-p,-h is the user name, password and host, and-D is the logical library of the connection.

As for why these are, this is related to configuration files.

The red section indicates that the connection is mycat.

Create a Travelrecord table

CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int);

Inserting data

mysql> INSERT INTO Travelrecord (id,user_id,traveldate,fee,days)  values (1, ' Victor ', 20160101,100,10); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into Travelrecord (id,user_id,traveldate,fee,days)  values ( 5000001, ' Job ', 20160102,100,10); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into Travelrecord (id,user_id,traveldate,fee,days)  values ( 10000001, ' Slow ', 20160103,100,10); Query OK, 1 row Affected (0.00 sec)

As to why the ID takes three values, this is related to the definition of autopartition-long.txt in the Conf directory, which mainly defines auto-sharding-long rules.

# range Start-end, data node index# k=1000,m=10000.0-500m=0500m-1000m=11000m-1500m=2

I mainly test the effect of sharding when the ID takes a different interval value.

Here's how the Shard works.

Mysql> SELECT * from db1.travelrecord;+----+---------+------------+------+------+| ID | user_id | TravelDate | Fee  | days |+----+---------+------------+------+------+|  1 | Victor  | 2016-01-01 |  |   |+----+---------+------------+------+------+1 row in Set (0.00 sec) mysql> Select * from db2.travelrecord;+-------- -+---------+------------+------+------+| ID      | user_id | traveldate | fee  | days |+---------+---------+------------+------+------+| 5000001 | Job     | 2016-01-02 |  |   |+---------+---------+------------+------+------+1 row in Set (0.00 sec) mysql> select * FROM db3.travelrecord;+--- -------+---------+------------+------+------+| ID       | user_id | traveldate | fee  | days |+----------+---------+------------+------+------+| 10000001 | Slow    | 2016-01-03 |  |   |+----------+---------+------------+------+------+1 row in Set (0.00 sec)

Sure enough, it was assigned to three different nodes.

If you want to see where the Mycat specifically assigns the data to, you can pass the route analysis.

The syntax is actually quite simple, that is, the SQL statement is preceded by the explain statement.

The following is based on the explain command to see which datanode the CREATE statement and INSERT statement are assigned to.

Mysql> Explain CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar (+), TravelDate DATE, fee deci mal,days int); +-----------+------------------------------------------------------------------------------------ -----------------------------------+| Data_node | SQL |+-- ---------+----------------------------------------------------------------------------------------------------- ------------------+| DN1 | CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int) | | DN2 | CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int) | | DN3 | CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int) |+-- ---------+-----------------------------------------------------------------------------------------------------------------------+3 rows in Set (0.00 sec) mysql> explain insert Into Travelrecord (id,user_id,traveldate,fee,days) VALUES (1, ' Victor ', 20160101,100,10), +-----------+-------------- --------------------------------------------------------------------------------+| Data_node | SQL |+-----------+--------------- -------------------------------------------------------------------------------+| DN1 | Insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (1, ' Victor ', 20160101,100,10) |+-----------+-------- --------------------------------------------------------------------------------------+1 Row in Set (0.01 sec) MySQL > Explain insert INTO Travelrecord (id,user_id,traveldate,fee,days) VALUES (5000001, ' Job ', 20160102,100,10); +------ -----+-------------------------------------------------------------------------------------------------+| Data_noDE | SQL |+-----------+------------ -------------------------------------------------------------------------------------+| DN2 | Insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (5000001, ' Job ', 20160102,100,10) |+-----------+----- --------------------------------------------------------------------------------------------+1 Row in Set (0.00 sec ) mysql> explain insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (10000001, ' Slow ', 20160103,100,10) ;+-----------+------------------------------------------------------------------------------------------------- --+| Data_node | SQL |+-----------+---------- -----------------------------------------------------------------------------------------+| DN3 | Insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (10000001, ' Slow ', 20160103,100,10) |+-----------+------------------------------------------------------------------------ ---------------------------+1 Row in Set (0.00 sec)

Summarize:

The configuration of the mycat is actually quite simple, the most important is familiar with the rules of each configuration file. The above user name, password, how to sub-library, are defined in the configuration file, follow-up, there is time to one by one detailed table.

For configuration files, the main following three conf directories need to be familiar.

Server.xml is a configuration file for Mycat server parameter tuning and user authorization

Schema.xml is a configuration file for logical library definitions and tables and shard definitions

Rule.xml is a configuration file for a shard rule

Mycat easy to get started