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 mysql@bootstrap.service

[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

巴特西

Mysql之PXC高可用

PXC高可用

1、环境准备

2、安装 Percona XtraDB Cluster 5.7

3、所有节点更改server-id

4、修改PXE配置文件

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

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

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

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

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

10、遇到的问题：

最新文章

热门文章

巴特西

Mysql之PXC高可用

PXC高可用

1、环境准备

2、 安装 Percona XtraDB Cluster 5.7

3、所有节点更改server-id

4、修改PXE配置文件

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

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

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

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

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

10、遇到的问题：

最新文章

热门文章

2、安装 Percona XtraDB Cluster 5.7

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