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.
Version 1
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_DATABASE, MYSQL_USER and
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:
mysql:
image: mysql:8.0.23
container_name: "db-vote"
ports:
- "3306:3306"
environment:
MYSQL_ROOT_PASSWORD: root_password
MYSQL_DATABASE: vote
MYSQL_USER: vote
MYSQL_PASSWORD: password
cap_add:
- SYS_NICE # removes error messages like: mbind: Operation not permitted
volumes:
- ./sql/docker/mysql-data:/var/lib/mysql
- ./sql/docker/my.cnf:/etc/mysql/conf.d/dev.cnf
- ./sql/docker:/docker-entrypoint-initdb.dAnd our project’s sql/docker/ directory has:
sql/
docker/
my.cnf
initial_data.sql
mysql-data/
....various files and directories for the actual databaseAnd don’t forget to exclude that mysql-data directory from your image by including it in your
.dockerignore file:
.git
.idea
# Ignore the mysql data files that the dev mysql server creates.
sql/docker/mysql-dataVersion 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
sql/docker/init.sql:
create user 'vote' identified with mysql_native_password by 'password';
grant all privileges on vote.* to 'vote';And then remove MYSQL_USER and MYSQL_PASSWORD from the docker-compose.yml environment.
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 sql/docker/ directory:
sql/
initial_data.sql
docker/
my.cnf
mysql-data/
....various files and directories for the actual databaseThen remove MYSQL_DATABASE, MYSQL_USER, and 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 as /sql_data in the container. The
docker-compose.yml should now look like this:
mysql:
image: mysql:8.0.23
container_name: "db-vote"
ports:
- "3306:3306"
environment:
MYSQL_ROOT_PASSWORD: root_password
cap_add:
- SYS_NICE # removes error messages like: mbind: Operation not permitted
volumes:
- ./sql/docker/mysql-data:/var/lib/mysql
- ./sql/docker/my.cnf:/etc/mysql/conf.d/dev.cnf
- ./sql/docker:/docker-entrypoint-initdb.d
- ./sql:/sql_dataThen, in sql/docker/init.sql, create your user and database; then load your data from the mounted file:
create database 'vote';
create user 'vote' identified with mysql_native_password by 'password';
grant all privileges on vote.* to 'vote';
-- load initial data
use vote
source /sql_data/initial.sql