mysql分布式中间件cobar

Cobar的分布式主要是通过将表放入不同的库来实现:
     1.Cobar支持将一张表水平拆分成多份分别放入不同的库来实现表的水平拆分
     2.Cobar也支持将不同的表放入不同的库
     3.多数情况下，用户会将以上两种方式混合使用
     4.Cobar不支持将一张表，例如test表拆分成test_1, test_2, test_3.....放在同一个库中，必须将拆分后的表分别放入不同的库来实现分布式

缺点:

    1.不支持跨库的关联操作:join、分页、排序、子查询

    2.不支持SAVEPOINT操作

    3.不支持SET语句的执行，事务和字符集设置语句除外

    4.只支持MySQL数据节点

    5.对于拆分表，插入操作须给出列名，必须包含拆分字段

环境规划:

IP                         数据库      表

192.168.1.247        test01       t1

192.168.1.247        test02       t1

192.168.1.247        test03       t1

说明:在本服务器创建三个数据库,数据库中创建相同的表和表类型,将t1表中的数据拆分到teat01,02,03数据库中.

1.创建数据库和表

[root@tong1 ~]# /usr/local/mysql-5.6.23/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6028
Server version: 5.6.23-log MySQL Community Server (GPL)

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> create database test01;                 --创建数据库test01,02,03
Query OK, 1 row affected (0.03 sec)

mysql> create database test02;
Query OK, 1 row affected (0.03 sec)

mysql> create database test03;
Query OK, 1 row affected (0.03 sec)

mysql> \u test01
Database changed
mysql> create table t1(a int,b char(5));        --在三个数据库创建相同的表
Query OK, 0 rows affected (0.34 sec)

mysql> \u test02
Database changed
mysql> create table t1(a int,b char(5));
Query OK, 0 rows affected (0.31 sec)

mysql> \u test03
Database changed
mysql> create table t1(a int,b char(5));
Query OK, 0 rows affected (0.30 sec)

mysql> show tables;
+------------------+
| Tables_in_test03 |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

mysql> grant all privileges on *.* to tong@'localhost' identified by 'system';
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

mysql> exit
Bye
[root@tong1 bin]#

2.安装java开发软件包

[root@tong1 ~]# tar xvf jdk-7u71-linux-x64.tar.gz  -C /usr/local/

[root@tong1 ~]# cd /usr/local/

[root@tong1 local]# chown  -R root:root jdk1.7.0_71/

[root@tong1 local]# vim /etc/profile       --添加环境变量

export PATH=$PATH:/usr/local/protobuf-2.5.0/bin:/usr/local/jdk1.7.0_71/bin
export JAVA_HOME=/usr/local/jdk1.7.0_71/
export CLASS_HOME=/usr/local/jdk1.7.0_71/lib

[root@tong1 local]# . /etc/profile

[root@tong1 local]# java -version          --查看java是否安装成功
java version "1.7.0_71"
Java(TM) SE Runtime Environment (build 1.7.0_71-b14)
Java HotSpot(TM) 64-Bit Server VM (build 24.71-b01, mixed mode)
[root@tong1 local]#

3.下载安装cobar

下载地址:http://pan.baidu.com/s/1o6igLwY

[root@tong1 ~]# tar xvf cobar-server-1.2.7.tar.gz  -C /usr/local/

[root@tong1 ~]# cd /usr/local/cobar-server-1.2.7/
[root@tong1 cobar-server-1.2.7]# ll
total 36
drwxr-xr-x. 2 root root  4096 Dec 29  2012 bin
drwxr-xr-x. 2 root root  4096 Dec 29  2012 conf
-rwsrwsrwt. 1 root root   575 Dec 29  2012 COPYRIGHT
drwxr-xr-x. 3 root root  4096 May 14 10:13 lib
-rwsrwsrwt. 1 root root 11549 Dec 29  2012 LICENSE
drwxr-xr-x. 2 root root  4096 Dec 29  2012 logs
-rwsrwsrwt. 1 root root   428 Dec 29  2012 README

[root@tong1 cobar-server-1.2.7]# cd conf/
[root@tong1 conf]# ll
total 16
-rw-r--r--. 1 root root 2604 Dec 29  2012 log4j.xml
-rw-r--r--. 1 root root 1262 Dec 29  2012 rule.xml
-rw-r--r--. 1 root root 1966 Dec 29  2012 schema.xml    --mysql数据库的IP,端口
-rw-r--r--. 1 root root 2292 Dec 29  2012 server.xml

[root@tong1 conf]# vim schema.xml

<!-- schema定义 -->
  <schema name="test" dataNode="test1">        --test架构名,用于用户登陆,test1第一个数据库
    <table name="t1" dataNode="test2,test3" rule="rule1" />   --t1是表名,拆分的表,test2,test3是两个数据库名
  </schema>

  <!-- 数据节点定义，数据节点由数据源和其他一些参数组织而成。-->
  <dataNode name="test1">                    --test1是第一个数据库
    <property name="dataSource">
      <dataSourceRef>test[0]</dataSourceRef>     --第一个数据库源
    </property>
  </dataNode>
  <dataNode name="test2">                             --第二个数据库
    <property name="dataSource">
      <dataSourceRef>test[1]</dataSourceRef>
    </property>
  </dataNode>
  <dataNode name="test3">                            --第三个数据库
    <property name="dataSource">
      <dataSourceRef>test[2]</dataSourceRef>
    </property>
  </dataNode>

  <!-- 数据源定义，数据源是一个具体的后端数据连接的表示。-->
  <dataSource name="test" type="mysql">          --test源数据,用于用户登陆
    <property name="location">
      <location>192.168.1.247:3306/test1</location>    --三个数据库服务器和数据库名,数据库也可在不同的服务器上
      <location>192.168.1.247:3306/test2</location>
      <location>192.168.1.247:3306/test3</location>
    </property>
    <property name="user">tong</property>            --用户登陆
    <property name="password">system</property>          --密码
    <property name="sqlMode">STRICT_TRANS_TABLES</property>
  </dataSource>

[root@tong1 conf]# vim rule.xml

  <!-- 路由规则定义，定义什么表，什么字段，采用什么路由算法 -->
  <tableRule name="rule1">
    <rule>
      <columns>id</columns>
      <algorithm><![CDATA[ func1(${id}) ]]></algorithm>
    </rule>
  </tableRule>

  <!-- 路由函数定义 -->
  <function name="func1" class="com.alibaba.cobar.route.function.PartitionByLong">
    <property name="partitionCount">2</property>
    <property name="partitionLength">512</property>

[root@tong1 conf]# vim server.xml

  <!-- 系统参数定义，服务端口、管理端口，处理器个数、线程池等。 -->
  <system>
    <property name="serverPort">8066</property>             --cobar服务启动端口 
    <property name="managerPort">9066</property>          --管理端口
    <property name="initExecutor">16</property>
    <property name="timerExecutor">4</property>
    <property name="managerExecutor">4</property>
    <property name="processors">4</property>
    <property name="processorHandler">8</property>
    <property name="processorExecutor">8</property>
    <property name="clusterHeartbeatUser">_HEARTBEAT_USER_</property>
    <property name="clusterHeartbeatPass">_HEARTBEAT_PASS_</property>
  </system>

  <!-- 用户访问定义，用户名、密码、schema等信息。 -->
  <user name="tong">            --登陆用户名
    <property name="password">system</property>           --密码
    <property name="schemas">test</property>                 --架构名,用户连接的数据库
  </user>

[root@tong1 bin]# ./startup.sh          --启动服务
"/usr/local/jdk1.7.0_71/bin/java" -Dcobar.home="/usr/local/cobar-server-1.2.7" -classpath "/usr/local/cobar-server-1.2.7/conf:/usr/local/cobar-server-1.2.7/lib/classes:/usr/local/cobar-server-1.2.7/lib/cobar-server-1.2.7.jar:/usr/local/cobar-server-1.2.7/lib/log4j-1.2.16.jar" -server -Xms1024m -Xmx1024m -Xmn256m -Xss256k -XX:+AggressiveOpts -XX:+UseBiasedLocking -XX:+UseFastAccessorMethods -XX:+DisableExplicitGC -XX:+UseParNewGC -XX:+UseConcMarkSweepGC -XX:+CMSParallelRemarkEnabled -XX:+UseCMSCompactAtFullCollection -XX:+UseCMSInitiatingOccupancyOnly -XX:CMSInitiatingOccupancyFraction=75 com.alibaba.cobar.CobarStartup >> "/usr/local/cobar-server-1.2.7/logs/console.log" 2>&1 &

[root@tong1 bin]# cat ../logs/stdout.log     --查看日志

15:33:02,933 INFO  ===============================================
15:33:02,934 INFO  Cobar is ready to startup ...
15:33:02,934 INFO  Startup processors ...
15:33:03,026 INFO  Startup connector ...
15:33:03,031 INFO  Initialize dataNodes ...
15:33:03,051 INFO  test2:0 init success
15:33:03,053 INFO  test1:0 init success
15:33:03,055 INFO  test3:0 init success
15:33:03,066 INFO  CobarManager is started and listening on 9066
15:33:03,068 INFO  CobarServer is started and listening on 8066
15:33:03,071 INFO  ===============================================

[root@tong1 bin]# cat ../logs/console.log

log4j:WARN 2015-05-14 15:33:02 [/usr/local/cobar-server-1.2.7/conf/log4j.xml] load completed.[root@tong1 bin]# netstat -antup | grep java
tcp        0      0 :::8066                     :::*                        LISTEN      25359/java          
tcp        0      0 :::9066                     :::*                        LISTEN      25359/java          
tcp        0      0 ::ffff:192.168.1.247:52451  ::ffff:192.168.1.247:3306   ESTABLISHED 25359/java          
tcp        0      0 ::ffff:192.168.1.247:52450  ::ffff:192.168.1.247:3306   ESTABLISHED 25359/java          
tcp        0      0 ::ffff:192.168.1.247:52452  ::ffff:192.168.1.247:3306   ESTABLISHED 25359/java          
[root@tong1 bin]#

4.登陆数据库插入数据(以下红色部分不能少)

[root@tong1 data]# /usr/local/mysql-5.6.23/bin/mysql -h 192.168.1.247 -utong -p -P8066 -Dtest
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.48-cobar-1.2.7 Cobar Server (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 databases;     --客户端连接的数据库名
+----------+
| DATABASE |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

mysql> \u test
Database changed
mysql> show tables;        --表名
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
2 rows in set (0.00 sec)

mysql> insert into t1 values(1,'c');                --插入数据
Query OK, 2 rows affected (0.14 sec)

mysql> insert into t1 values(2,'z');
Query OK, 2 rows affected (0.20 sec)

mysql> select * from t1;           --不知道为什么test1数据库中有重复的数据
+------+------+
| a    | b    |
+------+------+
|    1 | c    |
|    2 | z    |
|    1 | c    |
|    2 | z    |
+------+------+
4 rows in set (0.01 sec)

mysql>

5.在另外两个数据库中查看数据

[root@tong1 bin]# /usr/local/mysql-5.6.23/bin/mysql -u root -p -D test2　　　--在test2查看数据
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6124
Server version: 5.6.23-log MySQL Community Server (GPL)

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> select * from t1;     --有数据
+------+------+
| a    | b    |
+------+------+
|    1 | c    |
|    2 | z    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test3.t1;    --在test3数据库查看数据
+------+------+
| a    | b    |
+------+------+
|    1 | c    |
|    2 | z    |
+------+------+
3 rows in set (0.00 sec)

mysql>