Physical backup and restore of MySQL data in a docker volume

mysql-volume-backup.md

Physical backup and restore of MySQL data in a docker volume

Backup

• Login to mysql shell for the container named mysql:

docker run --rm -it --link mysql:mysql mysql:5 mysql -h mysql -u root

• Flush tables and prepare for shutdown:

FLUSH TABLES; SET GLOBAL innodb_fast_shutdown=0;

• Shutdown the mysql container.

• Create a directory on the host system to store data

mkdir $HOME/mysql-backup

• Find the name of your mysql volume:

docker volume ls

• Start a new utility container to access the volume called mysql-data:

docker run --rm -it -v mysql-data:/data -v $HOME/mysql-backup:/mysql-backup ubuntu /bin/bash

• Now in the container shell, start the backup:

cd /data
tar cfvz /mysql-backup/NAME_OF_BACKUP.tar.gz .

• Once the backup is finished, Ctrl-C out of the utility container

• Find your backup tarball in $HOME/mysql-backup

• Restart your mysql container.

• Upload the backup to your S3 host (optional):

  • apt install -y s3cmd
  • s3cmd --configure (see DigitalOcean specifics)
  • s3cmd put NAME_OF_BACKUP.tar.gz s3://YOUR_BUCKET/

Restore

• Download your backup from S3 (optional):

  • apt install -y s3cmd
  • s3cmd --configure (see DigitalOcean specifics)
  • s3cmd get s3://YOUR_BUCKET/NAME_OF_BACKUP.tar.gz

• Ensure the docker volume does not already exist:

docker volume ls

• Create a new docker volume called mysql-data inside a utility container:

docker run --rm -it -v $HOME/NAME_OF_BACKUP.tar.gz:/mysql.tar.gz -v mysql-data:/data ubuntu /bin/bash

• Now in the container shell, restore the backup:

cd /data
tar xfvz /mysql.tar.gz

• Start your mysql container with the new volume called mysql-data