引导第一个节点
我们已经在 CentOS 7 上配置了两个 PXC 节点。
现在是启动或者初始化 Percona XtraDB 集群的第一个节点的时候了。
Percona XtraDB Cluster 的第一个节点是包含要复制到其他节点的数据的节点。
使用以下命令引导 percona-01.onitroad.com 节点。
（注意：请确保所有节点上的 mysql.service 都已停止。
）
[root@percona-01 ~]# systemctl start mysql@bootstrap.service
连接 Percona 数据库实例并检查配置。
[root@percona-01 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show status like 'wsrep%'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | wsrep_local_state_uuid | 83b4278c-da37-11e9-82ae-1e52dc50d51c | | wsrep_protocol_version | 9 | | wsrep_last_applied | 2 | | wsrep_last_committed | 2 | | 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 | 2 | | wsrep_received_bytes | 149 | | 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 | 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 | [ 100, 100 ] | | wsrep_flow_control_interval_low | 100 | | wsrep_flow_control_interval_high | 100 | | 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.204: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 | f4826ade-dba8-11e9-a126-13c9944ecef9 | | wsrep_cluster_conf_id | 1 | | wsrep_cluster_size | 1 | | wsrep_cluster_state_uuid | 83b4278c-da37-11e9-82ae-1e52dc50d51c | | 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.39(rb3295e6) | | wsrep_ready | ON | +----------------------------------+--------------------------------------+ 71 rows in set (0.00 sec)
在将任何节点添加到我们的集群之前，我们必须为 SST（快照状态传输）创建一个用户，以便新节点与集群完全同步。
mysql> CREATE USER 'sstuser'@'%' IDENTIFIED BY 'Ahm3r'; Query OK, 0 rows affected (0.06 sec) mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> EXIT Bye
Percona XtraDB Cluster (PXC) 是用于 MySQL 数据库服务器的免费开源高可用性解决方案。
它将 Percona Server 和 Percona XtraBackup 与 Galera 库集成在一起，以实现同步多主复制。
PXC 由 Percona Experts 支持，并在 Attribution-ShareAlike 2.0 Generic (CC BY-SA 2.0) 许可下分发。
在 Percona XtraDB Cluster 中，可以有两个或者多个节点。
每个节点都具有在集群中同步的相同数据。
每个节点都是一个实际的 MySQL 数据库服务器（Percona、MariaDB、MySQL 等），我们可以轻松地将其添加到 Percona XtraDB 集群中。
在本文中，我们将在 CentOS 7 上安装 Percona XtraDB Cluster。
在 CentOS 7 上安装 Percona Yum 软件库
下载并安装 Percona yum 存储库的 rpm 包，如下所示。
[root@percona-01 ~]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
为所有 yum 存储库构建缓存。
[root@percona-01 ~]# yum makecache fast
我们已经安装了 Percona yum 存储库。
现在，我们可以使用 yum 命令轻松安装 Percona XtraDB Cluster。
[root@percona-01 ~]# yum install -y Percona-XtraDB-Cluster-57
启用并启动 Percona 数据库服务。
[root@percona-01 ~]# systemctl enable --now mysql.service
Percona 安装程序在 /var/log/mysqld.log 文件中为 root 用户生成一个临时密码。
我们可以使用 grep 命令获取此密码。
[root@percona-01 ~]# grep 'temporary password' /var/log/mysqld.log 2019-09-18T17:12:24.404976Z 1 [Note] A temporary password is generated for root@localhost: EAFR7kje,_HR
使用此临时密码登录 Percona 实例。
[root@percona-01 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.27-30-57-log Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
为 root 用户设置新密码。
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye
停止 Percona 数据库服务。
[root@percona-01 ~]# systemctl stop mysql.service
在 percona-02.onitroad.com 上重复上述步骤。
安装环境
这里我们将安装只有两个节点的集群。所以需要两台服务器：
节点 1
- CPU - 3.4 GHz（2 核）
- 内存 - 1 GB
- 存储 - 60 GB
- 主机名 - percona-01.onitroad.com
- IP 地址 - 192.168.1.204 /24
- 操作系统 - CentOS 7.6
节点 2
- CPU - 3.4 GHz（2 核）
- 内存 - 1 GB
- 存储 - 60 GB
- 主机名 - percona-02.onitroad.com
- IP 地址 - 192.168.1.205 /24
- 操作系统 - CentOS 7.6
在 CentOS 7 防火墙中允许 Percona XtraDB 集群服务端口
Percona XtraDB Cluster 需要以下服务端口进行通信，因此我们在 CentOS 7 防火墙中允许这些服务端口。
[root@percona-01 ~]# firewall-cmd --permanent --add-port={3306,4444,4567,4568}/tcp
success
[root@percona-01 ~]# firewall-cmd --reload
success
Percona XtraDB Cluster 与 SELinux 不完全兼容，PXC 官方文档中建议在安装前将 SELinux 置于 permissive 模式。
[root@percona-01 ~]# setenforce 0 [root@percona-01 ~]# sed -i 's/^SELINUX=.*/SELINUX=permissive/g' /etc/selinux/config && cat /etc/selinux/config # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=permissive # SELINUXTYPE= can take one of three values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted
在 CentOS 7 上向 Percona XtraDB 集群添加节点
以 root 用户身份使用 ssh 连接到 percona-02.onitroad.com。
使用 systemctl 命令启动 Percona 服务。
[root@percona-02 ~]# systemctl start mysql.service
如果我们的配置正确，那么 percona-02 节点应该会自动接收 SST。
[root@percona-02 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show status like 'wsrep%'; +----------------------------------+-------------------------------------------+ | Variable_name | Value | +----------------------------------+-------------------------------------------+ | wsrep_local_state_uuid | 83b4278c-da37-11e9-82ae-1e52dc50d51c | | wsrep_protocol_version | 9 | | wsrep_last_applied | 5 | | wsrep_last_committed | 5 | | 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 | 234 | | 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_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.1.205:3306,192.168.1.204: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 | b979106d-dbaa-11e9-b19a-ce0cf7b20e5d | | wsrep_cluster_conf_id | 2 | | wsrep_cluster_size | 2 | | wsrep_cluster_state_uuid | 83b4278c-da37-11e9-82ae-1e52dc50d51c | | 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.39(rb3295e6) | | wsrep_ready | ON | +----------------------------------+-------------------------------------------+ 71 rows in set (0.07 sec)
我们可以看到 wsrep_cluster_size 现在是 2 ，这表明 percona-02 节点已加入我们的 Percona XtraDB Cluster。
在我们的 Percona XtraDB 集群中验证复制
我们可以通过操作一个节点上的数据来验证复制，并检查它是否复制到另一个节点上。
以 root 用户身份使用 ssh 连接 percona-02.onitroad.com。
连接到 Percona 数据库实例并执行以下命令。
mysql> CREATE DATABASE RECIPES; Query OK, 1 row affected (0.06 sec) mysql> USE RECIPES; Database changed mysql> CREATE TABLE TAB1 (CONTACT_ID INT PRIMARY KEY, CONTACT_NAME VARCHAR(20)); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO TAB1 VALUES (1,'JackLi'); Query OK, 1 row affected (0.04 sec) mysql> mysql> INSERT INTO TAB1 VALUES (2,'Mansoor'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO TAB1 VALUES (3,'Salman'); Query OK, 1 row affected (0.00 sec)
以 root 用户身份使用 ssh 连接 percona-01.onitroad.com。
连接 Percona 数据库实例并查询我们在 percona-02 节点上插入的数据。
[root@percona-01 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> USE RECIPES; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM TAB1; +------------+--------------+ | CONTACT_ID | CONTACT_NAME | +------------+--------------+ | 1 | JackLi | | 2 | Mansoor | | 3 | Salman | +------------+--------------+ 3 rows in set (0.00 sec)
它表明，我们的 Percona XtraDB 集群运行良好。
我们已经在 CentOS 7 上成功安装并配置了两个节点的 Percona XtraDB Cluster。
https://onitroad.com 更多教程
为写集复制配置 PXC 节点
在 percona-01.onitroad.com 上配置 Percona XtraDB Cluster 设置。
[root@percona-01 ~]# vi /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
其中查找并设置以下指令。
wsrep_cluster_address=gcomm://192.168.1.204,192.168.1.205 wsrep_node_address=192.168.1.204 wsrep_node_name=percona-01 wsrep_sst_auth="sstuser:Ahm3r"
在 percona-02.onitroad.com 上配置 Percona XtraDB Cluster 设置。
[root@percona-02 ~]# vi /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
其中查找并设置以下指令。
wsrep_cluster_address=gcomm://192.168.1.204,192.168.1.205 wsrep_node_address=192.168.1.205 wsrep_node_name=percona-02 wsrep_sst_auth="sstuser:Ahm3r"
日期：2020-09-17 00:11:36 来源：oir作者：oir