PerconaXtraDBCluster(PXC5.7)
系统运维 环境 三台主机
pxc1:192.168.7.71
pxc2:192.168.7.72
pxc3: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.71
192.168.7.72
192.168.7.73
1.2/etc/ansible/ansible.cfg配置
module_name = shell #115行,将默认的模块command该为shell
host_key_checking = False #71行,去掉前面的注释
2.安装Percona XtraDB Cluster 5.7
# 1.使用清华大学的yum源,官方源太慢了
[root@pxc1 ~]#vim /etc/yum.repos.d/pxc.repo
[percona]
name=percona_repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled=1
gpgcheck=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_repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled=1
gpgcheck=0
192.168.7.72 | CHANGED | rc=0 >>
[percona]
name=percona_repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled=1
gpgcheck=0
192.168.7.71 | CHANGED | rc=0 >>
[percona]
name=percona_repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled=1
gpgcheck=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.cnf
0 directories, 3 files
[root@pxc1 ~]#egrep -v ^#|^$ /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73 #集群中每个几点的IP
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.7.71 #取消行首注释,指定本节点的IP
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1 #本节点在集群中的名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_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.so
wsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.7.72
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-2
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:123.com
192.168.7.73 | CHANGED | rc=0 >>
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.7.73
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-3
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:123.com
192.168.7.71 | CHANGED | rc=0 >>
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.7.71
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_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 22
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> 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.com
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 12
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 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 stdout
Database
information_schema
db1
mysql
performance_schema
sysmysql: [Warning] Using a password on the command line interface can be insecure.
192.168.7.72 | CHANGED | rc=0 >>
PAGER set to stdout
Database
information_schema
db1
mysql
performance_schema
sysmysql: [Warning] Using a password on the command line interface can be insecure.
192.168.7.71 | CHANGED | rc=0 >>
PAGER set to stdout
Database
information_schema
db1
mysql
performance_schema
sysmysql: [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 code
192.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.so
wsrep_cluster_address=gcomm://192.168.7.71,192.168.7.72,192.168.7.73,192.168.7.74
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.7.74
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-4
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_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.com
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 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_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.com
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 13
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 \'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.com
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 12
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> 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 >>
Database
information_schema
db1
db2
db3
mysql
performance_schema
sys
PAGER 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 code
192.168.7.74 | CHANGED | rc=0 >>
Database
information_schema
db1
db2
db3
mysql
performance_schema
sys
PAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.
192.168.7.71 | CHANGED | rc=0 >>
Database
information_schema
db1
db2
db3
mysql
performance_schema
sys
PAGER 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 >>
Database
information_schema
db1
db2
db3
mysql
performance_schema
sys
PAGER set to stdoutmysql: [Warning] Using a password on the command line interface can be insecure.
标题名称:PerconaXtraDBCluster(PXC5.7)
网址分享:http://cdiso.cn/article/cjsgpd.html