PXC基于docker搭建mysql集群

说明

Percona XtraDB Cluster(简称PXC)，是由percona公司推出的mysql集群解决方案。特点是每个节点都能进行读写，且都保存全量的数据。也就是说在任何一个节点进行写入操作，都会同步给其它所有节点写入到自己的磁盘。

环境

三台机器，设置/etc/hosts

10.196.69.88 node88
10.196.69.90 node90
10.196.69.92 node92

安装docker和安装docker-compose

省略，具体参考一键部署脚本。

Docker version 20.10.17
docker-compose version 1.29.2

PXC集群安装

1.镜像拉取

Docker的镜像仓库中包含了PXC数据库的镜像，地址：
https://hub.docker.com/r/percona/percona-xtradb-cluster

因生产为内网，后续根据内网镜像仓库拉取对应版本，当前使用版本为：percona/percona-xtradb-cluster:8.0.36

2.创建证书

percona集群要求必须安装证书，集群实现加密通讯，参考：https://docs.percona.com/percona-xtradb-cluster/8.0/docker.html

mkdir -m 777 -p /AppHome/docker/mysql-cluster/config
mkdir -m 777 -p /AppHome/docker/mysql-cluster/cert
mkdir -m 777 -p /AppHome/docker/mysql-cluster/logs
mkdir -m 777 -p /AppHome/docker/mysql-cluster/data


#创建custom.cnf文件到config
/AppHome/docker/mysql-cluster/config
vim custom.cnf
# 内容如下:
[mysqld]
ssl-ca = /cert/ca.pem
ssl-cert = /cert/server-cert.pem
ssl-key = /cert/server-key.pem

[client]
ssl-ca = /cert/ca.pem
ssl-cert = /cert/client-cert.pem
ssl-key = /cert/client-key.pem

[sst]
encrypt = 4
ssl-ca = /cert/ca.pem
ssl-cert = /cert/server-cert.pem
ssl-key = /cert/server-key.pem

生成证书

# 其中一台机器执行
docker run --name pxc-cert --rm -v /AppHome/docker/mysql-cluster/cert:/cert \
reg-hub.gzeport.com/tools/percona/percona-xtradb-cluster:8.0.36 mysql_ssl_rsa_setup -d /cert

# 设置证书对应权限
chown -R 1001:1001 /AppHome/docker/mysql-cluster/cert

# 拷贝到其他节点机器
scp /AppHome/docker/mysql-cluster/cert/* node90:/AppHome/docker/mysql-cluster/cert/
scp /AppHome/docker/mysql-cluster/cert/* node92:/AppHome/docker/mysql-cluster/cert/

证书必须所有节点都一致！在一台机器生成后拷贝到其他机器。

3.搭建pxc集群

节点1启动pxc

docker run -d \
--privileged \
--restart always \
--name=mysql-node88 \
--net=host \
-e TZ=Asia/Shanghai \
-e CLUSTER_NAME=PXC \
-e MYSQL_ROOT_PASSWORD=passwd \
-e XTRABACKUP_PASSWORD=passwd \
-e EXTRABACKUP_PASSWROD=passwd \
-v /AppHome/docker/mysql-cluster/data:/var/lib/mysql \
-v /AppHome/docker/mysql-cluster/logs:/var/log/mysql \
-v /AppHome/docker/mysql-cluster/cert:/cert \
-v /AppHome/docker/mysql-cluster/config:/etc/percona-xtradb-cluster.conf.d \
reg-hub.gzeport.com/tools/percona/percona-xtradb-cluster:8.0.36

节点2 加入pxc集群

docker run -d \
--privileged \
--restart always \
--name=mysql-node90 \
--net=host \
-e TZ=Asia/Shanghai \
-e CLUSTER_NAME=PXC \
-e CLUSTER_JOIN=10.196.69.88 \
-e MYSQL_ROOT_PASSWORD=passwd \
-e XTRABACKUP_PASSWORD=passwd \
-e EXTRABACKUP_PASSWROD=passwd \
-v /AppHome/docker/mysql-cluster/data:/var/lib/mysql \
-v /AppHome/docker/mysql-cluster/logs:/var/log/mysql \
-v /AppHome/docker/mysql-cluster/cert:/cert \
-v /AppHome/docker/mysql-cluster/config:/etc/percona-xtradb-cluster.conf.d \
reg-hub.gzeport.com/tools/percona/percona-xtradb-cluster:8.0.36

**注意 **

--name=mysql-node90

-e CLUSTER_JOIN=10.196.69.88

节点3 加入pxc集群

docker run -d \
--privileged \
--restart always \
--name=mysql-node92 \
--net=host \
-e TZ=Asia/Shanghai \
-e CLUSTER_NAME=PXC \
-e CLUSTER_JOIN=10.196.69.88 \
-e MYSQL_ROOT_PASSWORD=passwd \
-e XTRABACKUP_PASSWORD=passwd \
-e EXTRABACKUP_PASSWROD=passwd \
-v /AppHome/docker/mysql-cluster/data:/var/lib/mysql \
-v /AppHome/docker/mysql-cluster/logs:/var/log/mysql \
-v /AppHome/docker/mysql-cluster/cert:/cert \
-v /AppHome/docker/mysql-cluster/config:/etc/percona-xtradb-cluster.conf.d \
reg-hub.gzeport.com/tools/percona/percona-xtradb-cluster:8.0.36

**注意 **

--name=mysql-node92

-e CLUSTER_JOIN=10.196.69.88

4.搭建验证

日志查看

sql查看当前加入节点

show status like 'wsrep%';
show status like '%wsrep_cluster%';
select * from performance_schema.pxc_cluster_view;

5.验证过程存在问题

PXC集群注意事项

1.从节点启动之后闪退

第一种情况是由于主节点还没有启动，就启动了从节点，那这个时候从节点肯定是闪退的。
所以需要先启动主节点，再启动从节点。

2.从节点启动之后闪退

第二种情况，是由于PXC在退出的时候会给集群中最后退出的那台打上标记，当集群重新启动的时候需要先启动最后关闭的那台节点机器。(最后关闭的变为主节点)

可查看data/grastate.data文件内容，safe_to_bootstrap: 1为必须先启动的节点，后续节点需要加入到此ip。

3.主节点启动之后闪退

当主节点突然宕机，从节点都正常运行，这时候主节点不能按照主节点启动。

这时候需要删除主节点的容器，检查数据卷上的safe_to_bootstrap是否为0，如果不是则改为0。然后以从节点的方式创建容器，加入集群。可以使用任何PXC节点，然后从节点加入PXC集群即可。

如模拟node88主节点故障

docker stop mysql-node88
# 模拟重新重启
docker start mysql-node88
##########启动出差，错误日志#########################################################
2024-04-12T00:54:37.645683Z 0 [Note] [MY-000000] [Galera] Service thread queue flushed.
2024-04-12T00:54:37.645766Z 0 [Note] [MY-000000] [Galera] ####### Assign initial position for certification: b8cea80c-f862-11ee-a576-570e9e18be0c:39, protocol version: -1
2024-04-12T00:54:37.645872Z 0 [Note] [MY-000000] [WSREP] Starting replication
2024-04-12T00:54:37.645905Z 0 [Note] [MY-000000] [Galera] Connecting with bootstrap option: 1
2024-04-12T00:54:37.645915Z 0 [Note] [MY-000000] [Galera] Setting GCS initial position to b8cea80c-f862-11ee-a576-570e9e18be0c:39
2024-04-12T00:54:37.645924Z 0 [ERROR] [MY-000000] [Galera] It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .
2024-04-12T00:54:37.645934Z 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://) failed to establish connection with cluster (reason: 7)
2024-04-12T00:54:37.645943Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-04-12T00:54:37.646355Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-28.1)  Percona XtraDB Cluster (GPL), Release rel28, Revision bfb687f, WSREP version 26.1.4.3.
2024-04-12T00:54:37.647783Z 0 [ERROR] [MY-010065] [Server] Failed to shutdown components infrastructure.
2024-04-12T00:54:37.648029Z 0 [Note] [MY-000000] [Galera] dtor state: CLOSED
2024-04-12T00:54:37.648056Z 0 [Note] [MY-000000] [Galera] MemPool(TrxHandleSlave): hit ratio: 0, misses: 0, in use: 0, in pool: 0
2024-04-12T00:54:37.649795Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
2024-04-12T00:54:37.652215Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
2024-04-12T00:54:37.654757Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
2024-04-12T00:54:37.654783Z 0 [Note] [MY-000000] [Galera] cert index usage at exit 0
2024-04-12T00:54:37.654790Z 0 [Note] [MY-000000] [Galera] cert trx map usage at exit 0
2024-04-12T00:54:37.654794Z 0 [Note] [MY-000000] [Galera] deps set usage at exit 0
2024-04-12T00:54:37.654805Z 0 [Note] [MY-000000] [Galera] avg deps dist 0
2024-04-12T00:54:37.654813Z 0 [Note] [MY-000000] [Galera] avg cert interval 0

# 删除原来容器
docker rm -f mysql-node88

# 查找存活节点，如mysql-node92是存活节点，加入即可
docker run -d \
--privileged \
--restart always \
--name=mysql-node88 \
--net=host \
-e TZ=Asia/Shanghai \
-e CLUSTER_NAME=PXC \
-e CLUSTER_JOIN=10.196.69.92 \
-e MYSQL_ROOT_PASSWORD=passwd \
-e XTRABACKUP_PASSWORD=passwd \
-e EXTRABACKUP_PASSWROD=passwd \
-v /AppHome/docker/mysql-cluster/data:/var/lib/mysql \
-v /AppHome/docker/mysql-cluster/logs:/var/log/mysql \
-v /AppHome/docker/mysql-cluster/cert:/cert \
-v /AppHome/docker/mysql-cluster/config:/etc/percona-xtradb-cluster.conf.d \
reg-hub.gzeport.com/tools/percona/percona-xtradb-cluster:8.0.36

#基本上节点恢复同步。

数据库负载均衡

参考使用Haproxy做负载均衡官网：https://docs.percona.com/percona-xtradb-cluster/8.0/haproxy.html

暂未部署。

数据库备份

正在验证中。

参考

1.https://docs.percona.com/percona-xtradb-cluster/8.0

2.https://blog.csdn.net/dh_2017/article/details/108045552

3.https://blog.51cto.com/u_13482808/7543833