xtradb Percona XtraDB Cluster

Overview

Percona XtraDB is a storage engine for the MariaDB and Percona Server databases.

Data is written to all nodes simultaneously;
Any node can trigger a data update;
Recommended configuration is to have at least 3 nodes, but you can have 2 nodes as well;
No central management. You can loose any node at any point of time, and the cluster will continue to function without any data loss.

Cluster Nodes

Nose

Hostname

Nickname

IP

Node 1

host001

prod.galera-1

10.1.xxx.xxx

Node 2

host002

prod.galera-2

10.1.xxx.xxx

Node 3

host003

prod.galera-3

10.1.xxx.xxx

../_images/percona-xtradb-cluster-diagram.png

Running the Containers

Create the docker network

docker network create pxc-network

Bootstrap the cluster (create the first node)

docker run -d \
-e MYSQL_ROOT_PASSWORD=root \
-e CLUSTER_NAME=cluster1 \
--name=node1 \
--net=pxc-network \
percona/percona-xtradb-cluster:5.7

Create the second node

docker run -d \
-e MYSQL_ROOT_PASSWORD=root \
-e CLUSTER_NAME=cluster1 \
-e CLUSTER_JOIN=node1 \
--name=node2 \
--net=pxc-network \
percona/percona-xtradb-cluster:5.7

Create the third node

docker run -d \
-e MYSQL_ROOT_PASSWORD=root \
-e CLUSTER_NAME=cluster1 \
-e CLUSTER_JOIN=node1 \
--name=node3 \
--net=pxc-network \
percona/percona-xtradb-cluster:5.7

Node containers

docker ps                                                                                                                                                          ⏎
CONTAINER ID        IMAGE                                COMMAND             CREATED             STATUS              PORTS                     NAMES
fd9a4bd9732d        percona/percona-xtradb-cluster:5.7   "/entrypoint.sh "   25 minutes ago      Up 25 minutes       3306/tcp, 4567-4568/tcp   node3
9a08cf9931e1        percona/percona-xtradb-cluster:5.7   "/entrypoint.sh "   26 minutes ago      Up 26 minutes       3306/tcp, 4567-4568/tcp   node2
38533d54b41c        percona/percona-xtradb-cluster:5.7   "/entrypoint.sh "   30 minutes ago      Up 30 minutes       3306/tcp, 4567-4568/tcp   node1

Accessing the first node

sudo docker exec -it node1 /usr/bin/mysql -uroot -proot

View the wsrep status variables

mysql@node1> show status like 'wsrep%';

Configuring the Nodes

Configuration file

Edit /etc/mysql/my.cnf

# Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Logical name for your cluster
wsrep_cluster_name=pxc-cluster
# IP addresses of nodes in your cluster
wsrep_cluster_address=gcomm://192.168.70.61,192.168.70.62,192.168.70.63
# Logical name for each individual node
wsrep_node_name=pxc1
# IP address of this particular node
wsrep_node_address=192.168.70.61
wsrep_sst_method=xtrabackup-v2
# Specify authentication credentials
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
# Supports only row-level replication
binlog_format=ROW
# Supports only the InnoDB storage engine
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

Bootstrapping the First Node

Connecting the first node

sudo docker exec -it node1 /bin/bash
root@38533d54b41c:/#

Starting the first node

root@38533d54b41c:/# /etc/init.d/mysql bootstrap-pxc

Checking status

mysql@pxc1> show status like 'wsrep%';
[...]
| wsrep_cluster_size   | 1
| wsrep_cluster_status | Primary
| wsrep_connected | ON
| wsrep_ready | ON
[...]

Creating a user for SST

mysql@pxc1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
mysql@pxc1> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO
'sstuser'@'localhost';
mysql@pxc1> FLUSH PRIVILEGES;

Adding the Nodes to Cluster

Starting the second node

sudo docker exec -it node2 /bin/bash
root@9a08cf9931e1:/# /etc/init.d/mysql start

Starting the third node

sudo docker exec -it node3 /bin/bash
root@fd9a4bd9732d:/# /etc/init.d/mysql start

Checking the Replication

  1. Create a new database on the second node

mysql> CREATE DATABASE percona;
Query OK, 1 row affected (0.00 sec)
  1. Create a table on the third node

mysql> USE percona;
Database changed
mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.03 sec)
  1. Insert records on the first node

mysql> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.01 sec)
  1. Retrieve rows from that table on the second node

mysql> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
1 row in set (0.00 sec)