PXC是Percona XtraDB Cluster的简称,是一个基于Galera Cluster的开源集群解决方案。PXC的原理是将多个Percona XtraDB实例组成一个集群,实现数据同步和故障转移,从而提高数据库的可用性和容错性。
PXC的工作原理如下:
-
所有节点之间互相通信,使用Galera协议进行数据同步,并通过逻辑时钟协调写入事务的顺序。
-
任何一个节点都可以接收写入请求,并将其分配给集群中的其他节点。
-
当节点从群集中添加或删除时,群集会重新进行协调以确保所有节点上的数据都是一致的。
-
当一个节点失效时,集群会自动进行故障转移,并从其他节点中选出一个新的主节点,以确保数据的一致性和可用性。
实验目的:将原有的mysql主从同步读写分离集群迁移到PXC集群,实现数据存储的高可用。
实验环境:
database | 192.168.88.1 | 原担任主服务器 |
PXC1 | 192.168.88.10 | PXC集群 |
PXC2 | 192.168.88.20 | PXC集群 |
PXC3 | 192.168.88.30 | PXC集群 |
所需软件:
[root@pxc1 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
Percona-XtraDB-Cluster-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-57-debuginfo-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-client-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-devel-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-full-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-garbd-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-server-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-shared-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-shared-compat-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-test-57-5.7.25-31.35.1.el7.x86_64.rpm
qpress-1.1-14.11.x86_64.rpm
实验步骤:
1.将pxc1作为database的从加入主从结构实现数据同步
[root@database ~]# mysql -uroot -p123qqq...A #授权主从同步用户
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY '123qqq...A';
[root@databases ~]# mysqldump -uroot -p123qqq...A -A > allbak.sql #数据备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@databases ~]# scp allbak.sql 192.168.88.10:/root #传到pxc1
[root@databases ~]# cat /etc/my.cnf
......
[mysqld]
server_id=1
log_bin=database1 #开启binlog日志
......
#查看主从同步主file和position
[root@databases ~]# mysql -uroot -p123qqq...A -e "show master status"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| database1.000002 | 441 | | | |
+------------------+----------+--------------+------------------+-------------------+
#####################PXC1加入##########################
[root@pxc1 ~]# mysql -uroot -p123qqq...A < allbak.sql
[root@pxc1 ~]# cat /etc/my.cnf
[mysqld]
server_id=10 #开启binlog日志
log_bin=pxc10
[root@pxc1 ~]# mysql -uroot -p123qqq...A
mysql> change master to
-> master_user='repluser', #主从同步
-> master_log_pos=441,
-> master_log_file='database1.000002',
-> master_host='192.168.88.1',
-> master_password='123qqq...A';
Query OK, 0 rows affected, 2 warnings (0.53 sec)
mysql> start slave;
Query OK, 0 rows affected (0.11 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.88.1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: database1.000002
Read_Master_Log_Pos: 441
Relay_Log_File: mysql01-relay-bin.000006
Relay_Log_Pos: 320
Relay_Master_Log_File: database1.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #两个yes即可
2.pxc1安装Percona-XtraDB-Cluster,将pxc2和pxc3找pxc1做全量同步
[root@pxc11 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
Percona-XtraDB-Cluster-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-57-debuginfo-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-client-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-devel-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-full-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-garbd-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-server-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-shared-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-shared-compat-57-5.7.25-31.35.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-test-57-5.7.25-31.35.1.el7.x86_64.rpm
qpress-1.1-14.11.x86_64.rpm
[root@pxc1 pxc]# yum -y install *.rpm
[root@pxc1 pxc]# cat /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=10 #修改id
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[root@pxc1 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc1 ~]# sed -rn '8p;25p;27p;30p;39p' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm:// #集群成员列表
wsrep_node_address=192.168.88.10 #本机IP地址
wsrep_cluster_name=pxc-cluster #集群名称
wsrep_node_name=pxc1 #本机主机名
wsrep_sst_auth="sstuser:123qqq...A" #全量同步用户
[root@pxc1 ~]# systemctl start mysql #注意服务名没有d
[root@pxc1 ~]# mysql -hlocalhost -uroot -p'123qqq...A'
mysql> GRANT ALL ON *.* TO sstuser@'localhost'
-> IDENTIFIED BY '123qqq...A'; #授权全量同步用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
###############################pxc2######################
[root@pxc2 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[root@pxc2 ~]# sed -rn '7p' /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
server-id=20
[root@pxc2 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc2 ~]# sed -rn '8p;25p;27p;30p;39p' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.88.10#朝该节点同步数据
wsrep_node_address=192.168.88.20
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc2
wsrep_sst_auth="sstuser:123qqq...A"
[root@pxc2 ~]# systemctl restart mysql
###############################pxc3############################
[root@pxc3 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[root@pxc3 ~]# sed -rn '7p' /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
server-id=20
[root@pxc3 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc3 ~]# sed -rn '8p;25p;27p;30p;39p' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.88.10#朝该节点同步数据
wsrep_node_address=192.168.88.30
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc3
wsrep_sst_auth="sstuser:123qqq...A"
[root@pxc3 ~]# systemctl restart mysql
###########33
3.同步完再修改配置加入集群
#增量同步数据
################pxc1#################
[root@pxc1 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc1 ~]# sed -rn '8p' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.88.10,192.168.88.20,192.168.88.30
[root@pxc1 ~]# systemctl restart mysql
################pxc2#################
[root@pxc1 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc1 ~]# sed -rn '8p' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.88.10,192.168.88.20,192.168.88.30
[root@pxc1 ~]# systemctl restart mysql
################pxc3#################
[root@pxc1 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc1 ~]# sed -rn '8p' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.88.10,192.168.88.20,192.168.88.30
[root@pxc1 ~]# systemctl restart mysql