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_MEMBERSHIPG
*************************** 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_STATUSG
*************************** 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_STATUSG
*************************** 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)