一、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环境准备
- 下载相关包
- centos7 4台
192.168.1.4 lv1-4
192.168.1.5 lv1-5
192.168.1.6 lv1-6
192.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.log2020-07-11T08:22:42.090695Z 1 [Note] A temporary password is generated for root@localhost: SV<jtuOee0dA]# mysql -uroot -p'SV<jtuOee0dA'mysql> 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 3306tcp LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=5689,fd=29))[root@pxc1 ~]# ss -ntlup | grep 4567tcp 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 <info@codership.com> || 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
若有收获，就点个赞吧
0 人点赞
