pxc高可用数据库集群

一、PXC 介绍1.1 PXC 简1.2 PXC特性和优点1.3 PXC的局限和劣势1.4 PXC 常用服务端口二.搭建pxc集群2.1环境准备2.2 安装三 .启动与停止集群停止

一、PXC 介绍

1.1 PXC 简PXC 是一套 MySQL 高可用集群解决方案，与传统的基于主从复制模式的集群架构相比 PXC 最突出特点就是解决了诟病已久的数据复制延迟问题，基本上可以达到实时同步。而且节点与节点之间，他们相互的关系是对等的。PXC 最关注的是数据的一致性，对待事物的行为时，要么在所有节点上执行，要么都不执行，它的实现机制决定了它对待一致性的行为非常严格，这也能非常完美的保证 MySQL 集群的数据一致性；

1.2 PXC特性和优点

完全兼容 MySQL，部署简单没有主从切换操作，无需使用vip同步复制，事务要么在所有节点提交或不提交。多主复制，可以在任意节点进行写操作。多线程复制，在从服务器上并行应用事件，真正意义上的并行复制。节点自动配置，数据强一致性，不再是异步复制，无同步延迟故障切换：因为支持多点写入，所以在出现数据库故障时可以很容易的进行故障切换。自动节点克隆：在新增节点或停机维护时，增量数据或基础数据不需要人工手动备份提供，galera cluster会自动拉取在线节点数据，集群最终会变为一致；

1.3 PXC的局限和劣势

复制只支持InnoDB 引擎，其他存储引擎的更改不复制写入效率取决于节点中最慢的一台

1.4 PXC 常用服务端口

3306：数据库对外服务的端口号。4444：请求SST的端口。（SST ：State Snapshot Tansfer 全量同步）4567：组成员之间进行沟通的一个端口号4568：用于传输IST。（SST ：Incremental State Tansfer 增量同步）

二.搭建pxc集群

2.1环境准备

下载相关包

pxc安装包

centos7 4台192.168.1.4 lv1-4192.168.1.5 lv1-5192.168.1.6 lv1-6192.168.1.7 lv1-7

2.2 安装

解压(以lv1-4为例)

]#tar -xf /root/Percona-XtraDB-Cluster-5.7.30-31.43-r546-el7-x86_64-bundle.tar]# lsPercona-XtraDB-Cluster-57-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-57-debuginfo-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-client-57-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-devel-57-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-full-57-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-garbd-57-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-server-57-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-shared-57-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-shared-compat-57-5.7.30-31.43.1.el7.x86_64.rpmPercona-XtraDB-Cluster-test-57-5.7.30-31.43.1.el7.x86_64.rpm

下载percona-release配置Percona源

]# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm percona-release enable-only tools release

安装pxc

]# yum -y install *.rpm

改配置修改 /etc/my.cnf

[mysqld]server-id=1 #每个节点要不同datadir=/var/lib/mysqluser=mysqlwsrep_provider=/usr/lib64/libgalera_smm.so #指定Galera库的路径wsrep_cluster_name=pxc #Galera集群的名称wsrep_cluster_address=gcomm://192.168.1.4,192.168.1.5,192.168.1.6 #Galera集群中各节点地址。binlog_format=ROW #二进制日志的格式。#Galera只支持row格式的二进制日志default_storage_engine=InnoDB #指定默认存储引擎。Galera的复制功能只支持InnoDBinnodb_autoinc_lock_mode=2 #只能设置为2，设置为0或1时会无法正确处理死锁问题wsrep_node_name=node1 #本节点在Galera集群中的名称wsrep_node_address=192.168.1.4 #本节点在Galera集群中的通信地址wsrep_sst_method=xtrabackup-v2 #state_snapshot_transfer(SST)使用的传输方法，可用方法有mysqldump、rsync和xtrabackup，前两者在传输时都需要对Donor加全局只读锁(FLUSH TABLES WITH READ LOCK)，xtrabackup则不需要(它使用percona自己提供的backup lock)。强烈建议采用xtrabackupwsrep_sst_auth="sstuser:tarena" #在SST传输时需要用到的认证凭据，格式为："用户：密码"

三 .启动与停止

开启第一个节点

]# systemctl start mysql@bootstrap.service

登陆mysql

]# grep password /var/log/mysqld.log 2020-07-11T08:22:42.090695Z 1 [Note] A temporary password is generated for root@localhost: SV alter user root@localhost identified by "123qqq...A";mysql> grant all on *.* to lisi@'%' identified by "123qqq...A";mysql> flush privileges;

启动其他节点

]# systemctl start mysql //其他节点直接启动mysql服务就行

检查端口

[root@pxc1 ~]# ss -ntlup | grep 3306 tcp LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=5689,fd=29))[root@pxc1 ~]# ss -ntlup | grep 4567 tcp LISTEN 0 128 *:4567 *:* users:(("mysqld",pid=5689,fd=11))查看集群状态：

[root@pxc3 ~]# mysql -uroot -p123qqq...A查看集群启动状态mysql> show status like 'wsrep_ready';+---------------+-------+| Variable_name | Value |+---------------+-------+| wsrep_ready | ON |+---------------+-------+1 row in set (0.00 sec)查看集群节点个数mysql> show status like 'wsrep_cluster_size';+--------------------+-------+| Variable_name | Value |+--------------------+-------+| wsrep_cluster_size | 3 |+--------------------+-------+1 row in set (0.01 sec)查看更多wsrep的相关参数mysql> show status like "%wsrep%";+----------------------------------+-------------------------------------------------------+| Variable_name | Value |+----------------------------------+-------------------------------------------------------+| wsrep_local_state_uuid | 410b2dd4-c981-11ea-a5ab-e324b17a3373 || 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 | 4 || wsrep_received_bytes | 614 || 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.250000 || 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 | [ 173, 173 ] || wsrep_flow_control_interval_low | 173 || wsrep_flow_control_interval_high | 173 || wsrep_flow_control_status | OFF || 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 | 1320 || 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.1.52:3306,192.168.1.51:3306,192.168.1.50:3306 || wsrep_cluster_weight | 3 || wsrep_desync_count | 0 || wsrep_evs_delayed | || wsrep_evs_evict_list | || wsrep_evs_repl_latency | 0.00159522/0.00253514/0.00345095/0.000738718/4 || wsrep_evs_state | OPERATIONAL || wsrep_gcomm_uuid | 066eed39-c990-11ea-95f0-e65674cf258e || wsrep_cluster_conf_id | 3 || wsrep_cluster_size | 3 || wsrep_cluster_state_uuid | 410b2dd4-c981-11ea-a5ab-e324b17a3373 || 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 || wsrep_provider_version | 3.43(ra60e019) || wsrep_ready | ON |+----------------------------------+-------------------------------------------------------+71 rows in set (0.01 sec)

集群停止

关闭第一台启动的节点]# systemctl stop mysql@bootstrap.service 其他节点]# systemctl stop mysql若关闭集群式，严格意义是没有先后顺序，再次启动要优先先启动上次关闭时最后关闭的节点]# systemctl start mysql@bootstrap.service ]# systemctl start mysql