MyCAT簡易入門
MyCAT是mysql中間件，前身是阿里大名鼎鼎的Cobar，Cobar在開源了一段時間后，不了了之。于是MyCAT扛起了這面大旗，在大數(shù)據(jù)時代，其重要性愈發(fā)彰顯。這篇文章主要是MyCAT的入門部署。
一、安裝java
因Mycat是用java開發(fā)的，所以需要在實(shí)驗(yàn)環(huán)境下安裝java，官方建議jdk1.7及以上版本
Java Oracle官方下載地址為：
http://www.oracle.com/technetwork/java/javase/archive-139210.html
解壓jdk-7u79-linux-x64.tar.gz文件，并配置java的環(huán)境變量
# tar xvf jdk-7u79-linux-x64.tar.gz
# mv jdk1.7.0_79/ /usr/local/
編輯/etc/profile文件
# vim /etc/profile 添加如下內(nèi)容
export JAVA_HOME=/usr/local/jdk1.7.0_79
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
# source /etc/profile --使/etc/profile文件生效
# 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)
二、安裝MySQL
MySQL官方下載地址如下：
http://dev.mysql.com/downloads/mysql/
image.png
里面有各種版本，包括RPM，二進(jìn)制，源碼包。
為了方便起見，我這里選擇是RPM包
# yum install -y mysql-community-server-5.6.26-2.el5.x86_64.rpm
啟動MySQL
# /etc/init.d/mysqld start
Starting mysqld (via systemctl): [ OK ]
為root賬戶創(chuàng)建密碼
# mysqladmin -u root password "123456"
建議為123456，后面MyCAT配置文件schema.xml中會用到
三、安裝MyCAT
創(chuàng)建MyCAT用戶并設(shè)置密碼
# useradd mycat
# passwd mycat
解壓MyCAT文件
# tar xvf Mycat-server-1.5-alpha-20151221110028-linux.tar.gz
# mv mycat/ /usr/local/
設(shè)置mycat目錄的屬主和屬組
# chown -R mycat.mycat /usr/local/mycat/
四、測試MyCAT
首先在MySQL上創(chuàng)建三個數(shù)據(jù)庫：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)</pre>
啟動mycat服務(wù)
# cd /usr/local/mycat/bin/
# ./mycat start
Starting Mycat-server...
查看mycat服務(wù)是否啟動
# ps -ef |grep mycat
root 9640 7257 0 22:47 pts/3 00:00:00 grep --color=auto mycat
并沒有啟動
查看日志
# cd /usr/local/mycat/logs/
# cat wrapper.log
STATUS | wrapper | 2016/01/07 22:44:23 | --> Wrapper Started as Daemon
STATUS | 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.UnknownHostException: MySQL-Server1: MySQL-Server1: Name or service not known
STATUS | 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.UnknownHostException: MySQL-Server1: MySQL-Server1: Name or service not known
STATUS | 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: MySQL-Server1: MySQL-Server1: Name or service not known
STATUS | 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.UnknownHostException: MySQL-Server1: MySQL-Server1: Name or service not known
STATUS | 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.UnknownHostException: MySQL-Server1: MySQL-Server1: Name or service not known
FATAL | wrapper | 2016/01/07 22:44:43 | There were 5 failed launches in a row, each lasting less than 300 seconds. Giving up.
FATAL | wrapper | 2016/01/07 22:44:43 | There may be a configuration problem: please check the logs.
STATUS | wrapper | 2016/01/07 22:44:43 | <-- Wrapper Stopped</pre>
根據(jù)報錯信息，懷疑主機(jī)名沒有綁定
修改hosts文件，綁定主機(jī)名
# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.244.144 MySQL-Server1
重新啟動mycat服務(wù)
# cd /usr/local/mycat/bin/
# ./mycat start
這次mycat正常啟動
# 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/mycat
root 10881 7257 0 22:55 pts/3 00:00:00 grep --color=auto mycat
下面以Travelrecord為例，來進(jìn)行插入，查詢，路由分析等基本操作。
首先利用mycat連接mysql數(shù)據(jù)庫
# mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB
其中8066是mycat的監(jiān)聽端口，類似于mysql的3306端口，其中-u，-p，-h分別是用戶名，密碼和主機(jī)，-D是連接的邏輯庫。
至于為什么是這些，這個跟配置文件有關(guān)。
image.png
紅色部分表明連接的是mycat。
創(chuàng)建Travelrecord表
create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
插入數(shù)據(jù)
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)
至于ID為什么取三個值，這個與conf目錄下autopartition-long.txt的定義有關(guān)，這個文件主要定義auto-sharding-long的規(guī)則。
# range start-end ,data node index # K=1000,M=10000. 0-500M=0 500M-1000M=1 1000M-1500M=2
我主要是測試在id取不同區(qū)間的值時，分片的效果。
下面來看看分片的效果
mysql> select * from db1.travelrecord; +----+---------+------------+------+------+
| id | user_id | traveldate | fee | days |
+----+---------+------------+------+------+
| 1 | Victor | 2016-01-01 | 100 | 10 |
+----+---------+------------+------+------+
1 row in set (0.00 sec)
mysql> select * from db2.travelrecord; +---------+---------+------------+------+------+
| id | user_id | traveldate | fee | days |
+---------+---------+------------+------+------+
| 5000001 | Job | 2016-01-02 | 100 | 10 |
+---------+---------+------------+------+------+
1 row in set (0.00 sec)
mysql> select * from db3.travelrecord; +----------+---------+------------+------+------+
| id | user_id | traveldate | fee | days |
+----------+---------+------------+------+------+
| 10000001 | Slow | 2016-01-03 | 100 | 10 |
+----------+---------+------------+------+------+
1 row in set (0.00 sec)
果然是分配到三個不同的節(jié)點(diǎn)上了。
如果想看MyCAT具體會將數(shù)據(jù)分配到哪個節(jié)點(diǎn)上，可通過路由分析。
語法其實(shí)蠻簡單，就是SQL語句前加上explain語句。
下面根據(jù)explain命令查看create語句和insert語句具體會分配到哪些Datanode上。
mysql> explain create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int); +-----------+-----------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| dn1 | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn2 | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn3 | create table travelrecord (id bigint not null primary key,user_id varchar(100),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)
總結(jié)：
關(guān)于MyCAT的配置其實(shí)是蠻簡單的，最主要的是熟悉各配置文件的規(guī)則。以上用戶名，密碼，如何分庫，都是在配置文件中定義的，后續(xù)，有時間再一一詳表。
關(guān)于配置文件，conf目錄下主要以下三個需要熟悉。
server.xml是Mycat服務(wù)器參數(shù)調(diào)整和用戶授權(quán)的配置文件
schema.xml是邏輯庫定義和表以及分片定義的配置文件
rule.xml是分片規(guī)則的配置文件
