Docker, how to run .sql file in an image?

Question

It's my first time working with Docker an I am not sure if I am doing things well.

I have a rails applications that depends on a Mysql database, so I've configured the docker-compose.yml file like this:

db:   image: library/mysql:5.6   environment:     MYSQL_ALLOW_EMPTY_PASSWORD: "yes"   expose:     - "3306"   ports:     - "3306:3306"  rails-app:   build: .   dockerfile: Dockerfile   environment:     RAILS_ENV: development   links:     - db   volumes:     - ".:/home/app"   volumes_from:     - bundle    ... omitted lines ... 

Then, if I run the following:

$ docker-compose run db mysql --host=$DOCKER_LOCALHOST --port=3306 --protocol=tcp -u root < shared/create_common_tables.sql 

I get this error:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.99.100' (111) 

This sounds normal, because I suspect that I have to build before some container that links to db.

I know this because if I run this in this order:

$ docker-compose build rails-app $ docker-compose run -e RAILS_ENV=development rails-app bundle $ docker-compose run -e RAILS_ENV=development rails-app bundle exec rake db:create $ docker-compose run db mysql --host=$DOCKER_LOCALHOST --port=3306 --protocol=tcp -u root < shared/create_common_tables.sql 

It works fine.

But, how can I do to execute this sql before creating any container?

Answer 1

You can load the sql file during the build phase of the image. To do this you create a Dockerfile for the db service that will look something like this:

FROM mysql:5.6 COPY setup.sh /mysql/setup.sh COPY setup.sql /mysql/setup.sql RUN /mysql/setup.sh 

where setup.sh looks something like this:

#!/bin/bash set -e service mysql start mysql < /mysql/setup.sql service mysql stop 

And in your docker-compose.yml you'd change image to build: ./db or the path where you put your files.

Now this works if you have all your sql in a raw .sql file, but this wont be the case if you're using rails or a similar framework where the sql is actually stored in code. This leaves you with two options.

1. Instead of using FROM mysql:5.6 you can use FROM your_app_image_that_has_the_code_in_it and apt-get install mysql .... This leaves you with a larger image that contains both mysql and your app, allowing you to run the ruby commands above. You'd replace the mysql < /mysql/setup/sql with the rails-app bundle exec rake db:create lines. You'd also have to provide an app config that hits a database on localhost:3306 instead of db:3306

2. My preferred option is to create a script which exports the sql into a .sql file, which you can then use to build your database container. This is a bit more work, but is a lot nicer. It means that instead of running rails-app bundle exec rake db:create you'd just run the script to load a db.

Such a script would look something like this:

#!/bin/bash set -e docker-compose build rails-app docker run -d --name mysql_empty mysql:5.6 docker run --link mysql_empty:db -v $PWD:/output project_rails-app export.sh 

where export.sh looks something like this:

#!/bin/bash set -e RAILS_ENV=development rails-app bundle exec rake db:create mysqldump > /output/setup.sql 

You could also replace the docker run script with a second compose file if you wanted to.