MySQL docker DB snapshot

mysql-snapshot.sh

#!/bin/bash

print_usage() {
  echo "Usage: ./mysql-snapshot.sh <sqldump>"
}

real_path() {
  OURPWD=$PWD
  cd "$(dirname "$1")"
  LINK=$(readlink "$(basename "$1")")
  while [ "$LINK" ]; do
    cd "$(dirname "$LINK")"
    LINK=$(readlink "$(basename "$1")")
  done
  REALPATH="$PWD/$(basename "$1")"
  cd "$OURPWD"
  echo "$REALPATH"
}

if [ -z "$1" ]
  then
    echo "Must supply an argument for <sqldump>"
    print_usage
    exit
fi

cur_date=$(date "+%Y-%m-%d-%H-%M")
sqldump_path=$(real_path $1)
sqldump_filename=$(basename ${sqldump_path})
temp_container='mysql_temp'
temp_image="supercoffee/mysql-temp"
snapshot_image="supercoffee/mysql-snapshot:${cur_date}"

echo "path: ${sqldump_path}"
echo "filename ${sqldump_filename}"

docker build -t $temp_image - <<EOF
FROM mysql:8
RUN cp -r /var/lib/mysql /var/lib/mysql-no-volume
RUN sed -i -e "s|/var/lib/mysql|/var/lib/mysql-no-volume|" /etc/mysql/my.cnf
EOF

docker run --name $temp_container -e MYSQL_ROOT_PASSWORD=root -d $temp_image
echo "Waiting for mysql startup"
sleep 15
docker exec -i $temp_container sh -c 'exec mysql -uroot -proot' < $sqldump_path
docker exec $temp_container mysql -uroot -proot -e 'RESET MASTER'
docker commit $temp_container $snapshot_image
docker stop $temp_container
docker rm $temp_container
docker rmi $temp_image
echo "Snapshot finished: $snapshot_image"

Summary

This script imports a database dump into mysql and creates a snapshot of the container as a new image.

Why

When testing migrations against large databases, screwing up the script and corrupting the data wastes a lot of time. Instead of resetting the DB by restoring a dump file, we can simply delete the container and start it up again using a docker image that already has data in it.

Usage

Run the script to generate a new docker image from a database dump file.

./mysql-snapshot.sh path/to/dump.sql

When the script finishes, it outputs the name of the new DB image

Snapshot finished: supercoffee/mysql-snapshot:2020-03-03-10-05

Copy the image name and drop it into your docker-compose.yml or docker-compose.override.yml file in place of the original mysql:8 image.

services:
  mysql:
    image: supercoffee/mysql-snapshot:2020-03-03-10-05
    container_name: mysql

Notes

This image does NOT use docker volumes to store data. When you delete the container by running docker-compose down or docker rm, the data changes will be lost. This is by design, as this image is meant to be used for rapid testing of destructive DB migrations. When the container is recreated from this image, MySQL will start with the data that was restored from the dump file.