After spending some time on Mysql backup script, I realized that the backup for Mysql only comes with Enterprise Edition of Mysql.
Since I have started working on containers, I looked into building Docker images for a possible solutions for the typical production configuration:
1. High availability
2. Active-Active
3. Backup (Weekly full, daily incremental)
I came across
Galera which provides 1 and 2. Also, during my search, I came across the open source backup solution for MySql from
percona which will provide for item 3 on the list above.
So based on the above, I have come up with a docker build file to satisfy 1, 2 and 3. You can find the project on
GitHub.
Dockerfile:
# from http://galeracluster.com/2015/05/getting-started-galera-with-docker-part-1/
FROM ubuntu:14.04
MAINTAINER TYKOH
ENV DEBIAN_FRONTEND noninteractive
RUN apt-get update
RUN apt-get install -y software-properties-common
RUN apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 BC19DDBA
RUN add-apt-repository 'deb http://releases.galeracluster.com/ubuntu trusty main'
RUN apt-get update
RUN apt-get install -y galera-3 galera-arbitrator-3 mysql-wsrep-5.6 rsync lsof
RUN \
echo "deb http://repo.percona.com/apt trusty main testing" > /etc/apt/sources.list.d/percona.list && \
echo "deb-src http://repo.percona.com/apt trusty main testing" >> /etc/apt/sources.list.d/percona.list && \
apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A && \
apt-get update && apt-get install -y percona-xtrabackup
COPY my.cnf /etc/mysql/my.cnf
ENTRYPOINT ["mysqld"]
Building the docker image
docker build --tag=galera .
Local cluster setup
Starting the first node:
docker run \
-p 3306:3306 \
--detach=true \
--name node1 \
-h node1 \
galera \
--wsrep-cluster-name=default-cluster \
--wsrep-cluster-address=gcomm://
Starting node 2:
docker run \
--detach=true \
--name node2 \
-h node2 \
--link node1:node1 \
galera \
--wsrep-cluster-name=default-cluster \
--wsrep-cluster-address=gcomm://node1
Starting node 3
docker run \
--detach=true \
--name node3 \
-h node3 \
--link node1:node1 \
galera \
--wsrep-cluster-name=default-cluster \
--wsrep-cluster-address=gcomm://node1
Multi-node cluster
- Mysql port at 3306
- Galera cluster port at 4567
- Galera increamental state transfer (IST) port at 4568
- Galera state snapshot transfer (SST) port at 4444
We got to map the ports to different local port as we are all running in the same host machine.
If the containers are running on different host machine, there is no need to map to different local port. Just do a 1:1 mapping of the actual ports.
Get ip address of machine
export IP_NOW=`ifconfig | awk '/inet /{print substr($2,1)}' | tail -n 1`
Starting first node
docker run -d \
-p 33060:3306 \
-p 45670:45670 \
-p 44440:44440 \
-p 45680:45680 \
--name nodea \
galera \
--wsrep-cluster-address=gcomm:// \
--wsrep-node-address=192.168.99.100:45670 \
--wsrep-sst-receive-address=192.168.99.100:44440 \
--wsrep-provider-options="ist.recv_addr=192.168.99.100:45680"
Start node b
docker run -d \
-p 33061:3306 \
-p 45671:45671 \
-p 44441:44441 \
-p 45681:45681 \
--name nodeb \
galera \
--wsrep-cluster-address=gcomm://192.168.99.100:45670 \
--wsrep-node-address=192.168.99.100:45671 \
--wsrep-sst-receive-address=192.168.99.100:44441 \
--wsrep-provider-options="ist.recv_addr=192.168.99.100:45681"
Start node c
docker run -d \
-p 33062:3306 \
-p 45672:45672 \
-p 44442:44442 \
-p 45682:45682 \
--name nodec \
galera \
--wsrep-cluster-address=gcomm://192.168.99.100:45670 \
--wsrep-node-address=192.168.99.100:45672 \
--wsrep-sst-receive-address=192.168.99.100:44442 \
--wsrep-provider-options="ist.recv_addr=192.168.99.100:45682"
Post command to set up database
docker exec -t nodea mysql -e "create user 'username'@'localhost' identified by 'user_password';"
docker exec -t nodea mysql -e "create user 'username'@'192.168.0.0/255.255.0.0' identified by 'user_password';"
docker exec -t nodea mysql -e "grant all on *.* to 'username'@'192.168.0.0/255.255.0.0';"
docker exec -t nodea mysql -e "grant all on *.* to 'username'@'localhost';"
Start container to do backup
docker run \
--detach=true \
--name nodeBackup \
-h nodeBackup \
--link node1:node1 \
-v $(pwd):/data \
galera \
--wsrep-cluster-name=default-cluster \
--wsrep-cluster-address=gcomm://node1
Using innobackupex
docker exec -it nodeBackup /bin/bash
mkdir -p /data/backup
innobackupex --user=username --password=user_password --port=3306 --host=node1 /data/backup
Check cluster
docker exec -ti node1 mysql -e 'show status like "wsrep_cluster_size"'
