PXC高可用
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 部署PXC
1. 环境规划
|
主机名 |
IP |
数据版本 |
系统版本 |
Xtrabackup |
|
pxc01 |
192.168.174.134 |
5.7.32 |
CentOS7.9 |
2.4.22 |
|
pxc02 |
192.168.174.135 |
5.7.32 |
CentOS7.9 |
2.4.22 |
|
pxc03 |
192.168.174.136 |
5.7.32 |
CentOS7.9 |
2.4.22 |
2. 三节点二进制部署
（1）三个节点均需关闭防火墙及修改主机名
[root@localhost ~]# systemctl stop firewalld [root@localhost ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service. [root@localhost ~]# setenforce 0 [root@localhost ~]# [root@localhost ~]# sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/g‘ /etc/selinux/config [root@localhost ~]# hostnamectl set-hostname pxc01 [root@localhost ~]# bash [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.134 pxc01 192.168.174.135 pxc02 192.168.174.136 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 [root@pxc01 ~]# ls anaconda-ks.cfg percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz [root@pxc01 ~]# tar xf percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz -C /usr/local/ [root@pxc01 ~]# cd /usr/local/ [root@pxc01 local]# mv percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12/ xtrabackup [root@pxc01 local]# chown -R mysql:mysql /usr/local/xtrabackup/ [root@pxc01 local]# chmod -R 755 /usr/local/xtrabackup/
（4）三个节点均安装pxc
[root@pxc01 ~]# tar xf 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 epel-release
[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 ~]# vi /mysql/3306/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.134,192.168.174.135,192.168.174.136 wsrep_node_name=pxc01 wsrep_node_address=192.168.174.134 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=pxc:123456 innodb_autoinc_lock_mode=2
#pxc02的my.cnf
[root@pxc02 ~]# vi /mysql/3306/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.134,192.168.174.135,192.168.174.136 wsrep_node_name=pxc02 wsrep_node_address=192.168.174.135 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=pxc:123456 innodb_autoinc_lock_mode=2
#pxc03的my.cnf
[root@pxc03 ~]# vi /mysql/3306/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.134,192.168.174.135,192.168.174.136 wsrep_node_name=pxc03 wsrep_node_address=192.168.174.136 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=pxc:123456 innodb_autoinc_lock_mode=2
（6）三台节点均修改环境变量
[root@pxc01 ~]# su - mysql [mysql@pxc01 ~]$ vi .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin 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 [mysql@pxc02 ~]$ mysqld --defaults-file=/mysql/3306/my.cnf --initialize [mysql@pxc03 ~]$ mysqld --defaults-file=/mysql/3306/my.cnf --initialize
（8）启动PXC01节点
[mysql@pxc01 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf --wsrep-new-cluster & [1] 30048 [mysql@pxc01 ~]$ 2021-06-03T16:18:07.455117Z mysqld_safe Logging to ‘/mysql/3306/logs/alert_3306.log‘. 2021-06-03T16:18:07.457526Z mysqld_safe Logging to ‘/mysql/3306/logs/alert_3306.log‘. 2021-06-03T16:18:07.477333Z mysqld_safe Starting mysqld daemon with databases from /mysql/3306/data 2021-06-03T16:18:07.481542Z mysqld_safe Skipping wsrep-recover for empty datadir: /mysql/3306/data 2021-06-03T16:18:07.483190Z mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position [mysql@pxc01 ~]$ grep ‘temporary password‘ /mysql/3306/logs/alert_3306.log //查看数据库初始密码 2021-06-03T16:17:04.482780Z 1 [Note] A temporary password is generated for root@localhost: aSU3Ejl*gW9d [mysql@pxc01 ~]$ mysql -uroot -paSU3Ejl*gW9d 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 4 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.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.01 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: 48a7b416-c487-11eb-895c-86ff02b78180 seqno: -1 safe_to_bootstrap: 1
（9）启动节点pxc02，pxc03
pxc02与pxc03不要同时启动，待pxc02同步完成后再启动pxc03
[mysql@pxc02 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf & [1] 29666 [mysql@pxc02 ~]$ 2021-06-07T16:05:00.862733Z mysqld_safe Logging to ‘/mysql/3306/logs/alert_3306.log‘. 2021-06-07T16:05:00.866431Z mysqld_safe Logging to ‘/mysql/3306/logs/alert_3306.log‘. 2021-06-07T16:05:00.889102Z mysqld_safe Starting mysqld daemon with databases from /mysql/3306/data 2021-06-07T16:05:00.894387Z mysqld_safe Skipping wsrep-recover for empty datadir: /mysql/3306/data 2021-06-07T16:05:00.895966Z mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position [root@pxc02 ~]# cat /mysql/3306/logs/alert_3306.log |grep pxc02 2021-06-07T16:05:02.136435Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 1daf7b0d-c7aa-11eb-87af-bf4d59ef2506 from 0 (pxc02) 2021-06-07T16:05:02.893714Z 0 [Note] WSREP: Member 0.0 (pxc02) requested state transfer from ‘*any*‘. Selected 1.0 (pxc01)(SYNCED) as donor. 2021-06-07T16:05:14.820846Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 253f141c-c7aa-11eb-b794-c62fbd17c5a9 from 0 (pxc02) 2021-06-07T16:05:15.527383Z 0 [Note] WSREP: 1.0 (pxc01): State transfer to 0.0 (pxc02) complete. 2021-06-07T16:05:20.141339Z 0 [Note] WSREP: 0.0 (pxc02): State transfer from 1.0 (pxc01) complete. 2021-06-07T16:05:20.141674Z 0 [Note] WSREP: Member 0.0 (pxc02) synced with group
[mysql@pxc03 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf & [root@pxc03 ~]# cat /mysql/3306/logs/alert_3306.log |grep pxc03 2021-06-07T16:51:25.056710Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 253f141c-c7aa-11eb-b794-c62fbd17c5a9 from 2 (pxc03) 2021-06-07T16:51:25.801568Z 0 [Note] WSREP: Member 2.0 (pxc03) requested state transfer from ‘*any*‘. Selected 1.0 (pxc01)(SYNCED) as donor. 2021-06-07T16:51:37.998959Z 0 [Note] WSREP: 1.0 (pxc01): State transfer to 2.0 (pxc03) complete. 2021-06-07T16:51:41.838939Z 0 [Note] WSREP: 2.0 (pxc03): State transfer from 1.0 (pxc01) complete. 2021-06-07T16:51:41.840239Z 0 [Note] WSREP: Member 2.0 (pxc03) synced with group. 2021-06-25T13:40:11.614117Z mysqld_safe WSREP: Running position recovery with --log_error=‘/mysql/3306/data/wsrep_recovery.1ovT53‘ --pid-file=‘/mysql/3306/data/pxc03-recover.pid‘ 2021-06-25T13:43:02.829414Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 6517e2a7-c7ab-11eb-902a-d230b749461f from 1 (pxc03) 2021-06-25T13:43:02.835571Z 0 [Note] WSREP: Member 1.0 (pxc03) synced with group.
（9）验证数据同步
在pxc03下创建数据库ljb,表t1,并插入数据
[mysql@pxc03 ~]$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.33-36-49-log Percona XtraDB Cluster binary (GPL) 5.7.33-rel36-49, Revision a1ed9c3, wsrep_31.49 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> create database ljb; Query OK, 1 row affected (0.01 sec) mysql> use ljb; Database changed mysql> create table t1(id int primary key,name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> insert into t1(id,name) values(1,‘xiaoming‘); Query OK, 1 row affected (0.02 sec)
在pxc01,pxc02上查看表t1的数据
[mysql@pxc01 ~]$ mysql -p
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ljb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> use ljb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t1; +----+----------+ | id | name | +----+----------+ | 1 | xiaoming | +----+----------+ 1 row in set (0.00 sec)
[mysql@pxc02 ~]$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.33-36-49-log Percona XtraDB Cluster binary (GPL) 5.7.33-rel36-49, Revision a1ed9c3, wsrep_31.49 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>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ljb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use ljb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t1; +----+----------+ | id | name | +----+----------+ | 1 | xiaoming | +----+----------+ 1 row in set (0.00 sec)
3 加入一个新节点（192.168.174.138）
计划：
选用192.168.174.136 节点先与192.168.174.138组成主从节点
再转成集群节点
（1）新节点需关闭防火墙及修改主机名
[root@localhost ~]# systemctl stop firewalld [root@localhost ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service. [root@localhost ~]# setenforce 0 [root@localhost ~]# sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/g‘ /etc/selinux/config
[root@localhost ~]# hostnamectl set-hostname pxc04.
[root@localhost ~]# bash
（2）新节点修改内核参数
[root@pxc04 ~]# vi /etc/security/limits.conf mysql soft nproc 65535 mysql hard nproc 65535 mysql soft nofile 65535 mysql hard nofile 65535
（3）新节点均创建用户及用户组
[root@pxc04 ~]# groupadd mysql [root@pxc04 ~]# useradd -g mysql mysql [root@pxc04 ~]# mkdir -p /mysql/3306/{data,redo,binlog,tmp,logs} [root@pxc04 ~]# chown -R mysql.mysql /mysql/3306 [root@pxc04 ~]# chmod -R 755 /mysql/3306
（4）新节点均安装xtrabackup
[root@pxc04 ~]# 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 [root@pxc04 ~]# tar xf percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz -C /usr/local/ [root@pxc04 ~]# cd /usr/local/ [root@pxc04 local]# mv percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12/ xtrabackup [root@pxc04 local]# chown -R mysql:mysql /usr/local/xtrabackup/ [root@pxc04 local]# chmod -R 755 /usr/local/xtrabackup/
（5）新节点均安装pxc
[root@pxc04 ~]# tar xf Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12.tar.gz -C /usr/local/ [root@pxc04 ~]# mv /usr/local/Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12/ /usr/local/mysql [root@pxc04 ~]# chown -R mysql.mysql /usr/local/mysql/ [root@pxc04 ~]# chmod -R 755 /usr/local/mysql/
（6）新节点均安装依赖
[root@pxc04 ~]# yum install -y epel-release
[root@pxc04 ~]# 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
（7）新节点均创建my.cnf
[root@pxc04 ~]# vi /mysql/3306/my.cnf [client] default-character-set = utf8 port = 3306 socket = /mysql/3306/tmp/mysql.sock [mysqld] port = 3306 character_set_server = utf8 server-id = 103 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
（8）新节点修改环境变量
[root@pxc04 ~]# su - mysql [mysql@pxc04 ~]$ vi .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH=/usr/local/mysql/bin:$PATH:/usr/local/xtrabackup/bin [mysql@pxc04 ~]$ source .bash_profile
（9）新节点初始化数据库
[mysql@pxc04 ~]$ mysqld --defaults-file=/mysql/3306/my.cnf --initialize
（10）启动PXC04节点
[mysql@pxc04 ~]$ 2021-06-16T10:19:11.156954Z mysqld_safe Logging to ‘/mysql/3306/logs/alert_3306.log‘. 2021-06-16T10:19:11.158709Z mysqld_safe Logging to ‘/mysql/3306/logs/alert_3306.log‘. 2021-06-16T10:19:11.175922Z mysqld_safe Starting mysqld daemon with databases from /mysql/3306/data 2021-06-16T10:19:11.179544Z mysqld_safe Skipping wsrep-recover for empty datadir: /mysql/3306/data 2021-06-16T10:19:11.180534Z mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position [mysql@pxc04 ~]$ grep ‘temporary password‘ /mysql/3306/logs/alert_3306.log 2021-06-16T10:18:03.347960Z 1 [Note] A temporary password is generated for root@localhost: Gtl9,LkkM:?S [mysql@pxc04 ~]$ mysql -uroot -pGtl9,LkkM:?S 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 2 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.00 sec) mysql> quit
（11）pxc03 节点创建主从同步账号：
[mysql@pxc03 ~]$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.33-36-49-log Percona XtraDB Cluster binary (GPL) 5.7.33-rel36-49, Revision a1ed9c3, wsrep_31.49 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> create user ‘bak‘@‘192.168.174.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.02 sec) mysql> grant replication slave on *.* to ‘bak‘@‘192.168.174.%‘; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
（12）150数据拷贝至151上
[mysql@pxc03 ~]$ mysqldump --single-transaction -uroot -p123456 -A --master-data=2 > all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don‘t want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [mysql@pxc03 ~]$ scp all.sql root@192.168.174.138:/opt/ The authenticity of host ‘192.168.174.138 (192.168.174.138)‘ can‘t be established. ECDSA key fingerprint is c8:f8:b5:d8:f9:40:25:fb:24:ba:25:ab:70:0f:d9:1b. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added ‘192.168.174.138‘ (ECDSA) to the list of known hosts. root@192.168.174.138‘s password: all.sql 100% 855KB 854.6KB/s 00:00
（13）151上恢复数据
[mysql@pxc04 ~]$ mysql -p123456 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 4 Server version: 5.7.33-36-49-log Percona XtraDB Cluster binary (GPL) 5.7.33-rel36-49, Revision a1ed9c3, wsrep_31.49 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> source /opt/all.sql
（14）pxc04配置主从同步
查看pxc03主库
mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000007 | 1422 | | | b7584be9-3b78-ee14-76a3-7900fd487e7f:1-9 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
配置从服务器上的连接
mysql> change master to master_host=‘192.168.174.136‘,master_user=‘bak‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000007‘,master_log_pos=1422; Query OK, 0 rows affected, 2 warnings (0.02 sec)
开启同步
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.174.136 Master_User: bak Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 1422 Relay_Log_File: pxc04-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes
主从配置完成
（15）配置集群
修改my.cnf
[root@pxc04 ~]# vi /mysql/3306/my.cnf wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_name=pxc-cluster wsrep_cluster_address=gcomm://192.168.174.134,192.168.174.135,192.168.174.136,192.168.174.138 wsrep_node_name=pxc04 wsrep_node_address=192.168.174.138 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=pxc:123456 innodb_autoinc_lock_mode=2
在pxc03复制文件到pxc04
[root@pxc03 ~]# scp /mysql/3306/data/grastate.dat root@192.168.174.138:/mysql/3306/data/ The authenticity of host ‘192.168.174.138 (192.168.174.138)‘ can‘t be established. ECDSA key fingerprint is c8:f8:b5:d8:f9:40:25:fb:24:ba:25:ab:70:0f:d9:1b. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added ‘192.168.174.138‘ (ECDSA) to the list of known hosts. root@192.168.174.138‘s password: grastate.dat 100% 113 0.1KB/s 00:00
[root@pxc04 ~]# chown mysql:mysql /mysql/3306/data/grastate.dat
重启mysql
（16）pxc04查看测试结果：
mysql> show status like ‘wsrep%‘; +----------------------------------+-------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+-------------------------------------------------------------------------------------+ | wsrep_local_state_uuid | 5dc96d29-c47f-11eb-b31c-ef4d300b74fe | | wsrep_protocol_version | 9 | | wsrep_last_applied | 12 | | wsrep_last_committed | 12 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 2 | | wsrep_received_bytes | 360 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_max | 1 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.000000 | | wsrep_local_cached_downto | 0 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_interval | [ 200, 200 ] | | wsrep_flow_control_interval_low | 200 | | wsrep_flow_control_interval_high | 200 | | wsrep_flow_control_status | OFF | | wsrep_flow_control_active | false | | wsrep_flow_control_requested | false | | wsrep_cert_deps_distance | 0.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 0 | | wsrep_cert_bucket_count | 22 | | wsrep_gcache_pool_size | 1320 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_open_transactions | 0 | | wsrep_open_connections | 0 | | wsrep_ist_receive_status | | | wsrep_ist_receive_seqno_start | 0 | | wsrep_ist_receive_seqno_current | 0 | | wsrep_ist_receive_seqno_end | 0 | | wsrep_incoming_addresses | 192.168.174.134:3306,192.168.174.136:3306,192.168.174.138:3306,192.168.174.135:3306 | | wsrep_cluster_weight | 4 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0.000642634/0.00250936/0.00608401/0.00252849/3 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | ccc74f2a-d6cb-11eb-85f1-aa2e9bbb89fe | | wsrep_gmcast_segment | 0 | | wsrep_cluster_conf_id | 8 | | wsrep_cluster_size | 4 | | wsrep_cluster_state_uuid | 5dc96d29-c47f-11eb-b31c-ef4d300b74fe | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 2 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 3.49(r0ef0d79) | | wsrep_ready | ON | +----------------------------------+-------------------------------------------------------------------------------------+ 74 rows in set (0.00 sec)