Last active
March 3, 2020 21:00
-
-
Save supercoffee/a2755f01160b3112212ba1240b7cbd57 to your computer and use it in GitHub Desktop.
MySQL docker DB snapshot
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.
When the script finishes, it outputs the name of the new DB image
Copy the image name and drop it into your
docker-compose.yml
ordocker-compose.override.yml file
in place of the originalmysql:8
image.Notes
This image does NOT use docker volumes to store data. When you delete the container by running
docker-compose down
ordocker 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.