yovanggaanandhika/mariadb repository overview

$ backup

$ restore

name: dka-database

services:
  mariadb:
    image: yovanggaanandhika/mariadb
    container_name: dka-database-mariadb
    hostname: dka-database-mariadb
    restart: always
    environment:
      DKA_ROOT_PASSWORD: root
      DKA_DB_NAME: dka
      DKA_DB_USERNAME: dka
      DKA_DB_PASSWORD: dka
      DKA_CRON_ENABLE: true
      DKA_CRON_PRIODIC: 0 3 * * *
    ports:
      - target: 3306
        published: 3306
        protocol: tcp
        host_ip: 0.0.0.0
    deploy:
      resources:
        limits:
          memory: 500M
          cpus: '1.0'
        reservations:
          memory: 300M
          cpus: '0.8'
    volumes:
      - type: volume
        source: data
        target: /var/lib/mysql
      - type: volume
        source: backup
        target: /backup
    networks:
      - default


volumes:
  data:
    driver: local
  backup:
    driver: local

networks:
  default:
    driver: bridge

[mysqld]
bind-address = 0.0.0.0
port = 3306

# User and directories
user = mysql
basedir = /usr
datadir = /var/lib/mysql
socket = /run/mysqld/mysqld.sock

# Docker-specific optimizations
skip-host-cache
skip-name-resolve

thread_handling = pool-of-threads
thread_pool_size = 4  # Sesuaikan dengan 4 thread untuk 1 core CPU

# Disable symbolic-links to prevent issues within containers
symbolic-links = 0

# Logging settings
# log_error is not set to avoid permission issues
general_log = 1
general_log_file = /var/log/mysql/general.log  # Redirect access logs to a file
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log  # Redirect slow query logs to a file
long_query_time = 2  # Log queries that take longer than 2 seconds

# General optimizations
max_connections = 150
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

# Tuning file system for Docker
innodb_flush_method = O_DIRECT
innodb_use_native_aio = 1

# Avoid file locking issues
innodb_file_per_table = 1

# Optimizations to prevent corruption and ensure durability
innodb_flush_log_at_trx_commit = 2  # Adjusted for performance while maintaining some durability
innodb_doublewrite = 1
innodb_buffer_pool_size = 512M  # Increase as needed based on container limits

innodb_log_buffer_size = 32M  # Adjusted for performance
sync_binlog = 1  # Set to 1 for better durability; will flush binlog after each transaction

# Enable crash recovery options
innodb_flush_log_at_timeout = 1  # Flush the log file every second
innodb_log_file_size = 64M  # Size of each log file (increase as needed)

# Backup and recovery optimizations
innodb_fast_shutdown = 1  # Allow faster shutdown, but ensure data is flushed

# Query cache settings (optional, depending on your workload)
query_cache_type = 1  # Enable query cache
query_cache_size = 64M  # Set cache size
query_cache_limit = 1M  # Limit for individual query cache

# Connection settings
wait_timeout = 60  # Close idle connections sooner
interactive_timeout = 60  # Same for interactive sessions

# Thread settings
thread_cache_size = 8  # Improve thread reuse

# Temporary tables
tmp_table_size = 64M  # Increase temporary table size
max_heap_table_size = 64M  # Same as above