MySQL on Docker Swarm revisited

It all started with a central database for my Kodi media players. Then I migrated the setup from a dedicated Raspberry Pi to running MySQL on docker swarm. That gave me much more stability and availability for the solution but it still needed backup. The setup ended up with limiting the docker container to a specific node and, via a cron job, executing the backup script on the docker container. You ca read more about that setup in my Kodi central db backup post. This setup was not optimal since I was after more stability and availability by running the application containerized on a swarm. I still had that one single point of failure that I didn’t want! There is a better way, docker stack with the backup containerized!

Single point of failure

So what would be a better solution? You can never get rid of all single point of failures, especially not in a home environment. You should however try not to add more single point of failures then absolutely necessary! In this case it’s a database for my Kodi media players, so if we look at the complete chain we still have a single point of failure on the NAS were the media is stored. So I can use the NAS to store the MySQL data without adding an additional single point of failure.

Backups

The old solution for the backups with a cron job on the docker node running a container command definitely adds a single point of failure since the backup will not work if the container task is assigned to another node. The best way to get around this is to run the backup in a docker container. Then package that container with the MySQL container in a stack. That way they will have a private network where they can resolve each other by name at any time. There is no point reinventing the wheel on this one, there are plenty of docker containers for MySQL backups out there. I looked at two before deciding.

fradelg/mysql-cron-backup – This uses cron and have a lot of configuration options. It will also manage the number of backups saved. It has everything except the use of docker secrets for MySQL dba password which was a deal breaker. As soon as this has support for docker secrets I will consider switching to this.

dsteinkopf/backup-all-mysql – This uses a bash script that sleeps for a specific interval, not the cleanest solution but it supports docker secrets. It also over writes the last backup each time and has no support for backup retention management. In my use case this is acceptable since I use Duplicati to offload my backups to Google Drive and it handles the retention and cleanup of old backups.

Management

When I need access to the database MySQL workbench is required on my laptop/workstation. I want to move away from that setup and add phpMyAdmin to the stack. It’s the same thing here that this container can use the internal stack network to talk to the MySQL container and then publish ports on the ingress network for it’s web interface. This gives me a full phpMyAdmin connected to my kodi databases.

Deploying the stack

I created a docker compose file for the stack including the three services db, phpmyadmin, backup and all the configuration, secrets and networks needed for this to work. The docker compose file is available on my Github. The services section contains the three containers mentioned above in addition we have the network configuration like this:

networks:
  net:
    driver: overlay

This is called an overlay network and will be created as {stack name}_net. All connected containers can communicate and resolve each other with there service name. So if you call db:3306 from the phpMyAdmin container it will find it’s way into the MySQL container running on the same or another node.

secrets:
  MYSQL_PASSWORD:
    external: true
  MYSQL_ROOT_PASSWORD:
    external: true
  MYSQL_USER:
    external: true

This section configures the docker secrets that I already created on the swarm. This allows the containers to get confidential information without being available in clear text in the stacks compose file.

phpmyadmin:
    image: phpmyadmin:latest
    environment:
      PMA_HOST: db
      UPLOAD_LIMIT: 500MB
    ports:
     - 3380:80
    networks:
     - net
    logging:
      driver: json-file

This is an example of the service definition of phpMyAdmin. As you can see on the 4th line we specify the database engine just as db since the service for MySQL is named db. We also assign the network we defined to this service. If you need to access the defined secrets you also have to define them on each service, so both on the top level of the stack compose file and for each service that needs access.

Conclusion

So this is a much better solution then the last one I had. At the same time there is a few things I would like to improve when it comes to the backup handling, like retention management and cron functionality. At the same time this is the best solution under the circumstances.

Compose file on Github: https://github.com/kallsbo/DockerCompose/blob/master/mysql-stack/docker-compose.yaml

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: