数据库垂直拆分，水平拆分利器，cobar升级版mycat（转）
原文：数据库垂直拆分，水平拆分利器，cobar升级版mycat
1，关于Mycat
Mycat情报
基于阿里的开源cobar ，可以用于生产系统中，目前在做如下的一些改进：
非阻塞IO的实现，相对于目前的cobar，并发性能大大提升，而且不会陷入假死状态
优化线程池的分配，目前cobar的线程池分配效率不高
修复cobar一些ＢＵＧ
参考impala中的impala front部分的Java代码，实现高效的Map-Reduce，能够处理上亿的大数据量
实现自动分片特性，目前cobar需要手工分片，并有一定的编程限制
官方网站：
https://github.com/MyCATApache/
好多文档，大多数都是word的，写的非常详细。
https://github.com/MyCATApache/Mycat-doc
Mycat是cobar重新优化开发的版本，和cobar的很多配置都类似。
可以参考之前写的cobar安装：
http://blog.csdn.net/freewebsys/article/details/44022421
2，安装Mycat服务
下载二进制安装文件
https://github.com/MyCATApache/Mycat-download
解压缩到/usr/local/mycat
修改配置文件：
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://org.opencloudb/"> #数据库名称是TESTDB，sqlMaxLimit设置limit防止错误sql查询大量数据 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> #自动分库规则 <!-- auto sharding by id (long) --> <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> #全局配置表，所有数据均同步到每个数据库。 <!-- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node --> <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" /> <!-- random sharding using mod sharind rule --> <table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="mod-long" /> <table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" /> #关联子表配置，不太明白 <table name="customer" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile"> <childTable name="orders" primaryKey="ID" joinKey="customer_id" parentKey="id"> <childTable name="order_items" joinKey="order_id" parentKey="id" /> </childTable> <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id" parentKey="id" /> </table> </schema> #配置多个dataNode，制定dataHost和数据库名称。 <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> #配置dataHost的读写分配。同时mysql也要配置好，Master-Slave或Master-Master <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> <!-- can have multi read hosts --> <!-- <readHost host="hostS1" url="localhost:3306" user="root" password="123456" /> --> </writeHost> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> </mycat:schema>
wrapper.conf是mycat的配置文件启动参数等
rule.xml 是配置规则xml
还有几个配置文件慢慢研究
创建数据库
CREATE DATABASE db1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE db2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE db3 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
服务启动:/bin/mycat start
/bin/mycat这个问题同时可以作为service，拷贝到/etc/init.d/目录下即可。
3，登陆mycat服务和管理端
登陆mycat服务：（端口8066）
# mysql -utest -ptest -h 127.0.0.1 -P8066 Warning: USING a password ON the command line interface can be insecure. Welcome TO the MySQL monitor. Commands END WITH ; OR \g. Your MySQL connection id IS 1 Server version: 5.5.8-mycat-1.3 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2015, Oracle AND/OR its affiliates. ALL rights reserved. Oracle IS a registered trademark OF Oracle Corporation AND/OR its affiliates. Other names may be trademarks OF their respective owners. TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the CURRENT INPUT statement. mysql> SHOW DATABASES; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 ROW IN SET (0.02 sec) mysql> USE TESTDB; Reading TABLE information FOR completion OF TABLE AND COLUMN names You can turn off this feature TO GET a quicker startup WITH -A DATABASE changed mysql> SHOW TABLES; +------------------+ | TABLES IN TESTDB | +------------------+ | company | | customer | | customer_addr | | employee | | goods | | hotnews | | orders | | order_items | | travelrecord | +------------------+ 9 ROWS IN SET (0.00 sec) mysql> CREATE TABLE company(id INT NOT NULL PRIMARY KEY,name VARCHAR(100)); Query OK, 0 ROWS affected (0.05 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) mysql> INSERT INTO company(id,name) VALUES(1,'hp'); INSERT INTO company(id,name) VALUES(2,'ibm'); INSERT INTO company(id,name) VALUES(3,'oracle'); Query OK, 3 ROWS affected (0.37 sec) Query OK, 3 ROWS affected (0.01 sec) Query OK, 3 ROWS affected (0.00 sec) mysql> SELECT * FROM company ; +----+--------+ | id | name | +----+--------+ | 1 | hp | | 2 | ibm | | 3 | oracle | +----+--------+ 3 ROWS IN SET (0.01 sec)
使用管理端登陆：（端口9066）
# mysql -utest -ptest -h 127.0.0.1 -P9066 Warning: USING a password ON the command line interface can be insecure. Welcome TO the MySQL monitor. Commands END WITH ; OR \g. Your MySQL connection id IS 2 Server version: 5.5.8-mycat-1.3 CobarManager@Alibaba Copyright (c) 2000, 2015, Oracle AND/OR its affiliates. ALL rights reserved. Oracle IS a registered trademark OF Oracle Corporation AND/OR its affiliates. Other names may be trademarks OF their respective owners. TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the CURRENT INPUT statement. mysql> SHOW @@help; +--------------------------------------+-----------------------------------+ | STATEMENT | DESCRIPTION | +--------------------------------------+-----------------------------------+ | clear @@slow WHERE datanode = ? | Clear slow SQL BY datanode | | clear @@slow WHERE schema = ? | Clear slow SQL BY schema | | KILL @@connection id1,id2,... | KILL the specified connections | | offline | CHANGE MyCat STATUS TO OFF | | online | CHANGE MyCat STATUS TO ON | | reload @@config | Reload ALL config FROM file | | reload @@route | Reload route config FROM file | | reload @@USER | Reload USER config FROM file | | ROLLBACK @@config | ROLLBACK ALL config FROM memory | | ROLLBACK @@route | ROLLBACK route config FROM memory | | ROLLBACK @@USER | ROLLBACK USER config FROM memory | | SHOW @@backend | Report backend connection STATUS | | SHOW @@cache | Report system cache usage | | SHOW @@command | Report commands STATUS | | SHOW @@connection | Report connection STATUS | | SHOW @@connection.SQL | Report connection SQL | | SHOW @@DATABASE | Report DATABASES | | SHOW @@datanode | Report dataNodes | | SHOW @@datanode WHERE schema = ? | Report dataNodes | | SHOW @@datasource | Report dataSources | | SHOW @@datasource WHERE dataNode = ? | Report dataSources | | SHOW @@heartbeat | Report heartbeat STATUS | | SHOW @@parser | Report parser STATUS | | SHOW @@processor | Report processor STATUS | | SHOW @@router | Report router STATUS | | SHOW @@server | Report server STATUS | | SHOW @@SESSION | Report front SESSION details | | SHOW @@slow WHERE datanode = ? | Report datanode slow SQL | | SHOW @@slow WHERE schema = ? | Report schema slow SQL | | SHOW @@SQL WHERE id = ? | Report specify SQL | | SHOW @@SQL.detail WHERE id = ? | Report EXECUTE detail STATUS | | SHOW @@SQL.EXECUTE | Report EXECUTE STATUS | | SHOW @@SQL.slow | Report slow SQL | | SHOW @@threadpool | Report threadPool STATUS | | SHOW @@TIME.CURRENT | Report CURRENT TIMESTAMP | | SHOW @@TIME.startup | Report startup TIMESTAMP | | SHOW @@version | Report Mycat Server version | | stop @@heartbeat name:TIME | Pause dataNode heartbeat | | switch @@datasource name:INDEX | Switch dataSource | +--------------------------------------+-----------------------------------+ 39 ROWS IN SET (0.03 sec) mysql> SHOW @@backend; +------------+------+---------+-----------------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+------------+ | processor | id | mysqlId | host | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema | txlevel | autocommit | +------------+------+---------+-----------------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+------------+ | Processor0 | 12 | 33 | 127.0.0.1 | 3306 | 42505 | 596 | 501 | 3384 | FALSE | FALSE | 0 ....... 17 ROWS IN SET (0.03 sec) mysql> SHOW @@connection; +------------+------+-----------+------+------------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+ | PROCESSOR | ID | HOST | PORT | LOCAL_PORT | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit | +------------+------+-----------+------+------------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+ | Processor0 | 2 | 127.0.0.1 | 9066 | 35952 | NULL | utf8 | 156 | 4107 | 40 | 4096 | 0 | NULL | NULL | | Processor0 | 1 | 127.0.0.1 | 8066 | 16525 | TESTDB | utf8 | 2005 | 5132 | 3461 | 4096 | 0 | 3 | TRUE | +------------+------+-----------+------+------------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+ 2 ROWS IN SET (0.02 sec) mysql> SHOW @@heartbeat; +--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ | NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP | +--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ | hostM1 | mysql | 127.0.0.1 | 3306 | 1 | 0 | idle | 30000 | 0,0,0 | 2015-03-03 14:45:00 | FALSE | +--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ 1 ROW IN SET (0.01 sec) mysql> SHOW @@datanode; +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ | NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME | +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ | dn1 | localhost1/db1 | 0 | mysql | 0 | 9 | 50 | 27 | 0 | 0 | 0 | -1 | | dn2 | localhost1/db2 | 0 | mysql | 0 | 4 | 50 | 26 | 0 | 0 | 0 | -1 | | dn3 | localhost1/db3 | 0 | mysql | 0 | 3 | 50 | 17 | 0 | 0 | 0 | -1 | +------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ 3 ROWS IN SET (0.01 sec) mysql> SHOW @@cache; +-------------------------------------+-------+------+--------+------+------+---------------+---------------+ | CACHE | MAX | CUR | ACCESS | HIT | PUT | LAST_ACCESS | LAST_PUT | +-------------------------------------+-------+------+--------+------+------+---------------+---------------+ | SQLRouteCache | 10000 | 1 | 8 | 0 | 1 | 1425364473193 | 1425363891040 | | TableID2DataNodeCache.TESTDB_ORDERS | 50000 | 0 | 0 | 0 | 0 | 0 | 0 | | ER_SQL2PARENTID | 1000 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------------------------------+-------+------+--------+------+------+---------------+---------------+ 3 ROWS IN SET (0.05 sec)
4，总结
初步安装测试了下mycat组件，还是非常不错的，确实比起cobar有不少的进步。
同时他们开发团队也说了，解决了cobar之前存在的问题。
有了全局表，这样可以使用join了，同时增加读写分离。
规则增加了不少，能满足大部分需求。
和cobar一样，mycat可以直接伪装成一个mysql服务器，对业务进行垂直拆分，水平拆分。平滑的进行数据扩展。
保证在原有系统上进行优化。接下来继续研究mycat，非常好的解决方案。
