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';

關于MySQL高可用架構的PXC實踐是怎樣的就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。