Category: MySQL

Percona XtraDB Cluster

The Cluster consists of Nodes. Recommended configuration is to have at least 3 nodes, but you can make it running with 2 nodes as well. Each Node is regular MySQL / Percona Server setup. The point is that you can convert your existing MySQL / Percona Server into Node and roll Cluster using it as a base. Or otherwise – you can detach Node from Cluster and use it as just a regular server. Each Node contains the full copy of data.

Installation Steps

Debian and Ubuntu packages from Percona are signed with a key. Before using the repository, you should add the key to apt

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Create a dedicated Percona repository file /etc/apt/sources.list.d/percona.list(trusty)

deb http://repo.percona.com/apt trusty main

apt-get update
apt-get install percona-xtradb-cluster-56 percona-xtradb-cluster-galera-3.x

You should see something like this if installation successful:

* Starting MySQL (Percona XtraDB Cluster) database server mysqld     [ OK]

Now, edit my.cnf file with below template:(node1)

[mysqld]
datadir=/var/lib/mysql
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.X.X.1,10.X.X.2,10.X.X.3
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address 
wsrep_node_address=10.X.X.1
# SST method 
wsrep_sst_method=xtrabackup-v2
wsrep_node_name=node3
# Cluster name
wsrep_cluster_name=db_cluster
# Authentication for SST method
wsrep_sst_auth="billinguser:billingpass"
slow_query_log=1
slow_query_log_file=/var/log/mysqld-slow.log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Now you can simply bootstrap (start the first node that will initiate the cluster):

/etc/init.d/mysql bootstrap-pxc
      or
service mysql bootstrap-pxc

Check Status with below commands

SHOW GLOBAL STATUS LIKE 'wsrep_%';
SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';
SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';
SHOW GLOBAL STATUS LIKE 'wsrep_ready';
SHOW GLOBAL STATUS LIKE 'wsrep_connected';
SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';