Browsing Category "MySql"

Search This Blog

Powered by Blogger.

Pages

Browsing "Older Posts"

Browsing Category "MySql"

Docker Galera Image for Mysql clustering Active-Active with backup solutions

By TY → Monday, June 8, 2015


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"'