OracleMyCAT简易入门

MyCAT是mysql中间件,前身是Ali享誉的Cobar,Cobar在开源了一段时间后,不了了之。于是MyCAT扛起了那面大旗,在大数量时期,其利害攸关性愈发突显。那篇小说首假如MyCAT的入门安插。

一、安装java

因Mycat是用java开发的,所以需求在尝试环境下安装java,官方提议jdk1.7及以上版本

Java Oracle官方下载地址为:

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

 

解压jdk-7u79-linux-x64.tar.gz文书,并配备java的环境变量

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

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

 

编辑/etc/profile文件

# vim /etc/profile  添加如下内容

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/

Oracle 1

里头有各样版本,包蕴SportagePM,二进制,源码包。

为了便于起见,作者那边选用是凯雷德PM包

# 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账户创立密码

# mysqladmin -u root password “123456”

提出为123456,后边MyCAT配置文件schema.xml中会用到

 

三、安装MyCAT

始建MyCAT用户并设置密码

# useradd mycat

# passwd mycat

 

解压MyCAT文件

# tar xvf Mycat-server-1.5-alpha-20151221110028-linux.tar.gz

# mv mycat/ /usr/local/

 

设置mycat目录的属主和属组

# chown -R mycat.mycat /usr/local/mycat/

 

四、测试MyCAT

率先在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)

 

启动mycat服务

# cd /usr/local/mycat/bin/

# ./mycat start

Starting Mycat-server...

查阅mycat服务是或不是运维

# 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

 

依照报错音讯,思疑主机名没有绑定

修改hosts文件,绑定主机名

# 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服务

# 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为例,来进展扦插,查询,路由分析等基本操作。

首先使用mycat连接mysql数据库

# mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB

里面8066是mycat的监听端口,类似于mysql的3306端口,在那之中-u,-p,-h分别是用户名,密码和主机,-D是连续的逻辑库。

有关为什么是这一个,那几个跟配置文件有关。

Oracle 2

新民主主义革命部分注明连接的是mycat。

创建Travelrecord表

create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);

布置数据

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的定义有关,那一个文件根本定义auto-sharding-long的平整。

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

笔者根本是测试在id取不相同距离的值时,分片的意义。

下边来看看分片的机能

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)

果然是分配到八个不等的节点上了。

要是想看MyCAT具体会将数据分配到哪些节点上,可透过路由分析。

语法其实蛮简单,正是SQL语句前加上explain语句。

上边根据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)

 

总结:

关于MyCAT的安排其实是蛮简单的,最要紧的是十分熟练各配备文件的条条框框。以上用户名,密码,如何分库,都是在配置文件中定义的,后续,有时间再一一详表。

有关配置文件,conf目录下第②以下四个供给熟谙。

server.xml是Mycat服务器参数调整和用户授权的配备文件

schema.xml是逻辑库定义和表以及分片定义的布局文件

rule.xml是分片规则的配置文件

相关文章