Restoring a dead MySQL database using the database files with Docker

Lets say your server died and you want to export the latest database content from your MySQL instance.

Previously you had to install it into some virtual machine and copy your files to the MySQL data dir. Thanks to docker you can quite quickly do this now.

You will need all the files from the dead machine, it is usually in the /var/lib/mysql directory. Although some files are separated into the db named directories, all the files are necessary because the InnoDB engine uses some files from there. If you did not copy all the files then you will get the error below:

mysql> USE database_name;
mysql> SELECT * FROM table_name;
ERROR 1146 (42S02): Table 'database_name.table_name' doesn't exist

Now you have to get the correct version of MySQL running in a docker container. You can get the version by running the ./mysql --version command with the binary from the dead machine, for me it worked using a MySQL 5.6 instance with data from version 5.5.54.

You can use the docker image from the official repository: MySQL Docker. The command below will start a MySQL 5.6 instance:

export $PATH_TO_SAVED_DATA="/home/user/saved_mysql_data_dir"
export $MYSQL_VERSION="5.6"
docker run --name some-mysql -v "$PATH_TO_SAVED_DATA":/var/lib/mysql -d mysql:"$MYSQL_VERSION"

Note that even if you pass in a root password, it will not be used because we overwrite the MySQL data files, which means that the passwords will be the same as they were on the dead machine!

You can see in the command that we named our container ‘some-mysql’. This means even if we docker stop it we can not start it the same way, you have to docker rm some-mysql before restarting. Alternatively you can remove that part, then get the instance id by looking up it in docker ps.

You can do two things now:

docker exec -i -t some-mysql sh -c 'bash'

This will give you a console inside the machine. You can use any tools to recover the data. One thing you can not do is to stop the mysql because that will stop the container. You have to manually get and edit the Dockerfile to run mysqld_safe to reset your password.

If you just want to dump the data and you know your password then just use this command:

docker exec -i -t some-mysql sh -c 'mysqldump -u mysql_user -p --databases database_name'  > restore.sql

The command above will ask for a password then dump the SQL into the restore.sql file.