Percona XtraDB Cluster(PXC 5.7）

环境

三台主机

pxc1:192.168.7.71pxc2:192.168.7.72pxc3:192.168.7.73

firewalld 和 selinux都关闭，保证时间同步；如果已安装MySQL，必须卸载！！！

OS 版本

[root@pxc1 ~]#cat /etc/redhat-release CentOS Linux release 7.7.1908 (Core)

操作步骤

pxc1主机

1.安装辅助工具Ansible，前提三台主机做好key验证

[root@pxc1 ~]#yum -y install ansible

1.1/etc/ansible/hosts配置，在最后一行后面添加以下配置

[pxcservers]192.168.7.71192.168.7.72192.168.7.73

1.2/etc/ansible/ansible.cfg配置

module_name = shell  #115行，将默认的模块command该为shellhost_key_checking = False #71行，去掉前面的注释

2.安装Percona XtraDB Cluster 5.7

# 1.使用清华大学的yum源，官方源太慢了[root@pxc1 ~]#vim /etc/yum.repos.d/pxc.repo[percona]name=percona_repobaseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearchenabled=1gpgcheck=0# 2.同时将配置文件传给pxc2和pxc3[root@pxc1 ~]#ansible 'pxcservers:!192.168.7.71' -m copy -a 'src=/etc/yum.repos.d/pxc.repo dest=/etc/yum.repos.d'192.168.7.73 | CHANGED => {# 3.查看三台主机的pxc.repo文件是否一致[root@pxc1 ~]#ansible 'pxcservers'  -a 'cat /etc/yum.repos.d/pxc.repo'192.168.7.73 | CHANGED | rc=0 >>[percona]name=percona_repobaseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearchenabled=1gpgcheck=0192.168.7.72 | CHANGED | rc=0 >>[percona]name=percona_repobaseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearchenabled=1gpgcheck=0192.168.7.71 | CHANGED | rc=0 >>[percona]name=percona_repobaseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearchenabled=1gpgcheck=0# 4.执行以下命令，在三台主机都安装PXC 5.7[root@pxc1 ~]#ansible 'pxcservers'  -a 'yum install Percona-XtraDB-Cluster-57 -y'

3.修改配置文件

# /etc/my.cnf为主配置文件，当前版本中，其余的配置文件都放在/etc/percona-xtradb-cluster.conf.d目录里，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三个文件[root@pxc1 ~]#tree /etc/percona-xtradb-cluster.conf.d//etc/percona-xtradb-cluster.conf.d/├── mysqld.cnf├── mysqld_safe.cnf└── wsrep.cnf0 directories, 3 files[root@pxc1 ~]#egrep -v "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf[mysqld]wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73 #集群中每个几点的IPbinlog_format=ROWdefault_storage_engine=InnoDBwsrep_slave_threads= 8wsrep_log_conflictsinnodb_autoinc_lock_mode=2wsrep_node_address=192.168.7.71 #取消行首注释，指定本节点的IPwsrep_cluster_name=pxc-clusterwsrep_node_name=pxc-cluster-node-1 #本节点在集群中的名称pxc_strict_mode=ENFORCINGwsrep_sst_method=xtrabackup-v2wsrep_sst_auth="sstuser:123.com" #取消行首注释，并且修改密码

3.1将配置文件copy给pxc2，pxc3两台主机，然后稍作修改，主要项是wsrep_node_address和wsrep_node_name

# 1.copy[root@pxc1 ~]#ansible 'pxcservers:!192.168.7.71' -m copy -a 'src=/etc/percona-xtradb-cluster.conf.d/wsrep.cnf dest=/etc/percona-xtradb-cluster.conf.d'# 2.修改pxc2主机的配置文件[root@pxc1 ~]#ansible '192.168.7.72' -a 'sed -i -e s/wsrep_node_address=192.168.7.71/wsrep_node_address=192.168.7.72/ -e s/wsrep_node_name=pxc-cluster-node-1/wsrep_node_name=pxc-cluster-node-2/ /etc/percona-xtradb-cluster.conf.d/wsrep.cnf'# 3.修改pxc3主机的配置文件[root@pxc1 ~]#ansible '192.168.7.73' -a 'sed -i -e s/wsrep_node_address=192.168.7.71/wsrep_node_address=192.168.7.73/ -e s/wsrep_node_name=pxc-cluster-node-1/wsrep_node_name=pxc-cluster-node-3/ /etc/percona-xtradb-cluster.conf.d/wsrep.cnf'# 4.查看三个几点的配置文件信息[root@pxc1 ~]#ansible 'pxcservers' -a 'egrep -v "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf'[root@pxc1 ~]#ansible 'pxcservers' -a 'egrep -v "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf'192.168.7.72 | CHANGED | rc=0 >>[mysqld]wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73binlog_format=ROWdefault_storage_engine=InnoDBwsrep_slave_threads= 8wsrep_log_conflictsinnodb_autoinc_lock_mode=2wsrep_node_address=192.168.7.72wsrep_cluster_name=pxc-clusterwsrep_node_name=pxc-cluster-node-2pxc_strict_mode=ENFORCINGwsrep_sst_method=xtrabackup-v2wsrep_sst_auth="sstuser:123.com"192.168.7.73 | CHANGED | rc=0 >>[mysqld]wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73binlog_format=ROWdefault_storage_engine=InnoDBwsrep_slave_threads= 8wsrep_log_conflictsinnodb_autoinc_lock_mode=2wsrep_node_address=192.168.7.73wsrep_cluster_name=pxc-clusterwsrep_node_name=pxc-cluster-node-3pxc_strict_mode=ENFORCINGwsrep_sst_method=xtrabackup-v2wsrep_sst_auth="sstuser:123.com"192.168.7.71 | CHANGED | rc=0 >>[mysqld]wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73binlog_format=ROWdefault_storage_engine=InnoDBwsrep_slave_threads= 8wsrep_log_conflictsinnodb_autoinc_lock_mode=2wsrep_node_address=192.168.7.71wsrep_cluster_name=pxc-clusterwsrep_node_name=pxc-cluster-node-1pxc_strict_mode=ENFORCINGwsrep_sst_method=xtrabackup-v2wsrep_sst_auth="sstuser:123.com"

注：尽管Galera Cluster不再需要通过binlog的形式进行同步，但还是建议在配置文件中开启二进制日志功能，原因是后期如果有新节点需要加入，老节点通过SST全量传输的方式向新节点传输数据，很可能会拖垮集群性能，所以让新节点先通过binlog方式完成同步后再加入集群会是一种更好的选择

4.启动集群中的第一个节点

[root@pxc1 ~]#systemctl start mysql@bootstrap.service[root@pxc1 ~]#systemctl enable mysql@bootstrap.service# PXC最常使用的端口号如下，其中3306、4567启动的时候就会监听4567：组成员之间进行沟通的端口号3306：数据库对外服务的端口号4444：请求SST的端口号4568：用于传输IST的端口号

5.修改初始化密码，并且创建及授权sstuser用户

[root@pxc1 ~]#mysql -p`awk /root@localhost/'{print $NF}' /var/log/mysqld.log`mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 22Server version: 5.7.27-30-57-logCopyright (c) 2009-2019 Percona LLC and/or its affiliatesCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> alter user 'root'@'localhost' identified by '123.com';Query OK, 0 rows affected (0.01 sec)mysql> create user 'sstuser'@'localhost' identified by '123.com';Query OK, 0 rows affected (0.01 sec)mysql> grant reload,lock tables,process,replication client on *.* to 'sstuser'@'localhost';Query OK, 0 rows affected (0.00 sec)

6.查看相关的状态变量

mysql> show status like 'wsrep_cluster_size';+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| wsrep_cluster_size | 1     |+--------------------+-------+1 row in set (0.01 sec)# 表示该Galera集群中只有一个节点mysql> show status like 'wsrep_cluster_status';+----------------------+---------+| Variable_name        | Value   |+----------------------+---------+| wsrep_cluster_status | Primary |+----------------------+---------+1 row in set (0.00 sec)# 表示该节点在集群中的状态为Primary，且已经完全连接并准备好mysql> show status like 'wsrep_local_state%';+---------------------------+--------------------------------------+| Variable_name             | Value                                |+---------------------------+--------------------------------------+| wsrep_local_state_uuid    | b0fdc391-156e-11ea-89de-331ca2945d2c || wsrep_local_state         | 4                                    || wsrep_local_state_comment | Synced                               |+---------------------------+--------------------------------------+3 rows in set (0.00 sec)#状态为Synced(4)，表示数据已同步完成(因为是第一个引导节点，无数据需要同步)。 如果状态是Joiner, 意味着 SST 没有完成. 只有所有节点状态是Synced，才可以加新节点

7.启动PXC集群中pxc2,pxc3节点

[root@pxc1 ~]#ansible 'pxcservers:!192.168.7.71' -a 'systemctl enable --now mysql'

pxc2主机

8.查看集群状态，验证集群是否成功（也可以是其它任意节点）

[root@pxc2 ~]#mysql -p123.commysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 12Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39Copyright (c) 2009-2019 Percona LLC and/or its affiliatesCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like 'wsrep_node_name';+-----------------+--------------------+| Variable_name   | Value              |+-----------------+--------------------+| wsrep_node_name | pxc-cluster-node-2 |+-----------------+--------------------+1 row in set (0.01 sec)mysql> show variables like 'wsrep_node_address';+--------------------+--------------+| Variable_name      | Value        |+--------------------+--------------+| wsrep_node_address | 192.168.7.72 |+--------------------+--------------+1 row in set (0.00 sec)mysql> show variables like 'wsrep_on';+---------------+-------+| Variable_name | Value |+---------------+-------+| wsrep_on      | 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.00 sec)

pxc3主机

9.创建数据库（也可以是其它任意节点）

# 查看当前的数据库mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)# 创建db1数据库[root@pxc1 ~]#ansible '192.168.7.73' -a 'mysql -p123.com -e "create database db1;\n"'192.168.7.73 | CHANGED | rc=0 >>PAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.# 查看集群中的其它节点是否同步新建的db1数据库[root@pxc1 ~]#ansible 'pxcservers' -a 'mysql -p123.com -e "show databases\n"'192.168.7.73 | CHANGED | rc=0 >>PAGER set to stdoutDatabaseinformation_schemadb1mysqlperformance_schemasysmysql: [Warning] Using a password on the command line interface can be insecure.192.168.7.72 | CHANGED | rc=0 >>PAGER set to stdoutDatabaseinformation_schemadb1mysqlperformance_schemasysmysql: [Warning] Using a password on the command line interface can be insecure.192.168.7.71 | CHANGED | rc=0 >>PAGER set to stdoutDatabaseinformation_schemadb1mysqlperformance_schemasysmysql: [Warning] Using a password on the command line interface can be insecure.

10.在集群中的所有节点同时创建db2数据库，只有一个节点会成功

[root@pxc1 ~]#ansible 'pxcservers' -a 'mysql -p123.com -e "create database db2\n"'192.168.7.73 | FAILED | rc=1 >>PAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1007 (HY000) at line 1: Can't create database 'db2'; database existsnon-zero return code192.168.7.72 | CHANGED | rc=0 >>PAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.192.168.7.71 | FAILED | rc=1 >>PAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1007 (HY000) at line 1: Can't create database 'db2'; database existsnon-zero return code

在PXC集群中加入节点

一个节点加入到Galera集群有两种情况：新节点加入集群、暂时离组的成员再次加入集群  1）节点加入Galera集群新节点加入集群时，需要从当前集群中选择一个Donor节点来同步数据，也就是所谓的state_snapshot_tranfer(SST)过程。SST同步数据的方式由选项wsrep_sst_method决定，一般选择的是xtrabackup。必须注意，新节点加入Galera时，会删除新节点上所有已有数据，再通过xtrabackup(假设使用的是该方式)从Donor处完整备份所有数据进行恢复。所以，如果数据量很大，新节点加入过程会很慢。而且，在一个新节点成为Synced状态之前，不要同时加入其它新节点，否则很容易将集群压垮。如果是这种情况，可以考虑使用wsrep_sst_method=rsync来做增量同步，既然是增量同步，最好保证新节点上已经有一部分数据基础，否则和全量同步没什么区别，且这样会对Donor节点加上全局read only锁。2）旧节点加入Galera集群如果旧节点加入Galera集群，说明这个节点在之前已经在Galera集群中呆过，有一部分数据基础，缺少的只是它离开集群时的数据。这时加入集群时，会采用IST(incremental snapshot transfer)传输机制，即使用增量传输。但注意，这部分增量传输的数据源是Donor上缓存在GCache文件中的，这个文件有大小限制，如果缺失的数据范围超过已缓存的内容，则自动转为SST传输。如果旧节点上的数据和Donor上的数据不匹配(例如这个节点离组后人为修改了一点数据)，则自动转为SST传输。

11.在PXC集群中再加一台新的主机PXC4：192.168.7.74

11.1安装

# 先在ansible主控机的hosts文件中添加192.168.7.74，然后执行以下操作[root@pxc1 ~]#ansible '192.168.7.74' -m copy -a 'src=/etc/yum.repos.d/pxc.repo dest=/etc/yum.repos.d'[root@pxc1 ~]#ansible '192.168.7.74' -a 'yum -y install Percona-XtraDB-Cluster-57'[root@pxc1 ~]#ansible '192.168.7.74' -m copy -a 'src=/etc/percona-xtradb-cluster.conf.d/wsrep.cnf dest=/etc/percona-xtradb-cluster.conf.d'[root@pxc1 ~]#ansible '192.168.7.74' -a 'sed -i -e s/wsrep_node_address=192.168.7.71/wsrep_node_address=192.168.7.74/ -e s/wsrep_node_name=pxc-cluster-node-1/wsrep_node_name=pxc-cluster-node-4/ /etc/percona-xtradb-cluster.conf.d/wsrep.cnf'[root@pxc1 ~]#ansible 192.168.7.74 -a "sed -i 's/^wsrep_cluster_address.*/&,192.168.7.74/' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf"[root@pxc1 ~]#ansible 192.168.7.74 -a 'egrep -v "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf'192.168.7.74 | CHANGED | rc=0 >>[mysqld]wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73,192.168.7.74binlog_format=ROWdefault_storage_engine=InnoDBwsrep_slave_threads= 8wsrep_log_conflictsinnodb_autoinc_lock_mode=2wsrep_node_address=192.168.7.74wsrep_cluster_name=pxc-clusterwsrep_node_name=pxc-cluster-node-4pxc_strict_mode=ENFORCINGwsrep_sst_method=xtrabackup-v2wsrep_sst_auth="sstuser:123.com"

11.2启动

[root@pxc1 ~]#ansible 192.168.7.74 -a 'systemctl enable --now mysql'

11.3在新加入的节点中查看集群状态

[root@pxc4 ~]#mysql -p123.commysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 11Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39Copyright (c) 2009-2019 Percona LLC and/or its affiliatesCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show status like 'wsrep_cluster_size';+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| wsrep_cluster_size | 4     |+--------------------+-------+1 row in set (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db1                || db2                || mysql              || performance_schema || sys                |+--------------------+6 rows in set (0.00 sec)

修改集群中其它节点的配置文件，将新加入的节点添加到配置文件中

[root@pxc1 ~]#ansible 'pxcservers:!192.168.7.74' -a "sed -i 's/^wsrep_cluster_address.*/&,192.168.7.74/' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf"

在PXC集群中修复故障节点

12.停止集群中任意节点的mysql服务，这里停止pxc2

[root@pxc1 ~]#ansible 192.168.7.72 -a 'systemctl stop mysql'

13.在集群中的其它任意节点（这里在pxc3）查看wsrep_cluster_size变量少了一个节点

[root@pxc3 ~]#mysql -p123.commysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 13Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39Copyright (c) 2009-2019 Percona LLC and/or its affiliatesCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show status like 'wrep_cluster_size';Empty set (0.00 sec)mysql> show status like 'wrep_cluster_size';Empty set (0.00 sec)mysql> show status like 'wsrep_cluster_size';+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| wsrep_cluster_size | 3     |+--------------------+-------+1 row in set (0.00 sec)

14.在集群中的其它任意节点创建db3数据库（这里在pxc4），然后查看其它节点是否同步数据

[root@pxc4 ~]#mysql -p123.commysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 12Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39Copyright (c) 2009-2019 Percona LLC and/or its affiliatesCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database db3;Query OK, 1 row affected (0.01 sec)[root@pxc1 ~]#ansible pxcservers -a 'mysql -p123.com -e "show databases;\n"'192.168.7.73 | CHANGED | rc=0 >>Databaseinformation_schemadb1db2db3mysqlperformance_schemasysPAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.192.168.7.72 | FAILED | rc=1 >>mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)non-zero return code192.168.7.74 | CHANGED | rc=0 >>Databaseinformation_schemadb1db2db3mysqlperformance_schemasysPAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.192.168.7.71 | CHANGED | rc=0 >>Databaseinformation_schemadb1db2db3mysqlperformance_schemasysPAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.

15.恢复pxc2的mysql服务，数据同步

[root@pxc1 ~]#ansible 192.168.7.72 -a 'systemctl start mysql'192.168.7.72 | CHANGED | rc=0 >>[root@pxc1 ~]#ansible 192.168.7.72 -a 'mysql -p123.com -e "show databases;\n"'192.168.7.72 | CHANGED | rc=0 >>Databaseinformation_schemadb1db2db3mysqlperformance_schemasysPAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.