Percona XtraDB Cluster (PXC)

Percona XtraDB Cluster provides a high-performance cluster environment that can help easily configure and manage a database on multiple servers. It enables databases to communicate with each other using the binary logs. The cluster environment helps divide the load among different database servers and provides safety from failure in case a server is down.

To set up the cluster, we need the following servers:

One server with IP 10.211.55.1, which we will call Node1
A second server with IP 10.211.55.2, which we will call Node2
And a third server with IP 10.211.55.3, which we will call Node3

As we already have the Percona repository in our sources, let's start by installing and configuring Percona XtraDB Cluster, also called PXC. Perform the following steps:

First, install Percona XtraDB Cluster on Node1 by issuing the following command in the terminal:
```
apt-get install percona-xtradb-cluster-56
```
The installation will start similarly to a normal Percona Server installation. During the installation, the password for a root user will be also asked.
When the installation is complete, we need to create a new user that has replication privileges. Issue the following commands in the MySQL terminal after logging in to it:
```
CREATE USER 'sstpackt'@'localhost' IDENTIFIED BY 'sstuserpassword';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstpackt'@'localhost';
FLUSH PRIVILEGES;
```
The first query creates a user with the username sstpackt and password sstuserpassword. The username and password can be anything, but a good and strong password is recommended. The second query sets proper privileges to our new user, including locking tables and replication. The third query refreshes the privileges.

Now, open the MySQL configuration file located at /etc/mysql/my.cnf. Then, place the following configuration in the mysqld block:

#Add the galera library
wsrep_provider=/usr/lib/libgalera_smm.so

#Add cluster nodes addresses
wsrep_cluster_address=gcomm://10.211.55.1,10.211.55.2,10.211.55.3

#The binlog format should be ROW. It is required for galera to work properly
binlog_format=ROW

#default storage engine for mysql will be InnoDB
default_storage_engine=InnoDB

#The InnoDB auto increment lock mode should be 2, and it is required for galera
innodb_autoinc_lock_mode=2

#Node 1 address
wsrep_node_address=10.211.55.1

#SST method
wsrep_sst_method=xtrabackup

#Authentication for SST method. Use the same user name and password created in above step 2
wsrep_sst_auth="sstpackt:sstuserpassword"

#Give the cluster a name
wsrep_cluster_name=packt_cluster

Save the file after adding the preceding configuration.

Now, start the first node by issuing the following command:
```
/etc/init.d/mysql bootstrap-pxc
```
This will bootstrap the first node. Bootstrapping means getting the initial cluster up and running and defining which node has the correct information and which one all the other nodes should sync to. As Node1 is our initial cluster node and we created a new user here, we have to only bootstrap Node1.
Note
SST stands for State Snapshot Transfer. It is responsible for copying full data from one node to another. It is only used when a new node is added to the cluster and this node has to get complete initial data from an existing node. Three SST methods are available in Percona XtraDB Cluster, mysqldump, rsync, and xtrabackup.
Log in to the MySQL terminal on the first node and issue the following command:
```
SHOW STATUS LIKE '%wsrep%';
```
A very long list will be displayed. A few of them are shown in the following screenshot:
Now, repeat Step 1 and Step 3 for all nodes. The only configuration that needs to be changed for each node is wsrep_node_address, which should be the IP address of the node. Edit the my.cnf configuration file for all the nodes and place the node address in wsrep_node_address.
Start the two new nodes by issuing the following command in the terminal:
```
/etc/init.d/mysql start
```
Now each node can be verified by repeating step 7.

To verify whether the cluster is working fine, create a database in one node and add some tables and data into the tables. After this, check other nodes for the newly created database, tables, and the data entered in each table. We will have all this data synced to each node.

Previous Chapter

MySQL performance monitoring tools

Next Chapter

Redis – the key-value cache store

Table of Contents for PHP 7: Real World Application Development

Percona XtraDB Cluster (PXC)

Note

Table of Contents for
PHP 7: Real World Application Development