Import data into MySql with docker-compose

Having a docker-compose setup which involves a Database like Mysql or MariaDB, then at some point you might want to import data into those Databases.

There are several ways to import the data in your docker-compose setup.

1. Using a volume for import data
2. Using mysql client from commandline with docker-compose exec
3. Using phpmyadmin in docker-compose setup
4. Using a mysql GUI client on the host and connect to the DB in the Docker container

So let’s see how we can do this:

1. Using a volume for import data

This applies to both official Mysql and MariaDB official images.
These have builtin import mechanism to import data.
To quote from the docs:

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

So all dumps that are found in the /docker-entrypoint-initdb.d directory of the image will be imported unless the database already contains data.

So let’s add this volume to our docker-compose.yaml

volumes: - ./database_dump.sql:/docker-entrypoint-initdb.d/datadump.sql

On next docker-compose up the data will be imported in your empty database.
You will see something like this in your docker-compose logs:

2023-02-06 15:40:16+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/database_dump.sql

Note: if there is already data in the database you need to clear the data first by f.e. clearing the volume, otherwise the import will not start:
You can clear all volumes of your docker-compose setup with (caution: this will clear all volumes, not just the database volume):

docker-compose down -v

2. Using mysql client from commandline with docker-compose exec

With this one liner you can import a SQL dump from a docker image that has a MySql/MariaDB client installed and are linked to the DB container:

docker-compose exec my-app bash -c "mysql -u root -h mysql --password=root database < database_dump.sql"Code language: JavaScript (javascript)

This will execute the application image and import the data with the mysql client which connects to the host mysql which is the linked mysql container.

3. Use phpmyadmin in docker-compose setup

If the above is too consolish then you also can just add a phpmyaadmin container in your docker-compose setup and administer the database with a GUI from the browser.

  myproject_phpmyadmin:
	image: phpmyadmin/phpmyadmin:latest
	ports:
	  - 8080:80
	environment:
	  PMA_HOST: myproject_mysql

Then you can just open http://localhost:8080 in the browser and log into to Phpmyadmin.
To import data go to the import tab and upload the dump file.

4. Using a mysql GUI client on the host and connect to the DB in the Docker container

For this approach you just need to open a port in the database container to the outside and use this port with localhost in the settings of your database client.

  myproject_mysql:
    image: mysql:5.7
    hostname: mysql.${DOMAIN}
    container_name: ${CONTAINER_NAME}.mysql
      volumes:
	- ${PWD}/data/mysql/:/var/lib/mysql
	- ./container/mysql/my.cnf:/etc/mysql/conf.d/z_my.cnf
    ports: # open the mysql port to the host
      - "3306:3306"
    environment:
  ...Code language: PHP (php)

See this screenshot from IntellIj’s PHPStorm Database tool:

From my experience these clients are very good to check data but not so good for import, at least I had rather poor experience in the PHPStorm DB Tool.
So going consolish on imports is recommended :)