Percona XtraDB cluster – 2 machine setup on CentOS 6

The following article show how to setup a 2 machine PerconaDB XtraDB cluster on CentOS 6

Lets assume there are 2 machines

1. 10.10.10.76
2. 10.10.10.77

Installation

The installation procedure is the same for both the machines.Let us add the PerconaDB and EPEL repositories. We are adding the EPEL repository here, as the dependencies of PerconaDB are fetched from here.

rpm -ivh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Let’s install the required packages by running the following Yum command.

yum install Percona-XtraDB-Cluster-server-55 Percona-XtraDB-Cluster-client-55 Percona-XtraDB-Cluster-galera-2

First node configuration

Let us edit the following lines in the file /etc/my.cnf.

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.10.10.76,10.10.10.77

# 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.10.10.76

# SST method
wsrep_sst_method=rsync128.199.127.177

# Cluster name
wsrep_cluster_name=my_centos_cluster

Bootstapping the first node

The initial node needs to bootstrapped. We need to run the following command to do that.

/etc/init.d/mysql bootstrap-pxc

Second node configuration

Let us edit the following lines in the file /etc/my.cnf.

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.10.10.76,10.10.10.77

# 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 #2 address
wsrep_node_address=10.10.10.77

# Cluster name
wsrep_cluster_name=my_centos_cluster

Adding the second node to the cluster

Let’s run the following command to start mysql service

service mysql start

Verifying the setup

We can verify whether both the machines are add to the cluster by the following commands

mysql -uroot -p

mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | e6b8ae47-2bce-11e5-9176-c2eb48accfcd |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 6                                    |
| wsrep_received_bytes       | 382                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 0.000000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 0.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 0.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cert_index_size      | 0                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | 10.10.10.77:3306,10.10.10.76:3306    |
| wsrep_cluster_conf_id      | 2                                    |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | e6b8ae47-2bce-11e5-9176-c2eb48accfcd |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_index          | 1                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy     |
| wsrep_provider_version     | 2.8(r165)                            |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.00 sec)