基于haproxy +keepalived 的 percona xtradb cluster 安装配置

最新推荐文章于 2020-11-06 15:56:39 发布

wjl7813

最新推荐文章于 2020-11-06 15:56:39 发布

阅读量2.3k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/wjl7813/article/details/79238608

版权

本文详细介绍了如何在节点上配置Percona XtraDB Cluster，并结合Haproxy和Keepalived实现高可用性。涉及的步骤包括clustercheck检查、Haproxy和Keepalived的安装、配置，以及通过设置wsrep_desync进行主节点故障模拟和监控状态异常的处理。

摘要由CSDN通过智能技术生成

Percona XtraDB cluster 安装与运维维护

安装准备工作

[root@pxc3 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.137.151 pxc1
192.168.137.152 pxc2
192.168.137.153 pxc3

systemctl disable firewalld.service

[root@pxc3 ~]# cat /etc/sysconfig/selinux | grep disabled
# disabled - No SELinux policy is loaded.
SELINUX=disabled

3个节点都要做

安装配置
yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum install -y Percona-XtraDB-Cluster-57.x86_64

service mysql restart
grep 'temporary password' /var/log/mysqld.log
ALTER USER 'root'@'localhost' IDENTIFIED BY 'oracle';
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'oracle';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;

service mysql stop
节点2/3 同样执行上面的操作

vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.137.151,192.168.137.152,192.168.137.153
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads=20
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.137.151
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:oracle

第 2 个 Node,除了 wsrep_node_name， wsrep_node_address 外，其它配置保持一样
wsrep_node_name=pxc2 wsrep_node_address=10.6.1.149
第 3 个 Node,除了 wsrep_node_name， wsrep_node_address 外，其它配置保持一样
wsrep_node_name=pxc3
wsrep_node_address=10.6.1.150

systemctl start mysql@bootstrap.service 该命令只能在第一个PXC 节点启动,其他节点正常启动(service mysql stop)即可

mysql@pxc2> CREATE DATABASE percona;
Query OK, 1 row affected (0.01 sec)
Create a table on the third node:
mysql@pxc3> USE percona;
Database changed
mysql@pxc3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.05 sec)
Insert records on the first node:
mysql@pxc1> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.02 sec)
Retrieve rows from that table on the second node:
mysql@pxc2> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
| 1 | percona1 |
+---------+-----------+
1 row in set (0.00 sec)

[root@pxc1 ~]# clustercheck
HTTP/1.1 503 Service Unavailable
Content-Type: text/plain
Connection: close
Content-Length: 57

Percona XtraDB Cluster Node is not synced or non-PRIM.

vim /usr/bin/clustercheck

GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
flush privileges ;

每个节点都要执行

[root@pxc1 ~]# clustercheck

HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40

=====Haproxy配置=====
echo 'mysqlchk 9200/tcp # MySQL check' >> /etc/services
yum -y install xinetd

以上操作在 pxc1/pxc2 操作即可

vi /etc/xinetd.d/mysqlchk --看看就行不需要修改
yum install psmisc -y

keeplived 基于haproxy 切换

xinetd -d -f /etc/xinetd.conf -filelog /var/log/xinetd.log -cc 1

[root@pxc1 ~]# netstat -ntlp | grep 9200
tcp6 0 0 :::9200 :::* LISTEN 29722/xinetd

以上操作在 pxc1/pxc2/pxc3操作即可

yum install -y haproxy pxc1/pxc2操作即可

cd /etc/haproxy/
cp -rp haproxy.cfg haproxy.cfg.bak
> haproxy.cfg

vim /etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
use