Install Percona XtraDb Cluster 5.6.20 on CentOS 6.5

http://blog.51cto.com/hj192837/1546149

You should has odd number of real nodes.

Node #1
Hostname:percona1
ip:192.168.70.71

Node #2
Hostname:percona2
ip:192.168.70.72

Node #3
Hostname:percona3
ip:192.168.70.73

1. Disable SELinux and iptables

Service Iptables Stop
Chkconfig iptables off; Chkconfig Ip6tables off

Setenforce 0
Vi/etc/selinux/config
Selinux=disabled

2. Cat << EOF >/etc/yum.repos.d/iso.repo
[ISO]
Name=iso
Baseurl=http://mirrors.sohu.com/centos/6.5/os/x86_64
Enable=1
Gpgcheck=0
Eof

Cat << EOF >/etc/yum.repos.d/epel.repo
[Epel]
Name=epel
Baseurl=http://mirrors.sohu.com/fedora-epel/6server/x86_64
Enable=1
Gpgcheck=0
Eof

(or RPM-IVH http://www.percona.com/redir/downloads/percona-release/percona-release-0.0-1.x86_64.rpm )

Cat << EOF >/etc/yum.repos.d/percona.repo
[Percona]
Name=percona
Baseurl=file:///percona (http://repo.percona.com/centos/6Server/os/x86_64/)
Enable=1
Gpgcheck=0
Eof

3. Yum-y Install percona-xtradb-cluster-56

On Node 1:
Vi/etc/my.cnf
[Mysqld]
Wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.70.71
Wsrep_sst_method=xtrabackup-v2
Wsrep_cluster_name=my_centos_cluster
Wsrep_sst_auth= "Sstuser:s3cret"

expire_logs_days=10
max_binlog_size=100m

# Just for creating a NEW cluster

Service MySQL BOOTSTRAP-PXC

Or
Service MySQL start--wsrep_new_cluster
Or
Service MySQL start--wsrep-cluster-address= "gcomm://"

or for CentOS 7
Systemctl start [email protected]

Mysql-uroot
Mysql> Show status like ' wsrep% ';

mysql> UPDATE mysql.user SET password=password ("Passw0rd") where user= ' root ';
mysql> CREATE USER ' sstuser ' @ ' localhost ' identified by ' S3cret ';
Mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' sstuser ' @ ' localhost ';
mysql> FLUSH privileges;

On Node 2:
Vi/etc/my.cnf
[Mysqld]
Wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.70.72
Wsrep_sst_method=xtrabackup-v2
Wsrep_cluster_name=my_centos_cluster
Wsrep_sst_auth= "Sstuser:s3cret"

expire_logs_days=10
max_binlog_size=100m

Service MySQL Start

On Node 3:
Vi/etc/my.cnf
[Mysqld]
Wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.70.73
Wsrep_sst_method=xtrabackup-v2
Wsrep_cluster_name=my_centos_cluster
Wsrep_sst_auth= "Sstuser:s3cret"

expire_logs_days=10
max_binlog_size=100m

Service MySQL Start

Ports:

galera:4567
sst:4444
SST Incremental port:4568
mysql:3306

notes:http://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/

In the VMware Workstation test environment, it is not possible for MySQL to boot

1. On three nodes:

Chkconfig MySQL off

2. Poweroff sequence:node3 > Node2 > Node1

3. PowerOn sequence:node1 > Node2 > Node3

4. On Node1:service MySQL bootstrap-pxc

On Node2 and Node3:service MySQL start

Haproxy configuration:

Percona node1:192.168.70.71
Percona node2:192.168.70.72
Percona node3:192.168.70.73
Haproxy Server 1:192.168.70.12
Haproxy Server 2:192.168.70.13
Haproxy vip:192.168.70.10

On three Percona nodes:
Yum-y Install xinetd

Vi/etc/services
Mysqlchk 6033/tcp # Mysqlchk

Vi/etc/xinetd.d/mysqlchk
# port = 9200
Port = 6033

Service xinetd Start

If you want to use a different username or password for clustercheck, Vi/usr/bin/clustercheck

On one percona node:
Mysql-uroot-p
mysql> CREATE USER ' haproxy ' @ ' 192.168.70.12 ';
mysql> CREATE USER ' haproxy ' @ ' 192.168.70.13 ';
Mysql> Grant Process on * * to ' clustercheckuser ' @ ' localhost ' identified by ' clustercheckpassword! ';
mysql> flush Privileges;

On the haproxy server:
Yum-y install MySQL

Vi/etc/haproxy/haproxy.cfg
Defaults
# option Httplog
# option Http-server-close
# option Forwardfor except 127.0.0.0/8

Listen Mysqld-status 192.168.70.10:3306
Balance Source
Mode TCP
Option Tcpka
Option Mysql-check User Haproxy
Server MySQL1 192.168.70.71:3306 Check weight 1
Server MySQL2 192.168.70.72:3306 Check weight 1
Server MySQL2 192.168.70.73:3306 Check weight 1

Listen Mysql-cluster 192.168.70.10:3306
Mode TCP
Balance Source
Option Httpchk
Server MySQL1 192.168.70.71:3306 Check Port 6033 Inter 12000 rise 3 Fall 3
Server MySQL1 192.168.70.72:3306 Check Port 6033 Inter 12000 rise 3 Fall 3
Server MySQL1 192.168.70.73:3306 Check Port 6033 Inter 12000 rise 3 Fall 3

Service Haproxy Reload

Testing:

On one Percona server:
Mysql-uroot-p
Mysql> Grant All on * * to [e-mail protected] '% ' identified by ' password ' with GRANT option;

From MySQL client:

Mysql-h 192.168.70.10-uroot-p

To back up a database using Innobackupex:

Mkdir-p/data/backups

Full backup:
1. Innobackupex--user=sstuser--password=s3cret/data/backups

Restore full backup:
1. Innobackupex--apply-log/data/backups/2014-09-08_11-03-56
2. Service MySQL stop; rm-rf/var/lib/mysql/*
3. Innobackupex--copy-back/data/backups/2014-09-08_11-03-56
4. Chown-r Mysql:mysql/var/lib/mysql
5. Service MySQL Start


Incremental Backup:
1. Full backup
Innobackupex--user=sstuser--password=s3cret/data/backups
2. Incremental one, based on full backup
Innobackupex--user=sstuser--password=s3cret--incremental/data/backups--incremental-basedir=/data/backups/ 2014-09-08_11-20-51
3. Incremental, based on incremental one
Innobackupex--user=sstuser--password=s3cret--incremental/data/backups--incremental-basedir=/data/backups/ 2014-09-08_11-28-56

Restore Incremental Backup:
1. Innobackupex--apply-log--redo-only/data/backups/2014-09-08_11-20-51
2. Innobackupex--apply-log--redo-only/data/backups/2014-09-08_11-20-51--incremental-dir=/data/backups/ 2014-09-08_11-28-56
3. Innobackupex--apply-log/data/backups/2014-09-08_11-20-51--incremental-dir=/data/backups/2014-09-08_11-31-53
Notes:--redo-only should is used when merging all incrementals except the last one.
4. Innobackupex--apply-log/data/backups/2014-09-08_11-20-51
5. Service MySQL stop; rm-rf/var/lib/mysql/*
6. Innobackupex--copy-back/data/backups/2014-09-08_11-20-51
7. Chown-r Mysql:mysql/var/lib/mysql
8. Service MySQL Start

Install Percona XtraDb Cluster 5.6.20 on CentOS 6.5