mysql高可用集群PXC
目录
1.高可用集群PXC
yum源：https://repo.percona.com/yum/percona-release-latest.noarch.rpm
5.7：https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/LATEST/
8.0：https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/
官网安装文档：https://www.percona.com/doc/percona-xtradb-cluster/5.7/install/yum.html#yum
1.1介绍
Percona XtraDB Cluster，简称PXC。
PXC（Percona XtraDB Cluster）是一个开源的MySQL高可用解决方案。他将Percona Server和XtraBackup与Galera库集成，以实现同步多主复制。基于Galera的高可用方案主要有MariaDB Galera Cluster和Percona XtraDB Cluster，目前PXC架构在生产线上用的更多而且更成熟一些。PXC相比那些传统的基于主从模式的集群架构MHA和双主，Galera Cluster 最突出的特点就是解决了诟病已久的复制延迟问题，基本上可以达到实时同步。而且节点与节点之间，它们互相的关系是对等的。本身Galera Cluster也是一种多主架构。PXC是在存储引擎层实现的同步复制，而非异步复制，所以其数据的一致性是相当高的。
1.2PXC优缺点
优点：
- 实现了MySQL集群的高可用性和数据的强一致性，即便单个节点意外失败，也不会写入；
- 完成了真正的多节点读写的集群方案；
- 改善了主从复制延迟问题，基本上达到了实时同步；
- 真正的并行复制，从属节点上多线程行级别复制
- 新加入的节点可以自动部署，无需提交手动备份，维护方便；
- 由于是多节点写入，所以DB故障切换很容易。
缺点：
- 加入新节点时开销大。添加新节点时，必须从现有节点之一复制完整数据集。如果是100GB，则复制100GB。
- 任何更新的事务都需要全局验证通过，才会在其他节点上执行，集群性能受限于性能最差的节点，也就说常说的木桶定律。
- 因为需要保证数据的一致性，PXC采用的实时基于存储引擎层来实现同步复制，所以在多节点并发写入时，锁冲突问题比较严重。
- 存在写扩大的问题。所以节点上都会发生写操作，对于写负载过大的场景，不推荐使用PXC。
- 只支持InnoDB存储引擎。
2.安装配置示例
2.1前提条件
| node | host | ip |
|---|---|---|
| node1 | pxc1 | 192.168.190.128 |
| node2 | pxc2 | 192.168.190.129 |
| node3 | pxc3 | 192.168.190.130 |
确保以下端口未被防火墙阻止或被其他软件使用。(直接关闭防火墙和selinux)
- 3306 #数据库服务端口
- 4444 #SST端口
- 4567 #集群通信端口
- 4568 #IST端口
SST：State Snapshot Transfer 全量同步
IST：Incremental State Transfer 增量同步
2.2安装数据库
yum源下载，或是下载rpm包，建议yum
#配置yum源(无法在线安装就wget之后再install)
# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
#安装
# yum install Percona-XtraDB-Cluster-57
##安装后包含以下包
Percona-XtraDB-Cluster-57
Percona-XtraDB-Cluster-shared-57
Percona-XtraDB-Cluster-shared-compat-57
Percona-XtraDB-Cluster-client-57
Percona-XtraDB-Cluster-server-57
##也可以直接安装所有包
# yum -y install Percona-XtraDB-Cluster-full-57
# full包，包含下面额外附加组件
Percona-XtraDB-Cluster-devel-57
Percona-XtraDB-Cluster-test-57
Percona-XtraDB-Cluster-debuginfo-57
Percona-XtraDB-Cluster-galera-3-debuginfo
Percona-XtraDB-Cluster-shared-57
启动数据库
# service mysql start
#过滤出root密码
# grep 'temporary password' /var/log/mysqld.log
2021-08-30T06:35:09.580145Z 1 [Note] A temporary password is generated for root@localhost: _?_!Fysu8nGr
#修改密码
mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
添加同步用户sstuser:sstuser（SST用户）
之后将在配置文件的wsrep_sst_auth=中使用这个用户
提前创建，免得回过头来在建，一次搞定
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO
'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;
#在第一个节点确定状态正常后，将改账号能登录的权限放开
#或者一开始就这样配置
mysql> CREATE USER 'sstuser'@'%' IDENTIFIED BY 'passw0rd';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO
'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;
2.3配置集群参数
修改配置文件前停止服务
service mysql stop
配置文件解析
注意：
默认的
/etc/my.cnf配置!includedir /etc/percona-xtradb-cluster.conf.d/**要么直接改my.cnf，要么去/etc/percona-xtradb-cluster.conf.d/下进行更细致的配置
推荐去/etc/percona-xtradb-cluster.conf.d/下的文件配置，也方便别人去维护
而且percona也帮我们分配好了
/etc/percona-xtradb-cluster.conf.d/下包含三个文件
/etc/percona-xtradb-cluster.conf.d/mysqld.cnf默认内容如下（注意server-id,其余默认或者自适配，一般无需更改）
例如一些优化参数尽量在这里配置，
例如innodb_buffer_pool_size=1G等属于[mysql]的，直接配置在这里
尽量按照规范
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=1
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
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
/etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf（保持默认即可，无需更改）
#
# The Percona Server 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
nice = 0
/etc/percona-xtradb-cluster.conf.d/wsrep.cnf（这个才是我们主要配置的文件）
#注意这个库的位置，debian和ubantu与centos位置不同
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
#集群中节点的ip，就是集群成员的ip
wsrep_cluster_address=gcomm://
# In order for Galera to work correctly binlog format should be ROW
#必须是row，因为仅支持行复制
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# Slave thread to use
#最好等于cpu核数
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
#仅支持2，为其它时很可能导致复制时产生死锁
innodb_autoinc_lock_mode=2
# Node IP address
# 本地节点ip，默认注释
#wsrep_node_address=192.168.70.63
# Cluster name
#集群名字
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
#节点名称
wsrep_node_name=pxc-cluster-node-1
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
#严格模式。不启用一些实验性的功能
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
#用于sst同步的用户
#wsrep_sst_auth="sstuser:s3cretPass"
真正需要修改的只有：
wsrep_cluster_address=gcomm:// # 集群成员
wsrep_node_address= # 本节点IP地址
wsrep_cluster_name= # 集群名
wsrep_node_name= # 本节点名
wsrep_sst_auth="sstuser:TEST2021@guodong.com" # SST数据同步授权用户及密码
2.4修改配置文件
只需要在原来的基础上修改wsrep.cnf文件，示例
#node1
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.190.128,192.168.190.129,192.168.190.130
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.190.128
wsrep_cluster_name=pxc-cluster
wsrep_node_name=node-1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:passw0rd"
对于另外两个node，复制后只要更改下面两个：
#node2配置
wsrep_node_name=node-2
wsrep_node_address=192.168.190.129
#node3配置
wsrep_node_name=node-3
wsrep_node_address=192.168.190.130
2.5配置第一个节点
wsrep相关的变量详解https://www.percona.com/doc/percona-xtradb-cluster/5.7/wsrep-status-index.html#wsrep_cluster_conf_id
systemctl start mysql@bootstrap.service
#mysql@bootstrap.service与mysql的启动是分开的，mysql@bootstrap.service可以理解从当前节点初始化
#这样启动时，会以引导wsrep_cluster_address=gcomm://的配置，并设置 wsrep_cluster_conf_id为1，表示集群
show status like 'wsrep%';
示例：
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |
| ... | ... |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| ... | ... |
| wsrep_cluster_size | 1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| ... | ... |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+
mysql> show status like 'wsrep%';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 76e4371e-095c-11ec-91ce-fab5ef34c460 |
| wsrep_protocol_version | 9 |
| wsrep_last_applied | 6 |
| wsrep_last_committed | 6 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 200 |
| wsrep_repl_keys | 1 |
| wsrep_repl_keys_bytes | 32 |
| wsrep_repl_data_bytes | 104 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 145 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.500000 |
| wsrep_local_cached_downto | 6 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 100, 100 ] |
| wsrep_flow_control_interval_low | 100 |
| wsrep_flow_control_interval_high | 100 |
| wsrep_flow_control_status | OFF |
| wsrep_flow_control_active | false |
| wsrep_flow_control_requested | false |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 1 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 1560 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.190.128:3306 |
| wsrep_cluster_weight | 1 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 05644530-0963-11ec-a23f-2ee580b24126 |
| wsrep_gmcast_segment | 0 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 76e4371e-095c-11ec-91ce-fab5ef34c460 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.51(rd992932) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
74 rows in set (0.00 sec)
#确认无误后，将sstuser权限放开
2.6添加其他节点
在配置文件配置好后，启动即可，将会自动加入
也就是其他节点启动即可
service mysql start
或
systemctl start mysql
#查看状态
mysql> show status like '%wsrep%';
+----------------------------------+-------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------+
| wsrep_local_state_uuid | 76e4371e-095c-11ec-91ce-fab5ef34c460 |
| wsrep_protocol_version | 9 |
| wsrep_last_applied | 9 |
| wsrep_last_committed | 9 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 3 |
| wsrep_received_bytes | 226 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 141, 141 ] |
| wsrep_flow_control_interval_low | 141 |
| wsrep_flow_control_interval_high | 141 |
| wsrep_flow_control_status | OFF |
| wsrep_flow_control_active | false |
| wsrep_flow_control_requested | false |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 1456 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.190.128:3306,192.168.190.129:3306 |
| wsrep_cluster_weight | 2 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 8df3d8e7-096d-11ec-9b01-f30934e884bc |
| wsrep_gmcast_segment | 0 |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | 76e4371e-095c-11ec-91ce-fab5ef34c460 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 1 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.51(rd992932) |
| wsrep_ready | ON |
+----------------------------------+-------------------------------------------+
74 rows in set (0.00 sec)
#node3添加之后
.....
| wsrep_incoming_addresses | 192.168.190.128:3306,192.168.190.130:3306,192.168.190.129:3306
注意关注这些值
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |
| ... | ... |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| ... | ... |
| wsrep_cluster_size | 3 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| ... | ... |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+
2.7验证
#node2中
CREATE DATABASE percona;
#node3中
USE percona;
CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
#node1中
INSERT INTO percona.example VALUES (1, 'percona1');
#node2中
SELECT * FROM percona.example;
3.故障恢复
示例：
假如三个节点全部意外宕机，又按照官方文档无法恢复
真的无可奈何了，就只能重建集群了，只要数据文件没删除，恢复的也快
每个节点上执行
mysqld_safe --wsrep-recover
# mysqld_safe --wsrep-recover
2021-08-31T07:13:51.821312Z mysqld_safe Logging to '/var/log/mysqld.log'.
2021-08-31T07:13:51.825094Z mysqld_safe Logging to '/var/log/mysqld.log'.
2021-08-31T07:13:51.883732Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2021-08-31T07:13:51.921691Z mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.OrrlLl' --pid-file='/var/lib/mysql/node2-recover.pid'
2021-08-31T07:13:54.873946Z mysqld_safe WSREP: Recovered position 76e4371e-095c-11ec-91ce-fab5ef34c460:12
2021-08-31T07:13:57.790409Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
#找到Recovered position 76e4371e-095c-11ec-91ce-fab5ef34c460:12 （最后一次提交的序列号）
#对比，寻找最大的一个，假设是node2
修改数据目录中的文件
[root@node2 ~]# vim /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 2df3cc47-0a2b-11ec-8918-17c3ff77c4cf
seqno: -1
safe_to_bootstrap: 0
#将safe_to_bootstrap: 0设为1
#之后重置集群
systemctl start mysql@bootstrap
#其余节点重启即可，自动加入并同步
systemctl start mysql
登录，检查状态
mysql> show status like '%wsrep%';
......
| wsrep_incoming_addresses | 192.168.190.128:3306,192.168.190.129:3306,192.168.190.130:3306 |
| wsrep_cluster_weight | 3 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 2df150c3-0a2b-11ec-920b-e6f271912491 |
| wsrep_gmcast_segment | 0 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 2df3cc47-0a2b-11ec-8918-17c3ff77c4cf |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 1 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.51(rd992932) |
| wsrep_ready | ON |
+----------------------------------+----------------------------------------------------------------+
74 rows in set (0.01 sec)
#三个节点都在，恢复正常
4.搭配使用
可以搭配proxysql对整个pxc集群负载，这也是推荐的
Percona官方提供了一个带有proxysql-admin 自动配置 Percona XtraDB Cluster 节点的工具的 ProxySQL 包
能更省心的配置负载均衡，所以推荐proxysql
监控也可以考虑pmm或是premetheus或zabbix配合percona的模板
有关更多信息，请参阅使用 ProxySQL 进行负载平衡
也可是使用haproxysql：https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/virt_sandbox.html
