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

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

WBOYOriginal: 2016-06-07 14:55:571805browse

首先看节点规划： 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

首先看节点规划：

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
## 登录完成后，可以进行设置，图形界面的设置很简单，不详述，附几张图：

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：mysql存储引擎优化参数Next article：percona-xtrabackup实现数据库备份和灾难恢复

See more

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

Related articles