MySQL高可用架構的PXC實踐是怎樣的

208次閱讀

共計 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%