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.
- Using a volume for import data
- Using mysql client from commandline with docker-compose exec
- Using phpmyadmin in docker-compose setup
- 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
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
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:
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.
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.
ports: # open the mysql port to the host
...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 :)