手记

cobar实现mysql分片及分片集之内双节点之间的高可用

    首先看节点规划:

        IP:192.168.0.99     Host:mgs02      Role:cobar

        IP:192.168.0.101    Host:mysql01    Role:mysql-master1

        IP:192.168.0.102    Host:mysql02    Role:mysql-slave1

        IP:192.168.0.103    Host:mysql03    Role:mysql-master2

        IP:192.168.0.104    Host:mysql04    Role:mysql-slave2

    数据库分片规划:

        lens_conf.*                 不分片,数据分布在101和102这个单片上

        lens_mobapp_trace.test表    分片,101、102为一个片,103、104为一个片

        lens_mobapp_data.emp表      分片,101、102为一个片,103、104为一个片

        lens_mobapp_data.loc表      分片,101、102为一个片,103、104为一个片

    1、cobar不支持HA下的读写分离,如果使用了HA,每个分片内的节点都可能要有数据写入,因此,应该配置双向同步,首先配置mysql-master1和mysql-slave1、mysql-master2和mysql-slave2的双向同步,这里不再详述,与单向同步的区别就是需要开启log_slave_updates参数,然后彼此设置对方为自己的主库。

    接着需要在后端的mysql要做分片的表所在库中创建一张心跳表,并插入10条数据(针对多个cobar节点,避免单条数据的锁争用;如果只有一个cobar节点的话可以只插入一条数据,这里插入10条)

## 需要在lens_conf、lens_mobapp_data、lens_mobapp_trace三个schema中执行下面语句,可以只在

## 每个分片的某一个节点上执行,另一个节点就会同步执行。

create table xdual(id int not null,gmt datetime,primary key (id));

insert into xdual values(1,now());

insert into xdual values(2,now());

insert into xdual values(3,now());

insert into xdual values(4,now());

insert into xdual values(5,now());

insert into xdual values(6,now());

insert into xdual values(7,now());

insert into xdual values(8,now());

insert into xdual values(9,now());

insert into xdual values(10,now());

    2、安装cobar

## 解压

[root@mgs02 opt]# tar xf cobar-server-1.2.7.tar.gz -C /opt/

[root@mgs02 opt]# ls cobar-server-1.2.7/

bin  conf  lib  logs

    3、配置cobar

    首先配置schema.xml

[root@mgs02 opt]# vim  cobar-server-1.2.7/conf/schema.xml 

<?xml version="1.0" encoding="UTF-8"?>

<!--

 - Copyright 1999-2012 Alibaba Group.

 -  

 - Licensed under the Apache License, Version 2.0 (the "License");

 - you may not use this file except in compliance with the License.

 - You may obtain a copy of the License at

 -  

 -      http://www.apache.org/licenses/LICENSE-2.0

 -  

 - Unless required by applicable law or agreed to in writing, software

 - distributed under the License is distributed on an "AS IS" BASIS,

 - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

 - See the License for the specific language governing permissions and

 - limitations under the License.

-->

<!DOCTYPE cobar:schema SYSTEM "schema.dtd">

<cobar:schema xmlns:cobar="http://cobar.alibaba.com/">

  <!-- schema定义 -->

  <schema name="lens_conf" dataNode="dn_shared01[0]" />

  <schema name="lens_mobapp_data" dataNode="dn_shared01[1]" >

    <table name="test" dataNode="dn_shared01[1],dn_shared02[0]" rule="tbRule1" />

  </schema>

  <schema name="lens_mobapp_trace" dataNode="dn_shared01[2]">

    <table name="emp" dataNode="dn_shared01[2],dn_shared02[1]" rule="tbRule2" />

    <table name="loc" dataNode="dn_shared01[2],dn_shared02[1]" rule="tbRule3" />

  </schema>

  

  <!-- 数据节点定义,数据节点由数据源和其他一些参数组织而成。-->

  <dataNode name="dn_shared01">

    <property name="dataSource">

      <dataSourceRef>ds_master_shared01$0-2</dataSourceRef>

      <dataSourceRef>ds_slave_shared01$0-2</dataSourceRef>

    </property>

    <property name="poolSize">256</property>

    <property name="heartbeatSQL">update xdual set gmt=now() where id=${(1,10)}</property>

  </dataNode>

  <dataNode name="dn_shared02">

    <property name="dataSource">

      <dataSourceRef>ds_master_shared02$0-1</dataSourceRef>

      <dataSourceRef>ds_slave_shared02$0-1</dataSourceRef>

    </property>

    <property name="poolSize">256</property>

    <property name="heartbeatSQL">update xdual set gmt=now() where id=${(1,10)}</property>

  </dataNode>

  <!-- 数据源定义,数据源是一个具体的后端数据连接的表示。-->

  <dataSource name="ds_master_shared01" type="mysql">

    <property name="location">

      <location>192.168.0.101:3306/lens_conf</location>

      <location>192.168.0.101:3306/lens_mobapp_data</location>

      <location>192.168.0.101:3306/lens_mobapp_trace</location>

    </property>

    <property name="user">lens</property>

    <property name="password">123456</property>

    <property name="sqlMode">STRICT_TRANS_TABLES</property>

  </dataSource>

  <dataSource name="ds_slave_shared01" type="mysql">

    <property name="location">

      <location>192.168.0.102:3306/lens_conf</location>

      <location>192.168.0.102:3306/lens_mobapp_data</location>

      <location>192.168.0.102:3306/lens_mobapp_trace</location>

    </property>

    <property name="user">lens</property>

    <property name="password">123456</property>

    <property name="sqlMode">STRICT_TRANS_TABLES</property>

  </dataSource>

  <dataSource name="ds_master_shared02" type="mysql">

    <property name="location">

      <location>192.168.0.103:3306/lens_mobapp_data</location>

      <location>192.168.0.103:3306/lens_mobapp_trace</location>

    </property>

    <property name="user">lens</property>

    <property name="password">123456</property>

    <property name="sqlMode">STRICT_TRANS_TABLES</property>

  </dataSource>

  <dataSource name="ds_slave_shared02" type="mysql">

    <property name="location">

      <location>192.168.0.104:3306/lens_mobapp_data</location>

      <location>192.168.0.104:3306/lens_mobapp_trace</location>

    </property>

    <property name="user">lens</property>

    <property name="password">123456</property>

    <property name="sqlMode">STRICT_TRANS_TABLES</property>

  </dataSource>

</cobar:schema>

    配置rule.xml

[root@mgs02 opt]# vim cobar-server-1.2.7/conf/rule.xml   

<?xml version="1.0" encoding="UTF-8"?>

<!--

 - Copyright 1999-2012 Alibaba Group.

 -  

 - Licensed under the Apache License, Version 2.0 (the "License");

 - you may not use this file except in compliance with the License.

 - You may obtain a copy of the License at

 -  

 -      http://www.apache.org/licenses/LICENSE-2.0

 -  

 - Unless required by applicable law or agreed to in writing, software

 - distributed under the License is distributed on an "AS IS" BASIS,

 - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

 - See the License for the specific language governing permissions and

 - limitations under the License.

-->

<!DOCTYPE cobar:rule SYSTEM "rule.dtd">

<cobar:rule xmlns:cobar="http://cobar.alibaba.com/">

  <!-- 路由规则定义,定义什么表,什么字段,采用什么路由算法 -->

  <tableRule name="tbRule1">

    <rule>

      <columns>id</columns>

      <algorithm><![CDATA[ int_2(${id}) ]]></algorithm>

    </rule>

  </tableRule>

  <tableRule name="tbRule2">

    <rule>

      <columns>empno</columns>

      <algorithm><![CDATA[ int_2(${empno}) ]]></algorithm>

    </rule>

  </tableRule>

  <tableRule name="tbRule3">

    <rule>

      <columns>addr_id</columns>

      <algorithm><![CDATA[ int_2(${addr_id}) ]]></algorithm>

    </rule>

  </tableRule>

  <!-- 路由函数定义 -->

  <function name="int_2" class="com.alibaba.cobar.route.function.PartitionByLong">

    <property name="partitionCount">2</property>

    <property name="partitionLength">512</property>

  </function>

</cobar:rule>

    配置server.xml

[root@mgs02 opt]# cat  cobar-server-1.2.7/conf/server.xml 

<?xml version="1.0" encoding="UTF-8"?>

<!--

 - Copyright 1999-2012 Alibaba Group.

 -  

 - Licensed under the Apache License, Version 2.0 (the "License");

 - you may not use this file except in compliance with the License.

 - You may obtain a copy of the License at

 -  

 -      http://www.apache.org/licenses/LICENSE-2.0

 -  

 - Unless required by applicable law or agreed to in writing, software

 - distributed under the License is distributed on an "AS IS" BASIS,

 - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

 - See the License for the specific language governing permissions and

 - limitations under the License.

-->

<!DOCTYPE cobar:server SYSTEM "server.dtd">

<cobar:server xmlns:cobar="http://cobar.alibaba.com/">

  

  <!-- 系统参数定义,服务端口、管理端口,处理器个数、线程池等。 -->

  <system>

    <property name="serverPort">8066</property>

    <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="lens">

    <property name="password">123456</property>

  </user>

  <!--

  <user name="root">

    <property name="password"></property>

  </user>

  -->

  <!-- 集群列表定义,指定集群节点的主机和权重,用于集群间的心跳和客户端负载均衡。 -->

  <cluster>

    <node name="cobar01">

      <property name="host">192.168.0.99</property>

      <property name="weight">1</property>

    </node>

  </cluster>

   

  <!-- 隔离区定义,可以限定某个主机上只允许某个用户登录。 -->

  <!--

  <quarantine>

    <host name="1.2.3.4">

      <property name="user">test</property>

    </host>

  </quarantine>

  -->

</cobar:server>

    4、启动cobar

[root@mgs02 opt]# cobar-server-1.2.7/bin/startup.sh 

"/usr/java/jdk1.7.0_75/bin/java" -Dcobar.home="/opt/cobar-server-1.2.7" -classpath "/opt/cobar-server-1.2.7/conf:/opt/cobar-server-1.2.7/lib/classes:/opt/cobar-server-1.2.7/lib/cobar-server-1.2.7.jar:/opt/cobar-server-1.2.7/lib/log4j-1.2.17.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 >> "/opt/cobar-server-1.2.7/logs/console.log" 2>&1 &

[root@mgs02 opt]# ps -ef |grep cobar

root      1120     1 13 18:58 pts/0    00:00:01 /usr/java/jdk1.7.0_75/bin/java -Dcobar.home=/opt/cobar-server-1.2.7 -classpath /opt/cobar-server-1.2.7/conf:/opt/cobar-server-1.2.7/lib/classes:/opt/cobar-server-1.2.7/lib/cobar-server-1.2.7.jar:/opt/cobar-server-1.2.7/lib/log4j-1.2.17.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

root      1165  1062  0 18:59 pts/0    00:00:00 grep cobar

[root@mgs02 opt]# netstat -alnut |grep 8066

tcp        0      0 :::8066                     :::*                        LISTEN      

tcp        0      0 ::ffff:192.168.0.99:8066    ::ffff:192.168.0.99:36721   ESTABLISHED 

tcp        0      0 ::ffff:192.168.0.99:36721   ::ffff:192.168.0.99:8066    ESTABLISHED

    5、连接cobar代理

[root@mgs02 opt]# mysql -ulens -p -h127.0.0.1 -P8066 -Dlens_mobapp_data

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 3

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

Copyright (c) 2000, 2013, 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 tables;

+----------------------------+

| Tables_in_lens_mobapp_data |

+----------------------------+

| test                       |

| xdual                      |

+----------------------------+

2 rows in set (0.01 sec)

   6、测试分片功能

## 首先查看后端是否test表中是否有数据,如果有,清空,便于测试

[root@mgs02 opt]# mysql -ulens -p -h 192.168.0.101 -Dlens_mobapp_data

Enter password: 

mysql> show tables;

+----------------------------+

| Tables_in_lens_mobapp_data |

+----------------------------+

| test                       |

| xdual                      |

+----------------------------+

2 rows in set (0.00 sec)

mysql> truncate test;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;

Empty set (0.00 sec)

[root@mgs02 opt]# mysql -ulens -p -h 192.168.0.103 -Dlens_mobapp_data

Enter password: 

mysql> show tables;

+----------------------------+

| Tables_in_lens_mobapp_data |

+----------------------------+

| test                       |

| xdual                      |

+----------------------------+

2 rows in set (0.00 sec)

mysql> truncate test;

Query OK, 0 rows affected (0.03 sec)

mysql> select  * from test;

Empty set (0.00 sec)

## 使用cobar代理插入数据

[root@mgs02 opt]# mysql -ulens -p -h 127.0.0.1 -P8066 -Dlens_mobapp_data

Enter password: 

mysql> insert into test(id,obj) values(1,'Linux');

Query OK, 1 row affected (0.03 sec)

mysql> insert into test(id,obj) values(1020,'Mysql');

Query OK, 1 row affected (0.02 sec)

mysql> select * from test;

+------+-------+

| id   | obj   |

+------+-------+

|    1 | Linux |

| 1020 | Mysql |

+------+-------+

2 rows in set (0.02 sec)

## 后端查看数据是否分片

[root@mgs02 opt]# mysql -ulens -p -h 192.168.0.101 -Dlens_mobapp_data   

Enter password: 

mysql> select * from test;

+------+-------+

| id   | obj   |

+------+-------+

|    1 | Linux |

+------+-------+

1 row in set (0.00 sec)

[root@mgs02 opt]# mysql -ulens -p -h 192.168.0.103 -Dlens_mobapp_data

Enter password: 

mysql> select * from test;

+------+-------+

| id   | obj   |

+------+-------+

| 1020 | Mysql |

+------+-------+

1 row in set (0.00 sec)

## 可见数据已经成功分片了

    7、测试分片内部双节点的HA

## 首先停掉101机器上的mysql服务

[root@mysql01 ~]# /etc/init.d/mysql  stop

Shutting down MySQL.......... SUCCESS! 

## 然后在cobar代理中查询id=1的记录

[root@mgs02 opt]# mysql -ulens -p -h 127.0.0.1 -P8066 -Dlens_mobapp_data   

Enter password: 

mysql> select * from test where id=1;

+------+-------+

| id   | obj   |

+------+-------+

|    1 | Linux |

+------+-------+

1 row in set (0.01 sec)

## 停掉101服务,代理中查询id=1的记录,发现仍旧可以查询,说明101、102间的HA正常

## 需要注意的是如果101恢复后,不会自动切换回101,除非102异常

    8、安装cobar-manager实现cobar的监控

## 首先下载并安装jdk

[root@mgs02 opt]# yum install jdk-7u75-linux-x64.rpm  -y

## 配置java环境变量

[root@mgs02 opt]# vim ~/.bash_profile 

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/opt/amoeba/bin

export JAVA_HOME=/usr/java/jdk1.7.0_75

export JRE_HOME=/usr/java/jdk1.7.0_75/jre

export CLASSPATH=./:/usr/java/jdk1.7.0_75/lib:/usr/java/jdk1.7.0_75/jre/lib

export PATH

## 验证是否安装成功

[root@mgs02 opt]# java -version

java version "1.7.0_75"

Java(TM) SE Runtime Environment (build 1.7.0_75-b13)

Java HotSpot(TM) 64-Bit Server VM (build 24.75-b04, mixed mode)

## 安装tomcat

[root@mgs02 opt]# tar xf apache-tomcat-7.0.59.tar.gz  -C /opt/

## 将cobar-manager的war包放到tomcat容器中

[root@mgs02 opt]# cp cobar-manager-1.0.5.war /opt/apache-tomcat-7.0.59/webapps/

## 启动tomcat

[root@mgs02 opt]# /opt/apache-tomcat-7.0.59/bin/startup.sh &

## 查看tomcat是否启动成功

[root@mgs02 opt]# ps -ef |grep tomcat

root      1354     1 71 22:10 pts/1    00:00:07 /usr/java/jdk1.7.0_75/jre/bin/java -Djava.util.logging.config.file=/opt/apache-tomcat-7.0.59/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.endorsed.dirs=/opt/apache-tomcat-7.0.59/endorsed -classpath /opt/apache-tomcat-7.0.59/bin/bootstrap.jar:/opt/apache-tomcat-7.0.59/bin/tomcat-juli.jar -Dcatalina.base=/opt/apache-tomcat-7.0.59 -Dcatalina.home=/opt/apache-tomcat-7.0.59 -Djava.io.tmpdir=/opt/apache-tomcat-7.0.59/temp org.apache.catalina.startup.Bootstrap start

root      1374  1288  0 22:10 pts/1    00:00:00 grep tomcat

[root@mgs02 opt]# tail -f /opt/apache-tomcat-7.0.59/logs/catalina.out 

Mar 31, 2015 10:10:27 PM org.apache.catalina.startup.HostConfig deployDirectory

INFO: Deploying web application directory /opt/apache-tomcat-7.0.59/webapps/examples

Mar 31, 2015 10:10:28 PM org.apache.catalina.startup.HostConfig deployDirectory

INFO: Deployment of web application directory /opt/apache-tomcat-7.0.59/webapps/examples has finished in 609 ms

Mar 31, 2015 10:10:28 PM org.apache.coyote.AbstractProtocol start

INFO: Starting ProtocolHandler ["http-bio-8080"]

Mar 31, 2015 10:10:28 PM org.apache.coyote.AbstractProtocol start

INFO: Starting ProtocolHandler ["ajp-bio-8009"]

Mar 31, 2015 10:10:28 PM org.apache.catalina.startup.Catalina start

INFO: Server startup in 6160 ms

[root@mgs02 opt]# mkdir /home/admin/xml/ -p

[root@mgs02 opt]# cd apache-tomcat-7.0.59/webapps/cobar-manager-1.0.5/WEB-INF/classes/

[root@mgs02 classes]# cp *.xml /home/admin/xml/

## 重启tomcat,然后浏览器中访问:

## 初始登录口令:root/123456

## 登录完成后,可以进行设置,图形界面的设置很简单,不详述,附几张图:

wKioL1UaUN7zFV70AAOTikAPC2c783.jpg

wKiom1UaT6LQlxEBAAVzw-DCkO8717.jpg

wKioL1UaUN-DX6bpAAesD_aMkks901.jpg

       

©著作权归作者所有:来自51CTO博客作者quenlang的原创作品,如需转载,请注明出处,否则将追究法律责任

mysql分片cobarmysql


0人推荐
随时随地看视频
慕课网APP