基於CentOS 7 安裝Percona XtraDB Cluster(PXC) 5.7

Percona XtraDB Cluster(簡稱PXC)是很多企業基於MySQL實現叢集方案的不二選擇。PXC它支援服務高可用,資料同步複製(並發複製)，幾乎無延遲；多個可同時讀寫節點，可實現寫擴充等等。之前整理過一篇PXC 5.6的文章，本文是基於CentOS 7 PXC 5.7版一個更為標準的安裝，可供大家參考。 一、當前OS環境：

[root@node142 ~]# more /etc/redhat-releaseCentOS Linux release 7.2.1511 (Core)

二、配置運行環境

1、修改主機hosts檔案

[root@node142 ~]# vim /etc/hosts127.0.0.1 localhost.localdomain localhost 192.168.81.142 node142.example.com node142192.168.81.146 node146.example.com node146192.168.81.147 node147.example.com node147

2、修改selinux設定檔

[root@node142 ~]# vim /etc/selinux/configSELINUX=disabled

3、分發設定檔到其餘節點

[root@node142 ~]# scp /etc/hosts 192.168.81.146:/etc/hosts [root@node142 ~]# scp /etc/hosts 192.168.81.147:/etc/hosts [root@node142 ~]# scp /etc/selinux/config  192.168.81.146:/etc/hosts [root@node142 ~]# scp /etc/selinux/config 192.168.81.147:/etc/hosts 

4、分別3節點配置防火牆，如下樣本(僅列出一個節點)

[root@node142 ~]# firewall-cmd --add-port=3306/tcp --permanent[root@node142 ~]# firewall-cmd --add-port=4567/tcp --permanent[root@node142 ~]# firewall-cmd --add-port=4568/tcp --permanent[root@node142 ~]# firewall-cmd --add-port=4444/tcp --permanent[root@node142 ~]# firewall-cmd --reload

5、配置時間同步服務

[root@node248 ~]# crontab -e*/10 * * * * ntpdate ntp3.aliyun.com

6、分別重啟3節點

[root@node142 ~]# reboot [root@node146 ~]# reboot [root@node147 ~]# reboot 

三、安裝Percona XtraDB Cluster 5.7

1、3節點分別安裝Percona XtraDB Cluster 5.7 (如下樣本)

[root@node142 ~]# rpm -Uvh https://www.percona.com/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm [root@node142 ~]# yum install Percona-XtraDB-Cluster-57 -yyum安裝時會提示UDFs功能，根據需要可以在mysql啟動後執行以下語句Percona XtraDB Cluster is distributed with several useful UDFs from Percona Toolkit.Run the following commands to create these functions:mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

四、配置mysql及叢集設定檔

主要包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf
在當前的這個版本中，my.cnf為主設定檔，其餘的設定檔放在/etc/percona-xtradb-cluster.conf.d目錄
在這幾個設定檔中，大家根據自己的需要定製，本示範僅作基本修改
1、備份設定檔

[root@node142 ~]# cp /etc/percona-xtradb-cluster.conf.d/mysqld.cnf{,.org}[root@node142 ~]# cp /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf{,.org}[root@node142 ~]# cp /etc/percona-xtradb-cluster.conf.d/wsrep.cnf{,.org}

2、修改設定檔

[root@node142 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf[root@node142 ~]# more /etc/percona-xtradb-cluster.conf.d/mysqld.cnf# Template my.cnf for PXC# Edit to your requirements.[client]socket=/var/lib/mysql/mysql.sock[mysqld]server-id=142    #這個參數3個節點要使用不同的iddatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidlog-bin=/var/lib/mysqlbinlog_slave_updatesexpire_logs_days=7# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log_bin_trust_function_creators=oncharacter_set_server = utf8  ##Author : Leshamicollation_server = utf8_bin  ##Blog : http://blog.csdn.net/leshami[root@node142 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf[root@node142 ~]# grep -vE "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf[mysqld]wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.81.142,192.168.81.146,192.168.81.147binlog_format=ROWdefault_storage_engine=InnoDBwsrep_slave_threads= 8wsrep_log_conflictsinnodb_autoinc_lock_mode=2wsrep_node_address=192.168.81.142 ##這個參數要改成相應的IPwsrep_cluster_name=pxc-clusterwsrep_node_name=node142               ##這個參數要改成相應的節點名稱pxc_strict_mode=PERMISSIVEwsrep_sst_method=xtrabackup-v2wsrep_sst_auth="sstuser:s3cretPass"

3、修改剩餘節點設定檔

[root@node142 ~]# cp /etc/percona-xtradb-cluster.conf.d/mysqld.cnf{,.146}[root@node142 ~]# cp /etc/percona-xtradb-cluster.conf.d/mysqld.cnf{,.147}[root@node142 ~]# cp /etc/percona-xtradb-cluster.conf.d/wsrep.cnf{,.146}[root@node142 ~]# cp /etc/percona-xtradb-cluster.conf.d/wsrep.cnf{,.147}[root@node142 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf.146  #修改對應的server_id[root@node142 ~]# vim/etc/percona-xtradb-cluster.conf.d/mysqld.cnf.147  #修改對應的server_id以下操作，分別修改wsrep_node_name，wsrep_node_address至相應的節點名稱以及IP地址[root@node142 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf.146 [root@node142 ~]# vim  /etc/percona-xtradb-cluster.conf.d/wsrep.cnf.147

4、比較設定檔，並將設定檔分發到各個節點

[root@node142 ~]# cd /etc/percona-xtradb-cluster.conf.d/[root@node142 percona-xtradb-cluster.conf.d]# diff mysqld.cnf mysqld.cnf.1467c7< server-id=142---> server-id=146[root@node142 percona-xtradb-cluster.conf.d]# diff mysqld.cnf mysqld.cnf.1477c7< server-id=142---> server-id=147[root@node142 percona-xtradb-cluster.conf.d]# diff wsrep.cnf wsrep.cnf.14628c28< wsrep_node_address=192.168.81.142---> wsrep_node_address=192.168.81.14632c32< wsrep_node_name=node142---> wsrep_node_name=node146[root@node142 percona-xtradb-cluster.conf.d]# diff wsrep.cnf wsrep.cnf.14728c28< wsrep_node_address=192.168.81.142---> wsrep_node_address=192.168.81.14732c32< wsrep_node_name=node142---> wsrep_node_name=node147[root@node142 percona-xtradb-cluster.conf.d]# scp mysqld.cnf.146 node146:/etc/percona-xtradb-cluster.conf.d/mysqld.cnf[root@node142 percona-xtradb-cluster.conf.d]# scp wsrep.cnf.146 node146:/etc/percona-xtradb-cluster.conf.d/wsrep.cnf[root@node142 percona-xtradb-cluster.conf.d]# scp mysqld.cnf.147 node147:/etc/percona-xtradb-cluster.conf.d/mysqld.cnf[root@node142 percona-xtradb-cluster.conf.d]# scp wsrep.cnf.147 node147:/etc/percona-xtradb-cluster.conf.d/wsrep.cnf

五、啟動PXC叢集

1、啟動第一個節點

[root@node142 ~]# systemctl start mysql@bootstrap.service[root@node142 ~]# grep "temporary password" /var/log/mysqld.log2017-12-28T08:57:24.231185Z 1 [Note] A temporary password is generated for root@localhost: wj!v<z/2)ctZ[root@node142 ~]# mysql -uroot -pEnter password:mysql> alter user 'root'@'localhost' identified by '123456';mysql> create user 'sstuser'@'localhost' identified by 's3cretpass';mysql> grant reload, lock tables, replication client, process on *.* to 'sstuser'@'localhost';

2、啟動剩餘節點

[root@node146 ~]# systemctl start mysql[root@node147 ~]# systemctl start mysql  注，如果你使用的是CentOS7.2.1511，會發現mysql可以正常啟動，但是未加入叢集的情況需要升級openssl，建議全部升級後再啟動叢集，這問題在CentOS 7.4.1708不存在即openssl版本較新

六、驗證叢集

在146上完成如下操作[root@node146 ~]# mysql -uroot -pmysql> show variables like 'version';+---------------+--------------+| Variable_name | Value |+---------------+--------------+| version | 5.7.19-17-57 |+---------------+--------------+1 row in set (0.00 sec)mysql> create database pxcdb;mysql> use pxcdb;mysql> create table t1(id tinyint,ename varchar(20));mysql> insert into t1 values(1,'Leshami');在147上進行驗證[root@node147 ~]# mysql -uroot -pmysql> show databases;+--------------------+| Database       |+--------------------+| information_schema || mysql || performance_schema || pxcdb || sys |+--------------------+5 rows in set (0.00 sec)mysql> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id    | 147 |+---------------+-------+1 row in set (0.01 sec)mysql> select * from pxcdb.t1;+------+---------+| id | ename |+------+---------+| 1 | Leshami |+------+---------+1 row in set (0.00 sec)--查看叢集狀態mysql> show status like '%wsrep_clust%';+--------------------------+--------------------------------------+| Variable_name | Value |+--------------------------+--------------------------------------+| wsrep_cluster_conf_id | 13 |  ## Author : Leshami| wsrep_cluster_size | 3 |         ## Blog : http://blog.csdn.net/leshami| wsrep_cluster_state_uuid | aeb87793-ebb2-11e7-b33e-eeaf4988bbe4 || wsrep_cluster_status | Primary |   ## Weixin/QQ : 645746311+--------------------------+--------------------------------------+4 rows in set (0.00 sec)mysql> show status like 'wsrep_connected';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| wsrep_connected | ON |+-----------------+-------+1 row in set (0.00 sec)

七、openssl版本過低導致的錯誤

2017-12-28T09:23:19.605353Z 0 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib64/galera3/libgalera_smm.so: symbol SSL_COMP_free_compression_methods, version libssl.so.10 not defined in file libssl.so.10 with link time reference2017-12-28T09:23:19.605379Z 0 [ERROR] WSREP: Failed to load wsrep_provider (/usr/lib64/galera3/libgalera_smm.so). Error: Invalid argument (code: 22). Reverting to no provider.2017-12-28T09:23:19.605386Z 0 [Note] WSREP: Setting wsrep_ready to false[root@node146 ~]# rpm -qa|grep opensslopenssl-1.0.1e-42.el7.9.x86_64openssl-libs-1.0.1e-42.el7.9.x86_64[root@node146 ~]# yum update openssl -y