mysql复制架构迁移到pxc_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%';

show variables like 'wsrep_sst_method';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27067062/viewspace-2123689/，如需转载，请注明出处，否则将追究法律责任。