配置MySQL8.0使用PXC实现集群高可用

一，配置后hosts解析

# cat >> /etc/hosts << EOF
192.168.150.11 pxc1
192.168.150.12 pxc2
192.168.150.13 pxc3
EOF

二，在线安装PXC

        2.1配置 Percona 存储库

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

        2.2.查看现有存储库

         yum repolist

        2.3启用percona cluster存储库 

percona-release enable-only pxc-80 release
percona-release enable tools release

        2.4安装 Percona XtraDB 集群

dnf module disable mysql
yum install percona-xtradb-cluster

三，修改server-id

在/etc/my.cnf或子配置文件下修改

server-id=139----server-id唯一

 四，登录mysql并修改密码

systemctl start mysqld
tmp_pass=$(awk '/temporary password/ {print $NF}' /var/log/mysqld.log)
mysql -uroot -p${tmp_pass}

修改密码

 alter user root@localhost identified by '123456';

五，创建并授权用户 

mysql> CREATE USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'abc_123';
Query OK, 0 rows affected (0.00 sec)

 mysql> GRANT all privileges ON *.* TO 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)

六，创建集群，首先停止服务，在/etc/my.cnf修改配置

######## 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.150.11,192.168.150.12,192.168.150.13 # PXC集群的所有ip

# 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.150.11 # 当前节点的IP
# Cluster name
wsrep_cluster_name=pxc-cluster  # PXC集群的名称

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc1 # 当前节点的名称

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2 # 同步方法

加粗为需要修改的内容，pxc2，pxc3相同 

七，复制密钥和证书：

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

八，启动MySQL服务 

 [root@pxc1 ~]# systemctl start mysql@bootstrap.service--第一个节点引导模式启动

[root@pxc2 ~]# systemctl start mysql ---其余节点正常启动

九，查询集群信息

[root@pxc1 ~]# mysql -uroot -p123456 -e "show status like 'wsrep_cluster%';"