导航：首页 > 数据库 >

MySQL高可用架构的PXC实践是怎样的

发表于：2024-12-01 作者：千家信息网编辑

千家信息网最后更新 2024年12月01日，这篇文章给大家介绍MySQL高可用架构的PXC实践是怎样的，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。1、PXC简介官方介绍：Percona XtraDB Cluster is

千家信息网最后更新 2024年12月01日MySQL高可用架构的PXC实践是怎样的

这篇文章给大家介绍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%';