配置mysql8.0使用PXC实现高可用

一、配置hosts解析：

vim /etc/hosts

192.168.1.11 pxc1
192.168.1.12 pxc2
192.168.1.13 pxc3

二、进行安装配置

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

启用pxc 8.0

percona-release setup pxc-80

修改 /etc/my.cnf文件配置

# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock
 
[mysqld]
server-id=11
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800
 
######## wsrep ###############
# Path to Galera library
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
 
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://192.168.1.11,192.168.1.12,192.168.1.13
 
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
 
# Slave thread to use
wsrep_slave_threads=8
 
wsrep_log_conflicts
 
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
 
# Node IP address
wsrep_node_address=192.168.1.11
# Cluster name
wsrep_cluster_name=pxc-cluster
 
#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc11
 
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
 
# SST method
wsrep_sst_method=xtrabackup-v2

做出以下修改

1集群的所有IP地址

wsrep_cluster_address=gcomm://192.168.1.11,192.168.1.12,192.168.1.13

2当前节点的IP地址

wsrep_node_address=192.168.1.11

3当前节点名称

wsrep_node_name=pxc11

4服务id，这里取IP主机号

server-id=11

pxc2，pxc3如上配置

三、启动pxc1

 systemctl start mysql@bootstrap.service

查找临时密码

grep -i password /var/log/mysqld.log

用临时密码登录mysql

mysql -u root -p 

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

四、确保集群使用一套证书

scp /var/lib/mysql/*.pem pxc2:/var/lib/mysql/
scp /var/lib/mysql/*.pem pxc3:/var/lib/mysql/

修改pxc2、pxc3的证书文件权限

[root@pxc2 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@pxc3 ~]# chown -R mysql.mysql /var/lib/mysql/

节点2、3正常启动Mysql（要保证第一个打开的节点应该是最后关闭的）

查看集群信息

 show status where Variable_name in ('wsrep_cluster_size','wsrep_cluster_status','wsrep_connected','wsrep_ready') ;

五、关闭节点

PXC 集群允许动态下线节点，但需要注意的是节点的启动命令和关闭命令必须一致，

第一个节点必须以引导模式来进行关闭：

systemctl stop mysql@bootstrap.service

其余可以用正常方式关闭

systemctl stop mysql