1、PXC介绍
Percona XtraDB Cluster(简称PXC)
是基于Galera的MySQL高可用集群解决方案
Galera Cluster是Codership公司开发的一套免费开源的高可用方案
PXC集群主要由两部分组成:
Percona Server with XtraDB和Write Set Replication patches(同步、多主复制插件)
官网http://galeracluster.com
2、PXC特点
数据强一致性,无同步延迟
没有主从切换操作,无需使用虚拟ip
支持innoDB存储引擎
多线程复制
部署使用简单
支持节点自动加入,无需手动拷贝数据
3、相关的服务端口
3306 数据库服务端口
4444 SST 端口
4567 集群通信端口
4568 IST 端口
SST State Snapshot Transfer 全量同步
IST Incremental State Transfer 增量同步
4、PXC集群部署
4.1、环境准备
pxcnode10:192.168.4.10数据库服务器
pxcnode20:192.168.4.20数据库服务器
pxcnode30:192.168.4.30数据库服务器
链接:https://pan.baidu.com/s/1YA2Dd6O5f31j0cpJDoZEwQ
提取码:xbjy
三台服务器基本设置如下:
关闭防火墙、selinux
cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.4.10 pxcnode10
192.168.4.20 pxcnode20
192.168.4.30 pxcnode30
4.2、三台服务器按照以下顺序安装软件,因为软件之间有依赖性。(以10为例)
[root@pxcnode10 ~]# cd pxc
[root@pxcnode10 pxc]# ls
libev-4.15-1.el6.rf.x86_64.rpm
percona-release-0.1-4.noarch.rpm
percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar
qpress-1.1-14.11.x86_64.rpm
[root@pxcnode10 pxc]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
[root@pxcnode10 pxc]# yum -y install percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
[root@pxcnode10 pxc]# rpm -ivh qpress-1.1-14.11.x86_64.rpm
[root@pxcnode10 pxc]# tar -xvf Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar
[root@pxcnode10 pxc]# yum -y install Percona-XtraDB-Cluster-*.rpm
2)修改三台服务器的mysqld.cnf文件,除了server-id不一样,其他配置都一样
[root@pxcnode10 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=10 # server-id 不允许重复
datadir=/var/lib/mysql # 数据库目录
socket=/var/lib/mysql/mysql.sock # socket文件
log-error=/var/log/mysqld.log # 日志文件
pid-file=/var/run/mysqld/mysqld.pid # pid文件
log-bin # 启用binlog日志
log_slave_updates # 启用链式复制
expire_logs_days=7 # 日志文件保留天数
[root@pxcnode20 pxc]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=20
[root@pxcnode30 pxc]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=30
3)修改三台服务器的mysqld_safe.cnf文件,使用默认配置即可。(以10为例)
[root@pxcnode10 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
nice = 0
3)修改三台服务器的wsrep.cnf文件,其中成员列表、集群名、SST用户和密码必须相同
[root@pxcnode10 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
1 [mysqld]
8 wsrep_cluster_address=gcomm://192.168.4.10,192.168.4.20,192.168.4.30 # 成员列表
25 wsrep_node_address=192.168.4.10 # 本机IP
27 wsrep_cluster_name=pxc-cluster # 集群名
30 wsrep_node_name=pxcnode10 # 本机主机名
39 wsrep_sst_auth="jy:JY1987...zy2011" # SST数据同步授权用户及密码
[root@pxcnode20 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
1 [mysqld]
8 wsrep_cluster_address=gcomm://192.168.4.10,192.168.4.20,192.168.4.30
25 wsrep_node_address=192.168.4.20
27 wsrep_cluster_name=pxc-cluster
30 wsrep_node_name=pxcnode20
39 wsrep_sst_auth="jy:JY1987...zy2011"
[root@pxcnode30 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
1 [mysqld]
8 wsrep_cluster_address=gcomm://192.168.4.10,192.168.4.20,192.168.4.30
25 wsrep_node_address=192.168.4.30
27 wsrep_cluster_name=pxc-cluster
30 wsrep_node_name=pxcnode30
39 wsrep_sst_auth="jy:JY1987...zy2011"
4.3、初始化集群,在任意1台服务器上执行初始化集群操作都可以(仅需要执行一遍)
[root@pxcnode10 ~]# systemctl start mysql@bootstrap.service
[root@pxcnode10 ~]# ls /var/lib/mysql # 检查数据库文件列表,有文件说明成功
auto.cnf client-key.pem ib_buffer_pool ibtmp1 mysql.sock.lock pxcnode10-bin.000001 server-key.pem
ca-key.pem galera.cache ibdata1 mysql performance_schema pxcnode10-bin.000002 sys
ca.pem grastate.dat ib_logfile0 mysqld_safe.pid private_key.pem pxcnode10-bin.index xb_doublewrite
client-cert.pem gvwstate.dat ib_logfile1 mysql.sock public_key.pem server-cert.pem
[root@pxcnode10 ~]# grep password /var/log/mysqld.log
2022-05-18T14:32:54.806734Z 1 [Note] A temporary password is generated for root@localhost: !6F0yJ!=wqSc
[root@pxcnode10 ~]# mysql -uroot -p"!6F0yJ!=wqSc"
mysql> alter user root@"localhost" identified by "JY1987...zy2011";
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to jy@"%" identified by "JY1987...zy2011"; #添加全量同步用户jy
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@pxcnode10 ~]# netstat -utnalp | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 5782/mysqld
[root@pxcnode10 ~]# netstat -utnalp | grep 4567 # 查看集群端
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 5782/mysqld
1)在pxcnode10主机数据库服务器上,管理员登录并建库.表存储记录,pxc集群中的表必须要有主键,自增长可选。
[root@pxcnode10 ~]# mysql -uroot -pJY1987...zy2011
mysql> create database xb;
Query OK, 1 row affected (0.00 sec)
mysql> create table xb.zy(id int primary key auto_increment,age int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into xb.zy(age) values(11),(35),(35);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from xb.zy;
+----+------+
| id | age |
+----+------+
| 1 | 11 |
| 2 | 35 |
| 3 | 35 |
+----+------+
3 rows in set (0.00 sec)
4.4、在20和30主机上都启动数据库服务并验证数据,启动服务后会自动同步10主机的数据(以20为例)
[root@pxcnode20 ~]# systemctl start mysql
[root@pxcnode20 ~]# ls /var/lib/mysql
auto.cnf client-key.pem ib_buffer_pool ibtmp1 mysql.sock public_key.pem server-key.pem xtrabackup_binlog_pos_innodb
ca-key.pem galera.cache ibdata1 innobackup.move.log mysql.sock.lock pxcnode20-bin.000001 sys xtrabackup_galera_info
ca.pem grastate.dat ib_logfile0 innobackup.prepare.log performance_schema pxcnode20-bin.index xb xtrabackup_info
client-cert.pem gvwstate.dat ib_logfile1 mysql private_key.pem server-cert.pem xb_doublewrite xtrabackup_master_key_id
[root@pxcnode20 ~]# netstat -utnalp | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 3312/mysqld
[root@pxcnode20 ~]# netstat -utnalp | grep 4567
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 3312/mysqld
tcp 0 0 192.168.4.20:46176 192.168.4.10:4567 ESTABLISHED 3312/mysqld
tcp 0 0 192.168.4.20:4567 192.168.4.20:37076 CLOSE_WAIT 3312/mysqld
[root@pxcnode20 ~]# mysql -uroot -pJY1987...zy2011 -e 'select * from xb.zy'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | age |
+----+------+
| 1 | 11 |
| 2 | 35 |
| 3 | 35 |
+----+------+