MySQL使用pxc实现高可用

准备三台主机

三台主机配置主机名

[root@localhost ~]# hostnamectl set-hostname pxc1
[root@localhost ~]# hostnamectl set-hostname pxc2
[root@localhost ~]# hostnamectl set-hostname pxc3

停止mysql服务 

[root@localhost ~]# yum module disable mysql

安装Percona XtraDB Cluster（三台主机均进行此操作）

#添加 Percona 存储库
[root@localhost ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@localhost ~]# percona-release setup pxc-80
#安装pxc
[root@localhost ~]# yum install percona-xtradb-cluster

配置文件

pxc1(192.168.1.51)

[root@localhost ~]# /etc/my.cnf
[mysqld]
server-id=51
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

wsrep_cluster_address=gcomm://192.168.1.51,192.168.1.52,192.168.1.53
wsrep_node_address=192.168.1.51
wsrep_node_name=pxc1

pxc2(192.168.1.52)

[root@localhost ~]# /etc/my.cnf
[mysqld]
server-id=52
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

wsrep_cluster_address=gcomm://192.168.1.51,192.168.1.52,192.168.1.53
wsrep_node_address=192.168.1.52
wsrep_node_name=pxc2

pxc3(192.168.1.53) 

[root@localhost ~]# /etc/my.cnf
[mysqld]
server-id=53
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

wsrep_cluster_address=gcomm://192.168.1.51,192.168.1.52,192.168.1.53
wsrep_node_address=192.168.1.53
wsrep_node_name=pxc3

启动之前确保三台主机用同一套 密钥和证书

[root@localhost ~]# scp /var/lib/mysql/*.pem pxc2:/var/lib/mysql/
[root@localhost ~]# scp /var/lib/mysql/*.pem pxc3:/var/lib/mysql/

 修改权限

pxc2(192.168.1.52)

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

pxc3(192.168.1.53) 

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

启动服务

pxc1(192.168.1.51)

[root@localhost ~]# systemctl start mysql@bootstrap.service

pxc2(192.168.1.52)

[root@localhost ~]# systemctl start mysql.service

pxc3(192.168.1.53)

[root@localhost ~]# systemctl start mysql.service

 进入MySQL服务（修改密码之后）

[root@localhost mysql]# mysql -u root -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.41-32.1 Percona XtraDB Cluster (GPL), Release rel32, Revision 9cd31bf, WSREP version 26.1.4.3

Copyright (c) 2009-2025 Percona LLC and/or its affiliates
Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 查询 Percona XtraDB Cluster (PXC) 集群的视图信息

mysql> select * from performance_schema.pxc_cluster_view;
+-----------+--------------------------------------+--------+-------------+---------+
| HOST_NAME | UUID                                 | STATUS | LOCAL_INDEX | SEGMENT |
+-----------+--------------------------------------+--------+-------------+---------+
| pxc1      | 04e534f3-0333-11f0-b3eb-636663cd336a | SYNCED |           0 |       0 |
| pxc3      | af3108bc-0334-11f0-be46-9bcd0d0e82c0 | SYNCED |           1 |       0 |
| pxc2      | bee7cc62-0334-11f0-a0c9-5f47d1b228fe | SYNCED |           2 |       0 |
+-----------+--------------------------------------+--------+-------------+---------+
3 rows in set (0.01 sec)

 查看 Percona XtraDB Cluster (PXC) 相关状态（可以在任意一个节点执行以下命令）

mysql> show status where Variable_name in ('wsrep_cluster_size','wsrep_cluster_status','wsrep_connected','wsrep_ready') ;
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_size   | 3       |
| wsrep_cluster_status | Primary |
| wsrep_connected      | ON      |
| wsrep_ready          | ON      |
+----------------------+---------+
4 rows in set (0.00 sec)