架构

授予以普通执行命令,每台执行

1、groupadd docker
2、将用户加入docker组
gpasswd -a mysql docker
3、重启docker(脚本已经创建组，这里不需要重启)
systemctl restart docker

修改主机名,注意主机名要不一样与上面对应

node1服务器执行
hostnamectl set-hostname pxc_node01
node2服务器执行
hostnamectl set-hostname pxc_node02
node3服务器执行
hostnamectl set-hostname pxc_node03

修改hosts文件，每台执行

cat <<EOF >>/etc/hosts
192.168.192.15 pxc_node01
192.168.192.16 pxc_node02
192.168.192.17 pxc_node03
EOF

关闭防火墙和selinux,每台执行

1、关闭防火墙
systemctl stop firewalld && systemctl disable firewalld
2、关闭selinux
sed -i 's/enforcing/disabled/g'  /etc/selinux/config  #永久关闭
setenforce 0  #临时关闭

创建目录,每台执行

mkdir /mysql/{conf,data,logs,mysql-files,cert} -p

创建mysql用户每台执行

groupadd -g 1001 mysql
useradd -u 1001 -g 1001 mysql
chown -R mysql.mysql /mysql

修改centos服务器内核参数,每台执行

cat <<EOF >>/etc/sysctl.conf
net.core.somaxconn = 65535
fs.file-max=6553500
fs.nr_open = 6553500
EOF
sysctl -p
cat <<EOF >>/etc/security/limits.conf
* soft nofile 10240000
* hard nofile 10240000
* soft nproc 65535
* hard nproc 65535
EOF
编辑vim /etc/security/limits.d/20-nproc.conf
*          soft    nproc     65535

配置文件my.cnf 放到到/mysql/conf 下面 每台执行,注意server-id不能重复

[client]
port  = 3306
socket  = /var/lib/mysql/mysql.sock

[mysql]
prompt = "\u@mysqldb \R:\m:\s [\d]> "
no_auto_rehash
loose-skip-binary-as-hex

[mysqld]
user  = mysql
port  = 3306
server_id = 1
socket  = /var/lib/mysql/mysql.sock
pid_file = /var/run/mysqld/mysqldb.pid
character_set_server = UTF8MB4
skip_name_resolve = 1
max_allowed_packet = 200M
sql_mode= "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
default-authentication-plugin=mysql_native_password

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 40960
max_connect_errors = 10000
table_open_cache = 2048
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
tmp_table_size = 32M
max_heap_table_size = 32M

#log settings
log_timestamps = SYSTEM
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_bin = mysql-bin
binlog_format = ROW
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE

#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 12288M
innodb_buffer_pool_instances = 4
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
binlog_stmt_cache_size = 1048576
sync_binlog = 0

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

[mysqldump]
quick
[client]
socket= /tmp/mysql.sock
default-character-set=utf8mb4
[mysqld]
socket= /tmp/mysql.sock
skip-external-locking
skip_name_resolve = 1
lower_case_table_names=1
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
back_log = 1024
max_connections = 20000
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache_instances = 64
thread_stack = 512k
max_allowed_packet = 1024M
binlog_cache_size = 2048KB
max_heap_table_size = 256M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 8
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
datadir=/mysql/data
log-bin=/mysql/binlog/mysql-bin
log_slave_updates=true
gtid_mode=on
enforce_gtid_consistency=on
binlog_format=row
server_id=3
relay_log=/mysql/relaylog/mysql-relay-bin
slow_query_log
slow_query_log_file=/mysql/logs/slow.log
log-error   =/mysql/logs/mysql.err
secure_file_priv=/mysql/mysql-files
long_query_time = 5
event_scheduler =1
expire_logs_days = 7
log_timestamps=SYSTEM
log-bin-trust-function-creators=1
ft_min_word_len=1
default-authentication-plugin=mysql_native_password
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
default-storage-engine = innodb
innodb_buffer_pool_size=800M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 3000
innodb_io_capacity = 4000
innodb_io_capacity_max = 40000
innodb_max_dirty_pages_pct = 50
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
[mysql]
default-character-set=utf8mb4
no-auto-rehash
prompt="\\u@\\h:[\\d] \\r:\\m:\\s>"
[mysqld_safe]
open-files-limit = 10240000

node1节点docker-compose,注意container_name、CLUSTER_NAME

version: "3.8"
services:
  pxc8.0:
    image: percona/percona-xtradb-cluster:8.0
    container_name: pxc_node01
    environment:
      MYSQL_ROOT_PASSWORD: silk@2022
      CLUSTER_NAME: cluster1
    volumes:
      - /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime
      - /mysql/data:/var/lib/mysql
      - /mysql/conf/my.cnf:/etc/mysql/conf.d/my.cnf
      - /mysql/mysql-files:/mysql/mysql-files
      - /mysql/cert:/cert
    network_mode: host
    restart: always
    ulimits:
      nproc: 65000
      nofile:
        soft: 6000000
        hard: 6000000

启动第一台节点

docker-compose up -d

复制node1服务器上的证书到其他节点

cd /mysql/data
scp ca-key.pem ca.pem client-* server-* root@192.168.192.16:/mysql/cert
scp ca-key.pem ca.pem client-* server-* root@192.168.192.17:/mysql/cert

在其他节点设置权限

16服务器上
chown -R mysql.mysql /mysql
17服务器上
chown -R mysql.mysql /mysql

node2节点docker-compose,注意container_name、CLUSTER_NAME、CLUSTER_JOIN

version: "3.8"
services:
  pxc8.0:
    image: percona/percona-xtradb-cluster:8.0
    container_name: pxc_node02
    environment:
      MYSQL_ROOT_PASSWORD: silk@2021
      CLUSTER_NAME: cluster1
      CLUSTER_JOIN: pxc_node01
    volumes:
      - /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime
      - /mysql/data:/var/lib/mysql
      - /mysql/conf/my.cnf:/etc/mysql/conf.d/my.cnf
      - /mysql/mysql-files:/mysql/mysql-files
      - /mysql/cert:/cert
    network_mode: host
    restart: always
    ulimits:
      nproc: 65000
      nofile:
        soft: 6000000
        hard: 6000000

启动第二台节点

docker-compose up -d

node3节点docker-compose,注意container_name、CLUSTER_NAME、CLUSTER_JOIN

version: "3.8"
services:
  pxc8.0:
    image: percona/percona-xtradb-cluster:8.0
    container_name: pxc_node03
    environment:
      MYSQL_ROOT_PASSWORD: silk@2021
      CLUSTER_NAME: cluster1
      CLUSTER_JOIN: pxc_node01
    volumes:
      - /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime
      - /mysql/data:/var/lib/mysql
      - /mysql/conf/my.cnf:/etc/mysql/conf.d/my.cnf
      - /mysql/mysql-files:/mysql/mysql-files
      - /mysql/cert:/cert
    network_mode: host
    restart: always
    ulimits:
      nproc: 65000
      nofile:
        soft: 6000000
        hard: 6000000

启动第三台节点

docker-compose up -d

验证集群

show status like "%wsrep%";

docker部署haporxy

1、在pxc集群中创建用户,注意ip
create user haproxy_check@'192.168.30.%';
create user haproxy_check@'172.18.0.2';
2、配置文件
global
    log 127.0.0.1   local0
    log 127.0.0.1   local1 notice
    daemon
defaults
    mode                    http
    log                     global
    option                  httplog
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout connect         10s
    timeout client          120m
    timeout server          120m
frontend  mysql
    bind *:13365
    mode                        tcp
    option                      tcplog
    default_backend             pxc_server
backend pxc_server
    mode        tcp
    balance     roundrobin
    option      mysql-check user haproxy_check
    server      pxc_node01  192.168.30.64:3306 check
    server      pxc_node02  192.168.30.65:3306  check
    server      pxc_node03  192.168.30.66:3306 check
listen stats
    stats   enable
    bind    *:1080
    stats   uri  /admin
    stats   auth admin:admin
    stats   hide-version
    stats   admin if TRUE
    stats   refresh  10s
    
 3、docker-compose文件
version: "3.8"
services:
  haproxy:
    image: haproxy:2.4
    container_name: haproxy2
    restart: always
    ports:
      - 13365:13365
      - 1080:1080
    volumes:
      - /etc/localtime:/etc/localtime
      - /root/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg
    networks:
      haproxy:
        ipv4_address: 172.18.0.2
networks:
  haproxy:
    ipam:
      config:
        - subnet: 172.18.0.0/16

docker 部署keepalived

1、pull 镜像
docker pull  osixia/keepalived:latest
2、打tag
docker tag osixia/keepalived:latest osixia/keepalived:2.0.20
3、master keepalived配置文件
global_defs {
   script_user root
   enable_script_security
}
vrrp_script chk_haproxy {
    script "/container/service/keepalived/haproxy.sh"
    interval 2
    weight 2
}
vrrp_instance VI_1 {
  interface ens33
  state BACKUP
  virtual_router_id 51
  priority 100
  advert_int 1
  nopreempt
  
  unicast_src_ip 192.168.1.66 #master内外ip
  unicast_peer {
  192.168.1.138 #backup内外ip,如果有多个换行写
  }
  virtual_ipaddress {
    192.168.58.247
  }
  authentication {
    auth_type PASS
    auth_pass 123456
  }
  track_script {
      chk_haproxy
  }
  notify "/container/service/keepalived/assets/notify.sh"
}
4、master docker-compose 文件
version: "3.8"
services:
  keepalived:
    image: osixia/keepalived:2.0.20
    container_name: keepalived2
    cap_add:
      - NET_ADMIN
      - NET_BROADCAST
      - NET_RAW
    volumes:
      - /etc/localtime:/etc/localtime
      - /root/keepalived/haproxy.sh:/container/service/keepalived/haproxy.sh
      - /root/keepalived/keepalived.conf:/usr/local/etc/keepalived/keepalived.conf
    network_mode: host
5、backup keepalived配置文件
global_defs {
   script_user root
   enable_script_security
}
vrrp_script chk_haproxy {
    script "/container/service/keepalived/haproxy.sh"
    interval 2
    weight 2
}
vrrp_instance VI_1 {
  interface ens33
  state BACKUP
  virtual_router_id 51
  priority 90
  advert_int 1
  nopreempt
  
  unicast_src_ip 192.168.1.138 #备机内外ip
  unicast_peer {
  192.168.1.66 #master 内外ip
  }
  virtual_ipaddress {
    192.168.58.247
  }
  authentication {
    auth_type PASS
    auth_pass 123456
  }
  track_script {
      chk_haproxy
  }
  notify "/container/service/keepalived/assets/notify.sh"
}
6、bakcup docker-compose 配置文件
version: "3.8"
services:
  keepalived:
    image: osixia/keepalived:2.0.20
    container_name: keepalived2
    cap_add:
      - NET_ADMIN
      - NET_BROADCAST
      - NET_RAW
    volumes:
      - /etc/localtime:/etc/localtime
      - /root/keepalived/keepalived.conf:/usr/local/etc/keepalived/keepalived.conf
      - /root/keepalived/haproxy.sh:/container/service/keepalived/haproxy.sh
    network_mode: host
    
7、haproxy.sh 脚本文件
#!/bin/bash 
PORTS=$(netstat -lntp|grep 13365)
if [ -z "$PORTS" ];then
   pkill -9 keepalived
fi
如果vip不能释放需要用下面脚本,其中192.168.1.250为vip
#!/bin/bash 
PORTS=$(netstat -lntp|grep 13365)
if [ -z "$PORTS" ];then
   pkill -9 keepalived
   ip addr del 192.168.1.250 dev eth0
fi
8、授予可执行权限
chmod +x haproxy.sh
9、docker-compose up -d