Percona XtraDB cluster setup – Let’s do it!
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:
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
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.]
In short, we’ve discussed the Percona XtraDB cluster setup. Also, we saw how our Support Engineers setup the Percona cluster in Ubuntu.