cobar实现mysql分片及分片集之内双节点之间的高可用
来源：互联网 发布：矩阵的f范数怎么求 编辑：程序博客网 时间：2024/04/30 12:53
首先看节点规划： 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条）
1
2
3
4
5
6
7
8
9
10
11
12
13
## 需要在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
1
2
3
4
## 解压
[iyunv@mgs02 opt]# tar xf cobar-server-1.2.7.tar.gz -C /opt/
[iyunv@mgs02 opt]# ls cobar-server-1.2.7/
bin conf lib logs
3、配置cobar
首先配置schema.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
[iyunv@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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
[iyunv@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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
[iyunv@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
1
2
3
4
5
6
7
8
9
[iyunv@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 &
[iyunv@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
[iyunv@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代理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[iyunv@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、测试分片功能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
## 首先查看后端是否test表中是否有数据，如果有，清空，便于测试
[iyunv@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)
[iyunv@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代理插入数据
[iyunv@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)
## 后端查看数据是否分片
[iyunv@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)
[iyunv@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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## 首先停掉101机器上的mysql服务
[iyunv@mysql01 ~]# /etc/init.d/mysql stop
Shutting down MySQL.......... SUCCESS!
## 然后在cobar代理中查询id=1的记录
[iyunv@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的监控
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
## 首先下载并安装jdk
[iyunv@mgs02 opt]# yum install jdk-7u75-linux-x64.rpm -y
## 配置java环境变量
[iyunv@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
## 验证是否安装成功
[iyunv@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
[iyunv@mgs02 opt]# tar xf apache-tomcat-7.0.59.tar.gz -C /opt/
## 将cobar-manager的war包放到tomcat容器中
[iyunv@mgs02 opt]# cp cobar-manager-1.0.5.war /opt/apache-tomcat-7.0.59/webapps/
## 启动tomcat
[iyunv@mgs02 opt]# /opt/apache-tomcat-7.0.59/bin/startup.sh &
## 查看tomcat是否启动成功
[iyunv@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
[iyunv@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
[iyunv@mgs02 opt]# mkdir /home/admin/xml/ -p
[iyunv@mgs02 opt]# cd apache-tomcat-7.0.59/webapps/cobar-manager-1.0.5/WEB-INF/classes/
[iyunv@mgs02 classes]# cp *.xml /home/admin/xml/
## 重启tomcat，然后浏览器中访问：
## 初始登录口令:root/123456
## 登录完成后，可以进行设置，图形界面的设置很简单，不详述，附几张图：
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条）
1
2
3
4
5
6
7
8
9
10
11
12
13
## 需要在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
1
2
3
4
## 解压
[iyunv@mgs02 opt]# tar xf cobar-server-1.2.7.tar.gz -C /opt/
[iyunv@mgs02 opt]# ls cobar-server-1.2.7/
bin conf lib logs
3、配置cobar
首先配置schema.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
[iyunv@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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
[iyunv@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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
[iyunv@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
1
2
3
4
5
6
7
8
9
[iyunv@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 &
[iyunv@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
[iyunv@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代理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[iyunv@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、测试分片功能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
## 首先查看后端是否test表中是否有数据，如果有，清空，便于测试
[iyunv@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)
[iyunv@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代理插入数据
[iyunv@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)
## 后端查看数据是否分片
[iyunv@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)
[iyunv@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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## 首先停掉101机器上的mysql服务
[iyunv@mysql01 ~]# /etc/init.d/mysql stop
Shutting down MySQL.......... SUCCESS!
## 然后在cobar代理中查询id=1的记录
[iyunv@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的监控
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
## 首先下载并安装jdk
[iyunv@mgs02 opt]# yum install jdk-7u75-linux-x64.rpm -y
## 配置java环境变量
[iyunv@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
## 验证是否安装成功
[iyunv@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
[iyunv@mgs02 opt]# tar xf apache-tomcat-7.0.59.tar.gz -C /opt/
## 将cobar-manager的war包放到tomcat容器中
[iyunv@mgs02 opt]# cp cobar-manager-1.0.5.war /opt/apache-tomcat-7.0.59/webapps/
## 启动tomcat
[iyunv@mgs02 opt]# /opt/apache-tomcat-7.0.59/bin/startup.sh &
## 查看tomcat是否启动成功
[iyunv@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
[iyunv@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
[iyunv@mgs02 opt]# mkdir /home/admin/xml/ -p
[iyunv@mgs02 opt]# cd apache-tomcat-7.0.59/webapps/cobar-manager-1.0.5/WEB-INF/classes/
[iyunv@mgs02 classes]# cp *.xml /home/admin/xml/
## 重启tomcat，然后浏览器中访问：
## 初始登录口令:root/123456
## 登录完成后，可以进行设置，图形界面的设置很简单，不详述，附几张图：
0 0
- cobar实现mysql分片及分片集之内双节点之间的高可用
- MySQL分片高可用集群之Cobar部署使用
- MySQL分片高可用集群之Cobar部署使用
- MySQL 高可用：mysql+mycat实现数据库分片（分库分表）
- MySQL 高可用：mysql+mycat实现数据库分片（分库分表）
- MySQL 高可用：mysql+mycat实现数据库分片（分库分表）
- Redis 高可用： twemproxy实现缓存服务器分片集群
- Jedis客户端分片--构建高可用客户端分片
- 利用Mongodb的复制集搭建高可用分片，Replica Sets + Sharding的搭建过程
- 利用Mongodb的复制集搭建高可用分片，Replica Sets + Sharding的搭建过程
- 利用Mongodb的复制集搭建高可用分片，Replica Sets + Sharding的搭建过程
- 搭建高可用mongodb集群-Sharding 分片
- Mongodb分片高可用集群搭建
- 分片实验的实现
- MySQL分片
- 分片
- Mongodb数据分片的实现
- mycat高可用 ER分片的2种场景详细分析
- Delphi COM编程技术六（DCOM技术）
- iOS应用程序状态图
- MFC界面库BCGControlBar新版功能详解四：示例和其他功能
- 我已经写了48年代码了，我感觉我还能写下去
- okhttp是android平台最好的网络库
- cobar实现mysql分片及分片集之内双节点之间的高可用
- window下使用命令行创建symfony项目
- 如何优化C语言代码(程序员必读)
- iOS 原生二维码的扫描详解(代码)
- Leetcode 55. Jump Game
- 两台linux机器建立信任
- UNPv1第七章：套接口选项
- Oracle中tnsping命令解析
- BZOJ2134: 单选错位
