MariaDB and Docker Compose

This is a short instructions how to run #mariadb with #docker official image with docker compose and have suitable developer env. Let's have follow docker-compose.yml

version: "3.9"

volumes:  
  mysql:
  
services:

  mariadb:
    image: mariadb:latest
    environment:
      TZ: Europe/Prague
      MARIADB_ROOT_PASSWORD: root
      MARIADB_DATABASE: exampledb
      MARIADB_AUTO_UPGRADE: "yes" # will run mysql_uprade
    restart: always
    volumes:
        - mysql:/var/lib/mysql # mysql data      
      - ./etc/mysql.ini:/etc/mysql/conf.d/my.ini # config file
      - ./etc/db/mariadb:/docker-entrypoint-initdb.d/:ro # init sql
    ports:
      - "3306:3306"
# php-fpm:
# nginx:
# ...

Optionally you can define database with config file - here is mine ./etc/mysql.ini:

[mysqld]
character-set-server=utf8
collation-server=utf8_czech_ci

Initializing a fresh instance

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql, .sql.gz, .sql.xz and .sql.zst that are found in /docker-entrypoint-initdb.d

Let's create ./etc/db/mariadb/01-init.sql file that will create database:

CREATE DATABASE IF NOT EXISTS exampledb CHARACTER SET utf8 COLLATE utf8_general_ci;

Then you can add 02-schema.sql file with database schema and 03-data.sql file with init data. You can commit those files to git and share them with all coworkers.

Now it's time run docker compose up

Connect to database prompt

docker compose exec mariadb bash -c 'mysql -u root -p$MARIADB_ROOT_PASSWORD $MARIADB_DATABASE'

Backup database

First backup database schema

docker compose exec mariadb bash -c 'mysqldump --no-data -uroot -p$MARIADB_ROOT_PASSWORD $MARIADB_DATABASE --databases $MARIADB_DATABASE | sed "s/ AUTO_INCREMENT=[0-9]*\b//g"' > etc/db/mariadb/02-scheme.sql

then backup data

docker compose exec mariadb bash -c 'mysqldump --hex-blob --no-create-info --databases $MARIADB_DATABASE --skip-extended-insert --complete-insert=true --default-character-set=utf8 -uroot -p$MARIADB_ROOT_PASSWORD $MARIADB_DATABASE' > etc/db/mariadb/03-data.sql

or you can backup everything together

docker compose exec mariadb bash -c 'mysqldump -uroot -p$MARIADB_ROOT_PASSWORD $MARIADB_DATABASE --databases $MARIADB_DATABASE' > backup/database.sql

Restore database from SQL file

docker compose exec mariadb bash -c 'mysql -uroot -p$MARIADB_ROOT_PASSWORD $MARIADB_DATABASE' < backup/database.sql

Execute `mysql_upgrade` after upgrade

docker compose exec mariadb bash -c 'mysql_upgrade -uroot p$MARIADB_ROOT_PASSWORD'