PXC 高可用
PXC高可用
2.1 PXC介绍
Percona Server由领先的MySQL咨询公司Percona发布。 Percona Server是一款独立的数据库产品，其可以完全与MySQL兼容，可以在不更改代码的情况了下将存储引擎更换成XtraDB 。XtraDB可以看做是InnoDB存储引擎的增强版本，它完全兼容InnoDB，且提供了很多InnoDB不具备的有用的功能。
Percona团队的最终声明是“Percona Server是最接近官方MySQL Enterprise发行版的版本”。
Percona XtraDB Cluster是MySQL高可用性和可扩展性的解决方案.
Percona XtraDB Cluster提供的特性有
1.同步复制，事务要么在所有节点提交或不提交。
2.多主复制，可以在任意节点进行写操作。
3.在从服务器上并行应用事件，真正意义上的并行复制。
4.节点自动配置。
5.数据一致性，不再是异步复制。
PXC的实现是在原代码上通过Galera、Percona XtraBackup将不通的Mysql实例链接起来，实现了multi-master的集群架构。
下图中有三个实例，组成了一个集群，而这三个节点与普通的主从架构不通，它们都可以做为主节点，三个节点是对等的，这种一般称为multi-master架构，当有客户端要写入或者读取数据时，随便链接哪个实例都是一样的，读到的数据是相同的，写入某一个节点之后，集群自己会将新数据同步道其它节点上面，这种架构不共享任何数据，是一种高冗余架构。
PXC特点：
1.集群是有节点组成的，推荐配置至少3个节点，但是也可以运行在2个节点上。
2.每个节点都是普通的mysql/percona服务器，可以将现有的数据库服务器组成集群，从PXC机器群可以拆分成单独的服务器。
3.每个节点都包含完整的数据副本。
PXC优点：
1.当执行一个查询时，在本地节点上执行。因为所有数据都在本地，无需远程访问。
2.无需集中管理。可以在任何时间点失去任何节点，但是集群将照常工作。
3.良好的读负载扩展，任意节点都可以查询。
PXC缺点：
1.加入新节点，开销大。需要复制完整的数据
2.不能有效的解决写缩放问题，所有的写操作都将发生在所有节点上。
3.有多少个节点就有多少重复的数据
2.2 部署PXC
1. 环境规划
|
主机名 |
IP |
数据版本 |
系统版本 |
Xtrabackup |
|
pxc01 |
192.168.174.110 |
5.7.32 |
CentOS7.9 |
2.4.22 |
|
pxc02 |
192.168.174.111 |
5.7.32 |
CentOS7.9 |
2.4.22 |
|
pxc03 |
192.168.174.112 |
5.7.32 |
CentOS7.9 |
2.4.22 |
2. 三节点二进制部署
（1）三个节点均需关闭防火墙及修改主机名
[root@db1 ~]# systemctl stop firewalld && systemctl disable firewalld && setenforce 0 [root@db1 ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config [root@db1 ~]# hostnamectl set-hostname pxc01
[root@pxc01 ~]# vi /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.174.100 pxc01 192.168.174.101 pxc02 192.168.174.102 pxc03
（2）三台节点修改内核参数
[root@pxc01 ~]# vi /etc/security/limits.conf ...... mysql soft nproc 65535 mysql hard nproc 65535 mysql soft nofile 65535 mysql hard nofile 65535 ......
（3）三个节点均创建用户及用户组
[root@pxc01 ~]# groupadd mysql [root@pxc01 ~]# useradd -g mysql mysql [root@pxc01 ~]# mkdir -p /mysql/3306/{data,redo,binlog,tmp,logs} [root@pxc01 ~]# chown -R mysql.mysql /mysql/3306/ [root@pxc01 ~]# chmod -R 755 /mysql/3306/
（4）三个节点均安装xtrabackup
[root@pxc01 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/tarball/percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz --2021-06-24 06:31:30-- https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/tarball/percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz Resolving downloads.percona.com (downloads.percona.com)... 74.121.199.231, 162.220.4.221, 162.220.4.222 Connecting to downloads.percona.com (downloads.percona.com)|74.121.199.231|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 103066914 (98M) [application/x-gzip] Saving to: ‘percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz’ 100%[========================>] 103,066,914 880KB/s in 51s 2021-06-24 06:32:22 (1.93 MB/s) - ‘percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz’ saved [103066914/103066914]
[root@pxc01 ~]# tar zxvf percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz [root@pxc01 ~]# mv percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12 /usr/local/xtrabackup [root@pxc01 ~]# chown -R mysql:mysql /usr/local/xtrabackup/ [root@pxc01 ~]# chmod -R 755 /usr/local/xtrabackup/
（4）三个节点均安装pxc
[root@pxc01 ~]# ls anaconda-ks.cfg mha4mysql-node-0.58-0.el7.centos.noarch.rpm percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12.tar.gz
[root@pxc01 ~]# tar zxvf Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12.tar.gz -C /usr/local [root@pxc01 ~]# mv /usr/local/Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12/ /usr/local/mysql
[root@pxc01 ~]# chown -R mysql.mysql /usr/local/mysql
[root@pxc01 ~]# chmod -R 755 /usr/local/mysql
（4）三个节点均安装依赖
[root@pxc01 ~]# yum install -y git scons gcc gcc-c++ openssl check cmake bison boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel socat libcurl-devel socat nc perl-IO-Socket-SSL perl-Time-HiRes rsync lsof
（5）三个节点均创建my.cnf
#pxc01节点的my.cnf
[root@pxc01 ~]# cd /mysql/3306/ [root@pxc01 3306]# vi my.cnf [client] default-character-set = utf8 port = 3306 socket = /mysql/3306/tmp/mysql.sock [mysqld] port = 3306 character_set_server = utf8 server-id = 100 gtid_mode = on enforce_gtid_consistency = on skip-slave-start = 1 relay_log_purge = 0 basedir = /usr/local/mysql datadir = /mysql/3306/data tmpdir = /mysql/3306/tmp log-error = /mysql/3306/logs/alert_3306.log pid-file = /mysql/3306/tmp/mysql.pid log-bin = /mysql/3306/binlog/mysql-bin slow_query_log_file = /mysql/3306/logs/slow.log default-storage-engine = INNODB innodb_log_group_home_dir = /mysql/3306/redo innodb_file_per_table = 1 #PXC wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_name=pxc-cluster wsrep_cluster_address=gcomm://192.168.174.110,192.168.174.111,192.168.174.112 wsrep_node_name=pxc01 wsrep_node_address=192.168.174.110 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=pxc:123456 innodb_autoinc_lock_mode=2
#pxc02的my.cnf
[root@pxc02 ~]# cd /mysql/3306/ [root@pxc02 3306]# vi my.cnf [client] default-character-set = utf8 port = 3306 socket = /mysql/3306/tmp/mysql.sock [mysqld] port = 3306 character_set_server = utf8 server-id = 101 gtid_mode = on enforce_gtid_consistency = on skip-slave-start = 1 relay_log_purge = 0 basedir = /usr/local/mysql datadir = /mysql/3306/data tmpdir = /mysql/3306/tmp log-error = /mysql/3306/logs/alert_3306.log pid-file = /mysql/3306/tmp/mysql.pid log-bin = /mysql/3306/binlog/mysql-bin slow_query_log_file = /mysql/3306/logs/slow.log default-storage-engine = INNODB innodb_log_group_home_dir = /mysql/3306/redo innodb_file_per_table = 1 #PXC wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_name=pxc-cluster wsrep_cluster_address=gcomm://192.168.174.110,192.168.174.111,192.168.174.112 wsrep_node_name=pxc02 wsrep_node_address=192.168.174.111 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=pxc:123456 innodb_autoinc_lock_mode=2
#pxc03的my.cnf
[root@pxc03 ~]# cd /mysql/3306/ [root@pxc03 3306]# vi my.cnf [client] default-character-set = utf8 port = 3306 socket = /mysql/3306/tmp/mysql.sock [mysqld] port = 3306 character_set_server = utf8 server-id = 102 gtid_mode = on enforce_gtid_consistency = on skip-slave-start = 1 relay_log_purge = 0 basedir = /usr/local/mysql datadir = /mysql/3306/data tmpdir = /mysql/3306/tmp log-error = /mysql/3306/logs/alert_3306.log pid-file = /mysql/3306/tmp/mysql.pid log-bin = /mysql/3306/binlog/mysql-bin slow_query_log_file = /mysql/3306/logs/slow.log default-storage-engine = INNODB innodb_log_group_home_dir = /mysql/3306/redo innodb_file_per_table = 1 #PXC wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_name=pxc-cluster wsrep_cluster_address=gcomm://192.168.174.110,192.168.174.111,192.168.174.112 wsrep_node_name=pxc03 wsrep_node_address=192.168.174.112 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=pxc:123456 innodb_autoinc_lock_mode=2
（6）三台节点均修改环境变量
[root@pxc01 3306]# su - mysql
[mysql@pxc01 ~]$
[mysql@pxc01 ~]$ vi .bash_profile export PATH=/usr/local/mysql/bin:$PATH:/usr/local/xtrabackup/bin [mysql@pxc01 ~]$ source .bash_profile
（7）三台节点均初始化数据库
[mysql@pxc01 ~]$ mysqld --defaults-file=/mysql/3306/my.cnf --initialize
（8）启动PXC01节点
[mysql@pxc01 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf --wsrep-new-cluster & [1] 3503 [mysql@pxc01 ~]$ 2021-06-24T16:14:23.082937Z mysqld_safe Logging to '/mysql/3306/logs/alert_3306.log'. 2021-06-24T16:14:23.097502Z mysqld_safe Logging to '/mysql/3306/logs/alert_3306.log'. 2021-06-24T16:14:23.128883Z mysqld_safe Starting mysqld daemon with databases from /mysql/3306/data 2021-06-24T16:14:23.145458Z mysqld_safe Skipping wsrep-recover for empty datadir: /mysql/3306/data 2021-06-24T16:14:23.146374Z mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position [mysql@pxc01 ~]$ su Password: [root@pxc01 ~]# tail -f /mysql/3306/logs/alert_3306.log Version: '5.7.33-36-49-log' socket: '/tmp/mysql.sock' port: 3306 Percona XtraDB Cluster binary (GPL) 5.7.33-rel36-49, Revision a1ed9c3, wsrep_31.49 2021-06-24T16:14:23.768434Z 2 [Note] WSREP: Initialized wsrep sidno 2 2021-06-24T16:14:23.768451Z 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 1) (Increment: 1 -> 1) 2021-06-24T16:14:23.768465Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 2021-06-24T16:14:23.768483Z 2 [Note] WSREP: Assign initial position for certification: 0, protocol version: 4 2021-06-24T16:14:23.768513Z 0 [Note] WSREP: Service thread queue flushed. 2021-06-24T16:14:23.768915Z 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 3d5e4b61-d507-11eb-bde1-7a43393a96a5:0 2021-06-24T16:14:23.769359Z 2 [Note] WSREP: Synchronized with group, ready for connections 2021-06-24T16:14:23.769368Z 2 [Note] WSREP: Setting wsrep_ready to true 2021-06-24T16:14:23.769370Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
获取MySQL密码
[root@pxc01 3306]# grep 'temporary password' /mysql/3306/logs/alert_3306.log 2021-06-24T16:12:16.828577Z 1 [Note] A temporary password is generated for root@localhost: ila>x)u2,20W
[mysql@pxc01 ~]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.33-36-49-log Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> set password='123456'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> grant reload,lock tables,process,replication client on *.* to 'pxc'@'localhost' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
#注意这边需要修改safe_to_bootstrap: 0为 safe_to_bootstrap: 1
[mysql@pxc01 ~]$ vi /mysql/3306/data/grastate.dat # GALERA saved state version: 2.1 uuid: 3d5e4b61-d507-11eb-bde1-7a43393a96a5 seqno: -1 safe_to_bootstrap: 1
（9）启动节点pxc02，pxc03
pxc02与pxc03不要同时启动，待pxc02同步完成后再启动pxc0[mysql@pxc02 ~]$ mysqld --defaults-file=/mysql/3306/my.cnf --initializ[mysql@pxc02 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf &
[1] 13947 [mysql@pxc02 ~]$ 2021-06-24T16:37:15.597902Z mysqld_safe Logging to '/mysql/3306/logs/alert_3306.log'. 2021-06-24T16:37:15.599640Z mysqld_safe Logging to '/mysql/3306/logs/alert_3306.log'. 2021-06-24T16:37:15.616450Z mysqld_safe Starting mysqld daemon with databases from /mysql/3306/data 2021-06-24T16:37:15.620863Z mysqld_safe Skipping wsrep-recover for empty datadir: /mysql/3306/data 2021-06-24T16:37:15.621815Z mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position [mysql@pxc02 ~]$ cat /mysql/3306/logs/alert_3306.log |grep pxc02 2021-06-24T16:37:17.123621Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 6fc13d1e-d50a-11eb-ab94-1a494581ac91 from 1 (pxc02) 2021-06-24T16:37:18.540799Z 0 [Note] WSREP: Member 1.0 (pxc02) requested state transfer from '*any*'. Selected 0.0 (pxc01)(SYNCED) as donor. 2021-06-24T16:37:31.799759Z 0 [Note] WSREP: 0.0 (pxc01): State transfer to 1.0 (pxc02) complete. 2021-06-24T16:37:36.632754Z 0 [Note] WSREP: 1.0 (pxc02): State transfer from 0.0 (pxc01) complete.#已经同步完成，就是同步成功了。
2021-06-24T16:37:36.632975Z 0 [Note] WSREP: Member 1.0 (pxc02) synced with group.
[mysql@pxc03 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf & [1] 3500 [mysql@pxc03 ~]$ 2021-06-24T16:40:07.943746Z mysqld_safe Logging to '/mysql/3306/logs/alert_3306.log'. 2021-06-24T16:40:07.945524Z mysqld_safe Logging to '/mysql/3306/logs/alert_3306.log'. 2021-06-24T16:40:07.963796Z mysqld_safe Starting mysqld daemon with databases from /mysql/3306/data 2021-06-24T16:40:07.968751Z mysqld_safe Skipping wsrep-recover for empty datadir: /mysql/3306/data 2021-06-24T16:40:07.969860Z mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position [mysql@pxc03 ~]$ cat /mysql/3306/logs/alert_3306.log |grep pxc03 2021-06-24T16:40:09.506067Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: d6861d4e-d50a-11eb-8368-730fd97cec26 from 2 (pxc03) 2021-06-24T16:40:10.772965Z 0 [Note] WSREP: Member 2.0 (pxc03) requested state transfer from '*any*'. Selected 0.0 (pxc01)(SYNCED) as donor. 2021-06-24T16:40:23.608744Z 0 [Note] WSREP: 0.0 (pxc01): State transfer to 2.0 (pxc03) complete. 2021-06-24T16:40:27.655647Z 0 [Note] WSREP: 2.0 (pxc03): State transfer from 0.0 (pxc01) complete. （同步成功） 2021-06-24T16:40:27.656099Z 0 [Note] WSREP: Member 2.0 (pxc03) synced with group.
