Learning Notes: docker deploys highly available MySQL clusters

Environmental Science

• CentOS 7.5 (IP:106.xx.xx.xx)
• Docker 18.06.0-ce

1. docker settings

# Define Subnets
docker network create --subnet=172.18.0.0/16 net1

2. mysql Cluster (5)

• 172.18.10.11 30001:3306
• 172.18.10.12 30002:3306
• 172.18.10.13 30003:3306
• 172.18.10.14 30004:3306
• 172.18.10.15 30005:3306

# Install Mirror
docker pull percona/percona-xtradb-cluster
# Mirror rename
docker tag percona/percona-xtradb-cluster pxc
# Create five data volumes
docker volume create --name v1
docker volume create --name v2
docker volume create --name v3
docker volume create --name v4
docker volume create --name v5
# Create backup data volumes (for hot backup data)
# docker volume create --name backup
#Create the first MySQL node
docker run -d -p 30001:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -v v1:/var/lib/mysql -v backup:/data --privileged --name=db1 --net=net1 --ip 172.18.10.11 pxc
#Create the second MySQL node
docker run -d -p 30002:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -e CLUSTER_JOIN=db1 -v v2:/var/lib/mysql -v backup:/data --privileged --name=db2 --net=net1 --ip 172.18.10.12 pxc
#Create the third MySQL node
docker run -d -p 30003:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -e CLUSTER_JOIN=db1 -v v3:/var/lib/mysql -v backup:/data --privileged --name=db3 --net=net1 --ip 172.18.10.13 pxc
#Create the fourth MySQL node
docker run -d -p 30004:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -e CLUSTER_JOIN=db1 -v v4:/var/lib/mysql -v backup:/data --privileged --name=db4 --net=net1 --ip 172.18.10.14 pxc
#Create the fifth MySQL node
docker run -d -p 30005:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -e CLUSTER_JOIN=db1 -v v5:/var/lib/mysql -v backup:/data --privileged --name=db5 --net=net1 --ip 172.18.10.15 pxc

3. haproxy(2 units)

• 172.18.10.21 31011:8888 31021:3306
• 172.18.10.22 31012:8888 31022:3306

# Pull mirror
docker pull haproxy
# Create the first Haproxy load balancing server
docker run -it -d -p 31011:8888 -p 31021:3306 -v /root/soft/haproxy:/usr/local/etc/haproxy --name h1 --privileged --net=net1 --ip 172.18.10.21 haproxy
# Enter the h1 container and start Haproxy
docker exec -it h1 bash
haproxy -f /usr/local/etc/haproxy/haproxy.cfg
# Create the second Haproxy load balancing server
docker run -it -d -p 31012:8888 -p 31022:3306 -v /root/soft/haproxy:/usr/local/etc/haproxy --name h2 --privileged --net=net1 --ip 172.18.10.22 haproxy
# Enter the h2 container and start Haproxy
docker exec -it h2 bash
haproxy -f /usr/local/etc/haproxy/haproxy.cfg

Configuration file haproxy.cfg

global
    #working directory
    chroot /usr/local/etc/haproxy
    #Log file, using the local5 log device (/var/log/local5) in the rsyslog service, level info
    log 127.0.0.1 local5 info
    #Daemon Run
    daemon

defaults
    log global
    mode    http
    #Log format
    option  httplog
    #Load balanced heartbeat detection records are not recorded in the log
    option  dontlognull
    #Connection timeout (milliseconds)
    timeout connect 5000
    #Client timeout (milliseconds)
    timeout client  50000
    #Server timeout (milliseconds)
    timeout server  50000

#Monitoring interface   
listen  admin_stats
    #IP and Port for Monitoring Interface Access
    bind  0.0.0.0:8888
    #access protocol
    mode        http
    #URI relative address
    stats uri   /dbs
    #Statistical Report Format
    stats realm     Global\ statistics
    #Login Account Information
    stats auth  admin:ha123456
#Database Load Balancing
listen  proxy-mysql
    #Accessed IP and Ports
    bind  0.0.0.0:3306
    #Network Protocol
    mode  tcp
    #Load Balancing Algorithms (Polling Algorithms)
    #Polling algorithm: roundrobin
    #Weighting algorithm: static-rr
    #Least Connection Algorithm: leastconn
    #Request Source IP Algorithm:source
    balance  roundrobin
    #Log format
    option  tcplog
    #Create a haproxy user without permissions in MySQL with an empty password.Haproxy uses this account to detect heartbeat in MySQL databases
    #create user 'haproxy'@'%' identified by ''
    option  mysql-check user haproxy
    server  MySQL_1 172.18.10.11:3306 check weight 1 maxconn 2000
    server  MySQL_2 172.18.10.12:3306 check weight 1 maxconn 2000
    server  MySQL_3 172.18.10.13:3306 check weight 1 maxconn 2000
    server  MySQL_4 172.18.10.14:3306 check weight 1 maxconn 2000
    server  MySQL_5 172.18.10.15:3306 check weight 1 maxconn 2000
    #Detecting dead chains using keepalive
    option  tcpka

4. Double-machine Hot-up

1. Two haproxy installations keep alived

#Enter h1 container
docker exec -it h1 bash
#Update Package
apt-get update
#Install Keepalived
apt-get install keepalived
#Host copies configuration file to h1 container
docker cp keepalived.conf h1:/etc/keepalived/
#Start Keepalived
service keepalived start
#Host executes ping command
ping 172.18.8.11

Configuration file keepalived.conf, set virtual IP

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

2.1 (Scenario 1) Host machine installs keepalived, sets virtual IP, forwards virtual IP set in haproxy

# Configuration/etc/keepalived/keepalived.conf
# Start keepalived
service keepalived start
# Access via virtual ip(192.168.31.246)

Configuration file/etc/keepalived/keepalived.conf

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.31.246
    }
}

virtual_server 192.168.31.246 8888 {
    delay_loop 3
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 172.18.8.11 8888 {
        weight 1
    }
}

virtual_server 192.168.31.246 3306 {
    delay_loop 3
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 172.18.8.11 3306 {
        weight 1
    }
}

2.2 (scenario 2) nginx forwards directly to the virtual ip set in haproxy

Add port forwarding to specified virtual IP in configuration file/usr/local/nginx/conf/nginx.conf

stream {
    upstream ha8888 {
        server 172.18.8.11:8888;
    }
    upstream db3306 {
        server 172.18.8.11:3306;
    }
    server {
        listen 8888;
        proxy_connect_timeout 5s;
        proxy_timeout 5s;
        proxy_pass ha8888;
    }
    server {
        listen 3306;
        proxy_connect_timeout 5s;
        proxy_timeout 5s;
        proxy_pass db3306;
    }
}

Reference material

Muchow Web "Deployment and Maintenance of Front-End Separation Project in Docker Environment"