New Information schema tables for Galera membership and status

MariaDB Galera server logs all the cluster related information like node status, cluster status, membership, etc. in the error log. MariaDB 10.1.2 introduces a new INFORMATION SCHEMA plugin WSREP_INFO that enables querying these information via INFORMATION SCHEMA tables. The WSREP_INFO plugin adds two new tables to the Information Schema, WSREP_MEMBERSHIP and WSREP_STATUS. The plugin is not enabled by default, so in order to use it, it needs to be installed first :

MariaDB [test]> INSTALL SONAME 'wsrep_status';
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> SHOW PLUGINS;
+-----------------------------+----------+--------------------+-----------------+---------+
| Name                        | Status   | Type               | Library         | License |
+-----------------------------+----------+--------------------+-----------------+---------+
...
| WSREP_MEMBERSHIP            | ACTIVE   | INFORMATION SCHEMA | wsrep_status.so | GPL     |
| WSREP_STATUS                | ACTIVE   | INFORMATION SCHEMA | wsrep_status.so | GPL     |
+-----------------------------+----------+--------------------+-----------------+---------+
52 rows in set (0.01 sec)

MariaDB [test]> SHOW CREATE TABLE INFORMATION_SCHEMA.WSREP_MEMBERSHIP\G
*************************** 1. row ***************************
       Table: WSREP_MEMBERSHIP
Create Table: CREATE TEMPORARY TABLE `WSREP_MEMBERSHIP` (
  `INDEX` int(11) NOT NULL DEFAULT '0',
  `UUID` varchar(36) NOT NULL DEFAULT '',
  `NAME` varchar(32) NOT NULL DEFAULT '',
  `ADDRESS` varchar(256) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [test]> SHOW CREATE TABLE INFORMATION_SCHEMA.WSREP_STATUS\G
*************************** 1. row ***************************
       Table: WSREP_STATUS
Create Table: CREATE TEMPORARY TABLE `WSREP_STATUS` (
  `NODE_INDEX` int(11) NOT NULL DEFAULT '0',
  `NODE_STATUS` varchar(16) NOT NULL DEFAULT '',
  `CLUSTER_STATUS` varchar(16) NOT NULL DEFAULT '',
  `CLUSTER_SIZE` int(11) NOT NULL DEFAULT '0',
  `CLUSTER_STATE_UUID` varchar(36) NOT NULL DEFAULT '',
  `CLUSTER_STATE_SEQNO` bigint(21) NOT NULL DEFAULT '0',
  `CLUSTER_CONF_ID` bigint(21) NOT NULL DEFAULT '0',
  `GAP` varchar(10) NOT NULL DEFAULT '',
  `PROTOCOL_VERSION` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [test]> SELECT @@wsrep_provider;
+----------------------------------+
| @@wsrep_provider                 |
+----------------------------------+
| /usr/lib/galera/libgalera_smm.so |
+----------------------------------+
1 row in set (0.00 sec)

Now that WSREP_INFO plugin is installed, lets look into the contents of these tables on a 3-node cluster.

MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.WSREP_MEMBERSHIP;
+-------+--------------------------------------+----------+-----------------+
| INDEX | UUID                                 | NAME     | ADDRESS         |
+-------+--------------------------------------+----------+-----------------+
|     0 | 19058073-8940-11e4-8570-16af7bf8fced | my_node1 | 10.0.2.15:16001 |
|     1 | 19f2b0e0-8942-11e4-9cb8-b39e8ee0b5dd | my_node3 | 10.0.2.15:16003 |
|     2 | d85e62db-8941-11e4-b1ef-4bc9980e476d | my_node2 | 10.0.2.15:16002 |
+-------+--------------------------------------+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.WSREP_STATUS\G
*************************** 1. row ***************************
         NODE_INDEX: 0
        NODE_STATUS: Synced
     CLUSTER_STATUS: Primary
       CLUSTER_SIZE: 3
 CLUSTER_STATE_UUID: 190604d7-8940-11e4-a084-ebee5211c190
CLUSTER_STATE_SEQNO: 2
    CLUSTER_CONF_ID: 3
                GAP: NO
   PROTOCOL_VERSION: 3
1 row in set (0.00 sec)

As seen above, WSREP_MEMBERSHIP table shows information about current members in the cluster which includes node’s name and incoming address. WSREP_STATUS table, on the other hand, shows status information about the node and cluster as a whole.

SHOW command can also be used to query these tables. Its quick and reduces the number of columns for WSREP_STATUS to fit to the screen.

MariaDB [test]> SHOW WSREP_MEMBERSHIP;
+-------+--------------------------------------+----------+-----------------+
| Index | Uuid                                 | Name     | Address         |
+-------+--------------------------------------+----------+-----------------+
|     0 | 19058073-8940-11e4-8570-16af7bf8fced | my_node1 | 10.0.2.15:16001 |
|     1 | 19f2b0e0-8942-11e4-9cb8-b39e8ee0b5dd | my_node3 | 10.0.2.15:16003 |
|     2 | d85e62db-8941-11e4-b1ef-4bc9980e476d | my_node2 | 10.0.2.15:16002 |
+-------+--------------------------------------+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [test]> SHOW WSREP_STATUS;
+------------+-------------+----------------+--------------+
| Node_Index | Node_Status | Cluster_Status | Cluster_Size |
+------------+-------------+----------------+--------------+
|          0 | Synced      | Primary        |            3 |
+------------+-------------+----------------+--------------+
1 row in set (0.00 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *