快速体验MyCAT

横空出世的MyCat截至到2015年4月，保守估计已经有超过60个项目在使用，主要应用在电信领域、互联网项目，大部分是交易和管理系统，少量是信息系统。比较大的系统中，数据规模单表单月30亿。本人也初步尝鲜，体验了一把，见以下描述，供大家参考。

D:/mycat/bin>mysql -utest -ptest -P9066 -Dtestdb   mysql> show @@help;      -- 获取有关管理的相关命令   mysql> show @@database;  -- 查看逻辑数据库   +----------+   | DATABASE |   +----------+   | testdb   |   +----------+   mysql> show @@datanode;  --查看分片节点 +------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ | NAME | DATHOST           | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME | +------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ | dn1  | 192.168.1.204/db1 |     0 | mysql |      0 |   10 | 1000 |     152 |          0 |        0 |       0 |            -1 | | dn2  | 192.168.1.143/db2 |     0 | mysql |      0 |    0 | 1000 |       0 |          0 |        0 |       0 |            -1 | +------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+    mysql> show @@server;   --查看服务器状态  +---------------+-------------+--------------+------------+---------------+---------------+---------+--------+-----------------------+  | UPTIME        | USED_MEMORY | TOTAL_MEMORY | MAX_MEMORY | RELOAD_TIME   | ROLLBACK_TIME | CHARSET | STATUS | AVG_BUFPOOL_ITEM_SIZE |  +---------------+-------------+--------------+------------+---------------+---------------+---------+--------+-----------------------+  | 23m 50s 344ms |   344386624 |   2058878976 | 2058878976 | 1443150953033 |            -1 | gbk     | ON     |                  1878 |  +---------------+-------------+--------------+------------+---------------+---------------+---------+--------+-----------------------+     mysql> show @@version;  --查看版本   +----------------------------------------+   | VERSION                                |   +----------------------------------------+   | 5.5.8-mycat-1.4-RELEASE-20150922233010 |   +----------------------------------------+     --基于数据端口演示，分别创建连接用户及数据库   (root@192.168.1.204) [(none)]> grant all privileges on *.* to 'mycat'@'192.168.%.%' identified by 'pwd';   Query OK, 0 rows affected (0.00 sec)     (root@192.168.1.204) [(none)]> create database db1;   Query OK, 1 row affected (0.00 sec)      (root@192.168.1.143) [(none)]> grant all privileges on *.* to 'mycat'@'192.168.%.%' identified by 'pwd';   Query OK, 0 rows affected (0.00 sec)      (root@192.168.1.143) [(none)]> create database db2;   D:/mycat/bin>mysql -utest -ptest -P8066 -Dtestdb     D:/mycat/bin>mysql -utest -ptest -P8066 -Dtestdb   mysql> select database();   +------------+   | DATABASE() |   +------------+   | testdb     |   +------------+     mysql> show tables;   +------------------+   | Tables in testdb |   +------------------+   | goods            |   +------------------+     mysql> desc goods;   ERROR 1146 (42S02): Table 'db2.goods' doesn't exist     mysql> explain create table goods(id int auto_increment not null primary key,val varchar(20));   +-----------+--------------------------------------------------------------------------------+   | DATA_NODE | SQL                                                                            |   +-----------+--------------------------------------------------------------------------------+   | dn1       | create table goods(id int auto_increment not null primary key,val varchar(20)) |   | dn2       | create table goods(id int auto_increment not null primary key,val varchar(20)) |   +-----------+--------------------------------------------------------------------------------+     mysql> create table goods(id int auto_increment not null primary key,val varchar(20));   Query OK, 0 rows affected (0.05 sec)     mysql> insert into goods(val) values('apple');     mysql> explain select * from goods;   +-----------+-------------------------------+   | DATA_NODE | SQL                           |   +-----------+-------------------------------+   | dn2       | SELECT * FROM goods LIMIT 100 | --此处可知，select查询会直接查询第二个分片   +-----------+-------------------------------+      mysql> select 'Leshami' as author,'http://blog.csdn.net/leshami' as Blog;   +---------+------------------------------+   | author  | Blog                         |   +---------+------------------------------+   | Leshami | http://blog.csdn.net/leshami |   +---------+------------------------------+     mysql> select * from goods;   +----+-------+   | id | val   |   +----+-------+   |  1 | apple |   +----+-------+     D:/mycat/bin>mysql -umycat -ppwd -h192.168.1.143 -P3307 -e "select * from db2.goods"   Warning: Using a password on the command line interface can be insecure.   +----+-------+   | id | val   |   +----+-------+   |  1 | apple |   +----+-------+     D:/mycat/bin>mysql -umycat -ppwd -h192.168.1.204 -P3306 -e "select * from db1.goods"   Warning: Using a password on the command line interface can be insecure.   +----+-------+   | id | val   |   +----+-------+   |  1 | apple |   +----+-------+      D:/mycat/bin>mysql -uuser -puser -P8066 -Dtestdb  --只读用户连接   mysql> use testdb;   Database changed   mysql> show tables;   +------------------+   | Tables in testdb |   +------------------+   | goods            |   +------------------+     mysql> desc goods;   ERROR 1495 (HY000): User readonly   --提示用户只读

作者：robinson_0612 发表于2015/10/8 9:29:39 原文链接