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.
官方手冊：
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
libev-4.22.tar.gz
Percona-XtraDB-Cluster-5.6.30-76.3.tar.gz
DBD-mysql-4.033_02.tar.gz
percona-xtrabackup-2.2.9.tar.gz
socat-2.0.0-b9.tar.gz
DBI-1.636.tar.gz
Percona-XtraDB-Cluster-5.6.30-25.16-raa929cb-el6-x86_64-bundle.tar
zlib-1.2.3.tar.gz
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%';
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.
官方手冊：
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
libev-4.22.tar.gz
Percona-XtraDB-Cluster-5.6.30-76.3.tar.gz
DBD-mysql-4.033_02.tar.gz
percona-xtrabackup-2.2.9.tar.gz
socat-2.0.0-b9.tar.gz
DBI-1.636.tar.gz
Percona-XtraDB-Cluster-5.6.30-25.16-raa929cb-el6-x86_64-bundle.tar
zlib-1.2.3.tar.gz
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL高可用架構-MMM、MHA、MGR、PXCMySql架構
- MySQL高可用架構之MHA 原理與實踐MySql架構
- MySQL 實現高可用架構之 MHAMySql架構
- MySQL 高可用架構之 MMM 架構MySql架構
- MySQL高可用架構案例篇：UCloud最佳實踐MySql架構Cloud
- MySQL高可用架構：mysql+keepalived實現MySql架構
- MySQL高可用架構之Keepalived+主從架構部署MySql架構
- Mysql高可用架構方案MySql架構
- MySQL資料庫實現高可用架構之MHA的實戰MySql資料庫架構
- MySQL高可用架構對比MySql架構
- mysql高可用架構MHA搭建MySql架構
- 構建MHA實現MySQL高可用叢集架構MySql架構
- MySQL高可用架構設計分析MySql架構
- 《MySQL效能優化和高可用架構實踐》簡介與推薦序MySql優化架構
- 同程旅行基於 RocketMQ 高可用架構實踐MQ架構
- 深入高可用架構原理與實踐 書籍學習架構
- 高可用架構架構
- MySQL叢集架構：MHA+MySQL-PROXY+LVS實現MySQL叢集架構高可用/高效能MySql架構
- MySQL主從原理, 高可用架構與高效能架構MySql架構
- 大型網際網路高可用架構設計實踐2019架構
- 架構師日記-軟體高可用實踐那些事兒架構
- MySQL資料庫架構——高可用演進MySql資料庫架構
- mysql高可用架構MHA搭建（centos7+mysql5.7.28）MySql架構CentOS
- MySQL高可用方案－PXC(Percona XtraDB Cluster)環境部署詳解MySql
- MHA高可用架構的實現方式架構
- MySQL 高可用架構 - MHA環境部署記錄MySql架構
- MySQL資料庫各場景主從高可用架構實戰MySql資料庫架構
- GitHub 的 MySQL 高可用性實踐分享GithubMySql
- Canal高可用架構部署架構
- 實踐400+私有云打造的雲安全高可用架構詳解架構
- MySQL 中常見的幾種高可用架構部署方案MySql架構
- MySQL 高擴充套件架構構建百萬線上系統實踐MySql套件架構
- kubernetes實踐之十七：架構架構
- AWS 高可用AWS架構方案架構
- 【DB寶45】MySQL高可用之MGR+Consul架構部署MySql架構
- MySQL 高可用架構：主從備份及讀寫分離MySql架構
- 基於MySQL Cluster + LVS + KeepAlived部署負載均衡高可用架構MySql負載架構
- 基於Centos7.x 搭建MySQL Galera Cluster高可用架構CentOSMySql架構
- k8s高可用架構K8S架構