Rocky之Mysql-PXC高可用

PXC高可用

1、环境准备

pxc1: centos7 10.0.0.7
pxc2: centos7 10.0.0.17
pxc3: centos7 10.0.0.27
pxc4: centos7 10.0.0.37
注意： 关闭防火墙和SELinux，保证时间同步
      如果已经安装MySQL，必须卸载  至少三个节点，不能安装mysql server 或 mariadb-server

2、安装 Percona XtraDB Cluster 5.7

## 设置yum源
[root@pxe1 ~]# vim /etc/yum.repos.d/pxc.repo
[percona]
name=percona
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled=1
gpgcheck=0

## 将yum源传到其他服务器
[root@pxe1 ~]# scp /etc/yum.repos.d/pxc.repo 10.0.0.17:/etc/yum.repos.d/
[root@pxe1 ~]# scp /etc/yum.repos.d/pxc.repo 10.0.0.27:/etc/yum.repos.d/
[root@pxe1 ~]# scp /etc/yum.repos.d/pxc.repo 10.0.0.37:/etc/yum.repos.d/

## 在所有节点安装PXC
[root@pxe1 ~]# yum install -y Percona-XtraDB-Cluster-57
[root@pxe2 ~]# yum install -y Percona-XtraDB-Cluster-57
[root@pxe3 ~]# yum install -y Percona-XtraDB-Cluster-57
[root@pxe4 ~]# yum install -y Percona-XtraDB-Cluster-57

3、所有节点更改server-id

[root@pxe1 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=7                 #修改server-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
log_slave_updates
expire_logs_days=7

[root@pxe2 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=17              #修改server-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
log_slave_updates
expire_logs_days=7

[root@pxe3 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=27             #修改server-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
log_slave_updates
expire_logs_days=7

[root@pxe4 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=37             #修改server-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
log_slave_updates
expire_logs_days=7

4、修改PXE配置文件

[root@pxe1 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27      #所有节点的IP
wsrep_node_address=10.0.0.7                #指定自已的IP
wsrep_cluster_name=pxc-cluster             #集群名称，要一致
wsrep_node_name=pxc-cluster-node-1         #自己在集群内的名称
wsrep_sst_auth="pxcuser:123456"            #取消本行注释,集群内用户和密码信息必须一致


[root@pxe2 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27     
wsrep_node_address=10.0.0.17               
wsrep_cluster_name=pxc-cluster             
wsrep_node_name=pxc-cluster-node-2         
wsrep_sst_auth="pxcuser:123456"            

[root@pxe3 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27     
wsrep_node_address=10.0.0.27               
wsrep_cluster_name=pxc-cluster             
wsrep_node_name=pxc-cluster-node-3         
wsrep_sst_auth="pxcuser:123456"

5、启动PXC集群中第一个节点（引导节点）

# 这里将pxe2作为第一个节点
[root@pxe2 ~]# systemctl start [email protected]    
[root@pxe2 ~]# ss -ntul     #查看是否开启了默认的4567端口

[root@pxe2 ~]# grep "root@localhost" /var/log/mysqld.log       #查找root登陆密码
2022-09-05T12:18:30.745212Z 1 [Note] A temporary password is generated for root@localhost: sradqeE-q8x+

#登陆mysql修改密码,创建pxcuser并授权
[root@pxe2 ~]# mysql -uroot -p'sradqeE-q8x+'              
mysql> alter user root@'localhost' identified by '123456';
mysql> create user pxcuser@'localhost' identified by '123456';    
mysql> grant reload,lock tables,process,replication client on *.* to pxcuser@'localhost';

mysql> show variables like 'wsrep%'\G       #查看相关状态变量

6、启动PXC集群中其它所有节点

[root@pxe1 ~]# systemctl start mysql
[root@pxe3 ~]# systemctl start mysql

7、查看集群状态，验证集群是否成功

#在任意节点，查看集群状态
[root@pxe1 ~]# mysql -uroot -p123456
mysql> show variables like 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name   | Value              |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-1 |
+-----------------+--------------------+
1 row in set (0.01 sec)

mysql> show variables like 'wsrep_node_address';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| wsrep_node_address | 10.0.0.7 |
+--------------------+----------+
1 row in set (0.01 sec)

mysql> show variables like 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on      | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.04 sec)

mysql> source /root/hellodb_innodb.sql         #导入一个数据库或者创建个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

[root@pxe3 ~]# mysql -p123456      #其他节点验证数据是否同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.04 sec)

8、在PXC集群中加入新节点

#在PXC集群中再加一台新的主机PXC4：10.0.0.37
[root@pxe4 ~]# systemctl start mysql
[root@pxe4 ~]# mysql -p123456
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 4     |
+--------------------+-------+
1 row in set (0.07 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

#所有节点的配置文件加以修改
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27,10.0.0.37

9、在PXC集群中修复故障节点

[root@pxe1 ~]# systemctl stop mysql          #任意一节点停止服务

[root@pxe4 ~]# mysql -p123456                #其他节点查看
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.01 sec)
mysql> use hellodb;
mysql> insert students(name,age)values('wangwang',19);    #插入一条数据
Query OK, 1 row affected (0.02 sec)

[root@pxe2 ~]# mysql -p123456              #其他节点查看正常同步
mysql> use hellodb;
mysql> select * from students;
   26 | wangwang      |  19 | F      |    NULL |      NULL 
   
[root@pxe1 ~]# systemctl start mysql     #恢复服务，数据同步
[root@pxe1 ~]# mysql -p123456
mysql> use hellodb;
mysql> select * from students;
|    26 | wangwang      |  19 | F      |    NULL |      NULL |

10、遇到的问题：

[root@pxe1 ~]# tail -20 /var/log/mysqld.log 
   at gcomm/src/pc.cpp:connect():161
2022-09-05T12:36:17.957177Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():209: Failed to open backend connection: -110 (Connection timed out)
2022-09-05T12:36:18.958125Z 0 [Note] WSREP: gcomm: terminating thread
2022-09-05T12:36:18.958192Z 0 [Note] WSREP: gcomm: joining thread
2022-09-05T12:36:18.958824Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1578: Failed to open channel 'pxc-cluster' at 'gcomm://10.0.0.7,10.0.0.17,10.0.0.27': -110 (Connection timed out)
2022-09-05T12:36:18.959028Z 0 [ERROR] WSREP: gcs connect failed: Connection timed out
2022-09-05T12:36:18.959098Z 0 [ERROR] WSREP: Provider/Node (gcomm://10.0.0.7,10.0.0.17,10.0.0.27) failed to establish connection with cluster (reason: 7)
2022-09-05T12:36:18.959117Z 0 [ERROR] Aborting

2022-09-05T12:36:18.959127Z 0 [Note] Giving 0 client threads a chance to die gracefully
2022-09-05T12:36:18.959143Z 0 [Note] WSREP: Waiting for active wsrep applier to exit
2022-09-05T12:36:18.959150Z 0 [Note] WSREP: Service disconnected.
2022-09-05T12:36:18.959155Z 0 [Note] WSREP: Waiting to close threads......
2022-09-05T12:36:23.960598Z 0 [Note] WSREP: Some threads may fail to exit.
2022-09-05T12:36:23.970990Z 0 [Note] WSREP: Service thread queue flushed.
2022-09-05T12:36:23.971198Z 0 [Note] WSREP: MemPool(SlaveTrxHandle): hit ratio: 0, misses: 0, in use: 0, in pool: 0
2022-09-05T12:36:23.971369Z 0 [Note] WSREP: Shifting CLOSED -> DESTROYED (TO: 0)
2022-09-05T12:36:23.972159Z 0 [Note] Binlog end
2022-09-05T12:36:23.972315Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

解决办法：

防火墙未关闭，systemctl disable --now firewalld

Rocky之Mysql-PXC高可用

PXC高可用

继续阅读

2022秋招面试总结（cpp+java+测开）百度测开一面字节后端一面虾皮后端一面虾皮后端二面

数据库之DDL操作数据库DDL操作数据库DDL操作数据表

数据库之DQL操作数据库

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件