Restoring databases using Docker

README.md

Restoring databases using Docker

This guide requires you have the original database directories handy. You may also struggle restoring InnoDB instances because of the way they work, however a guide can be found at the bottom of this gist.

Move your backup database folders to an appropriate location, a temporary folder is preferable as MariaDB will put its own data within this directory.

Within the directory, run the following docker command:

docker run -d -p 3307:3306 --env MYSQL_ALLOW_EMPTY_PASSWORD=true --name mariadb_restore -v $(pwd):/var/lib/mysql mariadb:10.5 --skip-grant-tables

This will boot up a MariaDB server instance, bound to port 3307 on your local machine with no root password.

Using your favourite CLI, or terminal, access the temporary MariaDB instance on 127.0.0.1:3307.

• Host: 127.0.0.1
• Port: 3307
• Username: root
• Password: none

Once done, it's time to clean up:

docker rm -f mariadb_restore

You can also remove the temporary directory you created if no longer needed.

Restoring InnoDB tables with innodb files

If you have the servers original InnoDB tables, (ib*), you can add these alongside the database folder you want to restore and run the docker command. Everything should boot up as expected, ignoring tables that are missing.

Restoring InnoDB tables without innodb files

InnoDB tables use the ibdata file to manage tablespaces. When the ibdata file is lost, the tables structure is also lost with it. Below are steps to attempt to restore an InnoDB table, although requires you have the tables structure:

1. Make sure the database version matches (you can alter the docker version)
2. Drop the table, e.g. DROP TABLE my_table
3. cd to /var/lib/mysql/[my_database]
4. Move the ibd file to a backup instance mv my_table.ibd bkp
5. Restore the table using your CREATE TABLE schema
6. We've now created our table structure within MariaDB, discard the newly made tablespace ALTER TABLE my_table DISCARD TABLESPACE
7. Move your backed up ibd file back into the original location mv bkp my_table.ibd
8. Reimport our tablespace ALTER TABLE my_table IMPORT TABLESPACE
9. The server may crash, restart with docker start mariadb_restore
10. Open the database instance and you should have a working table.