Using docker-compose in order to create a MySQL schema/database

Question

I am trying to create a mysql database/schema if it doesn't already exist.

Here is what I have tried:

docker-compose.yml

mysql:
  image: mysql:5.6.26
  environment:
   - MYSQL_ROOT_PASSWORD=root
  command: "mysql -uroot -proot < createDB.sql"
  ports:
    - "3306:3306"

createDB.sql

CREATE DATABASE IF NOT EXISTS bignibou;

It does not work. What would be the best way to use docker/docker-compose in order to create a schema if it does not exist?

The issue with my above docker-compose is that `command:` is supposed to run the container. I am therefore looking for a way to run a post command on the container... — balteo, Aug 17 '15 at 15:18
Thanks. See my comment above. The issue is that the `command` replaces the entry point of the mysql image i.e. `mysqld`. I need a way to run both command and I am not sure what the syntax is with docker-compose... — balteo, Aug 17 '15 at 15:25
It just gives me the usage of mysql in the form of `Usage: mysql [OPTIONS] [database]` etc... — balteo, Aug 17 '15 at 15:28
It is really down to finding the correct syntax in order to run this post command on the docker container once it is running. But I am not sure how to do that... — balteo, Aug 17 '15 at 15:29
To run post command try: `docker exec -i db mysql -uroot -proot bignibou < createDB.sql` — Valery Viktorovsky, Aug 24 '15 at 15:35
@ValeryViktorovsky: where shall I place this command? Do you mean as a "standalone" command separate from the docker-compose.yml file? — balteo, Aug 24 '15 at 15:37

balteo · Accepted Answer · 2015-08-24T16:15:43.003

I finally found the beginning of a solution.

The MySQL image takes an environment variable i.e. MYSQL_DATABASE that initialize the container with the name of the database on image startup See here for full documentation.

Or read the excerpt below:

MYSQL_DATABASE

This variable is optional and allows you to specify the name of a database to be created on image startup. If a user/password was supplied (see below) then that user will be granted superuser access (corresponding to GRANT ALL) to this database.

Here is what I came up with:

mysql:
  image: mysql:5.6.26
  environment:
   - MYSQL_ROOT_PASSWORD=root
   - MYSQL_DATABASE=bignibou
  ports:
    - "3306:3306"

I now need a way to specify the default collation but that is another story...

edit: For those interested in specifying a different collation from the default, here are the instructions to use another config file that will override the default one. See below:

Using a custom MySQL configuration file The MySQL startup configuration is specified in the file /etc/mysql/my.cnf, and that file in turn includes any files found in the /etc/mysql/conf.d directory that end with .cnf. Settings in files in this directory will augment and/or override settings in /etc/mysql/my.cnf. If you want to use a customized MySQL configuration, you can create your alternative configuration file in a directory on the host machine and then mount that directory location as /etc/mysql/conf.d inside the mysql container.

If /my/custom/config-file.cnf is the path and name of your custom configuration file, you can start your mysql container like this (note that only the directory path of the custom config file is used in this command):

$ docker run --name some-mysql -v /my/custom:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag This will start a new container some-mysql where the MySQL instance uses the combined startup settings from /etc/mysql/my.cnf and /etc/mysql/conf.d/config-file.cnf, with settings from the latter taking precedence.

Here is a way to specify default collation in the docker-compose.yml file: mysql: image: mysql:5.7 command: [mysqld, --character-set-server=utf8mb4, --collation-server=utf8mb4_unicode_ci] — William, Dec 20 '16 at 17:26

score 4 · Answer 2 · answered May 08 '18 at 09:10

To not lost your data better use volumes as well:

version: '3'
services:
  db:
    image: mysql:5.7
    volumes:
        - mysql-db:/var/lib/mysql
    environment:
        MYSQL_ROOT_PASSWORD: root
        MYSQL_DATABASE: my_db_name
    ports:
        - "3307:3306"
volumes:
  mysql-db:

score 2 · Answer 3 · edited May 23 '17 at 12:02

2

probably what you are trying to do needs an additional script. So if building an image instead of directly using a prebuilt image is an option for you, you need to use a Dockerfile and use a script file which first imports the script in MySql and then runs the service itself.

take a look at this answer: Docker - Initialize mysql database with schema

edited May 23 '17 at 12:02

Community

1
1

answered Aug 20 '15 at 21:18

mohamnag

2,709
5
27
40

Hi @mohamnag and thanks for your reply! I have added a comment to grasshopper's reply that applies to your post too. – balteo Aug 24 '15 at 15:24
the above mentioned link is the situation where you have a dockerfile and you dont know if DB is just a new one or is a older one which shall not be initialised. the best solution in your case very much depends on what version shall be upgraded to which version. some times mounting an external volume and reusing it on new one will work sometimes not. – mohamnag Aug 25 '15 at 19:22

score 0 · Answer 4 · answered Aug 17 '15 at 19:58

0

From the docker-compose documentation - see Define Services - you can tell which Dockerfile it will use to build the image. Therefore you can create a Dockerfile based on the mysql image and create the database inside it using standard Dockerfile commands.

answered Aug 17 '15 at 19:58

Grasshopper

1,749
1
14
30

Hi. Umm. The only issue is that if I want to upgrade to another version of mysql, I need to change the Dockerfile and update it with my custom command that creates the database. It is not a big deal but not completely clean as I am not extending the Dockerfile but modifying it... – balteo Aug 24 '15 at 15:22

score 0 · Answer 5 · answered Aug 03 '17 at 18:03

This might be useful in case someone lands here in future. The real issue appears to be the "command" statement in the docker-compose file. Once the command finishes successfully the container will get destroyed. This sql script must be run only after docker-compose has run and containers have been created. docker-compose "command" is really to start a service in the container. In this case you overrode the mysql service with your command.

Using docker-compose in order to create a MySQL schema/database

5 Answers5