MySQL HA - Percona XtraDB Cluster Installation steps

All would like to keep their databases server for High Availabilty with out any downtime and data loss.Percona XtraDB Cluster is one of the method to keep your DB server HA.Here I have given the steps to install and configure Percona XtraDB cluster.

Basic Requirements:

Three servers for three nodes.

Node 1:

1.Create a new linux server.

2.Download Percona xtradb cluster “tar.gz” file from this link.

http://www.percona.com/downloads/Percona-XtraDB-Cluster/

I have downloaded 5.5.34-25.

3.Install as like mysql(there is no changes while installing Percona Xtradb).

Mysql Installation link, http://knowmysql.blogspot.in/2012/12/20-easy-steps-to-install-mysql-in-linux.html

4.Start as like Mysql

5.Give root privilges.

6.Stop the mysql server.

7.Copy my.cnf file for safety.

8.Add the below lines in my.cnf file.

basedir=

datadir=

server-id = 1

log-bin=mysql-bin

log_slave_updates

binlog_format=row

wsrep_cluster_address=gcomm://

162.213.38.46

,162.213.38.32,199.180.197.233 -- give all the 3 nodes server ip.

wsrep_provider=/usr/local/cluster1/lib/libgalera_smm.so

-- check this path correctly

wsrep_slave_threads=2

wsrep_node_address =162.213.38.46

(Current node server IP)

wsrep_cluster_name=clustertest1

-- give any name

wsrep_sst_method=rsync

wsrep_node_name=Clusternode1

-- give any name

innodb_autoinc_lock_mode=2

9.Start the server by following.

#./bin/mysqld_safe –defaults-file=/etc/my.cnf –basedir=/... --datadir=/.... --user=mysql --wsrep-cluster-address="gcomm://" &

or

service mysql start

10. Check the mysql error log. There it should be like this below,

[Note] WSREP: 0 (Clusternode1): State transfer to 2 (Clusternode1) complete.

[Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 7694)

[Note] WSREP: Member 0 (Clusternode1) synced with group.

[Note] WSREP: Shifting JOINED -> SYNCED (TO: 7694)

[Note] WSREP: Synchronized with group, ready for connections

10.Then login to mysql and check the status of these variables.

Mysql -u root -p

> show status like 'wsrep_%';

wsrep_cluster_size 1 -- no. of nodes connected to this node

wsrep_local_state_comment Synced

wsrep_connected ON --- node connection status

wsrep_incoming_addresses 162.213.38.46:3306 -- current node IP.

wsrep_ready ON

Node 2:

Do the same steps from 1 to 7 for this new node.

Then do the following,

1.Add the following in my.cnf.

basedir=

datadir=

server-id = 2

log-bin=mysql-bin

log_slave_updates

binlog_format=row

wsrep_cluster_address=gcomm://162.213.38.46 -- give node 1 server ip.

wsrep_provider=/usr/local/cluster2/lib/libgalera_smm.so

-- check this path correctly

wsrep_slave_threads=2

wsrep_cluster_name=clustertest1

-- give node 1 cluster name here

wsrep_sst_method=rsync

wsrep_node_name=Clusternode1

-- give node 1 name here.

innodb_autoinc_lock_mode=2

2.Start the server by following.

#/etc/init.d/mysql start

3.login to server.

#mysql -u root -p

4.Check the following status,

mysql> show status like 'wsrep_%';

wsrep_cluster_size 2 -- no. Of nodes connected to this node

wsrep_local_state_comment Synced

wsrep_connected ON --- node connection status

wsrep_incoming_addresses 162.213.38.46:3306,162.213.38.42:3306 -- current node IP and node 1 ip should be come here.

wsrep_ready ON

Node 3:

Do the same steps from 1 to 7 for this new node.

Then do the following,

1.Add the following in my.cnf.

basedir=

datadir=

server-id = 23

log-bin=mysql-bin

log_slave_updates

binlog_format=row

wsrep_cluster_address=gcomm://162.213.38.46 -- give node 1 server ip.

wsrep_provider=/usr/local/cluster2/lib/libgalera_smm.so

-- check this path correctly

wsrep_slave_threads=2

wsrep_cluster_name=clustertest1

-- give node 1 cluster name here

wsrep_sst_method=rsync

wsrep_node_name=Clusternode1

-- give node 1 name here.

innodb_autoinc_lock_mode=2

2.Start the server by following.

#/etc/init.d/mysql start

3.login to server.

#mysql -u root -p

4.Check the following status,

mysql> show status like 'wsrep_%';

wsrep_cluster_size 3 -- no. Of nodes connected to this node

wsrep_local_state_comment Synced

wsrep_connected ON --- node connection status

wsrep_incoming_addresses 162.213.38.46:3306,162.213.38.42:3306,199.180.197.233:3306 -- current node IP, node 1 and node 2 ips should be come here.

wsrep_ready ON

Then finally test the node by creating a database from any one of the node and check it is in all nodes or not.