MySQL分散式資料庫服務中介軟體--Cobar研究三，mysql中介軟體--cobar

MySQL分散式資料庫服務中介軟體--Cobar研究三，mysql中介軟體--cobar

        在實際應用中，經常需要部署一個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恢複後，不會自動切回主要資料源，但是當備資料來源異常時會自動切回主要資料源。