Mysql高可用管理-MySQL 部署PXC集群

MySQL 部署PXC集群

PXC集群概述

PXC介绍

Percona XtraDB Cluster（简称PXC）

—— 是基于Galera的MySQL高可用集群解决方案

—— Galera Cluster是Codership公司开发的一套免费开源的高可用方案

—— PXC集群主要由两部分组成：Percona Server with XtraDB（数据存储插件）和 Write Set Replication patches（同步、多主复制插件）

—— 官网：http://galeracluster.com

PXC特点

具体如下

—— 数据强一致性，无同步延迟（写入主服务器的数据，所有从服务器必须马上也得有）

—— 没有主从切换操作，无需使用虚拟IP（无需一主多从的结构，无需vip地址）

—— 支持InnoDB存储引擎

—— 多线程复制（多线程同步工作）

—— 部署使用简单

—— 支持节点自动加入，无需手动拷贝数据（服务器会自动同步宕机期间的数据，无需手动配置）
————————————————

相应端口

服务端口

端口

说明

3306

数据库服务端口

4444

SST 端口

4567

集群通信端口

4568

IST 端口

SST

State Snapshot Transfer 全量同步

IST

Incremental State Transfer 增量同步
注：集群通信端口是指集群中各服务器之间通信的端口；数据库服务端口3306和集群通信端口4567是每时每刻都开放的；

SST 端口4444和IST 端口4568只有在做数据同步的时候才会开放

主机角色

三台服务器
主机名

IP地址

角色

pxcnode71

192.168.233.71

数据库服务器

pxcnode72

192.168.233.72

数据库服务器

pxcnode73

192.168.233.73

数据库服务器

配置防火墙与selinux

-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 4444 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 4567 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 4568 -j ACCEPT

关闭selunix

配置主机名及映射关系

PXC集群各主机之间是通过主机名进行通信连接的。

1、修改主机名

主机192.168.233.71：

[root@host71 ~]# echo pxcnode71 > /etc/hostname

[root@host71 ~]# hostname pxcnode71

[root@host71 ~]# exit

主机192.168.233.72：

[root@host72 ~]# echo pxcnode72 > /etc/hostname

[root@host72 ~]# hostname pxcnode72

[root@host72 ~]# exit

主机192.168.233.73：

[root@host73 ~]# echo pxcnode73 > /etc/hostname

[root@host73 ~]# hostname pxcnode73

[root@host73 ~]# exit

2、做主机名与IP地址的映射关系

主机192.168.233.71：

[root@pxcnode71 ~]# vim /etc/hosts

192.168.233.71 pxcnode1

192.168.233.72 pxcnode2

192.168.233.73 pxcnode3

主机192.168.233.72：

[root@pxcnode72 ~]# vim /etc/hosts

192.168.233.71 pxcnode1

192.168.233.72 pxcnode2

192.168.233.73 pxcnode3

主机192.168.233.73：

[root@pxcnode73 ~]# vim /etc/hosts

192.168.233.71 pxcnode1

192.168.233.72 pxcnode2

192.168.233.73 pxcnode3

3、验证映射关系

主机192.168.233.71：

[root@pxcnode71 ~]# ping -c 2 pxcnode2

[root@pxcnode71 ~]# ping -c 2 pxcnode3

//如图所示：能通过主机名ping通，说明主机名与IP地址的映射关系配置cheng'g

主机192.168.233.72：

[root@pxcnode72 ~]# ping -c 2 pxcnode1

[root@pxcnode72 ~]# ping -c 2 pxcnode3

主机192.168.233.73：

[root@pxcnode73 ~]# ping -c 2 pxcnode1

[root@pxcnode73 ~]# ping -c 2 pxcnode2

部署PXC

安装软件

软件介绍

compat-readline5-5.2-17.4.x86_64.rpm
libev4-4.15-7.1.x86_64.rpm
numactl-2.0.9-2.el6.x86_64.rpm
percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm
Percona-XtraDB-Cluster-57-5.7.23-31.31.2.el6.x86_64.rpm
Percona-XtraDB-Cluster-client-57-5.7.23-31.31.2.el6.x86_64.rpm
Percona-XtraDB-Cluster-devel-57-5.7.23-31.31.2.el6.x86_64.rpm
Percona-XtraDB-Cluster-server-57-5.7.23-31.31.2.el6.x86_64.rpm
Percona-XtraDB-Cluster-shared-57-5.7.23-31.31.2.el6.x86_64.rpm
qpress-11-1.el6.x86_64.rpm
socat-1.7.2.4-1.el6.rf.x86_64.rpm

软件作用

percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

在线热备程序

qpress.1.1-14.11.x86_64.rpm

递归压缩程序

Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar

集群服务程序

软件下载

—— percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm 下载地址

https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/

—— qpress.1.1-14.11.x86_64.rpm 下载地址

RPM Search qpress

—— Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar 下载地址

https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/LATEST/

安装软件

—— 三台服务器分别安装

—— 这三个软件包有依赖关系，安装时注意安装顺序

将下载好的软件包上传到Linux操作系统上
主机192.168.233.71：

[root@pxcnode71 ~]# yum -y install libev //安装依赖

[root@pxcnode71 ~]# yum -y install percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

[root@pxcnode71 ~]# wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home%3A/AndreasStieger%3A/branches%3A/Archiving/RedHat_RHEL-6/x86_64/qpress-1.1-14.11.x86_64.rpm

[root@pxcnode71 ~]# rpm -ivh qpress-1.1-14.11.x86_64.rpm //安装依赖

[root@pxcnode71 ~]# tar -xvf Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar

//注：主机71、72、73并没有安装数据库服务软件mysql，数据服务存储的软件是由就集群里面的软件Percona-XtraDB-Cluster-server-57-5.7.25-31.35.1.el7.x86_64.rpm提供的。

[root@pxcnode71 ~]# yum -y install Percona-XtraDB-Cluster-*.rpm

注：主机192.168.233.72 和主机192.168.233.73 都需要安装以上软件，步骤如上所示。

配置服务

标准配置文件配置如下：方案1：

vim /etc/my.cnf

[client]

port=3306

socket = /tmp/mysql.sock

[mysqld]

server-id=1

port=3306

datadir=/data/database/mysql

socket=/tmp/mysql.sock

log-error=/var/run/mysqld/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

user=mysql

# General

back_log=2000

connect_timeout=15

skip_name_resolve=ON

max_connections=5000

table_definition_cache=2000

table_open_cache=10000

metadata_locks_hash_instances=256

ssl=0

core_file

# Innodb

innodb_buffer_pool_size = 10240M

innodb_buffer_pool_instances=8

innodb_log_file_size = 1024M

innodb_log_buffer_size = 16M

innodb_lock_wait_timeout = 20

innodb_autoinc_lock_mode=2

innodb_read_io_threads = 5

innodb_write_io_threads = 5

innodb_thread_concurrency = 8

innodb_doublewrite=1

innodb_flush_log_at_trx_commit = 2

innodb_flush_method = 'O_DIRECT'

innodb-page-cleaners=8

innodb_purge_threads=4

innodb_lru_scan_depth=2048

innodb_io_capacity=8000

innodb_io_capacity_max=16000

innodb_adaptive_hash_index=OFF

innodb-change-buffering=none

innodb_flush_neighbors=0

innodb_max_dirty_pages_pct = 90

innodb_max_dirty_pages_pct_lwm = 10

# Binlog

relay-log=relay-1

binlog_format=ROW

enforce-gtid-consistency

gtid-mode=on

master-info-repository=TABLE

relay-log-info-repository=TABLE

binlog-checksum=NONE

log-bin

log_slave_updates

expire_logs_days=3

sync_binlog=1

# Monitoring

innodb_monitor_enable='%'

performance_schema=ON

performance_schema_instrument='%synch%=on'

# Galera

symbolic-links=0

explicit_defaults_for_timestamp=true

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_address=gcomm://30.0.0.196,30.0.0.198,30.0.0.199

default_storage_engine=InnoDB

wsrep_slave_threads= 20

wsrep_log_conflicts

wsrep_cluster_name=pxc-cluster

wsrep_node_name=pxc3

wsrep_node_address=30.0.0.196

pxc_strict_mode=ENFORCING

wsrep_sst_method=xtrabackup-v2

wsrep_sst_auth="sstuser:s3cret"

差异化配置：

# 需要根据相应的节点配置
server-id=1
wsrep_node_name=pxc3
wsrep_node_address=30.0.0.196

配置数据目录：

mkdir -p /data/database/mysql
chown mysql:mysql -R /data/database/mysql

启动服务

启动初始化方案1：
在1台服务器上执行即可（192.168.233.71）

—— 启动集群服务

—— 添加授权用户

[root@pxcnode71 ~]# systemctl start mysql@bootstrap.service //启动集群服务

[root@pxcnode71 ~]# ls /var/lib/mysql //启动服务后数据初始化结束，数据库目录就有数据了

启动初始化方案2 ：

启动第一个节点

/etc/init.d/mysql bootstrap-pxc

或者这样：
# 初始化
/usr/sbin/mysqld --defaults-file=/etc/my.cnf --initialize --datadir=/data/database/mysql --user=mysql
# 启动
/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --wsrep-new-cluster &

[root@pxcnode71 ~]# grep pass /var/log/mysqld.log //查看数据库初始密码

2022-01-15T07:53:29.086066Z 1 [Note] A temporary password is generated for root@localhost: g3C6sjr-hAw;

[root@pxcnode71 ~]# mysql -uroot -p'g3C6sjr-hAw;' //使用初始密码登录

mysql> alter user root@"localhost" identified by "123456"; //修改登录密码，数据会自动同步到主机72和73上

mysql> grant reload,lock tables,replication client,process on *.* to sstuser@"localhost" identified by "123qqq...A";

//添加授权用户，数据会自动同步到主机72和73上。reload装载数据的权限；lock tables锁表的权限；replication client查看服务状态的权限；process管理服务的权限（查看进程信息）；授权用户和密码必须是配置文件中指定的。

在其他2台服务器上执行

—— 启动数据库服务

—— 会自动同步71主机的授权用户及管理员root密码

[root@pxcnode72 ~]# systemctl start mysql //启动数据库服务

[root@pxcnode73 ~]# systemctl start mysql //启动数据库服务

服务端口

—— 在所有服务器上查看

—— ]# netstat -lntup | grep 3306

—— ]# netstat -lntup | grep 4567

主机192.168.233.71：

[root@pxcnode71 ~]# netstat -lntup | grep 3306

tcp6 0 0 :::3306 :::* LISTEN 8919/mysqld

[root@pxcnode71 ~]# netstat -lntup | grep 4567

tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 8919/mysqld

主机192.168.233.72：

[root@pxcnode72 ~]# netstat -lntup | grep 3306

tcp6 0 0 :::3306 :::* LISTEN 59729/mysqld

[root@pxcnode72 ~]# netstat -lntup | grep 4567

tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 59729/mysqld

主机192.168.233.73：

[root@pxcnode73 ~]# netstat -lntup | grep 3306

tcp6 0 0 :::3306 :::* LISTEN 58840/mysqld

[root@pxcnode73 ~]# netstat -lntup | grep 4567

tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 58840/mysqld

测试配置

查看集群信息（可在任意一台数据库服务器执行）

—— 执行SQL命令

show status like "%wsrep%";

—— 相关参数

[root@pxcnode71 ~]# mysql -uroot -p123456

mysql> show status like "%wsrep%"; //输出所有与集群有关的状态

查看节点数：

show global status like 'wsrep_cluster_size';

查看集群状态：

show global status like 'wsrep%';

查看当前节点状态：

show global status like 'wsrep_cluster_status';

wsrep_incoming_addresses 192.168.233.72:3306,192.168.233.73:3306,192.168.233.71:3306 //成员列表

wsrep_cluster_size 3 //集群服务器台数

wsrep_cluster_status Primary //集群状态

wsrep_connected ON //连接状态

wsrep_ready ON //服务状态

测试集群功能

—— 在任意一台服务器上添加访问数据的授权用户

—— 在客户端使用授权用户连接任意数据库服务器都可以存储数据，且可以查看到同样的数据

—— 建表时，必须有主键字段

1、在任意一台服务器上添加访问数据的授权用户

主机192.168.233.71：

mysql> grant all on db10.* to njw@"%" identified by "123456"; //授权用户会自动添加到主机72和73

验证主机192.168.233.72授权用户是否同步：

mysql> show grants for njw@"%";

验证主机192.168.233.73授权用户是否同步：

mysql> show grants for njw@"%";

2、在客户端使用授权用户连接任意数据库服务器都可以存储数据，且可以查看到同样的数据

客户端192.168.233.50连接主机192.168.233.71插入数据：

[root@client50 ~]# mysql -h192.168.233.71 -unjw -p123456

mysql> create database db10;

mysql> create table db10.a(id int);

mysql> insert into db10.a values(71); //插入数据时报错

ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (db10.a) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER //报错原因：PXC集群建表时必须创建主键字段，但不一定要为自增长

mysql> create table db10.b(id int primary key auto_increment,name char(10)); //创建表b设置id字段为主键且自增长

mysql> insert into db10.b(name) values("bob");

mysql> insert into db10.b(name) values("tom");

mysql> insert into db10.b(name) values("lucy");

mysql> select * from db10.b;

//如图所示，发现id字段自增长的步长为3，因为有三台数据库服务器所以步长为3

mysql> exit

客户端192.168.233.50连接主机192.168.233.72查询数据同步：

[root@client50 ~]# mysql -h192.168.233.72 -unjw -p123456

mysql> insert into db10.b(name) values("jack"); //插入新数据

mysql> select * from db10.b; //可以看见主机71上插入的数据

//由于是在主机72上插入的数据，可以看出步长的变化。

mysql> exit

客户端192.168.233.50连接主机192.168.233.73查询数据同步：

[root@client50 ~]# mysql -h192.168.233.73 -unjw -p123456

mysql> select * from db10.b;

综上所述，可以在任意一台数据库服务器上插入数据，数据会同步到每一台数据库服务器上。

测试集群高可用

测试故障自动恢复

—— 任何一台数据库服务器宕机都不影响用户存取数据

—— 服务器运行后自动同步宕机期间的数据

1、模拟数据库服务器192.168.233.73宕机

[root@pxcnode73~]# netstat -lntup | grep 3306

tcp6 0 0 :::3306 :::* LISTEN 8919/mysqld

[root@pxcnode73 ~]# systemctl stop mysql

[root@pxcnode73 ~]# netstat -lntup | grep 3306 //没有输出结果，表示服务已经关闭

2、客户端50连接192.168.233.73

[root@client50 ~]# mysql -h192.168.233.73 -unjw -p123456 //主机73的服务已经关闭，所以无法连接

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.233.73' (111)

3、客户端连接数据库服务器72查看集群状态

主机192.168.233.50：

[root@client50 ~]# mysql -h192.168.233.72 -unjw -p123456

mysql> show status like "%wsrep%";

//如图所示：集群数量服务器数量变为2台，正在运行只有192.168.233.71和192.168.233.72

4、在数据库服务器72上插入数据

mysql> insert into db10.b(name) values("macka");

mysql> insert into db10.b(name) values("mackb");

mysql> insert into db10.b(name) values("mackc");

mysql> select *from db10.b;

//如图所示，红圈圈起来的数据为服务器73宕机期间新插入的数据，由于服务器73宕机，只剩下两台服务器，步长与服务器的数量有关，所以新插入的数据自增长的步长为2。

5、恢复数据库服务器73，并查看集群状态

[root@pxcnode73 ~]# systemctl start mysql

[root@pxcnode73 ~]# netstat -lntup | grep 3306

tcp6 0 0 :::3306 :::* LISTEN 67004/mysqld

mysql> show status like "%wsrep%";

//如图所示，数据库服务器73也正常运行，集群现在有三台数据库服务器

6、客户端访问数据库服务器73进行验证

[root@client50 ~]# mysql -h192.168.233.73 -unjw -p123456

mysql> select * from db10.b;
//如图所示，数据库服务器73已恢复正常，且数据库服务器73在宕机期间新插入的数据，也已经自动同步。

常见错误

查看日志信息排查错误

]# vim /var/log/mysqld.log

failed to open gcomm backend connection: 110

//如图所示，错误是无法连接

错误思路

—— 查看各主机的主机名与ip地址的映射关系

—— 查看各主机之间 wsrep.cnf 配置文件中集群名称是否一致
————————————————
版权声明：本文为CSDN博主「机佬在线搞机」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/N_jw107/article/details/122515433

端口	说明
3306	数据库服务端口
4444	SST 端口
4567	集群通信端口
4568	IST 端口
SST	State Snapshot Transfer 全量同步
IST	Incremental State Transfer 增量同步

主机名	IP地址	角色
pxcnode71	192.168.233.71	数据库服务器
pxcnode72	192.168.233.72	数据库服务器
pxcnode73	192.168.233.73	数据库服务器