Import Mysql Dump Locally

Lewis Denny published in Troubleshooting

2021-11-12 308 words 2 minutes

Contents

Sometimes it can be handy to examine a mysql database dump using the mysql commands, for this we need a database running locally to import the dump into.

Setup

First we need to create an import dir:

mkdir -p ~/db-import

Then move a copy of the database into the db-import import dir:

mv controller1_ALL_DB.sql ~/db-import

Create Container

Now we can create a container called db-import and map the db-import import dir to /docker-entrypoint-initdb.d inside the container, this is a special location that will be checked when the container is first started for db backups files (.sql, .sql.gz, .sql.xz) and will auto import them:

podman run --detach --name db-import --env MARIADB_USER=user --env MARIADB_PASSWORD=userpass --env MARIADB_ROOT_PASSWORD=rootpass  -v ~/db-import:/docker-entrypoint-initdb.d:Z mariadb:latest

Import database dump

Here we can see the backup being imported:

podman logs db-import | grep "docker-entrypoint"
2021-11-11 01:16:21+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/controller1_ALL_DB.sql

Note

If you see any errors in the podman logs db-import output like this:

ERROR 1050 (42S01) at line 6068: Table 'user' already exists

This is due to the database dump containing a database called mysql, we don’t want that so it can be safely removed from the database dump before importing.

Enter the container and use the database

Once the import has completed we can enter the container using podman exec with the credentials that are passed in the podman run command above:

podman exec -it db-import mysql -uroot -prootpass

Now you can browse the database like normal:

podman exec -it db-import mysql -uroot -prootpass
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| aodh               |
| cinder             |
| glance             |
| gnocchi            |
| heat               |
| information_schema |
| keystone           |
| mysql              |
| nova               |
| nova_api           |
| nova_cell0         |
| nova_placement     |
| ovs_neutron        |
| panko              |
| performance_schema |
+--------------------+
15 rows in set (0.008 sec)