Trying to do the Percona XtraDB cluster setup in Ubuntu? We can help you.

Percona XtraDB cluster setup prevents data loss, downtime and always ensures high availability.

At Bobcares, we often get requests for setting up the Percona XtraDB cluster as part of our Server Management Services.

Today, we’ll see how our Support Engineers set it up for our customers.

Installing Percona XtraBD cluster setup in ubuntu

A cluster comprises nodes and each node contains the same set of data synchronized across nodes.

Recently, one of our customers approached us with the request to set up the Percona cluster for him, as he is getting trouble while trying to set it up in his ubuntu servers. The customer provided three servers to set it up.

Let’s see, how our Support Engineers are going to set it up for our customers.

1. Installing Percona for cluster setup

First, we install Percona XtraDB Cluster from the official repository using apt.

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

dpkg -i percona-release_latest.generic_all.deb

apt-get update

apt-cache search percona

apt-get install percona-xtradb-cluster-57

We followed the same step in all nodes to install  Percona-XtraDB-cluster-57.

2. Configuring 1st node of Percona XtraDB cluster setup

Before configuring we need to stop the MySQL service. We use the command

service mysql stop

We open the file at the location /etc/mysql/my.cnf and paste the following code

[mysqld]
wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_cluster_name=my_ubuntu_cluster
wsrep_cluster_address=gcomm://1st node ip,2nd node ip,3rd node ip

wsrep_node_name=node1
wsrep_node_address=1st node ip
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:password

pxc_strict_mode=ENFORCING

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

3. Configuring 2nd and 3rd node

Configured 2nd and 3rd node in the same way as the 1st node. The only difference is in  wsrep_node_name and wsrep_node_address.

4.Bootstrapping 1st node in a Percona XtraDB cluster setup

Next, we Bootstrap the 1st node using the command below:

/etc/init.d/mysql bootstrap-pxc

This process is done only in the first node.

5. Check MySQL panel in 1st node

In the first node checked MySQL panel and created sstuser.

mysql -u root -p

Then we check the status using the command

show status like 'wsrep%';

Then we start the MySQL service in the 2nd and 3rd nodes. We start the service using the command

/etc/init.d/mysql start

6. Tested replication of the cluster

In order to test the replication of the cluster, a database ‘test’ and table ‘book’ was created in node1 and verified that the same has been created in all other nodes on the cluster.

Error when creating a table in the database

Commonly we face an issue when creating a table after setting up the cluster.  Let us discuss how our Support Engineers resolve the error.

We got an error while creating the table using normal create a query as given below:

” Percona-XtraDB-Cluster prohibits the use of DML command on a table (test.books) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER”

For creating the table, we use the MySQL DB query.

CREATE TABLE books (no INT PRIMARY KEY,name VARCHAR(30));

So, our Support Engineers concluded that the ‘PRIMARY KEY’ should also be added, otherwise, it will give error while using any DML commands.

[Having trouble in setting up the Percona XtraDB cluster? – Our Experts are available 24/7.]

Conclusion

In short, we’ve discussed the Percona XtraDB cluster setup. Also, we saw how our Support Engineers setup the Percona cluster in Ubuntu.