Importing data into Docker-based mysql container

Here is a "cheatsheet" for setting up a mysql server with Docker. If you're working with Wordpress, for example, the following should be fairly useful as you will need a working MySQL container for a typical local development setup for WP which usually involves importing an existing database. This article outlines a slight difference, depending on whether the mysql container is brand new or existing.

Setting-up a fresh instance of MySQL

The following section is applicable when we are starting from scratch - setting up a completely new database on our local machine where no prior data exists.

When creating a MySQL container we can do some one-time init actions to import one or more .sql files, often useful when importing a mysqldump sql file and re-creating a production database on our local machine we would like to work with.

The folder where this is run from contains the following:

[ROOT folder]
- ...
- docker-entrypoint-initdb.d/
  |
  |-- 01-init.sql
  |
  |-- 02-additional-data.sql

The docker-entrypoint-initdb.d sub-directory contains a mysql dump file.

When exporting a database using PhpMyAdmin, the resulting file does not pre-select or pre-defined a database. In other words, we have to specify the target database where the exported SQL is supposed to go into.

Here is a one liner that will import all SQL files inside the local sub-directory docker-entrypoint-initdb.d:

docker run --name mysql-cont 
       -e MYSQL_ROOT_PASSWORD=password \
       -e MYSQL_DATABASE=wordpress \
       -p 3306:3306 -d \
       --mount type=bind,source=$(pwd)/docker-entrypoint-initdb.d,target=/docker-entrypoint-initdb.d \
       mysql:5.7

What happens in the above line is that:

• Run a mysql:5.7 image from the official Docker image and create a container named mysql-cont
• Automatically create a database called wordpress, using the MYSQL_DATABASE environment variable parameter passed into the container. Also set root password to password
• Expose the MySQL on port 3306
• Mount a local sub directory $(pwd)/docker-entrypoint-initdb.d to container's /docker-entrypoint-initdb.d. Our local directory has several .sql files, alphabetically ordered to reflect the order in which we would like to execute them during initialization

IMPORTANT: this is done on a fresh new non-existent container that does not have any data (no volume is monted to /var/lib/mysql). If there were data present (by using a volume), the .sql files would be ignored!

Importing data into existing Docker MySQL Container

If a MySQL container we're working with already exists and has a mounted /var/lib/mysql directory then the init process described above will not work. The init script will realize that our MySQL container has some data in the data files folder (/var/lib/mysql) and will use whatever it finds there, ignoring all the .sql files we may have passed into Docker's /docker-entrypoint-initdb.d directory in the container.

To import data into such an environment we would then have two options:

1. Use your favourite MySQL GUI admin tool to import data
2. Connect to mysql via Docker and run database import commands.

We'll focus on #2 here.

docker exec -i mysql-cont sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' target_db_name < /some/path/on/your/host/target_db.sql

What the above does is:

• Connects to a running mysql-cont docker container (which is our mysql container)
• runs the mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' target_db_name < /some/path/on/your/host/target_db.sql on the shell inside that container which will use the target_db_name database (that should exist) and execute whatever SQL commands we have in the /some/path/on/your/host/target_db.sql