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