Percona XtraDB Cluster

Examples:

Node Host IP
Node 1 node1.localhost 192.168.184.161
Node 2 node2.localhost 192.168.184.162
Node 3 node3.localhost 192.168.184.163

Prerequisites

Make sure that the following ports are not blocked by firewall or used by other software. Percona XtraDB
Cluster requires them for communication.
• 3306
• 4444
• 4567
• 4568

The SELinux security module can constrain access to data for Percona XtraDB Cluster. The best solution is to
change the mode from enforcing to permissive by running the following command:
setenforce 0

sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config

Installing from Percona Repository

  1. Configure Percona repositories

    sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
    sudo percona-release setup <PRODUCT>

  2. Install the Percona XtraDB Cluster packages

    sudo yum install Percona-XtraDB-Cluster-57

  3. Start the Percona XtraDB Cluster server

    sudo systemctl start mysql

  4. Copy the automatically generated temporary password for the superuser account

    sudo grep 'temporary password' /var/log/mysqld.log

  5. Use this password to log in as root

    mysql -uroot -p

  6. Change the password for the superuser account and log out. For example

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'welcome';

  7. Stop the mysql service

    sudo systemctl stop mysql

CONFIGURING NODES FOR WRITE-SET REPLICATION

If you are running Red Hat or CentOS, modify the following configuration variables to /etc/percona-xtradb-cluster.conf.d/wsrep.cnf on the first node:

[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.184.161,192.168.184.162,192.168.184.163
wsrep_node_name=pxc1
wsrep_node_address=192.168.184.161
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

Use the same configuration for the second and third nodes, except the wsrep_node_name and wsrep_node_address variables:

  • For the second node:

    wsrep_node_name=pxc2
    wsrep_node_address=192.168.184.162

  • For the third node:

    wsrep_node_name=pxc3
    wsrep_node_address=192.168.184.163

Modify the following configuration variables to /etc/percona-xtradb-cluster.conf.d/mysqld.cnf on the all nodes.

[client]
socket=/data/mysql.sock

[mysqld]
user=mysql
server-id=161  # node2: 162, node3: 163
port=3306
datadir=/data
socket=/data/mysql.sock
log-error=/data/mysqld.log
pid-file=/data/mysqld.pid

BOOTSTRAPPING THE FIRST NODE

Start the first node with the following command on RedHat or CentOS:

[root@node1 ~]# systemctl start mysql@bootstrap.service

To make sure that the cluster has been initialized, run the following:

mysql@node1> show status like 'wsrep%';
+----------------------------------+----------------------------------------------------------------+
| Variable_name                    | Value                                                          |
+----------------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid           | b3d61b95-0f21-11ed-a626-e3a8b0be03b3                           |
...
| wsrep_local_state                | 4                                                              |
| wsrep_local_state_comment        | Synced                                                         |
...
| wsrep_cluster_size               | 1                                                              |
| wsrep_cluster_state_uuid         | b3d61b95-0f21-11ed-a626-e3a8b0be03b3                           |
| wsrep_cluster_status             | Primary                                                        |
| wsrep_connected                  | ON                                                             |
...
| wsrep_ready                      | ON                                                             |
+----------------------------------+----------------------------------------------------------------+
75 rows in set (0.01 sec)

Create a user for SST and provide the necessary privileges for that user account:

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

Starting the Second Node

Start the second node by using either of the following commands:

[root@node2 ~]# systemctl start mysql

After the server starts, it should receive SST automatically.
To check the status of the second node, run the following:

mysql@node2> show status like 'wsrep%';
+----------------------------------+----------------------------------------------------------------+
| Variable_name                    | Value                                                          |
+----------------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid           | b3d61b95-0f21-11ed-a626-e3a8b0be03b3                           |
...
| wsrep_local_state                | 4                                                              |
| wsrep_local_state_comment        | Synced                                                         |
...
| wsrep_cluster_size               | 2                                                              |
| wsrep_cluster_state_uuid         | b3d61b95-0f21-11ed-a626-e3a8b0be03b3                           |
| wsrep_cluster_status             | Primary                                                        |
| wsrep_connected                  | ON                                                             |
...
| wsrep_ready                      | ON                                                             |
+----------------------------------+----------------------------------------------------------------+
75 rows in set (0.01 sec)

Starting the Third Node

Start the second node by using either of the following commands:

[root@node3 ~]# systemctl start mysql

After the server starts, it should receive SST automatically.
To check the status of the second node, run the following:

mysql@node3> show status like 'wsrep%';
+----------------------------------+----------------------------------------------------------------+
| Variable_name                    | Value                                                          |
+----------------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid           | b3d61b95-0f21-11ed-a626-e3a8b0be03b3                           |
...
| wsrep_local_state                | 4                                                              |
| wsrep_local_state_comment        | Synced                                                         |
...
| wsrep_cluster_size               | 3                                                              |
| wsrep_cluster_state_uuid         | b3d61b95-0f21-11ed-a626-e3a8b0be03b3                           |
| wsrep_cluster_status             | Primary                                                        |
| wsrep_connected                  | ON                                                             |
...
| wsrep_ready                      | ON                                                             |
+----------------------------------+----------------------------------------------------------------+
75 rows in set (0.01 sec)

VERIFYING REPLICATION

Use the following procedure to verify replication by creating a new database on the second node, creating a table for that database on the third node, and adding some records to the table on the first node.

  1. Create a new database on the second node:

    mysql@node2> CREATE DATABASE percona;
    Query OK, 1 row affected (0.01 sec)

  2. Create a table on the third node:

    mysql@node3> USE percona;
    Database changed
    mysql@node3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
    Query OK, 0 rows affected (0.05 sec)

  3. Insert records on the first node:

    mysql@node1> INSERT INTO percona.example VALUES (1, 'percona1');
    Query OK, 1 row affected (0.02 sec)

  4. Retrieve rows from that table on the second node:

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