MySQL Distributed Database Service middleware-Cobar research 3, mysql middleware-cobar

MySQL Distributed Database Service middleware-Cobar research 3, mysql middleware-cobar

In practical applications, you often need to deploy a Cobar cluster and then combine the soft load LVS or the Hardware load device F5 to provide external services. We call a Cobar in the cluster a Cobar node, in this article, we will learn about cobar cluster deployment and some common management commands.

1. Configure cobar cluster

You only need to add the cluster configuration in server. xml.

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 Parameter definition, service port, Management port, number of processors, thread pool, etc. --> <! -- <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 = "clusterHeartbe AtUser "> _ HEARTBEAT_USER _ </property> <property name =" clusterHeartbeatPass "> _ HEARTBEAT_PASS _ </property> </system> --> <! -- User Access definition, user name, password, schema, and other information. --> <User name = "test"> <property name = "password"> test </property> <property name = "schemas"> UserCenter </property> </user> <! -- <User name = "root"> <property name = "password"> </property> </user> --> <! -- Cluster list definition: Specifies the host and weight of the cluster node for heartbeat among clusters and client load balancing. --> <! -- Create a Cobar cluster. You only need to configure all Cobar nodes (Note: including the current Cobar itself) in the cluster configuration. --> <! -- Node name. A node indicates a Cobar node. Once a node is configured, the current Cobar periodically initiates a heartbeat request to the node, test the running status of a node --> <cluster> <node name = "cobar1"> <! -- Cobar node IP address, indicating that the current Cobar will send heartbeat to the Cobar deployed on 192.168.66.89 --> <property name = "host"> 192.168.66.89 </property> <! -- Node weight, used for Client Server Load balancer, you can use commands to query the running status and weight of a node --> <property name = "weight"> 1 </property> </node> <! -- Current node --> <node name = "cobar2"> <property name = "host"> 192.168.89.8 </property> <property name = "weight"> 1 </property> </node> </cluster> <! -- The isolation area defines that only one user can log on to a host. --> <! -- <Quarantine> 

You only need to log on to the Service port (8066) of Cobar and run the show cobar_cluster command that comes with Cobar to query the running status and weight of all nodes in the cluster, load Balancing is performed based on the query results.
Log on to any node
[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> 

Note:
1. If you need to configure the Cobar cluster, the current Cobar itself also needs to be configured as a node in the cluster, and the Cobar will not send heartbeat to itself by default;
2. show cobar_cluster only displays normal Cobar nodes in cluster configuration. If a node is abnormal (such as timeout or error), the result will not contain this node.
Cobar provides some management and monitoring commands through port 9066.
Note: No permission control is implemented for port 9066. Normal users and Super Users can log on to port.
You can use show @ help to view all the commands supported by port 9066 and briefly explain them.
[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)
Ii. Test
MySQL downtime Test
Before testing, let's take a look at the current data source and heartbeat information.
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: The NAME of datanode.
# DATASOURCES: The primary-standby datasource corresponding to datanode, which is listed in sequence as the primary, standby, and secondary data in the order listed in the Results ......, cobar numbers 0, 1, and 2 ..... and so on.
# INDEX: indicates the data source that datanode is using. If the INDEX of node1 is 0, it indicates that datanode is using the source-master [0] data source.

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 | 17:46:16 | false |
| Cobar2 | COBAR | 192.168.89.8 | 8066 | 1 | 0 | idle | 10000 |, 0 | 17:46:16 | false |
| Node1 | MYSQL | 192.168.89.4 | 3306 | 1 | 0 | idle | 30000 |, 0 | 17:46:20 | false |
| Node2 | MYSQL | 192.168.89.4 | 3306 | 1 | 0 | idle | 30000 |, 0 | 17:46:20 | false |
+ -------- + ------- + --------------- + ------ + --------- + ------- + -------- + --------- + -------------- + ------------------- + ------- +
4 rows in set (0.00 sec)

We can see that the MySQL data source we use is 89.4, which consists of two cobar nodes (66.89 and 89.8) to form a cobar cluster.
Manually shut down the 89.5 MySQL service and check the heartbeat status. It is found that the cobar has been switched to the backup data source.
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: NAME of the node. For Cobar heartbeat, it is the NAME of the Cobar node. For MySQL heartbeat, it is the NAME of datanode.
# TYPE: Heartbeat TYPE
# HOST | PORT: host and port connected by heartbeat
# RS_CODE: Heartbeat result. 0 indicates the initial state, 1 indicates OK,-1 indicates ERROR, 2 indicates OFF, and-2 indicates TIMEOUT.
# RETRY: Number of retries when a heartbeat error occurs
# STATUS: if the current connection is heartbeat, the status is checking; otherwise, the STATUS is idle.
# TIMEOUT: The heartbeat TIMEOUT time set by the System
# EXECUTE_TIME: Average of the last minute, 10 minutes, and 30 minutes of heartbeat
# LAST_ACTIVE_TIME: last heartbeat End Time
# STOP: whether the heartbeat is stopped
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 |+-------+-----------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
We can see that the data source for node1 is source-backup [0], and the data source for node2 is source-backup [1], indicating that all data sources have been switched to the backup data source.
 
Restore the MySQL master database and view it again. The data source is not switched.
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)
Manually shut down the backup data source 89.5MySQL. Check again and find that the data source has been switched to 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 downtime Test
Check the status of the cobar node before testing. Both nodes are online.
mysql> show cobar_cluster;+---------------+--------+| HOST          | WEIGHT |+---------------+--------+| 192.168.89.8  |      1 || 192.168.66.89 |      1 |+---------------+--------+2 rows in set (0.00 sec)
One of the nodes is down manually. Check again that only 66.89 of nodes are active.
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)
 
Conclusion: when the primary DB is abnormal, The cobar will switch to the standby data source (on the standby DB). After the primary DB is restored, it will not be automatically switched back to the primary data source, however, when the standby data source is abnormal, it is automatically switched back to the primary data source.