Install MariaDB or MySQL with Docker: Users, Volumes and Backups

MariaDB and MySQL #

MariaDB is a fork of MySQL and they use the same protocol, so the same client library connects to both. The commands in this tutorial use MariaDB; for MySQL replace the image with mysql:8 and the mariadb commands with mysql and mysqldump.

Run MariaDB #

docker run -d --name mariadb \
  -e MARIADB_ROOT_PASSWORD=change-me \
  -e MARIADB_DATABASE=appdb \
  -e MARIADB_USER=app -e MARIADB_PASSWORD=app-secret \
  -p 127.0.0.1:3306:3306 \
  -v mariadb-data:/var/lib/mysql \
  mariadb:11

The variables create a database and a user the first time the container starts. The volume mariadb-data keeps the data (see Docker volumes). Wait a few seconds and connect:

docker exec -it mariadb mariadb -u app -papp-secret appdb

First SQL #

CREATE TABLE notes (id INT AUTO_INCREMENT PRIMARY KEY, body VARCHAR(200) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO notes (body) VALUES ('hello MariaDB');
SELECT * FROM notes;
SHOW DATABASES;
SHOW TABLES;

Create another user with limited rights as root:

CREATE USER 'reporter'@'%' IDENTIFIED BY 'report-secret';
GRANT SELECT ON appdb.* TO 'reporter'@'%';

Docker Compose with initial data #

services:
  db:
    image: mariadb:11
    environment:
      MARIADB_ROOT_PASSWORD: change-me
      MARIADB_DATABASE: appdb
      MARIADB_USER: app
      MARIADB_PASSWORD: app-secret
    ports:
      - "127.0.0.1:3306:3306"
    volumes:
      - mariadb-data:/var/lib/mysql
      - ./init:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 10s
      timeout: 5s
      retries: 5
volumes:
  mariadb-data:

Files in init/ (.sql, .sql.gz or .sh) run once on an empty data directory. Other services connect to the host db on port 3306.

Configuration #

Mount a file in /etc/mysql/conf.d/ for server options:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections = 200
innodb_buffer_pool_size = 256M

Backup and restore #

docker exec mariadb mariadb-dump -u root -pchange-me --single-transaction appdb > appdb.sql
docker exec -i mariadb mariadb -u root -pchange-me appdb < appdb.sql

--single-transaction produces a consistent backup of InnoDB tables without locking them.

MariaDB on AWS #

In AWS use Amazon RDS instead of running the database yourself. The tutorial MariaDB on RDS with Terraform creates one with infrastructure as code.