【MySQL Database】通过Percona XtraDB Cluster实现数据库高可用集群

[root@wallet01 ~]# wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
[root@wallet01 ~]# rpm -ivh epel-release-latest-6.noarch.rpm
[root@wallet01 ~]# yum install -y socat

[root@wallet01 ~]# wget https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-6.noarch.rpm
[root@wallet01 ~]# rpm -ivh percona-release-0.1-6.noarch.rpm
[root@wallet01 ~]# yum install Percona-XtraDB-Cluster-56
[root@wallet01 ~]# rpm -qa Percona-*
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
Percona-XtraDB-Cluster-server-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-galera-3-3.30-1.el6.x86_64
Percona-XtraDB-Cluster-client-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-shared-56-5.6.42-28.30.1.el6.x86_64

[root@wallet01 ~]# vi /etc/my.cnf 
[mysqld]
bind-address=0.0.0.0
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_provider_options="gcache.size=1024M; gcache.page_size=512M"
wsrep_cluster_name = "Percona XtraDB Cluster"
wsrep_cluster_address = "gcomm://192.168.1.201,192.168.1.202,192.168.1.203"
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sst:sst
wsrep_node_name = wallet01
wsrep_node_address="192.168.1.201"

[root@wallet01 ~]# /etc/init.d/mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster).Logging to '/var/lib/mysql/wallet01.err'.
. SUCCESS! 

[root@wallet01 ~]# service mysql status
 SUCCESS! MySQL (Percona XtraDB Cluster) running (9474)

[root@wallet01 ~]# netstat -tunlp | grep mysqld
tcp        0      0 0.0.0.0:4567    0.0.0.0:*    LISTEN      9474/mysqld         
tcp        0      0 0.0.0.0:3306    0.0.0.0:*    LISTEN      9474/mysqld

[root@wallet01 ~]# mysql -uroot -pabcd.1234
mysql> create user sst@'localhost' identified by 'sst';
Query OK, 0 rows affected (0.10 sec)

mysql> grant reload,lock tables,replication client,process on *.* to  sst@'localhost';
Query OK, 0 rows affected (0.06 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.07 sec)

[root@wallet02 ~]# wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
[root@wallet02 ~]# rpm -ivh epel-release-latest-6.noarch.rpm
[root@wallet02 ~]# yum install -y socat

[root@wallet02 ~]# wget https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-6.noarch.rpm
[root@wallet02 ~]# rpm -ivh percona-release-0.1-6.noarch.rpm
[root@wallet02 ~]# yum install Percona-XtraDB-Cluster-56
[root@wallet02 ~]# rpm -qa Percona-*
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
Percona-XtraDB-Cluster-server-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-galera-3-3.30-1.el6.x86_64
Percona-XtraDB-Cluster-client-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-shared-56-5.6.42-28.30.1.el6.x86_64

[root@wallet02 ~]# vi /etc/my.cnf 
[mysqld]
bind-address=0.0.0.0
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_provider_options="gcache.size=1024M; gcache.page_size=512M"
wsrep_cluster_name = "Percona XtraDB Cluster"
wsrep_cluster_address = "gcomm://192.168.1.201,192.168.1.202,192.168.1.203"
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sst:sst
wsrep_node_name = wallet02
wsrep_node_address="192.168.1.202"

[root@wallet02 ~]# /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster).Logging to '/var/lib/mysql/wallet02.err'.
..State transfer in progress, setting sleep higher
.... SUCCESS! 

[root@wallet02 ~]# service mysql status
 SUCCESS! MySQL (Percona XtraDB Cluster) running (18115)
 
[root@wallet02 ~]# netstat -tunlp | grep mysqld
tcp        0      0 0.0.0.0:4567    0.0.0.0:*    LISTEN      18115/mysqld        
tcp        0      0 0.0.0.0:3306    0.0.0.0:*    LISTEN      18115/mysqld

[root@wallet03 ~]# wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
[root@wallet03 ~]# rpm -ivh epel-release-latest-6.noarch.rpm
[root@wallet03 ~]# yum install -y socat

[root@wallet03 ~]# wget https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-6.noarch.rpm
[root@wallet03 ~]# rpm -ivh percona-release-0.1-6.noarch.rpm
[root@wallet03 ~]# yum install Percona-XtraDB-Cluster-56
[root@wallet03 ~]# rpm -qa Percona-*
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
Percona-XtraDB-Cluster-server-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-galera-3-3.30-1.el6.x86_64
Percona-XtraDB-Cluster-client-56-5.6.42-28.30.1.el6.x86_64
Percona-XtraDB-Cluster-shared-56-5.6.42-28.30.1.el6.x86_64

[root@wallet03 ~]# vi /etc/my.cnf 
[mysqld]
bind-address=0.0.0.0
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_provider_options="gcache.size=1024M; gcache.page_size=512M"
wsrep_cluster_name = "Percona XtraDB Cluster"
wsrep_cluster_address = "gcomm://192.168.1.201,192.168.1.202,192.168.1.203"
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sst:sst
wsrep_node_name = wallet03
wsrep_node_address="192.168.1.203"

[root@wallet01 ~]# su - mysql
[mysql@wallet01 ~]$ ssh-keygen
[mysql@wallet01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@wallet03
[mysql@wallet01 ~]$ ssh wallet03 date
Thu Feb 21 14:15:03 CST 2019

[mysql@wallet01 ~]$ xtrabackup --backup --user=xtrabackup --password=xtrabackup --galera-info \
--stream=tar | ssh mysql@wallet03 \ "gzip >/home/mysql/backup/`date '+%Y-%m-%d_%H-%M-%S'`.tar.gz"

[root@wallet03 ~]# su - mysql
[mysql@wallet03 ~]$ cd backup/
[mysql@wallet03 backup]$ tar izxvf 2019-02-21_13-02-21.tar.gz
[mysql@wallet03 ~]$ xtrabackup --prepare --target-dir=/home/mysql/backup
[mysql@wallet03 ~]$ xtrabackup --copy-back --target-dir=/home/mysql/backup

[mysql@wallet03 ~]$ cd /var/lib/mysql
[mysql@wallet03 mysql]$ cat xtrabackup_galera_info
68a4447c-3588-11e9-b342-5f2a0eb60718:87910

[mysql@wallet03 mysql]$ vim grastate.dat
# GALERA saved state
version: 2.1
uuid:    68a4447c-3588-11e9-b342-5f2a0eb60718
seqno:   87910
safe_to_bootstrap: 0

[root@wallet03 ~]#  /etc/init.d/mysql start   
Starting MySQL (Percona XtraDB Cluster).Logging to '/var/lib/mysql/wallet03.err'.
..State transfer in progress, setting sleep higher
. SUCCESS! 

[root@wallet03 ~]# service mysql status
 SUCCESS! MySQL (Percona XtraDB Cluster) running (24097)

[root@wallet03 ~]# netstat -tunlp | grep mysqld
tcp        0      0 0.0.0.0:4567    0.0.0.0:*    LISTEN      24097/mysqld        
tcp        0      0 0.0.0.0:3306    0.0.0.0:*    LISTEN      24097/mysqld