[MySQL] Percona XtraDB Cluster on docker-compose

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130

version: '3'

services:
 mysql-node-1:
  image: percona/percona-xtradb-cluster:5.7
  container_name: mysql-node-1
  hostname: mysql-node-1
  privileged: true
  networks:
   cluster-network:
    ipv4_address: 172.18.0.2
  expose:
   - 3306
  volumes:
   - node1-data:/var/lib/mysql
  environment:
   - CLUSTER_NAME=mysql-cluster-test
   - MYSQL_ROOT_PASSWORD=a123456
   - XTRABACKUP_PASSWORD=a123456  
   - MYSQL_DATABASE=appdb
   - MYSQL_USER=RoyBatty
   - MYSQL_PASSWORD=RoyBatty2019
  
 mysql-node-2:
  image: percona/percona-xtradb-cluster:5.7
  container_name: mysql-node-2
  hostname: mysql-node-2
  privileged: true
  networks:
   cluster-network:
    ipv4_address: 172.18.0.3
  expose:
   - 3306
  volumes:
   - node2-data:/var/lib/mysql
  environment:
   - CLUSTER_NAME=mysql-cluster-test
   - MYSQL_ROOT_PASSWORD=a123456
   - XTRABACKUP_PASSWORD=a123456  
   - CLUSTER_JOIN=mysql-node-1
  depends_on:
   - mysql-node-1

 mysql-node-3:
  image: percona/percona-xtradb-cluster:5.7
  container_name: mysql-node-3
  hostname: mysql-node-3
  privileged: true
  networks:
   cluster-network:
    ipv4_address: 172.18.0.4
  expose:
   - 3306
  volumes:
   - node3-data:/var/lib/mysql
  environment:
   - CLUSTER_NAME=mysql-cluster-test
   - MYSQL_ROOT_PASSWORD=a123456
   - XTRABACKUP_PASSWORD=a123456  
   - CLUSTER_JOIN=mysql-node-1
  depends_on:
   - mysql-node-1
   - mysql-node-2

 mysql-shell:
  image: neumayer/mysql-shell-batch
  container_name: mysql-shell-batch-1
  networks:
   cluster-network:
    ipv4_address: 172.18.0.20
  volumes:
   - ./scripts/:/scripts/
  environment:
   - MYSQL_USER=root
   - MYSQL_HOST=mysql-node-1
   - MYSQL_PORT=3306
   - MYSQL_PASSWORD=a123456
   - MYSQL_SCRIPT=/scripts/db.sql
  depends_on:
   - mysql-node-1
   - mysql-node-2
   - mysql-node-3

 haproxy-keepalived1:
  image: pelin/haproxy-keepalived
  container_name: haproxy-keepalived-1
  volumes:
   - ./haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg
   - ./keepalived.cfg:/etc/keepalived/keepalived.conf
  networks:
   cluster-network:
    ipv4_address: 172.18.0.7
  ports:
   - "4001:8888"
   - "4002:3306"
  depends_on:
   - mysql-node-1
   - mysql-node-2
   - mysql-node-3

 haproxy-keepalived2:
  image: pelin/haproxy-keepalived
  container_name: haproxy-keepalived-2
  volumes:
   - ./haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg
   - ./keepalived.cfg:/etc/keepalived/keepalived.conf
  networks:
   cluster-network:
    ipv4_address: 172.18.0.8
  ports:
   - "4003:8888"
   - "4004:3306"
  depends_on:
   - mysql-node-1
   - mysql-node-2
   - mysql-node-3
   - haproxy-keepalived1

volumes:
    node1-data:
    node2-data:
    node3-data:

networks:
 cluster-network:
  driver: bridge
  ipam:
    driver: default
    config:
    - subnet: 172.18.0.0/24

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

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:abc123456
#数据库负载均衡
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数据库心跳检测
    #CREATE USER IF NOT EXISTS 'haproxy'@'%' IDENTIFIED BY '';FLUSH PRIVILEGES;
    option  mysql-check user haproxy
    server  Mysql-1 mysql-node-1:3306 check weight 1 maxconn 2000  
    server  Mysql-2 mysql-node-2:3306 check weight 1 maxconn 2000  
    server  Mysql-3 mysql-node-3:3306 check weight 1 maxconn 2000 
    #使用keepalive检测死链
    option  tcpka

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

vrrp_instance  VI_1 {
    state  MASTER
    interface  eth0
    virtual_router_id  51
    priority  100
    advert_int  1
    authentication {
        auth_type  PASS
        auth_pass  aaa123456
    }
    virtual_ipaddress {
        172.18.0.201
    }
}