数据库–Cobar分布式数据库集群MySQL中间件_MySQL

原创作品，转载请标明：http://blog.geekcome.com/archives/252

运行环境：

• 主机1：Ubuntu14.04 Desktop + MySQL5.5 + JDK 1.7(HP Z400) 内网IP地址:192.168.137.8
• NODE1：Ubuntu 13.04 server + MySQL5.5 内网IP地址:192.168.137.31
• NODE2：Ubuntu 13.04 server + MySQL5.5 内网IP地址:192.168.137.32

  注：(NODE1和NODE2运行于XEN虚拟化平台，硬件环境HP Z800)

  Cobar简介：

  Cobar是关系型数据库的分布式处理系统，它可以在分布式的环境下看上去像传统数据库一样为您提供海量数据服务。

  • 产品在阿里巴巴B2B公司已经稳定运行了3年以上。
  • 目前已经接管了3000+个MySQL数据库的schema，为应用提供数据服务。
  • 据最近统计cobar集群目前平均每天处理近50亿次的SQL执行请求。

    Cobar最主要解决的问题是：分布式和HA。

    分布式：主要是通过将同一个表的数据拆分成多个，放入不同的数据库实例，查询的时候也会按照同样的操作方式，来更新具体数据库实例中的对应的数据。

    HA：高可用性，在设置了MYSQL心跳的情况下，如果主数据库发生了异常，Cobar会自动连接从数据库，如果主数据库恢复正常，只能手动恢复到主数据库。Cobar只负责切换数据库实例，不负责主从数据库的同步，所以需要提前将主从数据库设置双向同步。

    存在的不足:

    • (1).不支持跨库情况下的join、分页、排序、子查询操作。
    • (2).SET语句执行会被忽略，事务和字符集设置除外。
    • (3).分库情况下，insert语句必须包含拆分字段列名。
    • (4).分库情况下，update语句不能更新拆分字段的值。
    • (5).不支持SAVEPOINT操作。
    • (6).暂时只支持MySQL数据节点。
    • (7).使用JDBC时，不支持rewriteBatchedStatements=true参数设置(默认为false)。
    • (8).使用JDBC时，不支持useServerPrepStmts=true参数设置(默认为false)。
    • (9).使用JDBC时，BLOB, BINARY, VARBINARY字段不能使用setBlob()或setBinaryStream()方法设置参数。

      当然，如果想努力实现这些功能，可以fork官方的源码：https://github.com/alibaba/cobar

      环境搭建

      • Cobar服务器：192.168.137.8:8066 用户名/密码：root/sa 实例名：dbtest
      • 主机1:192.168.137.8:3306 用户名/密码：cobar/sa 实例名：dbtest1
      • Node1:192.168.137.31:3306 用户名/密码：cobar/sa 实例名：dbtest2
      • Node2:192.168.137.32:3306 用户名/密码：cobar/sa 实例名：dbtest3

        Cobar-Server-1.2.7版本下载：http://pan.baidu.com/s/1pJudQh9

        实验拓扑图如下：

        
        

        首先分别在三个主机创建数据库实例：

        01	#创建dbtest1脚本

        02	<code class="Brush plain">dropdatabaseif exists dbtest1;

        03	<code class="Brush plain">createdatabasedbtest1;

        04	<code class="Brush plain">use dbtest1;

        05	<code class="Brush plain">#在dbtest1上创建tb1

        06	<code class="Brush plain">createtabletb1(

        07	<code class="Brush plain">id intnotnull,

        08	<code class="Brush plain">gmt datetime);

        09	#创建dbtest2

        10	<code class="Brush plain">dropdatabaseif exists dbtest2;

        11	<code class="Brush plain">createdatabasedbtest2;

        12	<code class="Brush plain">use dbtest2;

        13	<code class="Brush plain">#在dbtest2上创建tb2

        14	<code class="Brush plain">createtabletb2(

        15	<code class="Brush plain">id intnotnull,

        16	<code class="Brush plain">val <code class="Brush keyword">varchar<code class="Brush plain">(256));

        17	#创建dbtest3

        18	<code class="Brush plain">dropdatabaseif exists dbtest3;

        19	<code class="Brush plain">createdatabasedbtest3;

        20	<code class="Brush plain">use dbtest3;

        21	<code class="Brush plain">#在dbtest3上创建tb2

        22	<code class="Brush plain">createtabletb2(

        23	<code class="Brush plain">id intnotnull,

        24	<code class="Brush plain">val <code class="Brush keyword">varchar<code class="Brush plain">(256));

        Cobar配置：

        schema.xml配置如下

        01

        02	cobar:schema <code class="Brush color1">xmlns:cobar<code class="Brush plain">=<code class="Brush string">"http://cobar.alibaba.com/"<code class="Brush plain">>

        03	<code class="Brush comments"><!-- schema定义 -->

        04	<code class="Brush plain">schema <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"dbtest" <code class="Brush color1">dataNode<code class="Brush plain">=<code class="Brush string">"dnTest1"<code class="Brush plain">>

        05

        <code class="Brush plain">table <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"tb2" <code class="Brush color1">dataNode<code class="Brush plain">=<code class="Brush string">"dnTest2,dnTest3" <code class="Brush color1">rule<code class="Brush plain">=<code class="Brush string">"rule1" <code class="Brush plain">/>

        06	<code class="Brush plain"><code class="Brush keyword">schema<code class="Brush plain">>

        07	<code class="Brush comments"><!-- 数据节点定义，数据节点由数据源和其他一些参数组织而成。-->

        08	<code class="Brush plain">dataNode <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"dnTest1"<code class="Brush plain">>

        09	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"dataSource"<code class="Brush plain">>

        10	<code class="Brush plain">dataSourceRef<code class="Brush plain">>dsTest[0]<code class="Brush keyword">dataSourceRef<code class="Brush plain">>

        11	<code class="Brush plain"><code class="Brush keyword">property<code class="Brush plain">>

        12	<code class="Brush plain"><code class="Brush keyword">dataNode<code class="Brush plain">>

        13	<code class="Brush plain">dataNode <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"dnTest2"<code class="Brush plain">>

        14	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"dataSource"<code class="Brush plain">>

        15	<code class="Brush plain">dataSourceRef<code class="Brush plain">>dsTest[1]<code class="Brush keyword">dataSourceRef<code class="Brush plain">>

        16	<code class="Brush plain"><code class="Brush keyword">property<code class="Brush plain">>

        17	<code class="Brush plain"><code class="Brush keyword">dataNode<code class="Brush plain">>

        18	<code class="Brush plain">dataNode <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"dnTest3"<code class="Brush plain">>

        19	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"dataSource"<code class="Brush plain">>

        20	<code class="Brush plain">dataSourceRef<code class="Brush plain">>dsTest[2]<code class="Brush keyword">dataSourceRef<code class="Brush plain">>

        21	<code class="Brush plain"><code class="Brush keyword">property<code class="Brush plain">>

        22	<code class="Brush plain"><code class="Brush keyword">dataNode<code class="Brush plain">>

        23	<code class="Brush comments"><!-- 数据源定义，数据源是一个具体的后端数据连接的表示。-->

        24	<code class="Brush plain">dataSource <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"dsTest" <code class="Brush color1">type<code class="Brush plain">=<code class="Brush string">"mysql"<code class="Brush plain">>

        25	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"location"<code class="Brush plain">>

        26	<code class="Brush plain">location<code class="Brush plain">>192.168.137.8:3306/dbtest1<code class="Brush keyword">location<code class="Brush plain">>

        27	<code class="Brush plain">location<code class="Brush plain">>192.168.137.31:3306/dbtest2<code class="Brush keyword">location<code class="Brush plain">>

        28	<code class="Brush plain">location<code class="Brush plain">>192.168.137.32:3306/dbtest3<code class="Brush keyword">location<code class="Brush plain">>

        29	<code class="Brush plain"><code class="Brush keyword">property<code class="Brush plain">>

        30	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"user"<code class="Brush plain">>cobar<code class="Brush keyword">property<code class="Brush plain">>

        31	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"password"<code class="Brush plain">>sa<code class="Brush keyword">property<code class="Brush plain">>

        32	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"sqlMode"<code class="Brush plain">>STRICT_TRANS_TABLES<code class="Brush keyword">property<code class="Brush plain">>

        33	<code class="Brush plain"><code class="Brush keyword">dataSource<code class="Brush plain">>

        34	<code class="Brush keyword">cobar:schema<code class="Brush plain">>

        server.xml简单配置

        1

        2	cobar:server <code class="Brush color1">xmlns:cobar<code class="Brush plain">=<code class="Brush string">"http://cobar.alibaba.com/"<code class="Brush plain">>

        3	<code class="Brush comments"><!-- 用户访问定义，用户名、密码、schema等信息。 -->

        4	<code class="Brush plain">user <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"root"<code class="Brush plain">>

        5	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"password"<code class="Brush plain">>sa<code class="Brush keyword">property<code class="Brush plain">>

        6	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"schemas"<code class="Brush plain">>dbtest<code class="Brush keyword">property<code class="Brush plain">>

        7	<code class="Brush plain"><code class="Brush keyword">user<code class="Brush plain">>

        8	<code class="Brush keyword">cobar:server<code class="Brush plain">>

        rule.xml配置

        01

        02	cobar:rule <code class="Brush color1">xmlns:cobar<code class="Brush plain">=<code class="Brush string">"http://cobar.alibaba.com/"<code class="Brush plain">>

        03	<code class="Brush comments"><!-- 路由规则定义，定义什么表，什么字段，采用什么路由算法 -->

        04	<code class="Brush plain">tableRule <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"rule1"<code class="Brush plain">>

        05	<code class="Brush plain">rule<code class="Brush plain">>

        06	<code class="Brush plain">columns<code class="Brush plain">>val<code class="Brush keyword">columns<code class="Brush plain">>

        07	<code class="Brush plain">algorithm<code class="Brush plain">><code class="Brush color2"><code class="Brush plain"><code class="Brush keyword">algorithm<code class="Brush plain">>

        08	<code class="Brush plain"><code class="Brush keyword">rule<code class="Brush plain">>

        09	<code class="Brush plain"><code class="Brush keyword">tableRule<code class="Brush plain">>

        10	<code class="Brush comments"><!-- 路由函数定义 -->

        11	<code class="Brush plain">function <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"func1" <code class="Brush color1">class<code class="Brush plain">=<code class="Brush string">"com.alibaba.cobar.route.function.PartitionByLong"<code class="Brush plain">>

        12	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"partitionCount"<code class="Brush plain">>2<code class="Brush keyword">property<code class="Brush plain">>

        13	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"partitionLength"<code class="Brush plain">>512<code class="Brush keyword">property<code class="Brush plain">>

        14	<code class="Brush plain"><code class="Brush keyword">function<code class="Brush plain">>

        15	<code class="Brush comments"><!-- 路由函数定义 -->

        16	<code class="Brush plain">function <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"func2" <code class="Brush color1">class<code class="Brush plain">=<code class="Brush string">"com.alibaba.cobar.route.function.PartitionByString"<code class="Brush plain">>

        17	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"partitionCount"<code class="Brush plain">>2<code class="Brush keyword">property<code class="Brush plain">>

        18	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"partitionLength"<code class="Brush plain">>512<code class="Brush keyword">property<code class="Brush plain">>

        19	<code class="Brush plain">property <code class="Brush color1">name<code class="Brush plain">=<code class="Brush string">"hashSlice"<code class="Brush plain">>-5:<code class="Brush keyword">property<code class="Brush plain">>

        20	<code class="Brush plain"><code class="Brush keyword">function<code class="Brush plain">>

        21	<code class="Brush keyword">cobar:rule<code class="Brush plain">>

        这里需要说明，INSERT语句中必须包含路由规则定义的字段，否则Cobar不会对数据进行拆分，同时存储到多个数据库实例中，比如上面的tb2表中，id字段如果设置了auto_increment，插入语句中不用指明id字段，这样就无法达到数据包拆分的目的。

        准备完成之后直接运行bin目录下的./startup.sh即可。

        然后查看输入的日志：

        01	yan@yan-Z400:~/cobar-server-1.2.7/logs$ tail -f stdout.log

        02	09:57:00,155 INFO Cobar is ready to startup ...

        03	09:57:00,155 INFO Startup processors ...

        04	09:57:00,198 INFO Startup connector ...

        05	09:57:00,202 INFO Initialize dataNodes ...

        06	09:57:00,811 INFO dnTest1:0 init success

        07	09:57:00,816 INFO dnTest3:0 init success

        08	09:57:00,821 INFO dnTest2:0 init success

        09	09:57:00,835 INFO CobarManager is started and listening on 9066

        10	09:57:00,837 INFO CobarServer is started and listening on 8066

        11	09:57:00,837 INFO ===============================================

        这样cobar服务端就已经启动。

        直接使用jdbc或mysql终端连接cobar：

        01	yan@yan-Z400:~$ mysql -uroot -psa -P8066 -h192.168.137.8

        02	Welcome to the MySQL monitor. Commands end with ; or \g.

        03	Your MySQL connection id is 1

        04	Server version: 5.1.48-cobar-1.2.7 Cobar Server (ALIBABA)

        05

        06	Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

        07

        08	Oracle is a registered trademark of Oracle Corporation and/or its

        09	affiliates. Other names may be trademarks of their respective

        10

        owners.

        11

        12	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

        下面使用几句Pyhton进行数据库的插入操作：

        01	#!/usr/bin/env python

        02	#coding=utf-8

        03	import <code class="Brush plain">MySQLdb

        04

        05

        #连接

        06

        cxn <code class="Brush keyword">= <code class="Brush plain">MySQLdb.Connect(host<code class="Brush keyword">=<code class="Brush string">'192.168.137.8'<code class="Brush plain">,port<code class="Brush keyword">=<code class="Brush value">8066<code class="Brush plain">, user <code class="Brush keyword">= <code class="Brush string">'root'<code class="Brush plain">, passwd <code class="Brush keyword">= <code class="Brush string">'sa'<code class="Brush plain">)

        07

        #游标

        08	cur <code class="Brush keyword">= <code class="Brush plain">cxn.cursor()

        09

        10	cur.execute(<code class="Brush string">"USE dbtest"<code class="Brush plain">)

        11

        12	for <code class="Brush plain">i <code class="Brush keyword">in <code class="Brush functions">range<code class="Brush plain">(<code class="Brush value">1<code class="Brush plain">,<code class="Brush value">200<code class="Brush plain">):

        13	<code class="Brush plain">cur.execute(<code class="Brush string">"INSERT INTO tb2 (val) values ('this is a test record %d')"<code class="Brush keyword">%<code class="Brush plain">i)

        14	<code class="Brush keyword">print <code class="Brush string">'insert the %d record into cobar'<code class="Brush keyword">%<code class="Brush plain">i

        15

        16	cur.close()

        17	cxn.commit()

        18	cxn.close()

        插入后查看数据库dbtest2和数据dbtest3的情况：

        可以看到有100条数据插入了dbtest2,99条数据插入了dbtest3。

        后面会对Cobar进行更深入的了解。我的Fork分支

        (完)