共计 12307 个字符，预计需要花费 31 分钟才能阅读完成。
这篇文章给大家介绍 MySQL 高可用架构的 PXC 实践是怎样的，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
1、PXC 简介
官方介绍：
Percona XtraDB Cluster is High Availability and Scalability solution for MySQL Users.
主要特点：
Synchronous replication. Transaction either committed on all nodes or none.
Multi-master replication. You can write to any node.
Parallel applying events on slave. Real“parallel replication”.
Automatic node provisioning.
Data consistency. No more unsynchronized slaves.
官方手册：
https://www.percona.com/doc/percona-xtradb-cluster/5.6/index.html
2、PXC 架构
此图参考 http://blog.csdn.net/signmem/article/details/17379427
下图来自官方文档：
3、PXC 安装部署
本实验采用 3 节点,centos6.4_x86_64 操作系统，PXC 版本为 5.6，galera-3。
1）下载所需软件包
cmake-2.8.12.2.tar.gz http://www.linuxfromscratch.org/blfs/view/7.5/general/cmake.html
libev-4.22.tar.gz http://dist.schmorp.de/libev/Attic/
Percona-XtraDB-Cluster-5.6.30-76.3.tar.gz https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/LATEST/
DBD-mysql-4.033_02.tar.gz http://www.filewatcher.com/d/FreeBSD/distfiles/Other/DBD-mysql-4.018.tar.gz.133427.html
percona-xtrabackup-2.2.9.tar.gz
socat-2.0.0-b9.tar.gz http://www.dest-unreach.org/socat/
DBI-1.636.tar.gz http://www.cpan.org/modules/by-module/DBI/
Percona-XtraDB-Cluster-5.6.30-25.16-raa929cb-el6-x86_64-bundle.tar
zlib-1.2.3.tar.gz http://www.zlib.net/
2）解压安装包
for i in `ls`; do tar -xzvf $i; tar -xvf $i; done
3）安装依赖包
配置本地 yum 源：
[root@node3 PXC]# mkdir /media/cdrom
[root@node3 PXC]# mount CentOS-6.4-x86_64-bin-DVD1.iso /media/cdrom/ -o loop
[root@node3 PXC]# rm -rf /etc/yum.repos.d/*.repo
[root@node3 PXC]# vi /etc/yum.repos.d/CentOS6.repo
[Base]
name=CentOS6 ISO Base
baseurl=file:///media/cdrom
enabled=1
gpgcheck=0
依赖包检查安装：
yum install -y git scons gcc g++ gcc-c++ openssl check cmake bison libaio libboost-all-dev libasio-dev libaio-dev libncurses5-dev libreadline-dev libpam-dev ncurses-devel
rpm -q git scons gcc g++ gcc-c++ openssl check cmake bison libaio libboost-all-dev libasio-dev libaio-dev libncurses5-dev libreadline-dev libpam-dev ncurses-devel
cmake 安装：
[root@node3 PXC]# cd cmake-2.8.12.2
[root@node3 cmake-2.8.12.2]# ./bootstrap
[root@node3 cmake-2.8.12.2]# make -j 8
[root@node3 cmake-2.8.12.2]# make install
socat 安装：
[root@node3 PXC]# cd socat-2.0.0-b9
[root@node3 socat-2.0.0-b9]# ./configure
[root@node3 socat-2.0.0-b9]# make -j 8
[root@node3 socat-2.0.0-b9]# make install
[root@node3 socat-2.0.0-b9]# ln -s /usr/local/bin/socat /usr/bin/
DBI 安装：
[root@node3 PXC]# cd DBI-1.636
[root@node3 DBI-1.636]# perl Makefile.PL
[root@node3 DBI-1.636]# make -j 8
[root@node3 DBI-1.636]# make install
DBD 安装：
[root@node3 PXC]# cd DBD-mysql-4.033_02
[root@node3 DBD-mysql-4.033_02]# perl Makefile.PL –mysql_config=/usr/local/mysql/bin/mysql_config
[root@node3 DBD-mysql-4.033_02]# make -j 8
[root@node3 DBD-mysql-4.033_02]# make install
percona 工具包安装：
[root@node3 PXC]# for i in `rpm -qa |grep mysql`; do rpm -e $i –nodeps; done
[root@node3 PXC]# cd percona-xtrabackup-2.2.9
[root@node3 percona-xtrabackup-2.2.9]# cmake ./
[root@node3 percona-xtrabackup-2.2.9]# make -j 8
[root@node3 percona-xtrabackup-2.2.9]# make install
[root@node3 percona-xtrabackup-2.2.9]# ln -s /usr/local/xtrabackup/bin/* /usr/bin/
[root@node3 PXC]# rpm -ivh Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm –nodeps
[root@node3 PXC]# rpm -ivh Percona-XtraDB-Cluster-devel-56-5.6.30-25.16.1.el6.x86_64.rpm
[root@node3 PXC]# rpm -ivh Percona-XtraDB-Cluster-galera-3-3.16-1.rhel6.x86_64.rpm –nodeps
4）PXC 数据库安装和配置
Percona-XtraDB-Cluster 安装：
[root@node3 PXC]# tar -xzvf Percona-XtraDB-Cluster-5.6.30-76.3.tar.gz
[root@node3 PXC]# useradd mysql -s /sbin/nologin
[root@node3 PXC]# cd Percona-XtraDB-Cluster-5.6.30-76.3
[root@node3 Percona-XtraDB-Cluster-5.6.30-76.3]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/usr/local/mysql/data/ -DMYSQL_USER=mysql -DENABLE_DOWNLOADS=1 -DWITH_WSREP=1 -DWITH_EDITLINE=0
[root@node3 Percona-XtraDB-Cluster-5.6.30-76.3]# make -j 4
[root@node3 Percona-XtraDB-Cluster-5.6.30-76.3]# make install
初始化数据库：
[root@node3 PXC]# cd /usr/local/mysql
[root@node3 mysql]# ./scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data/
[root@node3 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node3 mysql]# chkconfig –add mysqld
[root@node3 mysql]# chkconfig mysqld on
[root@node3 mysql]# chown -R mysql.mysql /usr/local/mysql
[root@node3 mysql]# vi ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@node3 mysql]# source ~/.bash_profile
配置 my.cnf
node1 节点：
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://IP1,IP2,IP3
wsrep_slave_threads=2
wsrep_cluster_name=my_centos_cluster
#wsrep_sst_method=rsync
#wsrep_sst_method=mysqldump
wsrep_sst_method=xtrabackup
wsrep_node_name=node1
wsrep_node_address=IP1
wsrep_sst_auth= repuser:userpasswd
node2 节点
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://IP1,IP2,IP3
wsrep_slave_threads=2
wsrep_cluster_name=my_centos_cluster
#wsrep_sst_method=rsync
#wsrep_sst_method=mysqldump
wsrep_sst_method=xtrabackup
wsrep_node_name=node2
wsrep_node_address=IP2
wsrep_sst_auth= repuser:userpasswd
node3 节点
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://IP1,IP2,IP3
wsrep_slave_threads=2
wsrep_cluster_name=my_centos_cluster
#wsrep_sst_method=rsync
#wsrep_sst_method=mysqldump
wsrep_sst_method=xtrabackup
wsrep_node_name=node3
wsrep_node_address=IP3
wsrep_sst_auth= repuser:userpasswd
启动 Node1 数据库
[root@node1 mysql]# service mysqld bootstrap-pxc
创建 PXC 复制账户并授权
mysql show status like wsrep%
mysql delete from mysql.user where user =
mysql delete from mysql.user where user = root and host= ::1
mysql delete from mysql.user where user = root and host= node1
mysql delete from mysql.user where user = root and host= 127.0.0.1
mysql CREATE USER repuser @ localhost IDENTIFIED BY userpasswd
mysql GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO repuser @ localhost
mysql FLUSH PRIVILEGES;
启动 node2 节点数据库
(先将 my.cnf 的 wsrep_sst_method 参数值设置为 rsync，完成节点加入后，可以设置回 xtrabackup, 重启数据库)
[root@node2 mysql]# service mysqld start
启动 node3 节点数据库
(先将 my.cnf 的 wsrep_sst_method 参数值设置为 rsync，完成节点加入后，可以设置回 xtrabackup, 重启数据库)
[root@node3 mysql]# service mysqld start
4、安装错误信息以及解决方案
错误信息 01：
[root@node3 PXC]# rpm -ivh Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm
warning: Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
libcrypto.so.10(libcrypto.so.10)(64bit) is needed by Percona-XtraDB-Cluster-shared-56-1:5.6.30-25.16.1.el6.x86_64
libssl.so.10(libssl.so.10)(64bit) is needed by Percona-XtraDB-Cluster-shared-56-1:5.6.30-25.16.1.el6.x86
解决方案：
检查是否安装了对应的 openssl，如果已安装并找到相应的库文件，可以忽略依赖关系。
[root@node3 PXC]# rpm -qa |grep openssl
openssl-1.0.0-27.el6.x86_64
openssl098e-0.9.8e-17.el6.centos.2.x86_64
openssl-devel-1.0.0-27.el6.x86_64
[root@node3 PXC]# ls -l /usr/lib64/libssl.so.10
lrwxrwxrwx. 1 root root 15 Aug 17 17:41 /usr/lib64/libssl.so.10 – libssl.so.1.0.0
[root@node3 PXC]# rpm -ivh Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm –nodeps
错误信息 02：
which: no socat in (/usr/sbin:/sbin:/sbin:/usr/sbin:/bin:/usr/bin:/usr/local/mysql/bin)
WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/sbin:/usr/sbin:/bin:/usr/bin:/usr/local/mysql/bin (20160818 14:45:08.903)
2016-08-18 14:45:08 7131 [ERROR] WSREP: Failed to read ready from: wsrep_sst_xtrabackup –role joiner –address IP2 –datadir /usr/local/mysql/data/ –defaults-file /etc/my.cnf –defaults-group-suffix –parent 7131
Read: (null)
2016-08-18 14:45:08 7131 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup –role joiner –address IP2 –datadir /usr/local/mysql/data/ –defaults-file /etc/my.cnf –defaults-group-suffix –parent 7131 : 2 (No such file or directory)
2016-08-18 14:45:08 7131 [ERROR] WSREP: Failed to prepare for xtrabackup SST. Unrecoverable.
2016-08-18 14:45:08 7131 [ERROR] Aborting
解决方案：
安装 socat 并做软连 ln -s /usr/local/bin/socat /usr/bin/
错误信息 03：
node2 节点
tar: This does not look like a tar archive
tar: Exiting with failure status due to previous errors
WSREP_SST: [ERROR] Error while getting data from donor node: exit codes: 0 2 (20160818 15:32:52.658)
WSREP_SST: [ERROR] Cleanup after exit with status:32 (20160818 15:32:52.660)
2016-08-18 15:32:52 12825 [Warning] WSREP: 0.0 (node1): State transfer to 1.0 (node2) failed: -22 (Invalid argument)
2016-08-18 15:32:52 12825 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():736: Will never receive state. Need to abort
2016-08-18 15:32:52 12825 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup –role joiner –address IP2 –datadir /usr/local/mysql/data/ –defaults-file /etc/my.cnf –defaults-group-suffix –parent 12825 : 32 (Broken pipe)
2016-08-18 15:32:52 12825 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2016-08-18 15:32:52 12825 [ERROR] WSREP: SST script aborted with error 32 (Broken pipe)
2016-08-18 15:32:52 12825 [ERROR] WSREP: SST failed: 32 (Broken pipe)
2016-08-18 15:32:52 12825 [ERROR] Aborting
node1 节点
160818 15:32:52 innobackupex: Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/usr/local/mysql/mysql.sock as repuser (using password: YES).
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 2999
main::mysql_connect(abort_on_error , 1) called at /usr/bin/innobackupex line 1539
innobackupex: Error: Failed to connect to MySQL server as DBD::mysql module is not installed at /usr/bin/innobackupex line 2999.
解决方案：
安装 DBI 和 DBD 软件包，并且需要安装 Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64 和 Percona-XtraDB-Cluster-devel-56-5.6.30-25.16.1.el6.x86_64
错误信息 04：
WSREP_SST: [ERROR] xtrabackup process ended without creating /usr/local/mysql/data//xtrabackup_galera_info (20160818 16:30:33.614)
WSREP_SST: [ERROR] Cleanup after exit with status:32 (20160818 16:30:33.628)
WSREP_SST: [INFO] Removing the sst_in_progress file (20160818 16:30:33.629)
2016-08-18 16:30:33 15314 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup –role joiner –address IP2 –datadir /usr/local/mysql/data/ –defaults-file /etc/my.cnf –defaults-group-suffix –parent 15314 : 32 (Broken pipe)
2016-08-18 16:30:33 15314 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2016-08-18 16:30:33 15314 [ERROR] WSREP: SST script aborted with error 32 (Broken pipe)
2016-08-18 16:30:33 15314 [ERROR] WSREP: SST failed: 32 (Broken pipe)
2016-08-18 16:30:33 15314 [ERROR] Aborting
解决方案：
初始数据同步问题，可以先通过设置 node2，node3 参数 wsrep_sst_method=rsync 完成数据同步，然后再改回 wsrep_sst_method=xtrabackup 即可。
5、附录
1）openssl 源码安装
wget ftp://ftp.openssl.org/source/openssl-1.0.0c.tar.gz
tar -zxf openssl-1.0.0c.tar.gz
cd openssl-1.0.0c/
./config –prefix=/usr/local –openssldir=/usr/local/ssl
make make install
./config shared –prefix=/usr/local –openssldir=/usr/local/ssl
make clean
make make install
2）gcc 升级
需要四个安装包，分别是：boost_1_60_0.tar.gz gcc-4.8.0.tar.gz gmp-5.1.3.tar.gz mpc-1.0.3.tar.gz mpfr-3.1.3.tar.gz
gmp 安装
./configure –prefix=/usr/local/gmp
make
make test
make install
mpfr 安装
./configure –with-gmp-include=/usr/local/gmp/include –with-gmp-lib=/usr/local/gmp/lib –prefix=/usr/local/mpfr
make
make install
mpc 安装
./configure –with-mpfr-include=/usr/local/mpfr/include –with-mpfr-lib=/usr/local/mpfr/lib –with-gmp-include=/usr/local/gmp/include –with-gmp-lib=/usr/local/gmp/lib
make
make install
gcc 安装
./configure –with-gmp-include=/usr/local/gmp/include –with-gmp-lib=/usr/local/gmp/lib –with-mpfr-include=/usr/local/mpfr/include –with-mpfr-lib=/usr/local/mpfr/lib –with-mpc-include=/usr/local/mpc/include –with-mpc-lib=/usr/local/mpc/lib –enable-languages=c,c++ –enable-threads=posix –disable-multilib
3）参考命令
show status like %wsrep%
show variables like wsrep_sst_method
关于 MySQL 高可用架构的 PXC 实践是怎样的就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
