It’s not uncommon for applications to use more than one database. We may need to place sensitive information on a different database with its own credentials. Or maybe our application is multi-tenant, and we want one database per tenant.
In this short article, we’ll see how to run our services with docker-compose and MariaDB to access multiple databases.
Also, the following explanation does not cover incremental upgrades of the database. That can be achieved on the application layer with migrations.
2. Export Schema and Data With MariaDB
The approach we’ll take in this tutorial is backing up two already existing databases. Then we’ll use that backup as the base to rebuild those databases from scratch on a new system with docker-compose.
Let’s suppose we already have our databases, db1 and db2, in our development environment. We can use mariadb-dump to back them up.
$ mariadb-dump --skip-add-drop-table --databases db1 db2 > databases-backup.sql
The command will back up both the schemas and the data. We could add the –no-data argument if we only want the schemas.
We need the –skip-add-drop-table argument to skip the delete table commands. Later, docker-compose should initialize the database only once. But even if it tries to initialize the database again, our tables and data won’t be deleted.
This is the databases-backup.sql from the command above:
... CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` USE `db1`; CREATE TABLE `table1` ( ... ... INSERT INTO `table1` VALUES ( ... CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` USE `db2`; ... CREATE TABLE `table2` ( ... ... INSERT INTO `table2` VALUES ( ...
This one backup contains both databases. It will be the main part of our docker-compose configuration to create a new environment.
3. Create the docker-compose File
Let’s begin by showing the docker-compose.yml file. We’ll explain the main sections later.
version: '3.8' services: db: image: mariadb environment: MYSQL_ALLOW_EMPTY_PASSWORD volumes: - databases:/var/lib/mysql - ./databases-backup.sql:/docker-entrypoint-initdb.d/databases-backup.sql app: build: ./app ports: - 80:80 volumes: databases:
We can start the services by executing:
$ docker-compose up
Let’s focus on the volumes section at the end of the YAML file. Docker containers are ephemeral, nothing we change survives after a restart. Volumes address that issue by providing storage outside the container and independent of it.
In our example above, we requested a volume and named it databases. After that, we need to mount the volume for MariaDB, in /var/lib/mysql. That can be seen in the volumes section inside our db service.
The second line in volumes doesn’t request a volume but is still really important. It mounts our backup from the last section to a specific location in the container. This was taken from the MariaDB Docker image documentation. To initialize a fresh instance, we need our backup file inside the /docker-entrypoint-initdb.d folder.
As soon as our docker-compose is up, we can access the database by referring to its service name. For example, inside the app service, we could execute the MariaDB terminal client to access both databases:
$ mariadb -h db > USE db1; > SELECT * FROM table1; ... > USE db2; > SELECT * FROM table2; ...
In the docker-compose.yml file, we only exposed port 80 on the app service, but no port in the database. This means MariaDB will only be accessible to other services in docker-compose, while the app will be accessible from outside.
In this short article, we learned that one way we can manage multiple databases is using MariaDB and docker-compose. MariaDB provides a handy way to initialize the database, while docker-compose provides the wiring to connect all other services with the database server.
Although there are other ways to achieve the same result, the one explained here is among the easiest to implement.