基于CentOS 7 安装Percona XtraDB Cluster(PXC) 5.7

逃离我推掉我的手 2022-06-02 04:30 213阅读 0赞

Percona XtraDB Cluster(简称PXC)是很多企业基于MySQL实现集群方案的不二选择。PXC它支持服务高可用,数据同步复制(并发复制)，几乎无延迟；多个可同时读写节点，可实现写扩展等等。之前整理过一篇PXC 5.6的文章，本文是基于CentOS 7 PXC 5.7版一个更为标准的安装，可供大家参考。

## 一、当前OS环境： ##

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

## 二、配置运行环境 ##

1、修改主机hosts文件

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

2、修改selinux配置文件

[root@node142 ~]# vim /etc/selinux/config
    SELINUX=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 -y
    
    yum安装时会提示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个节点要使用不同的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 risks
    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               ##这个参数要改成相应的节点名称
    pxc_strict_mode=PERMISSIVE
    wsrep_sst_method=xtrabackup-v2
    wsrep_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.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
    < 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.log
    2017-12-28T08:57:24.231185Z 1 [Note] A temporary password is 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、启动剩余节点

[root@node146 ~]# systemctl start mysql
    [root@node147 ~]# systemctl start mysql 
    注，如果你使用的是CentOS7.2.1511，会发现mysql可以正常启动，但是未加入集群的情况
    需要升级openssl，建议全部升级后再启动集群，这问题在CentOS 7.4.1708不存在即openssl版本较新

## 六、验证集群 ##

## 七、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 reference
    2017-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
    openssl-1.0.1e-42.el7.9.x86_64
    openssl-libs-1.0.1e-42.el7.9.x86_64
    [root@node146 ~]# yum update openssl -y

[![DBA牛鹏社(SQL/NOSQL/LINUX)][DBA_SQL_NOSQL_LINUX]][DBA_SQL_NOSQL_LINUX_DBA_SQL_NOSQL_LINUX]

[DBA_SQL_NOSQL_LINUX]: http://pub.idqqimg.com/wpa/images/group.png
[DBA_SQL_NOSQL_LINUX_DBA_SQL_NOSQL_LINUX]: /images/20220602/80ea5614c5ad4081944d5f7b3c426173.png