Based on CentOS 7 installation Percona xtradb Cluster (PXC) 5.7

Percona xtradb Cluster (PXC) is the choice of many enterprises based on MySQL to implement the cluster scheme. PXC it supports high availability of services, data synchronization replication (concurrent replication), almost no latency, multiple simultaneous read and write nodes, write extensions, and so on. Before finishing a article Pxc 5.6, this article is based on the CentOS 7 PXC 5.7 version of a more standard installation, for everyone to reference. first, the current OS environment:

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

Second, configure the operating environment

1. Modify host Hosts File

[root@node142 ~]# vim/etc/hosts
127.0.0.1 localhost.localdomain localhost 
node142.example.com node142
192.168.81.146 node146.example.com node146
192.168.81.147 node147

2, modify the SELinux configuration file

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

3, distribution configuration file to the remaining nodes

[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 

4, 3 nodes to configure the firewall, the following example (only one node listed)

[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. Configure Time Synchronization Service

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

6. Restart 3 nodes separately

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

third, the installation Percona xtradb Cluster 5.7

1, 3 nodes are installed Percona xtradb Cluster 5.7 (example below)

[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-y

installation prompts Yum features , you can execute the following statement after MySQL startup
Percona xtradb Cluster is distributed with several useful UDFs from Percona.
Run the following commands to create these functions:
mysql-e "Create FUNCTION fnv1a_64 RETURNS INTEGER soname" LIBFN V1a_udf.so ' "
mysql-e" create function fnv_64 RETURNS INTEGER soname ' libfnv_udf.so ' "
mysql-e" Create function mu Rmur_hash RETURNS INTEGER soname ' libmurmur_udf.so '

iv. Configure MySQL and cluster configuration files

mainly including mysqld.cnf,mysqld_safe.cnf,wsrep.cnf
In this current release, MY.CNF is the primary profile and the rest of the configuration files are placed in the/ETC/PERCONA-XTRADB-CLUSTER.CONF.D directory
In these several profiles, you customize according to your needs, this demo only make basic changes
1, Backup configuration file

[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, modify the configuration file

[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个节点要使用不同的id datadir=/var/lib/mysql socket=/ Var/lib/mysql/mysql.sock Log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid log-bin=/var/lib/ Mysqlbin log_slave_updates expire_logs_days=7 # Disabling symbolic-links is recommended to prevent assorted security risk s symbolic-links=0 log_bin_trust_function_creators=on character_set_server = UTF8 # #Author: Leshami collation_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.so wsrep_cluster_address=gcomm://192.168.81.142,192.168.81.146,192.168.81.147 Binlog_Format=row Default_storage_engine=innodb wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 Wsrep _node_address=192.168.81.142 # #这个参数要改成相应的IP Wsrep_cluster_name=pxc-cluster wsrep_node_name=node142 # #这个参数要改成 The corresponding node name pxc_strict_mode=permissive wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth= "Sstuser:s3cretpass"

3, modify the remaining node configuration file

[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

The following actions, modify Wsrep_node_name,wsrep_node_ separately Address to the appropriate node name and IP addresses
[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, compare the configuration file, and distribute the configuration file to each node

[root@node142 ~]# cd/etc/percona-xtradb-cluster.conf.d/[root@node142 percona-xtradb-cluster.conf.d]# diff MYSQLD.CNF mysqld.cnf.146 7C7 < server-id=142---> server-id=146 [root@node142 percona-xtradb-cluster.conf.d]# Diff mysqld.cnf mysqld.cnf.147 7c7 < server-id=142---> server-id=147 [root@node142 percona-xtradb-cluster.conf.d
]# diff wsrep.cnf wsrep.cnf.146 28c28 < wsrep_node_address=192.168.81.142---> wsrep_node_address=192.168.81.146 32C32 < wsrep_node_name=node142---> wsrep_node_name=node146 [root@node142 percona-xtradb-cluster.conf.d]# diff WSREP.CNF wsrep.cnf.147 28C28 < wsrep_node_address=192.168.81.142---> wsrep_node_address=192.168.81.147 32c32 & Lt 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

v. Start-up of PXC Cluster

1. Start the first node

[root@node142 ~]# systemctl start Mysql@bootstrap.service
[root@node142 ~]# grep "Temporary password"/var/log/ Mysqld.log
2017-12-28t08:57:24.231185z 1 [note] A temporary password are generated for ROOT@LOCALHOST:WJ!V<Z/2) CtZ

[root@node142 ~]# mysql-uroot-p
Enter 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, start the remaining node

[root@node146 ~]# systemctl start MySQL
[root@node147 ~]# systemctl start MySQL  
note, If you are using CentOS7.2.1511, you will find that MySQL can start normally, but do not join the cluster
needs to upgrade OpenSSL, recommended all upgrades and then start the cluster, the problem in CentOS 7.4.1708 does not exist that OpenSSL version newer

vi. validation of clusters

Do the following on 146 [root@node146 ~]# mysql-uroot-p mysql> 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 ');
Verify on 147 [root@node147 ~]# mysql-uroot-p mysql> 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 | +------+---------+ 1Row in Set (0.00 sec)--View cluster status 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) Mys
Ql> Show status like ' wsrep_connected '; +-----------------+-------+
| variable_name |
Value | +-----------------+-------+
| wsrep_connected |
On | +-----------------+-------+ 1 row in Set (0.00 sec)

Vii. errors caused by OpenSSL version too low

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 reference
201 7-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 OpenSSL
o penssl-1.0.1e-42.el7.9.x86_64
openssl-libs-1.0.1e-42.el7.9.x86_64
[root@node146 ~]# yum Update openssl-y