mysql pxc高可用离线部署（三）_percona-xtradb-cluster prohibits use of dml comman-CSDN博客

本文链接：https://blog.csdn.net/qq_36120342/article/details/134810716

pxc学习流程

mysql pxc高可用单主机多主机部署（一）
mysql pxc 高可用多主机离线部署（二）
mysql pxc高可用离线部署（三）

mysql pxc高可用

在这里插入图片描述

跨主机部署pxc

本文使用docker进行安装，主机间通过docker swarm网络进行通讯。不了解Docker的请自行先网上看以下教程入门一下。这里没有深入的Docker知识，只要跟着命令做就可以了。

三台主机都安装好了docker：

虚拟ip	主机	IP
	node1	192.168.56.100
192.168.56.120	node2	192.168.56.101
	node3	192.168.56.102

node1 开放防火墙

1）TCP端口2377
该端口用于Docker集群或集群中各节点之间的通信。

它只需要在管理器节点上打开。

2）TCP和UDP端口7946
该端口用于节点之间的通信(容器网络发现)。

3）UDP端口4789
此端口用于覆盖网络流量(集装箱接入网络)。

在node1 开放防火墙：

hostnamectl set-hostname node1 && bash
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.101" accept"
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.102" accept"
firewall-cmd --reload
firewall-cmd --list-all

在node2 开放防火墙：

hostnamectl set-hostname node2 && bash
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.100" accept"
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.102" accept"

firewall-cmd --reload
firewall-cmd --list-all

在node3 开放防火墙：

hostnamectl set-hostname node3 && bash
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.101" accept"
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.100" accept"

firewall-cmd --reload
firewall-cmd --list-all

1. 在3台主机上配置swarm

创建 /home/pxc

mkdir -p /home/pxc && cd /home/pxc

pxcPackage.zip下载地址
链接：https://pan.baidu.com/s/1pbzlkx5U3BhnoWiuxqJGOg?pwd=r1dx
3台机器分别将pxcPackage.zip拷贝到 /home/pxc
在node1上执行：

docker swarm init --advertise-addr 192.168.56.100
docker swarm join-token manager

返回类似以下内容：

docker swarm join --token SWMTKN-1-614xi9dvksycykobgifxb4pgopc1wwgczwqct5wqkq8zao6tmx-0ds4jj3ozclrr2wukcaoakxso 192.168.56.100:2377

在node2、node3上执行上面的返回结果：

docker swarm join --token SWMTKN-1-2c2xopn2rld8oltcof24sue370681ijhbo3bwcqarjlhq9lkea-2g53o5qn2anre4j9puv4hecrn 192.168.0.101:2377

2. 创建swarm网络

在node1上执行以下命令：

docker network create -d overlay --attachable pxc_swarm

3. 3台主机导入PXC镜像

cd /home/pxc/images && bash images-load.sh

4. 创建文件

在3台服务器上分别执行以下命令：

chmod +777 /home/pxc/data

5. 安装第一个节点

node1执行

docker run -d  -v /home/pxc/data:/var/lib/mysql -v /home/pxc/conf/my.cnf:/etc/mysql/my.cnf -v /etc/localtime:/etc/localtime:ro --name node1 -e CLUSTER_NAME=scsdm_pxc -e MYSQL_ROOT_PASSWORD=hancloud1234! -e MYSQL_DATABASE=scsdm -e MYSQL_USER=scsdm -e MYSQL_PASSWORD=scsdm123 --net=pxc_swarm --privileged --restart=always percona/percona-xtradb-cluster:5.7.27

docker-compose.yml

cat > /home/pxc/docker-compose.yml << EOF
version: '3'
services:
  node1:
    image: percona/percona-xtradb-cluster:5.7.27
    hostname: node1
    container_name: node1
    restart: always
    networks:
      - pxc_swarm
    environment:
      - CLUSTER_NAME=scsdm_pxc
      - MYSQL_ROOT_PASSWORD=hancloud1234!
      - MYSQL_DATABASE=scsdm
      - MYSQL_USER=scsdm
      - MYSQL_PASSWORD=scsdm123
    volumes:
      - /home/pxc/data:/var/lib/mysql
      - /home/pxc/conf/my.cnf:/etc/mysql/my.cnf
      - /etc/localtime:/etc/localtime:ro
    privileged: true

networks:
  pxc_swarm:
    external: true
EOF
docker-compose up -d

注意自行修改密码，不要使用过于简单的密码。参数说明：

CLUTER_NAME: 集群名称
MYSQL_ROOT_PASSWORD: root密码
MYSQL_DATABASE: 默认初始化数据库名
MYSQL_USER: 默认初始化账号
MYSQL_PASSWORD: 默认初始化密码

docker logs -f node1 
# 出现下面的内容代表启动成功
2023-12-05T09:03:29.104535+08:00 1 [Note] WSREP: Setting wsrep_ready to true
2023-12-05T09:03:29.104539+08:00 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2023-12-05T09:03:29.185599+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 231205  9:03:29

6. 加入其他节点

node2(192.168.56.101):

docker run -d -v /home/pxc/data:/var/lib/mysql  -v /home/pxc/conf/my.cnf:/etc/mysql/my.cnf -v /etc/localtime:/etc/localtime:ro --name node2 -e CLUSTER_NAME=scsdm_pxc -e CLUSTER_JOIN=node1 -e MYSQL_ROOT_PASSWORD=hancloud1234! -e MYSQL_DATABASE=scsdm -e MYSQL_USER=scsdm -e MYSQL_PASSWORD=scsdm123 --net=pxc_swarm --privileged --restart=always percona/percona-xtradb-cluster:5.7.27

docker-compose.yml

cat > /home/pxc/docker-compose.yml << EOF
version: '3'
services:
  node2:
    image: percona/percona-xtradb-cluster:5.7.27
    hostname: node2
    container_name: node2
    restart: always
    networks:
      - pxc_swarm
    environment:
      - CLUSTER_JOIN=node1
      - CLUSTER_NAME=scsdm_pxc
      - MYSQL_ROOT_PASSWORD=hancloud1234!
      - MYSQL_DATABASE=scsdm
      - MYSQL_USER=scsdm
      - MYSQL_PASSWORD=scsdm123
    volumes:
      - /home/pxc/data:/var/lib/mysql
      - /home/pxc/conf/my.cnf:/etc/mysql/my.cnf
      - /etc/localtime:/etc/localtime:ro
    privileged: true

networks:
  pxc_swarm:
    external: true
EOF
docker-compose up -d

node3(192.168.56.102):

docker run -d -v /home/pxc/data:/var/lib/mysql  -v /home/pxc/conf/my.cnf:/etc/mysql/my.cnf -v /etc/localtime:/etc/localtime:ro --name node3 -e CLUSTER_NAME=scsdm_pxc -e CLUSTER_JOIN=node1 -e MYSQL_ROOT_PASSWORD=hancloud1234! -e MYSQL_DATABASE=scsdm -e MYSQL_USER=scsdm -e MYSQL_PASSWORD=scsdm123 --net=pxc_swarm --privileged --restart=always percona/percona-xtradb-cluster:5.7.27

docker-compose.yml

cat > /home/pxc/docker-compose.yml << EOF
version: '3'
services:
  node3:
    image: percona/percona-xtradb-cluster:5.7.27
    hostname: node3
    container_name: node3
    restart: always
    networks:
      - pxc_swarm
    environment:
      - CLUSTER_JOIN=node1
      - CLUSTER_NAME=scsdm_pxc
      - MYSQL_ROOT_PASSWORD=hancloud1234!
      - MYSQL_DATABASE=scsdm
      - MYSQL_USER=scsdm
      - MYSQL_PASSWORD=scsdm123
    volumes:
      - /home/pxc/data:/var/lib/mysql
      - /home/pxc/conf/my.cnf:/etc/mysql/my.cnf
      - /etc/localtime:/etc/localtime:ro
    privileged: true

networks:
  pxc_swarm:
    external: true
EOF
docker-compose up -d

7. 主机启动haproxy服务

在node1服务器上运行以下命令：

# pxc节点挂掉重启后，先执行删除haproxy，再执行create（重启后之前的代理找不到服务）
# docker service rm haproxy
docker service create --replicas 3 --hostname haproxy -p 3306:3306 -p 8888:8888 --mount type=bind,source=/home/pxc/haproxy/haproxy.cfg,destination=/usr/local/etc/haproxy/haproxy.cfg --mount type=bind,source=/etc/localtime,destination=/etc/localtime:ro --name haproxy --network=pxc_swarm haproxy:alpine

8.安装keepalive 3台都安装

安装依赖

cd /home/pxc/installKeepalived && chmod +x installKeepalived.sh && ./installKeepalived.sh

修改 keepalive配置

cp  /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.confbak 

cat > /etc/keepalived/keepalived.conf << EOF
global_defs {
   router_id LVS_2
   script_user root                    # 脚本执行用户
   enable_script_security              # 开启脚本安全权限
}

vrrp_script checkhaproxy
{
    script "/home/pxc/installKeepalived/check-haproxy.sh"  # 这个地址写的事容器内脚本路径
    interval 2
    weight -30
}

vrrp_instance VI_1 {
    state BACKUP
    interface enp0s3
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt
    
    virtual_ipaddress {
      # 需要修改成对应的虚拟ip
        192.168.56.120
    }

    authentication {
        auth_type PASS
        auth_pass password
    }

    track_script {
        checkhaproxy
    }
}
EOF

启动

service keepalived start

设置开机自启

chkconfig keepalived on
systemctl list-unit-files | grep keepalived

9. 连接数据库

对外：

使用虚拟ip 192.168.56.120 3306

访问 http://192.168.56.120:8888/dbs 进行监控

对内运维:

可以通过连接192.168.56.100 3306 192.168.56.101 3306 192.168.56.102 3306进行数据库操作了

访问 http://192.168.56.100:8888/dbs http://192.168.56.101:8888/dbs http://192.168.56.102:8888/dbs 分别进行监控

账号 admin

密码 admin

在这里插入图片描述

10.数据迁移

导出数据

mysqldump -u root -p mcp_manager --skip_add_locks --skip-lock-tables > mcp_manager_20220118.sql

FAQ:

常用命令

Docker-Swarm集群管理

1、查看swarm集群节点情况

在manager节点查看

docker node ls

在这里插入图片描述

2、查看网络情况

docker network ls

在这里插入图片描述

3、退出swarm集群

在需要退出的节点执行

docker swarm leave -f

在这里插入图片描述

在manager节点查看

docker node ls

在这里插入图片描述

4、重新加入swarm集群

在manager节点查看加入命令

docker swarm join-token worker

在这里插入图片描述

查看节点情况

docker node ls

在这里插入图片描述

5、删除无用的节点

docker node rm 节点   【强制移除运行中的节点】
docker node rm xlpm9aemu7psw5j7mi7udpbqq

在这里插入图片描述

查看节点情况

docker node ls

在这里插入图片描述

6、查看keepalived日志

tail -f /var/log/messages

在这里插入图片描述

常见错误

1、node1 pxc 出现错误日志

在这里插入图片描述

修改方法：

修改配置文件edit the grastate.dat file manually and set safe_to_bootstrap to 1 .

在这里插入图片描述

修改后

在这里插入图片描述

再次重启node1

在这里插入图片描述

2、浏览监控页面查看节点情况

访问 http://192.168.56.120:8888/dbs 进行监控

账号密码都是admin

正常情况

在这里插入图片描述

异常情况

在这里插入图片描述

恢复流程

检查服务器是否启动–>检查docker是否启动–>检查docker服务是否启动–>检查服务日志是否正常–>查看监控界面是否恢复

1、检查服务器是否启动

开启服务器

2、检查docker是否启动

在这里插入图片描述

启动docker

systemctl restart docker
systemctl status docker

3、检查docker服务是否启动

docker ps

在这里插入图片描述

docker ps -a

在这里插入图片描述

启动服务

docker start 179
docker start a79

在这里插入图片描述

访问对应node3机器的监控界面

http://192.168.56.102:8888/dbs

在这里插入图片描述

其他节点的监控界面

在这里插入图片描述

重启对应的节点的haproxy

docker restart haproxy01
docker restart haproxy02

集群恢复正常

3、 with pxc_strict_mode = ENFORCING

在这里插入图片描述

Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE
WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING

导出sql的时候执行

 mysqldump -u root -p database --skip_add_locks --skip-lock-tables > mcp_manager_20220118.sql

追加 --skip_add_locks --skip-lock-tables

4、unblock with ‘mysqladmin flush-hosts’"

在haproxy日志中发现

在这里插入图片描述

Server mysql/s1 is DOWN, reason: Layer7 wrong status, code: 1129, info: “Host ‘10.0.0.28’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’”, check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.

去pxc 节点都去执行

docker exec -it -u root node1 bash -c 'mysqladmin flush-hosts -uroot -phancloud1234!'

5、pxc_strict_mode = ENFORCING or MASTER

在这里插入图片描述

ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (mcp_manager_3_7_6.scs_field_dict) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

执行

show global variables like '%pxc_strict_mode%';
SET GLOBAL pxc_strict_mode=PERMISSIVE;
show global variables like '%pxc_strict_mode%';

6、Got an error reading communication packets

在这里插入图片描述

2022-01-18T08:07:02.521305Z 21053 [Note] Aborted connection 21053 to db: ‘mcp_manager_3_7_6’ user: ‘root’ host: ‘haproxy02.pxc_swarm’ (Got an error reading communication packets)

vi /home/pxc/package/my.cnf
# 修改
max_allowed_packet	= 1024M
innodb_buffer_pool_size=2048M

7、Variable ‘time_zone’ can’t be set to the value of ‘NULL’

在这里插入图片描述

这个问题的原因是因为数据sql文件内部有注释，去掉就可以了(也可以不用管)

/*!40000 ALTER TABLE `t_error_identification` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

在这里插入图片描述

page_cleaner: 1000ms intended loop took 4087ms. The settings might not be optimal. (flushed=897, during the time.)

8、haproxy代理使用一台其他节点做备用

global
    maxconn     4000

defaults
        log     global
        log 127.0.0.1 local3
        mode    http
        option  tcplog
        option  dontlognull
        retries 10
        option redispatch
        maxconn         2000
        timeout connect         10s
        #timeout client          1m
        #timeout server          1m
        timeout http-keep-alive 10s
        timeout check           10s
######## 监控界面配置 #################	
listen  admin_stats
	#监控界面的访问的IP和端口
	bind  0.0.0.0:8888
	#访问协议
    mode        http
	#URI相对地址
    stats uri   /dbs
	#统计报告格式
    stats realm     Global\ statistics
	#登陆帐户信息
    stats auth  admin:admin
    # 隐藏HAProxy的版本号
    stats hide-version
    # 管理界面，如果认证成功了，可通过webui管理节点
    stats admin if TRUE
    # 统计页面自动刷新时间
    stats  refresh  30s
listen  mysql
        bind 0.0.0.0:3306
        mode tcp
        #负载均衡算法（轮询算法）
	    #轮询算法：roundrobin
	    #权重算法：static-rr
	    #最少连接算法：leastconn
	    #请求源IP算法：source 
        balance  roundrobin
        # 监控检查需要一个无密码的账号
        # mysql健康检查  haproxy为mysql登录用户名(需要在实体数据有这个账户,且无密码)
	    # option mysql-check  user haproxy 
        server s1 mysql-master1:3306 check weight 1 maxconn 2000 inter 5000 rise 2 fall 2
        # 主要是下面的backup
        server s2 mysql-master2:3306 check weight 1 maxconn 2000 inter 5000 rise 2 fall 2 backup

	    # 使用keepalive检测死链
        option tcpka