MySQL分布式数据库服务中间件--Cobar研究三

来源：互联网发布：医院体检软件编辑：程序博客网时间：2024/05/22 13:49

在实际应用中，经常需要部署一个Cobar集群，再结合软负载LVS或者硬件负载设备F5对外提供服务；我们称集群中的一台Cobar为一个Cobar节点，这篇就来学习下cobar集群部署以及一些常用管理命令。

一、配置cobar cluster

只需要在server.xml中加入cluster配置即可

more server.xml

<?xml version="1.0" encoding="UTF-8"?><!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="test">    <property name="password">test</property>    <property name="schemas">UserCenter</property>  </user>  <!--  <user name="root">    <property name="password"></property>  </user>  -->  <!-- 集群列表定义，指定集群节点的主机和权重，用于集群间的心跳和客户端负载均衡。 --><!--组建一个Cobar集群，只需在cluster配置中把所有Cobar节点（注意：包括当前Cobar自身）都配置上便可--><!--node名称，一个node表示一个Cobar节点，一旦配置了node，当前Cobar便会向此节点定期发起心跳，探测节点的运行情况--> <cluster>    <node name="cobar1">      <!--Cobar节点IP, 表示当前Cobar将会向192.168.66.89上部署的Cobar发送心跳-->      <property name="host">192.168.66.89</property>      <!--节点的权重，用于客户端的负载均衡，用户可以通过命令查询某个节点的运行情况以及权重-->      <property name="weight">1</property>    </node>     <!-- 当前节点 -->    <node name="cobar2">      <property name="host">192.168.89.8</property>      <property name="weight">1</property>    </node> </cluster>     <!-- 隔离区定义，可以限定某个主机上只允许某个用户登录。 -->  <!--  <quarantine>    <host name="1.2.3.4">      <property name="user">test</property>    </host>  </quarantine>  --></cobar:server>

用户只需登录Cobar的服务端口(8066)，运行Cobar自带的查询命令show cobar_cluster，便可查询集群中所有节点的运行情况以及权重，并根据查询结果做负载均衡。

任意登录其中一个节点

[root@localhost ~]# mysql -h192.168.66.89 -utest -ptest -P8066 -DUserCenter Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.1.48-cobar-1.2.7 Cobar Server (ALIBABA)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show cobar_cluster;+---------------+--------+| HOST          | WEIGHT |+---------------+--------+| 192.168.89.8  |      1 || 192.168.66.89 |      1 |+---------------+--------+2 rows in set (0.00 sec)mysql>

注意：
1、如果需要配置Cobar集群，当前Cobar自身也需要作为一个节点配置在cluster中，Cobar不会默认向自己发心跳；
2、show cobar_cluster只显示cluster配置中的正常Cobar节点，如果节点异常（如超时或错误），结果中便不会包含此节点。

Cobar通过9066端口向用户提供了一些管理和监控命令

注意：9066端口暂时未做权限控制，普通用户和超级用户均可登陆

通过show @@help可以查看9066端口支持的所有命令,以及简单解释

[root@localhost ~]# mysql -h192.168.66.89 -utest -ptest -P9066 -DUserCenter Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 11Server version: 5.1.48-cobar-1.2.7 CobarManager@AlibabaCopyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show @@help;+--------------------------------------+-----------------------------------+| STATEMENT                            | DESCRIPTION                       |+--------------------------------------+-----------------------------------+| clear @@slow where datanode = ?      | Clear slow sql by datanode        || clear @@slow where schema = ?        | Clear slow sql by schema          || kill @@connection id1,id2,...        | Kill the specified connections    || offline                              | Change Cobar status to OFF        || online                               | Change Cobar status to ON         || reload @@config                      | Reload all config from file       || reload @@route                       | Reload route config from file     || reload @@user                        | Reload user config from file      || rollback @@config                    | Rollback all config from memory   || rollback @@route                     | Rollback route config from memory || rollback @@user                      | Rollback user config from memory  || show @@backend                       | Report backend connection status  || show @@command                       | Report commands status            || show @@connection                    | Report connection status          || show @@connection.sql                | Report connection sql             || show @@database                      | Report databases                  || show @@datanode                      | Report dataNodes                  || show @@datanode where schema = ?     | Report dataNodes                  || show @@datasource                    | Report dataSources                || show @@datasource where dataNode = ? | Report dataSources                || show @@heartbeat                     | Report heartbeat status           || show @@parser                        | Report parser status              || show @@processor                     | Report processor status           || show @@router                        | Report router status              || show @@server                        | Report server status              || show @@slow where datanode = ?       | Report datanode slow sql          || show @@slow where schema = ?         | Report schema slow sql            || show @@sql where id = ?              | Report specify SQL                || show @@sql.detail where id = ?       | Report execute detail status      || show @@sql.execute                   | Report execute status             || show @@sql.slow                      | Report slow SQL                   || show @@threadpool                    | Report threadPool status          || show @@time.current                  | Report current timestamp          || show @@time.startup                  | Report startup timestamp          || show @@version                       | Report Cobar Server version       || stop @@heartbeat name:time           | Pause dataNode heartbeat          || switch @@datasource name:index       | Switch dataSource                 |+--------------------------------------+-----------------------------------+37 rows in set (0.00 sec)

二、测试

MySQL宕机测试

测试前我们先看下现在所用的数据源及心跳等信息

mysql> show @@datanode;+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| NAME  | DATASOURCES                       | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| node1 | source-master[0],source-backup[0] |     0 | mysql |      0 |    0 |  256 |       0 |          0 |        0 |       0 |            -1 || node2 | source-master[1],source-backup[1] |     0 | mysql |      0 |    0 |  256 |       0 |          0 |        0 |       0 |            -1 |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+

# NAME： datanode名称。
# DATASOURCES： datanode对应的主备datasource，按照结果列出的顺序依次为主、备、第二备......,Cobar依次为其编号为0、1、2.....依次类推。
# INDEX：指示datanode正在使用的数据源，如node1的INDEX为0，就表示此datanode正在使用source-master[0]这个数据源

mysql> show @@heartbeat;
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| cobar1 | COBAR | 192.168.66.89 | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-13 17:46:16 | false |
| cobar2 | COBAR | 192.168.89.8 | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-13 17:46:16 | false |
| node1 | MYSQL | 192.168.89.4 | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-13 17:46:20 | false |
| node2 | MYSQL | 192.168.89.4 | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-13 17:46:20 | false |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
4 rows in set (0.00 sec)

看以看出，我们用的MySQL数据源是89.4,由两个cobar节点(66.89、89.8)组成一个cobar集群

人为将89.4MySQL服务关闭，然后查看心跳状态，发现cobar已经切换到备数据源89.5上了

mysql> show @@heartbeat;+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| NAME   | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| cobar1 | COBAR | 192.168.66.89 | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-13 18:08:41 | false || cobar2 | COBAR | 192.168.89.8  | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-13 18:08:41 | false || node1  | MYSQL | 192.168.89.5  | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-13 18:08:40 | false || node2  | MYSQL | 192.168.89.5  | 3306 |       1 |     0 | idle   |   30000 | 4,4,4        | 2015-02-13 18:08:40 | false |+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+4 rows in set (0.00 sec)

# NAME：节点的名称，如果是Cobar心跳，则为Cobar节点的名称，如果是MySQL心跳，则为datanode的名称。
# TYPE：心跳类型
# HOST | PORT ：心跳连接的host与port
# RS_CODE：心跳结果，0为初始状态，1为OK，-1为ERROR，2为OFF， -2为TIMEOUT
# RETRY：当心跳发生错误时，已经重试的心跳次数
# STATUS：如果当前连接正在心跳，则status为checking，否则为idle
# TIMEOUT：系统设定的心跳超时时间
# EXECUTE_TIME：最近1分钟，10分钟，30分钟心跳时间的平均值
# LAST_ACTIVE_TIME: 上次心跳结束时间
# STOP：心跳是否被停止

mysql> show @@datanode;+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| NAME  | DATASOURCES                       | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| node1 | source-master[0],source-backup[0] |     1 | mysql |      0 |    0 |  256 |       0 |          0 |        0 |       0 |            -1 || node2 | source-master[1],source-backup[1] |     1 | mysql |      0 |    0 |  256 |       0 |          0 |        0 |       0 |            -1 |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+

我们可以看出node1用的数据源是source-backup[0]，node2用的数据源是source-backup[1]，表明已经都切换到备数据源了。

恢复MySQL主库，再次查看，发现数据源并没有发生切换。

mysql> show @@datanode;+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| NAME  | DATASOURCES                       | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| node1 | source-master[0],source-backup[0] |     1 | mysql |      0 |    0 |  256 |       0 |          0 |        0 |       0 |            -1 || node2 | source-master[1],source-backup[1] |     1 | mysql |      0 |    0 |  256 |       0 |          0 |        0 |       0 |            -1 |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+2 rows in set (0.00 sec)mysql> show @@heartbeat;+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| NAME   | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| cobar1 | COBAR | 192.168.66.89 | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-15 12:48:57 | false || cobar2 | COBAR | 192.168.89.8  | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-15 12:48:57 | false || node1  | MYSQL | 192.168.89.5  | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-15 12:48:57 | false || node2  | MYSQL | 192.168.89.5  | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-15 12:48:57 | false |+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+4 rows in set (0.00 sec)

人为使备数据源89.5MySQL宕机，再次查看发现数据源已切换到89.4

mysql> show @@datanode;+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| NAME  | DATASOURCES                       | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| node1 | source-master[0],source-backup[0] |     0 | mysql |      0 |    0 |  256 |       0 |          0 |        0 |       0 |            -1 || node2 | source-master[1],source-backup[1] |     0 | mysql |      0 |    0 |  256 |       0 |          0 |        0 |       0 |            -1 |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+2 rows in set (0.00 sec)mysql> show @@heartbeat;+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| NAME   | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| cobar1 | COBAR | 192.168.66.89 | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-15 12:51:47 | false || cobar2 | COBAR | 192.168.89.8  | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-15 12:51:47 | false || node1  | MYSQL | 192.168.89.4  | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-15 12:51:47 | false || node2  | MYSQL | 192.168.89.4  | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-15 12:51:47 | false |+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+4 rows in set (0.00 sec)

Cobar宕机测试

测试前先查看下cobar节点状态，两个节点都在线

mysql> show cobar_cluster;+---------------+--------+| HOST          | WEIGHT |+---------------+--------+| 192.168.89.8  |      1 || 192.168.66.89 |      1 |+---------------+--------+2 rows in set (0.00 sec)

人为宕机其中一个节点，再次查看发现活动的节点只剩下66.89了

mysql> show cobar_cluster;+---------------+--------+| HOST          | WEIGHT |+---------------+--------+| 192.168.66.89 |      1 |+---------------+--------+1 row in set (0.00 sec)

mysql> show @@heartbeat;+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| NAME   | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| cobar1 | COBAR | 192.168.66.89 | 8066 |       1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-15 12:59:07 | false || cobar2 | COBAR | 192.168.89.8  | 8066 |      -1 |     0 | idle   |   10000 | 0,0,0        | 2015-02-15 12:59:07 | false || node1  | MYSQL | 192.168.89.4  | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-15 12:59:07 | false || node2  | MYSQL | 192.168.89.4  | 3306 |       1 |     0 | idle   |   30000 | 0,0,0        | 2015-02-15 12:59:07 | false |+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+4 rows in set (0.00 sec)

结论：当主DB异常，cobar会切到备数据源（备DB上），主DB恢复后，不会自动切回主数据源，但是当备数据源异常时会自动切回主数据源。

1 0