Restoring mysql data from a Docker Volume

Question

It's the second time that when my local system (Mac OS) crashes and restarts, I lose the running Docker container of MySQL. By "lose" I mean even docker ps -a doesn't show it. It's vanished.

I am using the official mysql-server docker image (https://hub.docker.com/r/mysql/mysql-server) so luckily the data of /var/lib/mysql is in a volume. And I am lucky that after the loss of the container, the volume is there.

The question is, how can I restore the data (e.g. a mysqldump) out of a Docker volume of /var/lib/mysql?

Answer 1

Step 1: Find and verify the volume

Via docker volume ls you can find the name of the volume. Let's say it's <abcdef>.

Then, via docker run -it --rm -v <abcdef>:/var/lib/mysql busybox ls -l /var/lib/mysql make sure you see the files and the dates of the files matches your recent changes to the lost DB. (credits to this answer)

Optionally, you can create a backup out of this volume via this method.

Step 2: Create a new container, and mount this volume on

Whatever docker run command you are already using to start a MySQL container, add -v <abcdef>:/var/lib/mysql_old to it. It should give you a fresh MySQL container up and running, without any issue. Your data is not loaded there yet, just the files are accessible.

Step 3: Copy and overwrite the MySQL data

Now, go into the shell of that container (e.g. docker exec -it <CONTAINER_NAME> bash) and do ls /var/lib/mysql_old to make sure the files from your volume are there.

Then, do cp -R /var/lib/mysql_old/. /var/lib/mysql (or sudo cp ... depending on the user you got in with) and then chown -R mysql:mysql /var/lib/mysql. (Credits to this tutorial)

Step 4: Restart the container

Exit the container and do docker stop <CONTAINER_NAME> to stop the container. Then start it again via docker start <CONTAINER_NAME>. Voila! It should now be a DB with all your data.

Optionally, if you want to start off with a non-hacked container, you can do docker exec <CONTAINER_NAME> sh -c 'exec mysqldump -uroot -p --databases <DATABASE_NAME>' > dump.sql to get a mysqldump out of it, and import that dump.sql into a fresh new container via docker exec -i <CONTAINER_NAME> sh -c 'exec mysql ' < dump.sql.