mysql8.0使用PXC实现高可用
一、PXC简介
Percona XtraDB Cluster (简称 PXC)集群是基于 Galera 2.x library，事务型应用下的通用的多主同步复制插件，主要用于解决强一致性问题，使得各个节点之间的数据保持实时同步以及实现多节点同时读写。提高了数据库的可靠性，也可以实现读写分离，是 MySQL 关系型数据库中大家公认的集群优选方案之一。(https://docs.percona.com/percona-xtradb-cluster/8.4/)
二、mysql8.0使用PXC实现高可用
准备三台虚拟机
ip 角色 172.25.254.101 pxc1 172.25.254.102 pxc2 172.25.254.103 pxc3 配置hosts解析
## 三台虚拟机同时做 [root@Rocky ~]# vim /etc/hosts + 172.25.254.101 pxc1 172.25.254.102 pxc2 172.25.254.103 pxc3RHEL 8 和其他 EL8 系统默认启用 MySQL 模块。此模块隐藏了 Percona 提供的软件包，必须禁用该模块才能使这些软件包可见。
[root@Rocky ~]# yum module disable mysql在RHEL8或更高版本上安装
[root@Rocky ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm [root@Rocky ~]# percona-release setup pxc-80 [root@Rocky ~]# yum install percona-xtradb-cluster启动mysql服务，查询临时密码并修改
[root@Rocky ~]# service mysql start [root@Rocky ~]# grep -i password /var/log/mysqld.log 2025-02-27T08:49:38.774269Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: WMg:a_4KrnGb [root@Rocky ~]# mysql -uroot -p'WMg:a_4KrnGb' mysql> alter user root@localhost identified by 'MySQL@123'; mysql> exit ## 如果不想每次登录mysql时输入密码 [root@Rocky ~]# vim /etc/my.cnf [client] user=root password=MySQL@123 [root@Rocky ~]# service mysql stop配置
## 172.25.254.101 [root@Rocky ~]# vim /etc/my.cnf server-id=101 wsrep_cluster_address=gcomm://172.25.254.101,172.25.254.102,172.25.254.103 wsrep_node_address=172.25.254.101 wsrep_node_name=pxc1 ## 172.25.254.102 [root@Rocky ~]# vim /etc/my.cnf server-id=102 wsrep_cluster_address=gcomm://172.25.254.101,172.25.254.102,172.25.254.103 wsrep_node_address=172.25.254.102 wsrep_node_name=pxc2 ## 172.25.254.103 [root@Rocky ~]# vim /etc/my.cnf server-id=103 wsrep_cluster_address=gcomm://172.25.254.101,172.25.254.102,172.25.254.103 wsrep_node_address=172.25.254.103 wsrep_node_name=pxc3 ## 设置流量加密设置。集群的每个节点都必须使用相同的 SSL 证书。 [mysqld] wsrep_provider_options=”socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem” [sst] encrypt=4 ssl-key=server-key.pem ssl-ca=ca.pem ssl-cert=server-cert.pem启动mysql
## 第一个节点启动,以引导模式启动 [root@Rocky ~]# systemctl start mysql@bootstrap.service mysql> show status like 'wsrep%'; ## 此时输出显示集群大小为 1 个节点，它是主组件，节点处于状态Synced，已完全连接并准备好进行写集复制。 ## 预期输出 +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_cluster_size | 1 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+--------------------------------------+ 40 rows in set (0.01 sec) ## 第二个节点启动 [root@Rocky ~]# service mysql start Redirecting to /bin/systemctl start mysql.service Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details. ## 如果第二个节点的mysql启动失败，原因可能是：三个节点必须使用相同的SSL证书 ## 解决方案：删除第二个节点的证书，复制第一个节点的证书 ## 删除第二个节点的证书 [root@Rocky ~]# cd /var/lib/mysql [root@Rocky mysql]# ll *.pem -rw-------. 1 mysql mysql 1680 Feb 27 16:49 ca-key.pem -rw-r--r--. 1 mysql mysql 1120 Feb 27 16:49 ca.pem -rw-r--r--. 1 mysql mysql 1120 Feb 27 16:49 client-cert.pem -rw-------. 1 mysql mysql 1676 Feb 27 16:49 client-key.pem -rw-------. 1 mysql mysql 1680 Feb 27 16:49 private_key.pem -rw-r--r--. 1 mysql mysql 452 Feb 27 16:49 public_key.pem -rw-r--r--. 1 mysql mysql 1120 Feb 27 16:49 server-cert.pem -rw-------. 1 mysql mysql 1680 Feb 27 16:49 server-key.pem [root@Rocky mysql]# rm -fr *.pem ## 复制第一个节点的证书给第二个节点 [root@Rocky ~]# scp /var/lib/mysql/*.pem pxc2:/var/lib/mysql/ ##第三个节点重复第二个节点的相同操作验证
[root@Rocky ~]# mysql mysql> show status like 'wsrep%'; +----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+ ...... | wsrep_incoming_addresses | 172.25.254.103:3306,172.25.254.102:3306,172.25.254.101:3306 | | wsrep_cluster_capabilities | | | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | e4840d46-f4e8-11ef-80d8-83f09fc6dfd0 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | ...... +----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+