We could probably use a single database with multiple schemas for all our development work. That’s how we have been doing it for years. But sometimes that leads to us making compromises - like running a slightly different version of the database in dev vs production. And it leads to being somewhat conservative when trying out different database options. Now that we are using Docker for deploying our applications, it makes sense to use docker-compose to create all the services our apps use: relational databases, ElasticSearch, caches. The docker-compose file also manages mounted volumes (for preserving the data in our development databases) AND a private network for each set of containers.
Creating a database and user
If we want to take full advantage of docker-compose’s automated workflow, we need to be able to
recreate our databases - including loading dev data - automatically. The official MySQL database
container image supports a really easy way to do this. If you set a
handful of environment variables in your
docker-compose.yml file, the first time you start the
database container, it will create the database instance and set the root password to the value from
MYSQL_ROOT_PASSWORD. If you include values for
MYSQL_PASSWORD, the first startup of the database will create that database and grant all
privileges on that database to the specified user. Excellent! That gets us most of the way there.
Now if we could only load some initial data….
Loading initial data
The MySQL image provides for this too. In the section “Initializing a fresh instance”:
When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.
So the database section of our standard
docker-compose.yml looks like:
And our project’s
sql/docker/ directory has:
And don’t forget to exclude that
mysql-data directory from your image by including it in your
Version 2a - custom user creation
The stock setup above works great - until you have some super old code that you don’t want to have
to upgrade to recent MySQL libraries. If you want to connect those projects to MySQL 8, you need to
tell the database that this user will be using an older authentication plugin. So you need to issue
the user create yourself. One option is to only do the user creation and privilege setting in your
script, leaving the database creation and loading as above. Put the following in
And then remove
MYSQL_PASSWORD from the
Version 2b - fully custom script
The version above works fine, but if you need additional configuration of the database - or just want to take full control of the user and database creation and data loading - you can use the following.
First, move the initial_data.sql file out of the project’s
MYSQL_PASSWORD variables from your
docker-compose.yml and add another volume to mount the directory where you put initial_data.sql. I
just moved the file up one level and then mounted
/sql_data in the container. The
docker-compose.yml should now look like this:
sql/docker/init.sql, create your user and database; then load your data from the mounted file: