PXC高可用数据库安装部署

2023-07-20 18:10:16

说明

Percona XtraDB Cluster(简称PXC)，是由percona公司推出的mysql集群解决方案。特点是每个节点都能进行读写，且都保存全量的数据。也就是说在任何一个节点进行写入操作，都会同步给其它所有节点写入到自己的磁盘。这点跟Oracle Rac有本质的区别，Rac是多个节点连同一个共享存储，假如Oracle的共享存储挂了，整个集群就挂了。而Mysql pxc中任何一台机器挂了，集群照常运转，因为节点间并不共享磁盘。

写在前面的注意事项(节选自官方手册)

  1. 由于pxc只作用于innodb引擎，而mysql自带的系统库(mysql)里面有些表是MyISAM的存储引擎，因此不能直接对系统库(mysql)的表进行dml操作，比如INSERT INTO mysql.user...。而是使用CREATE USER...，这个是没有问题的，而且也是正确的方式。
  2. 不支持LOCK TABLES和UNLOCK TABLES语句
    mysql> lock tables world write;
    ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING
  3. log_output参数不能是TABLE
  4. 不支持分布式事务
  5. 新建表必须要有主键，否则对表进行dml操作会报以下错误
    ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (hello.world) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER
  6. 推荐的节点数最小是3个

创建pxc镜像

  • node.cnf
    node.cnf主要是mysql的cnf配置文件，对mysql做了一些优化 
[mysqld]
ignore-db-dir=lost+found
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
skip-host-cache
#server_id=0
binlog_format=ROW
default_storage_engine=InnoDB
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_file_per_table           = 1
innodb_autoinc_lock_mode=2
bind_address = 0.0.0.0
wsrep_slave_threads=2
wsrep_cluster_address=gcomm://
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=noname
wsrep_node_address=172.17.0.2
wsrep_node_incoming_address=1fdcaae3a06c:3306
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth='xtrabackup:xtrabackup'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_bin
##########################
# connection
##########################
max_connections = 2000
max_user_connections = 1900
max_connect_errors = 100000
max_allowed_packet = 1G
table_open_cache = 8000
##########################
# time out
##########################
connect_timeout = 20
wait_timeout = 600
explicit_defaults_for_timestamp=1
lower_case_table_names=1
auto_increment_increment=1
auto_increment_offset=1
delay_key_write=ON
delayed_insert_limit=100
delayed_insert_timeout=300
delayed_queue_size=1000
div_precision_increment=4
ft_min_word_len=4
ft_query_expansion_limit=20
[client]
socket=/tmp/mysql.sock
[sst]
progress=/var/lib/mysql/sst_in_progress
  • dockerfile
#Dockerfile-pxc
FROM registry.cn-beijing.aliyuncs.com/helen-devops/percona-xtradb-cluster:5.7
USER root
RUN rm -rf /etc/mysql/node.cnf
ADD ./node.cnf /etc/mysql/
RUN chown mysql.mysql /etc/mysql/node.cnf && \
		chmod 664 /etc/mysql/node.cnf
USER mysql
#CMD bash
  • buid 构建镜像
docker build -t registry.cn-beijing.aliyuncs.com/helen-devops/pxc:5.7 -f Dockerfile-pxc .
docker push registry.cn-beijing.aliyuncs.com/helen-devops/pxc:5.7

1. 创建mysql使用的数据盘

mkdir -p /data/mysql

2. 节点1启动pxc

 docker run -d -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=n******Nt \
    -e CLUSTER_NAME=PXC \
    -e XTRABACKUP_PASSWORD=n******Nt \
    -v /data/mysql:/var/lib/mysql  \
    --name=mysql1 \
    --network=host  \
    -dit registry.cn-beijing.aliyuncs.com/helen-devops/pxc:5.7

3. 节点2 加入pxc集群

docker run -d -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=n*******Nt \
    -e CLUSTER_NAME=PXC \
    -e XTRABACKUP_PASSWORD=n*******Nt \
    -e XTRABACKUP_PASSWORD=n*******Nt \
    -e CLUSTER_JOIN=private-ha-node-1 \
    -v /data/mysql:/var/lib/mysql \
    --name=mysql2 \
    --network=host  \
    -dit registry.cn-beijing.aliyuncs.com/helen-devops/pxc:5.7

4. 节点3 加入pxc集群

docker run -d -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=n*******Nt \
    -e CLUSTER_NAME=PXC \
    -e XTRABACKUP_PASSWORD=n*******Nt \
    -e XTRABACKUP_PASSWORD=n*******Nt \
    -e CLUSTER_JOIN=private-ha-node-1 \
    -v /data/mysql:/var/lib/mysql \
    --name=mysql3 \
    --network=host  \
    -dit registry.cn-beijing.aliyuncs.com/helen-devops/pxc:5.7

原版本registry.cn-beijing.aliyuncs.com/helen-devops/percona-xtradb-cluster:5.7

5. 验证

show status like '%wsrep%';

数据库负载均衡的必要性

虽然搭建了集群,但是不使用数据库负载均衡,单节点处理所有请求，负载高，性能差
proxy

将请求均匀地发送给集群中的每一个节点。

所有请求发送给单一节点，其负载过高，性能很低，而其他节点却很空闲。
使用Haproxy做负载均衡，可以将请求均匀地发送给每个节点，单节点负载低，性能好

  • 负载均衡中间件对比

　　负载均衡首先是数据库的集群，加入5个集群，每次请求都是第一个的话，有可能第一个数据库就挂掉了，所以更优的方案是对不同的节点都进行请求，这就需要有中间件进行转发，比较好的中间件有nginx，haproxy等，因nginx 支持插件，但是刚刚支持了tcp/ip 协议，haproxy 是一个老牌的中间转发件。如果要用haproxy的话，可以从官方下载镜像，然后呢对镜像进行配置（自己写好配置文件，因为这个镜像是没有配置文件的，配置好之后再运行镜像的时候进行文件夹的映射，配置文件开放3306（数据库请求，然后根据check心跳检测访问不同的数据库，8888 对数据库集群进行监控））。配置文件里面设置用户（用户在数据库进行心跳检测，判断哪个数据库节点是空闲的，然后对空闲的进行访问），还有各种算法（比如轮训），最大连接数，时间等，还有对集群的监控。配置文件写好以后运行这个镜像，镜像运行成功后进入容器启动配置文件 。其实haprocy返回的也是一个数据库实例（但是并不存储任何的数据，只是转发请求），这个实例用来check其他节点。
　　

安装Haproxy

从Docker仓库拉取haproxy镜像：https://hub.docker.com/_/haproxy

docker pull haproxy
[root@localhost ~]# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
docker.io/haproxy   latest              11fa4d7ff427        11 days ago         72.2 MB

创建Haproxy配置文件。

# 启动容器时使用目录映射技术使容器读取该配置文件
touch /home/soft/haproxy/haproxy.cfg

# haproxy.cfg
global
    #工作目录
    chroot /usr/local/etc/haproxy
    #日志文件，使用rsyslog服务中local5日志设备（/var/log/local5），等级info
    log 127.0.0.1 local5 info
    #守护进程运行
    daemon
defaults
    log    global
    mode    http
    #日志格式
    option    httplog
    #日志中不记录负载均衡的心跳检测记录
    option    dontlognull
    #连接超时（毫秒）
    timeout connect 5000
    #客户端超时（毫秒）
    timeout client  50000
    #服务器超时（毫秒）
    timeout server  50000
#监控界面    
listen  admin_stats
    #监控界面的访问的IP和端口
    bind  0.0.0.0:8888
    #访问协议
    mode        http
    #URI相对地址
    stats uri   /dbs
    #统计报告格式
    stats realm     Global\ statistics
    #登陆帐户信息
    stats auth  admin:n*******Nt
#数据库负载均衡
listen  proxy-mysql
    #访问的IP和端口
    bind  0.0.0.0:3306  
    #网络协议
    mode  tcp
    #负载均衡算法（轮询算法）
    #轮询算法：roundrobin
    #权重算法：static-rr
    #最少连接算法：leastconn
    #请求源IP算法：source 
    balance  roundrobin
    #日志格式
    option  tcplog
    #在MySQL中创建一个没有权限的haproxy用户，密码为空。Haproxy使用这个账户对MySQL数据库心跳检测
    option  mysql-check user haproxy
    server  MySQL_1 172.*.*.197:3306 check weight 1 maxconn 2000  
    server  MySQL_2 172.*.*.198:3306 check weight 1 maxconn 2000  
    server  MySQL_3 172.*.*.199:3306 check weight 1 maxconn 2000 
    #使用keepalive检测死链
    option  tcpka
  1. 在数据库集群中创建空密码、无权限用户haproxy，来供Haproxy对MySQL数据库进行心跳检测
create user 'haproxy'@'%' identified by '';
  1. 创建Haproxy容器(name=h1的原因是为了高可用)
# 这里要加 --privileged
docker run -it -d -p 8888:8888 -p 3306:3306 -v /root/soft/haproxy:/usr/local/etc/haproxy --name h1 --privileged registry.cn-beijing.aliyuncs.com/helen-devops/haproxy:v2

k8s部署haproxy

文件列表

-rw-r--r-- 1 root root 1648 Nov 27 19:50 haproxy.cfg
-rw-r--r-- 1 root root 1967 Nov 27 19:55 haproxy-config.yaml
-rw-r--r-- 1 root root 2333 Nov 29 23:29 haproxy-deployment.yaml

k8s 创建configmap

kubectl create configmap haproxy-config -n public --from-file=haproxy.cfg

#haproxy-config.cfg
global
    #工作目录
    chroot /usr/local/etc/haproxy
    #日志文件，使用rsyslog服务中local5日志设备（/var/log/local5），等级info
    log 127.0.0.1 local5 info
    #守护进程运行
    daemon
defaults
    log    global
    mode    http
    #日志格式
    option    httplog
    #日志中不记录负载均衡的心跳检测记录
    option    dontlognull
    #连接超时（毫秒）
    timeout connect 5000
    #客户端超时（毫秒）
    timeout client  50000
    #服务器超时（毫秒）
    timeout server  50000
#监控界面
listen  admin_stats
    #监控界面的访问的IP和端口
    bind  0.0.0.0:8888
    #访问协议
    mode        http
    #URI相对地址
    stats uri   /dbs
    #统计报告格式
    stats realm     Global\ statistics
    #登陆帐户信息
    stats auth  admin:n*******Nt
#数据库负载均衡
listen  proxy-mysql
    #访问的IP和端口
    bind  0.0.0.0:3306
    #网络协议
    mode  tcp
    #负载均衡算法（轮询算法）
    #轮询算法：roundrobin
    #权重算法：static-rr
    #最少连接算法：leastconn
    #请求源IP算法：source
    balance  roundrobin
    #日志格式
    option  tcplog
    #在MySQL中创建一个没有权限的haproxy用户，密码为空。Haproxy使用这个账户对MySQL数据库心跳检测
    option  mysql-check user haproxy
    server  MySQL_1 172.*.*.197:3306 check weight 1 maxconn 2000  
    server  MySQL_2 172.*.*.198:3306 check weight 1 maxconn 2000  
    server  MySQL_3 172.*.*.199:3306 check weight 1 maxconn 2000 
    #使用keepalive检测死链
    option  tcpka

创建有状态pod服务

# cat haproxy-deployment.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  annotations:
  generation: 1
  labels:
    ksyun-app: haproxy
  name: haproxy
  namespace: public
spec:
  podManagementPolicy: OrderedReady
  replicas: 2
  revisionHistoryLimit: 2147483647
  selector:
    matchLabels:
      ksyun-app: haproxy
  serviceName: ""
  template:
    metadata:
      creationTimestamp: null
      labels:
        ksyun-app: haproxy
    spec:
      volumes:
        - name: haproxy-config
          configMap:
            name: haproxy-config
            defaultMode: 0664
      containers:
      - image: registry.cn-beijing.aliyuncs.com/helen-devops/haproxy:v2
        imagePullPolicy: Always
        livenessProbe:
          failureThreshold: 3
          initialDelaySeconds: 35
          periodSeconds: 2
          successThreshold: 1
          tcpSocket:
            port: 8888
          timeoutSeconds: 2
        name: haproxy
        readinessProbe:
          failureThreshold: 3
          initialDelaySeconds: 30
          periodSeconds: 2
          successThreshold: 1
          tcpSocket:
            port: 8888
          timeoutSeconds: 2
        resources:
          requests:
            cpu: "1"
            memory: 2Gi
        securityContext:
          privileged: true
          procMount: Default
          runAsUser: 0
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
        volumeMounts:
        - mountPath: /usr/local/etc/haproxy/
          name: haproxy-config
        workingDir: /root
      dnsPolicy: ClusterFirst
      imagePullSecrets:
      - name: ksyunregistrykey
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      terminationGracePeriodSeconds: 30
  updateStrategy:
    rollingUpdate:
      partition: 0
    type: RollingUpdate
---
apiVersion: v1
kind: Service
metadata:
  annotations:
  name: haproxy
  namespace: public
spec:
  ports:
  - port: 3306
    protocol: TCP
    targetPort: 3306
  selector:
    ksyun-app: haproxy
  sessionAffinity: None
  type: ClusterIP
status:
  loadBalancer: {}
---
kind: Service
apiVersion: v1
metadata:
  labels:
    ksyun-app: haproxy
  name: haproxy-out
  namespace: public
spec:
  type: NodePort
  ports:
  - port: 8888
    targetPort: 8888
    nodePort: 30003
  selector:
    ksyun-app: haproxy

备份及恢复

备份方式两种：全量、增量，一般一周一次全量备份，一天一次增量备份。

备份

1、备份数据docker服务上需要有数据卷映射到MySQL-PXC的backup备份文件夹。具体在搭建pxc笔记中有。

2、要进入docker中MySQL上执行操作

innobackupex --user=root --password=****** /tmp/mysql/full

由于PXC各个节点数据节点都是一致的，所以只需要进入一个节点进行备份就行了。

MySQL进行增量备份

1、先对数据库进行全备

innobackupex --defaults-file=/etc/my.cnf --user=root --password=$(cat /data/save/mysql_root) /root/backup/

2、创建测试库test1，在全量备份的基础上再进行增量备份

create database test1;
innobackupex --defaults-file=/etc/my.cnf --user=root --password=$(cat /data/save/mysql_root) --incremental-basedir=/root/backup/2015-10-27_15-47-26/ --incremental /root/backup/

3、创建测试库test2，在增量备份的基础上再进行增量备份

create database test2;
innobackupex --defaults-file=/etc/my.cnf --user=root --password=$(cat /data/save/mysql_root) --incremental-basedir=/root/backup/2015-10-27_15-49-17/ --incremental /root/backup/

4、查看所备份的文件

这边可以看到所备份的三个文件

[root@hongxue_216 backup]# ll /root/backup/
total 12
drwx------ 5 root root 4096 Oct 27 15:47 2015-10-27_15-47-26
drwx------ 6 root root 4096 Oct 27 15:49 2015-10-27_15-49-17
drwx------ 7 root root 4096 Oct 27 15:51 2015-10-27_15-51-15

恢复

#进入mysql1 节点操作

#首先先清空/var/lib/mysql/*
rm -rf /var/lib/mysql/*
#
xtrabackup --defaults-file=/tmp/my.cnf --prepare --target-dir=/tmp/mysql/full/2020-12-09_12-55-12/

xtrabackup --defaults-file=/tmp/my.cnf --copy-back --target-dir=/tmp/mysql/full/2020-12-09_12-55-12/

#重启mysql1
docker restart mysql1

注：my.cnf请查看文章开头node.cnf文件

更新mysql，root密码

update user set authentication_string=password('n********Nt') where user='root';
update user set authentication_string=password('n********Nt') where user='xtrabackup';


flush privileges;

错误解决

如: 机器重启\或者是docker服务重启,导致服务无法启动

#错误代码
2021-02-20T01:10:20.495974Z 0 [Note] WSREP: GCache history reset: ff8e5820-3a31-11eb-b5d4-1b61fbd8eca5:0 -> ff8e5820-3a31-11eb-b5d4-1b61fbd8eca5:18961890
2021-02-20T01:10:20.496233Z 0 [Note] WSREP: Assign initial position for certification: 18961890, protocol version: -1
2021-02-20T01:10:20.496259Z 0 [Note] WSREP: Preparing to initiate SST/IST
2021-02-20T01:10:20.496264Z 0 [Note] WSREP: Starting replication
2021-02-20T01:10:20.496275Z 0 [Note] WSREP: Setting initial position to ff8e5820-3a31-11eb-b5d4-1b61fbd8eca5:18961890
2021-02-20T01:10:20.496281Z 0 [ERROR] WSREP: 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 .
2021-02-20T01:10:20.496288Z 0 [ERROR] WSREP: Provider/Node (gcomm://) failed to establish connection with cluster (reason: 7)
2021-02-20T01:10:20.496292Z 0 [ERROR] Aborting

2021-02-20T01:10:20.496295Z 0 [Note] Giving 0 client threads a chance to die gracefully
2021-02-20T01:10:20.496300Z 0 [Note] WSREP: Waiting for active wsrep applier to exit
2021-02-20T01:10:20.496307Z 0 [Note] WSREP: Service disconnected.
2021-02-20T01:10:20.496310Z 0 [Note] WSREP: Waiting to close threads......
2021-02-20T01:10:25.496407Z 0 [Note] WSREP: Some threads may fail to exit.
2021-02-20T01:10:25.496448Z 0 [Note] Binlog end
2021-02-20T01:10:25.498306Z 0 [Note] mysqld: Shutdown complete
  • 解决方法
#删除相关文件
rm -rf grastate.dat GRA_2* GRA_4* ib_log*
#删除重启mysql1
 docker run -d -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=n*******Nt \
    -e CLUSTER_NAME=PXC \
    -e XTRABACKUP_PASSWORD=n*******Nt \
    -v /data/mysql:/var/lib/mysql  \
    --name=mysql1 \
    --network=host  \
    -dit registry.cn-beijing.aliyuncs.com/helen-devops/pxc:5.7
上一篇：其它综合-使用Putty远程连接管理Linux实践


下一篇：docker 下mysql集群，pxc方式