Percona XtraDB Cluster

Release 5.7.25-31.35

Introduction

  • 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

Component

Galera write set replication (wsrep) API

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 docker containers

Create 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

Access node 1

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

View the wsrep status variables

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

Configuring 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

Connect node 1

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

Start the first node

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

Check status

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

Create 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 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

Verifying 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)