数据库垂直拆分,水平拆分利器,cobar升级版mycat

标签: 数据库 垂直 cobar | 发表时间:2015-03-04 07:24 | 作者:freewebsys
出处:http://blog.csdn.net

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 -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,非常好的解决方案。

作者:freewebsys 发表于2015/3/3 23:24:29 原文链接
阅读:189 评论:0 查看评论

相关 [数据库 垂直 cobar] 推荐:

数据库垂直拆分,水平拆分利器,cobar升级版mycat

- - CSDN博客推荐文章
基于阿里的开源cobar ,可以用于生产系统中,目前在做如下的一些改进:. 非阻塞IO的实现,相对于目前的cobar,并发性能大大提升,而且不会陷入假死状态. 优化线程池的分配,目前cobar的线程池分配效率不高. 修复cobar一些BUG. 参考impala中的impala front部分的Java代码,实现高效的Map-Reduce,能够处理上亿的大数据量.

阿里开源Mysql分布式中间件:Cobar

- - 数据库 - ITeye博客
Cobar是阿里巴巴研发的关系型数据的分布式处理系统(Amoeba的升级版,该产品成功替代了原先基于Oracle的数据存储方案,目前已经接管了3000+个MySQL数据库的schema,平均每天处理近50亿次的SQL执行请求. )(github上面的是源码,大家下来需要自己用maven2编译后运行、者放Eclipse里面运行,一开始我用maven3没有执行成功.

科普专线:MySQL大型集群解决方案 Cobar使用说明测试案例

- - Seay's blog 小屌丝守护着信息安全、
最近好不容易抽空研究了下Cobar,感觉这个产品确实很不错(在文档方面比Amoeba强多了),特此推荐给大家. Cobar是阿里巴巴研发的关系型数据的分布式处理系统,该产品成功替代了原先基于Oracle的数据存储方案,目前已经接管了3000+个MySQL数据库的schema,平均每天处理近50亿次的SQL执行请求.

数据库sharding

- - 数据库 - ITeye博客
当团队决定自行实现sharding的时候,DAO层可能是嵌入sharding逻辑的首选位置,因为在这个层面上,每一个DAO的方法都明确地知道需要访问的数据表以及查询参数,借助这些信息可以直接定位到目标shard上,而不必像框架那样需要对SQL进行解析然后再依据配置的规则进行路由. 另一个优势是不会受ORM框架的制约.

数据库索引

- - CSDN博客推荐文章
索引是由用户创建的、能够被修改和删除的、实际存储于数据库中的物理存在;创建索引的目的是使用户能够从整体内容直接查找到某个特定部分的内容. 一般来说,索引能够提高查询,但是会增加额外的空间消耗,并且降低删除、插入和修改速度. 1.聚集索引:表数据按照索引的顺序来存储的. 2.非聚集索引:表数据存储顺序与索引顺序无关.

数据库事务

- - 数据库 - ITeye博客
事务传播发生在类似以下情形:. 假设methodB的配置是:. 如果methodA在事务里,那么methodB也在这个事务中运行. 如果methodA不在事务里,那么methodB重新建立一个事务运行. 如果methodA在事务里,那么methodB也在这个事务中运行. 如果methodA不在是事务里,那么methodB在非事务中运行.

数据库优化

- - 数据库 - ITeye博客
程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点: . a) SQL的使用规范: .   i.尽量避免大事务操作,慎用holdlock子句,提高系统并发能力.   ii.尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接.   iii.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作.

数据库调优

- - 数据库 - ITeye博客
1、1、调整数据结构的设计. 这一部分在开发信息系统之前完成,程序员需要考虑是否使用ORACLE数据库的分区功能,对于经常访问的数据库表是否需要建立索引等. 这一部分也是在开发信息系统之前完成,程序员在这一步需要考虑应用程序使用什么样的体系结构,是使用传统的Client/Server两层体系结构,还是使用Browser/Web/Database的三层体系结构.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.