paint-brush
Customizing MariaDB Docker Imagesby@alejandroduarte
4,869 reads
4,869 reads

Customizing MariaDB Docker Images

by Alejandro DuarteOctober 20th, 2022
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

Tips and tricks on how to customize your MariaDB images for development environments, like executing SQL and shell scripts when the containers start or passing custom configuration files.

Companies Mentioned

Mention Thumbnail
Mention Thumbnail
featured image - Customizing MariaDB Docker Images
Alejandro Duarte HackerNoon profile picture


Frequently, I have to deploy MariaDB for development on my working laptop, a dedicated server, and even on Raspberry Pi devices. I try to use Docker when possible to reduce clutter on these machines—it’s very easy to create and delete containers without having to deal with installation and uninstallation procedures.


MariaDB publishes Docker images for all the products I use, including MariaDB Community Server, MariaDB Enterprise Server, ColumnStore, MaxScale, and XPand. Although these images offer flexible configuration options that are great for development or evaluation environments, sometimes it’s useful to have custom images tailored to specific applications. Here are some tips and tricks on how to customize your MariaDB Docker images for development environments.

Using MariaDB Docker images

It’s always recommended to use the official MariaDB Docker images as they are maintained by the team that best knows MariaDB. Here are some of these images:



It’s worth mentioning that there also is a MariaDB Enterprise Docker Registry which provides Docker images for MariaDB Enterprise Server (a hardened, optimized version of MariaDB plus additional components for high levels of scalability, security, reliability, and uptime).


Spinning up a MariaDB container for development doesn’t require major configurations. Typically you’ll need to expose a port and set a root password. This can be done using environment variables. Check each image documentation to learn about the available parameters. Additionally, you might want to set up a custom Docker network and volume. For example, this is how to start a MariaDB database in a Docker container for an application running on the same machine:


docker run --name mariadb-database \
  --detach \
  --volume mariadb-database-volume:/var/lib/mysql \
  --publish 3333:3306 \
  --env MARIADB_ROOT_PASSWORD='the_root_password' \
  --env MARIADB_DATABASE=the_database \
  --env MARIADB_USER=the_app_user \
  --env MARIADB_PASSWORD='the_user_password' \
  mariadb:latest


This configures a detached (running in the background) container with a MariaDB database using a Docker volume named mariadb-database-volume. The database is, accessible on port 3333 (3333:3306 means “forward requests on port 3333 to 3306 in the container”). The root user has the_root_password. the_database is automatically created and the_app_user can access this database using the_user_password. If you want the database to start automatically when Docker starts, add the --restart unless-stopped option somewhere before the image name (mariadb:latest).

Using custom configurations

MariaDB can be configured using system variables and option files (.cnf).

Configuring via command line

You can pass system variables in the command line when you run a container. For example, to enable the binary log, and set a custom server ID and port, you can run the container as follows:


docker run --name mariadb-database \
  --detach \
  --env MARIADB_ROOT_PASSWORD='the_root_password' \
  mariadb:latest \
  --log_bin=primary_log_bin --server_id=1 --port=3333


Check the result using:

docker exec -it mariadb-database \
  mariadb --port 3333 --password='the_root_password' \
    --execute='select @@log_bin, @@server_id'

Configuring via config files

To pass custom configuration files, use Docker volumes. Custom .cnf files should be placed in the /etc/mysql/conf.d/ directory inside the container. For example, you can enable the binary log and set a server ID using the following my-server.cnf file:


[mariadb]
log_bin = primary_log_bin
server_id = 1


To pass this file to the container, place the config file in the current directory and run the container as follows:


docker run --name mariadb-database \
  --detach \
  --volume $PWD:/etc/mysql/conf.d \
  --env MARIADB_ROOT_PASSWORD='the_root_password' \
  mariadb:latest


Check the result using:


docker exec -it mariadb-database \
  mariadb --password='the_root_password' \
    --execute='select @@log_bin, @@server_id'

Running shell and SQL scripts after container creation

Any .sql, .sh (as well as .sql.gz, .sql.xz and .sql.zst) files in the /docker-entrypoint-initdb.d/ directory inside the container, are run after the container is created and initialized. Let’s combine this feature with option files to configure a primary server with a user for replication and a user for MaxScale (check this tutorial if you want to quickly learn about automatic read-write splitting with MaxScale). We need to enable the binary log, set a server ID, and run a SQL script like the following:


CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION REPLICA ON *.* TO 'replication_user'@'%';
RESET MASTER;

CREATE USER 'maxscale_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON mysql.* TO 'maxscale_user'@'%';
GRANT SHOW DATABASES, SLAVE MONITOR ON *.* to 'maxscale'@'%';


To keep things tidy, we can create two directories in the host machine. One to store .cnf files and one to store .sql files:


A directory structure with separate subdirectories for config and SQL files

To pass these files to the container, run it as follows:


docker run --name mariadb-database \
  --detach \
  --volume mariadb-database-volume:/var/lib/mysql \
  --volume $PWD/sql:/docker-entrypoint-initdb.d \
  --volume $PWD/cnf:/etc/mysql/conf.d \
  --env MARIADB_ROOT_PASSWORD='the_root_password' \
  mariadb:latest


All the SQL scripts in your local ./sql directory and all the config files in ./cnf are passed to the container. For examples of how to use .sh files see this GitHub repository.

Creating a custom image for demos

MariaDB Docker images, like any Docker image, can be extended using Dockerfiles. Let’s create a custom MariaDB image for demo purposes (don’t use it in production!). We need the directory structure and files shown in the previous section placed in a new directory (./copy) alongside a new Dockerfile file:


A Dockerfile alongside a “copy” directory that follows the structure of the container’s filesystem

Instead of using Docker volumes, we can simply copy the files to the image filesystem using the COPY instruction. This instruction is placed in a custom Dockerfile:


FROM mariadb
ENV MARIADB_ROOT_PASSWORD="password"
ENV MARIADB_DATABASE="demo"
ENV MARIADB_USER="user"
ENV MARIADB_PASSWORD="password"
COPY primary/copy/ /
EXPOSE 3306


Since the .sql and .cnf files are placed in the expected subdirectories inside the copy/ directory, they will work as described in the previous sections.

What’s next?

After getting a MariaDB database running in a Docker container, you might want to connect to it externally. Remember to publish the port on which MariaDB Server is listening (the default is 3306) using something like --publish 3333:3306 when you run the container. The following examples use 3333 but you can also use any free port in the host, even 3306 (--publish 3306:3306).

Connecting to the database from SQL clients

You can connect to the database using any SQL client that directly supports MariaDB, ODBC, or JDBC. For example DBeaver, or one of the SQL client extensions for VSCode. Configure the host as 127.0.0.1, the port as 3333, the user as the_app_user (or root), and the password as the_user_password (or the_root_password).


You can also use the mariadb CLI client to connect to the database as follows:


mariadb --host 127.0.0.1 --port 3333 --user the_app_user -p


If the container is running on a remote machine and assuming the port is open on the network, just replace the IP address.

Connecting to the database from Java, Python, and Node.js apps

You can connect to the database from Java, Python, Node.js, and others using the MariaDB connectors.


In Java you can use the following JDBC connection string (omitting credentials):


jdbc:mariadb://localhost:3333/the_database


From Python apps:


import mariadb

connection = mariadb.connect(
    host="127.0.0.1",
    port=3333,
    database="the_database"
    user="the_app_user",
    password="the_user_password")


And form Node.js apps:


const mariadb = require('mariadb');

connection = await mariadb.createConnection({
    host: 127.0.0.1',
    port: '3333',
    database: 'the_database',
    user: 'the_app_user',
    password: 'the_user_password'
});


You can find more examples on the quick start page of the MariaDB Developer Hub.