MySQL 8.0使用PXC实现高可用

MySQL 8.0使用PXC实现高可用

1. 环境说明

2. Pre-task preparation

3. PXC安装

4. 验证集群同步状态

5. 环境说明

序号 操作系统 IP 主机名 备注

1 CentOS7.6 192.168.100.33 node1

2 CentOS7.6 192.168.100.22 node2

3 CentOS7.6 192.168.100.55 node3

2. Pre-task preparation

2.1 config hostname、hosts file、ip address

cat /etc/hosts

cat > /etc/hosts > /etc/sysctl.conf > /etc/security/limits.conf

mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘rootPass’;

Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

systemctl stop mysql

[root@node1 ~]# systemctl stop mysql

3.6 编辑配置文件/etc/my.cnf

node1：

[root@node1 ~]# grep -Ev “#|$” /etc/my.cnf

[client]

socket=/var/lib/mysql/mysql.sock

[mysqld]

server-id=333306

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

binlog_expire_logs_seconds=604800

wsrep_provider=/usr/lib64/galera4/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.100.33,192.168.100.22,192.168.100.55

binlog_format=ROW

wsrep_slave_threads=8

wsrep_log_conflicts

innodb_autoinc_lock_mode=2

wsrep_node_address=192.168.100.33

wsrep_cluster_name=pxc-cluster

wsrep_node_name=pxc-cluster-node-1

pxc_strict_mode=ENFORCING

wsrep_sst_method=xtrabackup-v2

wsrep_provider_options=“socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem”

[sst]

encrypt=4

ssl-key=server-key.pem

ssl-ca=ca.pem

ssl-cert=server-cert.pem

其他节点配置

scp /etc/my.cnf 192.168.100.22:/etc/my.cnf

scp /etc/my.cnf 192.168.100.55:/etc/my.cnf

node2：

除了下面这两个参数，其他参数所有节点必须一模一样

server-id=293306

wsrep_node_address=192.168.100.22

wsrep_node_name=pxc-cluster-node-2

node3:

除了下面这两个参数，其他参数所有节点必须一模一样

server-id=283306

wsrep_node_address=192.168.100.55

wsrep_node_name=pxc-cluster-node-3

其他节点配置

scp /etc/my.cnf 192.168.100.22:/etc/my.cnf

scp /etc/my.cnf 192.168.100.55:/etc/my.cnf

3.7 启动第一个节点

需要注意，这样启动的mysql，关闭时也要用systemctl stop mysql@bootstrap.service；而不能用systemctl stop mysql

【首节点初次启动集群】

systemctl start mysql@bootstrap.service

【首节点非初次启动集群】

systemctl start mysql

【其他节点启动】

systemctl start mysql

正常的启动mysql服务命令：

启停：

systemctl start mysql@bootstrap.service

systemctl stop mysql@bootstrap.service

查看状态：

systemctl status mysql@bootstrap.service

[root@node1 ~]# systemctl status mysql@bootstrap.service

?.mysql@bootstrap.service - Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap

Loaded: loaded (/usr/lib/systemd/system/mysql@.service; disabled; vendor preset: disabled)

Active: active (running) since Tue 2024-01-30 20:02:27 CST; 1h 10min ago

Process: 7452 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=0/SUCCESS)

Process: 7450 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

Process: 7342 ExecStartPre=/bin/sh -c VAR=bash /usr/bin/mysql-systemd galera-recovery; [ ? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=VAR || exit 1 (code=exited, status=0/SUCCESS)

Process: 7339 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

Process: 7299 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)

Main PID: 7393 (mysqld)

Status: “Server is operational”

CGroup: /system.slice/system-mysql.slice/mysql@bootstrap.service

?..7393 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=3215d4fa-bf67-11ee-bc30-56f407ead246:3


Jan 30 20:02:25 node1 systemd[1]: Starting Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap…

Jan 30 20:02:27 node1 systemd[1]: Started Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap.

[root@node1 mysqld]# cat /etc/sysconfig/mysql.bootstrap

Arguments to pass to mysql-systemd

For Bootstrapping

You can append any extra options in to the same variable.

EXTRA_ARGS=" --wsrep-new-cluster "

Other environment variables if any

ABC=123 for example.

确保集群已初始化，登录mysql执行命令

节点状态定义

OPEN：节点启动成功

PRIMARY：节点成功加入集群

JOINER：与其他节点同步数据

JOINED：与其他节点同步数据成功

SYNCED：与集群同步完成，可以对外提供服务

DONER：接收其他节点的全量数据同步，处于不可用

最常使用的查看命令

show variables like ‘wsrep%’;

show status like ‘wsrep%’;

mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_size%’;”

[root@node1 sbin]# mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_size%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±-------------------±------+

| Variable_name | Value |

±-------------------±------+

| wsrep_cluster_size | 1 |

±-------------------±------+

mysql -uroot -prootPass -e “show status like ‘wsrep_local_state_comment%’;”

[root@node1 sbin]# mysql -uroot -prootPass -e “show status like ‘wsrep_local_state_comment%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±--------------------------±-------+

| Variable_name | Value |

±--------------------------±-------+

| wsrep_local_state_comment | Synced |

±--------------------------±-------+

[root@node1 mysqld]# mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_status%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±---------------------±--------+

| Variable_name | Value |

±---------------------±--------+

| wsrep_cluster_status | Primary |

±---------------------±--------+

输出显示 wsrep_cluster_size为1 ， wsrep_local_state_comment为Synced状态；

3.5 向集群添加节点(ndoe2,node3)

启动node2节点

systemctl start mysql

启动mysql会报错报错：

2024-01-30T12:04:16.857764Z 0 [ERROR] [MY-000000] [Galera] /mnt/jenkins/workspace/pxc80-autobuild-RELEASE/test/rpmbuild/BUILD/Percona-XtraDB-Cluster-8.0.35/percona-xtradb-cluster-galera/gcs/src/gcs.cpp:gcs_open():1880: Failed to open channel ‘pxc-cluster’ at ‘gcomm://192.168.100.33,192.168.100.22,192.168.100.55’: -110 (Connection timed out)

2024-01-30T12:04:16.857790Z 0 [ERROR] [MY-000000] [Galera] gcs connect failed: Connection timed out

2024-01-30T12:04:16.857806Z 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://192.168.100.33,192.168.100.22,192.168.100.55) failed to establish connection with cluster (reason: 7)

2024-01-30T12:04:16.857827Z 0 [ERROR] [MY-010119] [Server] Aborting

解决：

根据官网资料pxc-encrypt-cluster-traffic变量默认是启用，所以要求所有节点使用相同的密钥和证书文件，MySQL生成默认密钥和证书文件，并将其放在数据目录中。这些自动生成的文件适用于自动SSL配置。所以将第一个节点数据目录下的 *.pem全部复制到第二个节点，再次执行systemctl start mysql命令，节点正常启动。

node1:

[root@node1 mysql]# scp *pem root@192.168.100.22:/var/lib/mysql

[root@node1 mysql]# scp *pem root@192.168.100.55:/var/lib/mysql

node2:

chown -R mysql.mysql /var/lib/mysql

systemctl start mysql

node3:

chown -R mysql.mysql /var/lib/mysql

systemctl start mysql

查看node2状态

systemctl status mysql

检查第二个节点的状态

[root@node2 sbin]# mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_status%’;”

[root@node2 sbin]# mysql -uroot -prootPass -e “show status like ‘wsrep_local_state_comment%’;”

[root@node2 sbin]# mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_size%’;”

输出内容如下：

[root@node2 mysqld]# mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_status%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±---------------------±--------+

| Variable_name | Value |

±---------------------±--------+

| wsrep_cluster_status | Primary |

±---------------------±--------+

[root@node2 mysqld]# mysql -uroot -prootPass -e “show status like ‘wsrep_local_state_comment%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±--------------------------±-------+

| Variable_name | Value |

±--------------------------±-------+

| wsrep_local_state_comment | Synced |

±--------------------------±-------+

[root@node2 mysqld]# mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_size%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±-------------------±------+

| Variable_name | Value |

±-------------------±------+

| wsrep_cluster_size | 1 |

±-------------------±------+

[root@node3 ~]# mysql -uroot -prootPass -e “show status like ‘wsrep_local_state_comment%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±--------------------------±-------+

| Variable_name | Value |

±--------------------------±-------+

| wsrep_local_state_comment | Synced |

±--------------------------±-------+

[root@node3 ~]# mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_status%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±---------------------±--------+

| Variable_name | Value |

±---------------------±--------+

| wsrep_cluster_status | Primary |

±---------------------±--------+

[root@node3 ~]# mysql -uroot -prootPass -e “show status like ‘wsrep_cluster_size%’;”

mysql: [Warning] Using a password on the command line interface can be insecure.

±-------------------±------+

| Variable_name | Value |

±-------------------±------+

| wsrep_cluster_size | 1 |

±-------------------±------+

4. 验证集群同步状态

4.1 登录node1创建数据库

mysql -uroot -p’rootPass’ -e “CREATE DATABASE percona;”

4.2 登录node2查看数据库

mysql -uroot -p’rootPass’ -e “SHOW DATABASES;”

输出

±-------------------+

| Database|

±-------------------+

| information_schema |

| mysql |

| percona |

| performance_schema |

| sys |

±-------------------+

4.3 登录node1创建表

mysql -uroot -p’rootPass’ -e “CREATE TABLE percona.example (node_id INT PRIMARY KEY, node_name VARCHAR(30));”

4.4 登录node2插入数据

mysql -uroot -p’rootPass’ -e “INSERT INTO percona.example VALUES (1, ‘percona1’);”

4.5 在两台mysql分别执行语句查询数据

mysql -uroot -p’rootPass’ -e “SELECT * FROM percona.example;”

输出

±--------±----------+

| node_id | node_name |

±--------±----------+

| 1 | percona1 |

±--------±----------+

本文为实验环境学习笔记，参考链接地址

https://www.cnblogs.com/tigacc/articles/17578378.html

https://docs.percona.com/percona-xtradb-cluster/8.0/index.html

https://liking.site/2021/03/08/percona-xtradb-cluster-8-0-21%E9%83%A8%E7%BD%B2%E5%AE%9E%E6%88%98/