MyCAT初体验

2015-06-12 数据库

MyCAT可以视为“MySQL”集群的企业级数据库，用来替代昂贵的Oracle集群，其背后是阿里曾经开源的知名产品Cobar。MyCAT的目标是：低成本的将现有的单机数据库和应用平滑迁移到“云”端，解决数据存储和业务规模迅速增长情况下的数据瓶颈问题。

一、部署步骤详解

(1) 用命令行工具或图形化客户端，连接MySQL，创建DEMO所用三个分片数据库：

CREATE database db1;
CREATE database db2;
CREATE database db3;

(2) 修改my.inf新增以下语句，my.inf 一般会放在/etc/my.cnf 或 /etc/mysql/my.cnf，设置为Mysql大小写不敏感，否则可能会发生表找不到的问题。

lower_case_table_names = 1

(3) 下载解压Mycat-server-1.3.0.2-20150105144205-linux.tar.gz到/usr/local/mycat (4) 修改/usr/local/conf/schema.xml，URL、用户名、密码修改，其余不变

二、运行步骤详解

(1) 进入 /usr/local/mycat/bin（默认数据端口为8066，管理端口为9066），执行：

./mycat start

(2) 进入logs目录，查看日志，如果wrapper.log 报错 java.net.BindException: Address already in use 杀掉正在执行的相关java进程：

ps -ef|grep java
kill -9 xxx

三、使用步骤详解

(1) 登录mysql，执行以下命令

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

(mycat的用户账号和授权信息是在conf/server.xml文件中配置)

(2) 表创建测试：

mysql> create table employee (id int not null primary key,name varchar(100),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(100),sharding_id int notnull);
+-----------+-----------------------------------------------------------------------------------------------+
| DATA_NODE |SQL                                                                                            |
+-----------+-----------------------------------------------------------------------------------------------+
| dn1       | create table employee (id int not null primary key,name varchar(100),sharding_id int notnull) | 
| dn2       | create table employee (id int not null primary key,name varchar(100),sharding_id int notnull) | 
+-----------+-----------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

可看到TESTDB数据库下已创建表employee，打开db1，db2 数据库也可看到已创建表employee。

(3) 插入数据测试

mysql> insert into employee(id,name,sharding_id) values (1,'jerry',10000),(2, 'jerry2', 10010),(3, 'jerry3', 10000),(4, 'jerry4', 10010);
Query OK, 1 row affected (0.03 sec)
mysql> explain insert into employee(id,name,sharding_id) values(1,'jerry',10000);
+-----------+------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                            |
+-----------+------------------------------------------------------------------------------------------------+
| dn1       | INSERT INTO employee (id, name, sharding_id) VALUES (1, 'jerry', 10000), (3, 'jerry3', 10000)  |
| dn2       | INSERT INTO employee (id, name, sharding_id) VALUES (2, 'jerry2', 10010), (4, 'jerry4', 10010) |
+-----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(4) 根据规则auto-sharding-long（主键范围）进行分片测试

mysql> create table company(id int not null primary key,name varchar(100));
Query OK, 0 rows affected (0.01 sec)
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)

(5) 三个分片上都插入了3条数据

mysql> insert into company(id,name) values(1,'jerry');
Query OK, 3 rows affected (0.00 sec)
mysql> explain insert into company(id,name) values(1,'jerry');
+-----------+------------------------------------------------+
| DATA_NODE | SQL                                            |
+-----------+------------------------------------------------+
| dn1       | insert into company(id,name) values(1,'jerry') | 
| dn2       | insert into company(id,name) values(1,'jerry') | 
| dn3       | insert into company(id,name) values(1,'jerry') | 
+-----------+------------------------------------------------+
3 rows in set (0.00 sec)

(6) 确认分片存储

mysql> select * from employee;
+----+--------+-------------+
| id | name   | sharding_id |
+----+--------+-------------+
|  1 | jerry  |       10000 |
|  3 | jerry3 |       10000 |
|  2 | jerry2 |       10010 |
|  4 | jerry4 |       10010 |
+----+--------+-------------+
4 rows in set (0.00 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)

Jerry

MyCAT初体验

一、部署步骤详解

二、运行步骤详解

三、使用步骤详解