mysql分布式中间件cobar51CTO博客 - 牛牛娱乐

mysql分布式中间件cobar51CTO博客

2019年03月01日08时27分54秒 | 作者: 运珧 | 标签: 数据库,数据,拆分 | 浏览: 2810

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-rr. 1 root root 2604 Dec 29  2012 log4j.xml
-rw-rr. 1 root root 1262 Dec 29  2012 rule.xml
-rw-rr. 1 root root 1966 Dec 29  2012 schema.xml    mysql数据库的IP,端口
-rw-rr. 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" >
    <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>

 

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表牛牛娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章