MySQL and MariaDB in Docker: Setup, Replication and Performance Tuning

MySQL and MariaDB are the workhorses of the web. Together they power the majority of applications built on WordPress, Laravel, Django, Rails, and countless other frameworks. Running them in Docker is straightforward for development, but production deployments demand careful configuration of InnoDB, replication, and backup procedures that the default Docker images do not provide out of the box.

This guide covers both MySQL (the Oracle-maintained fork) and MariaDB (the community fork), highlighting their differences where they matter and providing Docker-specific configuration for each.

Production Docker Compose Setup

MySQL 8.4

services:
  mysql:
    image: mysql:8.4
    container_name: mysql
    restart: unless-stopped
    ports:
      - "127.0.0.1:3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mysql_root_password
      MYSQL_DATABASE: appdb
      MYSQL_USER: appuser
      MYSQL_PASSWORD_FILE: /run/secrets/mysql_app_password
    secrets:
      - mysql_root_password
      - mysql_app_password
    volumes:
      - mysql-data:/var/lib/mysql
      - ./mysql/my.cnf:/etc/mysql/conf.d/custom.cnf:ro
      - ./mysql/init:/docker-entrypoint-initdb.d:ro
    deploy:
      resources:
        limits:
          cpus: '4.0'
          memory: 8G
        reservations:
          memory: 4G
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "--password=$$MYSQL_ROOT_PASSWORD"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

secrets:
  mysql_root_password:
    file: ./secrets/mysql_root_password.txt
  mysql_app_password:
    file: ./secrets/mysql_app_password.txt

volumes:
  mysql-data:

MariaDB 11.4

services:
  mariadb:
    image: mariadb:11.4
    container_name: mariadb
    restart: unless-stopped
    ports:
      - "127.0.0.1:3306:3306"
    environment:
      MARIADB_ROOT_PASSWORD_FILE: /run/secrets/mariadb_root_password
      MARIADB_DATABASE: appdb
      MARIADB_USER: appuser
      MARIADB_PASSWORD_FILE: /run/secrets/mariadb_app_password
    secrets:
      - mariadb_root_password
      - mariadb_app_password
    volumes:
      - mariadb-data:/var/lib/mysql
      - ./mariadb/my.cnf:/etc/mysql/mariadb.conf.d/custom.cnf:ro
    deploy:
      resources:
        limits:
          cpus: '4.0'
          memory: 8G
    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

Key differences between the Docker images:

my.cnf Tuning

The default configuration is designed for a machine with minimal RAM. Here is a production configuration for a container with 8GB allocated:

[mysqld]
# InnoDB Buffer Pool - the single most important setting
innodb_buffer_pool_size = 5G          # 60-70% of container memory
innodb_buffer_pool_instances = 4       # 1 instance per GB of buffer pool
innodb_buffer_pool_chunk_size = 128M

# InnoDB Log Configuration
innodb_log_file_size = 1G             # Larger = better write throughput
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1    # ACID compliant (2 for speed)
innodb_flush_method = O_DIRECT         # Bypass OS cache (Linux)

# InnoDB I/O
innodb_io_capacity = 2000             # SSD: 2000+, HDD: 200
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# InnoDB Other
innodb_file_per_table = ON
innodb_open_files = 4000
innodb_lock_wait_timeout = 60
innodb_deadlock_detect = ON

# Connection Settings
max_connections = 500
max_connect_errors = 1000000
wait_timeout = 600
interactive_timeout = 600
thread_cache_size = 128

# Memory per-thread
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M

# Query Cache (removed in MySQL 8.0, available in MariaDB)
# For MariaDB: query_cache_type = 0 (disable, use app-level caching)

# Binary Logging (required for replication)
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800    # 7 days
sync_binlog = 1                        # ACID compliant
gtid_mode = ON                         # MySQL 8 (MariaDB uses different syntax)
enforce_gtid_consistency = ON          # MySQL 8 only

# Slow Query Log
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

# Character Set
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# Performance Schema
performance_schema = ON
performance_schema_instrument = '%=ON'

InnoDB Optimization Deep Dive

The InnoDB buffer pool is the single most impactful setting for MySQL/MariaDB performance. It caches data pages and index pages in memory, avoiding disk I/O for frequently accessed data.

-- Check buffer pool hit ratio (should be > 99%)
SELECT
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
  AS hit_ratio
FROM (
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) reads,
(
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) requests;

-- Check InnoDB status for deadlocks and lock waits
SHOW ENGINE INNODB STATUS\G

-- Identify tables without primary keys (bad for InnoDB performance)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS c
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
  AND t.TABLE_NAME = c.TABLE_NAME
  AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND c.CONSTRAINT_NAME IS NULL;

Master-Replica Replication

MySQL and MariaDB asynchronous replication sends binary log events from a primary (master) to one or more replicas:

services:
  mysql-primary:
    image: mysql:8.4
    container_name: mysql-primary
    environment:
      MYSQL_ROOT_PASSWORD: ${ROOT_PASSWORD}
    volumes:
      - mysql-primary-data:/var/lib/mysql
      - ./primary.cnf:/etc/mysql/conf.d/replication.cnf:ro
    ports:
      - "127.0.0.1:3306:3306"

  mysql-replica:
    image: mysql:8.4
    container_name: mysql-replica
    environment:
      MYSQL_ROOT_PASSWORD: ${ROOT_PASSWORD}
    volumes:
      - mysql-replica-data:/var/lib/mysql
      - ./replica.cnf:/etc/mysql/conf.d/replication.cnf:ro
    depends_on:
      mysql-primary:
        condition: service_healthy
    ports:
      - "127.0.0.1:3307:3306"

volumes:
  mysql-primary-data:
  mysql-replica-data:

Primary configuration (primary.cnf):

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

Replica configuration (replica.cnf):

[mysqld]
server-id = 2
relay_log = relay-bin
read_only = ON
super_read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

Configure replication on the replica:

-- On the primary: create replication user
CREATE USER 'replicator'@'%' IDENTIFIED BY 'repl-password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

-- On the replica: start replication (MySQL 8.0.23+)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='mysql-primary',
  SOURCE_USER='replicator',
  SOURCE_PASSWORD='repl-password',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

-- Check replication status
SHOW REPLICA STATUS\G
-- Key fields: Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source

MariaDB Galera Cluster

Galera provides synchronous multi-master replication for MariaDB, where every node can accept writes:

services:
  galera-1:
    image: mariadb:11.4
    container_name: galera-1
    environment:
      MARIADB_ROOT_PASSWORD: ${ROOT_PASSWORD}
    volumes:
      - galera1-data:/var/lib/mysql
      - ./galera.cnf:/etc/mysql/mariadb.conf.d/galera.cnf:ro
    command: >
      --wsrep-new-cluster
      --wsrep-cluster-name=my_cluster
      --wsrep-cluster-address=gcomm://
      --wsrep-node-address=galera-1

  galera-2:
    image: mariadb:11.4
    container_name: galera-2
    environment:
      MARIADB_ROOT_PASSWORD: ${ROOT_PASSWORD}
    volumes:
      - galera2-data:/var/lib/mysql
      - ./galera.cnf:/etc/mysql/mariadb.conf.d/galera.cnf:ro
    command: >
      --wsrep-cluster-name=my_cluster
      --wsrep-cluster-address=gcomm://galera-1,galera-2,galera-3
      --wsrep-node-address=galera-2
    depends_on:
      - galera-1

  galera-3:
    image: mariadb:11.4
    container_name: galera-3
    environment:
      MARIADB_ROOT_PASSWORD: ${ROOT_PASSWORD}
    volumes:
      - galera3-data:/var/lib/mysql
      - ./galera.cnf:/etc/mysql/mariadb.conf.d/galera.cnf:ro
    command: >
      --wsrep-cluster-name=my_cluster
      --wsrep-cluster-address=gcomm://galera-1,galera-2,galera-3
      --wsrep-node-address=galera-3
    depends_on:
      - galera-1

Galera configuration (galera.cnf):

[mysqld]
binlog_format = ROW
innodb_autoinc_lock_mode = 2      # Required for Galera
innodb_flush_log_at_trx_commit = 2
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_sst_method = mariabackup
wsrep_sst_auth = root:${ROOT_PASSWORD}

Backup Strategies

Logical Backups with mysqldump

# Single database backup
docker exec mysql mysqldump -u root -p"${ROOT_PASSWORD}" \
  --single-transaction --routines --triggers --events \
  appdb | gzip > backups/appdb_$(date +%Y%m%d_%H%M%S).sql.gz

# All databases
docker exec mysql mysqldump -u root -p"${ROOT_PASSWORD}" \
  --all-databases --single-transaction --routines --triggers --events \
  | gzip > backups/all_$(date +%Y%m%d_%H%M%S).sql.gz

# Restore
gunzip -c backups/appdb_20250518_120000.sql.gz | \
  docker exec -i mysql mysql -u root -p"${ROOT_PASSWORD}" appdb

Physical Backups with mariabackup

# Full backup (MariaDB only - mariabackup is included in the image)
docker exec mariadb mariabackup --backup \
  --target-dir=/backups/full_$(date +%Y%m%d) \
  --user=root --password="${ROOT_PASSWORD}"

# Prepare the backup (apply log)
docker exec mariadb mariabackup --prepare \
  --target-dir=/backups/full_20250518

# Incremental backup
docker exec mariadb mariabackup --backup \
  --target-dir=/backups/incr_$(date +%Y%m%d_%H%M%S) \
  --incremental-basedir=/backups/full_20250518 \
  --user=root --password="${ROOT_PASSWORD}"

Note: For MySQL, the equivalent tool is mysqlbackup (MySQL Enterprise Backup, paid) or the open-source Percona XtraBackup. Neither is included in the official MySQL Docker image, so you need a custom image or a sidecar container.

Monitoring

Use mysqld_exporter for Prometheus metrics:

  mysqld-exporter:
    image: prom/mysqld-exporter:v0.15.1
    container_name: mysqld-exporter
    restart: unless-stopped
    environment:
      DATA_SOURCE_NAME: "exporter:${EXPORTER_PASSWORD}@(mysql:3306)/"
    ports:
      - "127.0.0.1:9104:9104"
    depends_on:
      mysql:
        condition: service_healthy

Create the exporter user with minimal privileges:

CREATE USER 'exporter'@'%' IDENTIFIED BY 'exporter-password';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
FLUSH PRIVILEGES;

Character Sets and Collation

Always use utf8mb4 instead of utf8 (which is an alias for utf8mb3 in MySQL and supports only 3-byte Unicode, missing emojis and many CJK characters):

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# For MySQL 8.0+, consider the more performant ICU collation:
# collation_server = utf8mb4_0900_ai_ci

[client]
default-character-set = utf8mb4

Upgrading MySQL/MariaDB in Docker

Database upgrades in Docker require care because the data directory format may change between major versions:

1. Backup everything - Full mysqldump plus a volume snapshot
2. Test the upgrade - Run the new version against a copy of your data
3. Stop the old container - docker compose stop mysql
4. Update the image tag - Change mysql:8.0 to mysql:8.4
5. Start with the new version - docker compose up -d mysql
6. Run mysql_upgrade - docker exec mysql mysql_upgrade -u root -p
7. Verify - Check logs, run CHECK TABLE on critical tables

# Upgrade procedure
docker compose stop mysql
docker compose pull mysql         # Pull new image
docker compose up -d mysql        # Start with new version
docker exec mysql mysql_upgrade -u root -p"${ROOT_PASSWORD}"
docker logs mysql --tail 100      # Check for upgrade errors

Docker management platforms like usulnet help track which database versions are running across your infrastructure, making it easier to identify containers that need upgrading and to monitor the health of database containers during the upgrade process.

Production Checklist

1. Storage: Named volumes for data, separate fast storage for binary logs if high throughput
2. Tuning: innodb_buffer_pool_size at 60-70% of container memory, appropriate flush settings for your durability needs
3. Replication: At least one read replica with GTID-based replication, semi-synchronous for critical data
4. Backups: mysqldump (logical) + mariabackup/XtraBackup (physical), tested restore procedure
5. Monitoring: mysqld_exporter for Prometheus, slow query log analysis, InnoDB buffer pool hit ratio alerts
6. Security: Non-root application users, strong passwords via Docker secrets, SSL/TLS for connections
7. Character set: utf8mb4 everywhere, verified on database, table, and column levels