数据库垂直拆分,水平拆分利器,cobar升级版mycat
来源:互联网 发布:java 堆栈使用 编辑:程序博客网 时间:2024/06/07 04:59
本文原文连接: http://blog.csdn.net/freewebsys/article/details/44046365 转载请注明出处!
1,关于Mycat
Mycat情报
基于阿里的开源cobar ,可以用于生产系统中,目前在做如下的一些改进:
非阻塞IO的实现,相对于目前的cobar,并发性能大大提升,而且不会陷入假死状态
优化线程池的分配,目前cobar的线程池分配效率不高
修复cobar一些BUG
参考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 -P8066Warning: 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 1Server 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 itsaffiliates. Other names may be trademarks OF their respectiveowners.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 namesYou can turn off this feature TO GET a quicker startup WITH -ADATABASE changedmysql> 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 -P9066Warning: 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 2Server version: 5.5.8-mycat-1.3 CobarManager@AlibabaCopyright (c) 2000, 2015, Oracle AND/OR its affiliates. ALL rights reserved.Oracle IS a registered trademark OF Oracle Corporation AND/OR itsaffiliates. Other names may be trademarks OF their respectiveowners.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,非常好的解决方案。
- 数据库垂直拆分,水平拆分利器,cobar升级版mycat
- 数据库垂直拆分 水平拆分
- 数据库垂直拆分 水平拆分
- 数据库水平拆分和垂直拆分区别(
- 数据库优化-水平拆分 垂直拆分
- 数据库水平拆分和垂直拆分区别
- 数据库垂直拆分与水平拆分概念
- 数据库水平拆分和垂直拆分区别(
- 数据库优化-水平拆分 垂直拆分
- 数据库优化-水平拆分 垂直拆分
- 数据库优化-水平拆分 垂直拆分
- 数据库优化-水平拆分 垂直拆分
- 数据库的水平拆分和垂直拆分
- 数据库的水平拆分与垂直拆分
- 数据库垂直拆分与水平拆分
- 数据库的分库分表拆分:垂直拆分,水平拆分
- 垂直拆分与水平拆分
- 垂直拆分与水平拆分
- bootstrap、jquery easyui、extjs界面比较
- Feekood基本语法(2)-- 数据类型、关键字、操作符
- MIME详解 01
- Think in Java(十六):枚举类型
- .NET/ASP.NET Routing路由(深入解析路由系统架构原理)
- 数据库垂直拆分,水平拆分利器,cobar升级版mycat
- 关于android的日志输出&LogCat
- 浅谈语义网
- uva1437 String painter
- Think in Java(十七):注解
- Android网络数据加密传输
- UVa 11346 - Probability
- 函数、作用域1
- Deep learning:一(基础知识_1)