This Guide will briefly show how to setup MariaDB on Docker in rootless- mode as written here https://obel1x.de/dokuwiki/doku.php?id=content:serverbasics:docker|
Note that this will NOT work out of the Box right now, see this bug here: https://github.com/phpmyadmin/docker/issues/187#issuecomment-1872177089 Currently you need to change the file docker-entrypoint.sh manually after downloading the images.
Using Postgres for some time, i switched to mysql and than to mariadb as i like it most, as you can do good performence- measurements with the ability to slowlog.
Remarks to my setup:
On the Host, the directory /run/mysqld/mysqld.sock will be used instead of tcp/ip. This directory needs to be created as tmpfs by systemd at boot.
Create the File /etc/tmpfiles.d/docker-mariadb.conf with content
#Type Path Mode UID GID Age Argument d /run/mysqld 0755 docker docker - -
First, create the directory /srv/docker-compose/mariadb and put the following files and content in it:
File .env
COMPOSE_HTTP_TIMEOUT=180 MARIADB_TAG=11.2.2
File docker-compose.yml
version: '3.8'
#
# Nach einigem hin und her, hat sich mariadb als zuverlässigste datenbank herausgestellt
# Achtung: NICHT mysql, sondern mariadb !
#
services:
mariadb:
image: mariadb:${MARIADB_TAG}
platform: linux/amd64
cap_add:
- SYS_NICE
restart: always
command: --default-storage-engine innodb --transaction-isolation=READ-COMMITTED --log-bin=binlog --binlog-format=ROW
environment:
#Attention: won't work !
# - MARIADB_ROOT_PASSWORD="XXX"
#use this and check the password at the logs of the first start:
- MARIADB_RANDOM_ROOT_PASSWORD=1
- MARIADB_AUTO_UPGRADE=1
# 0 = Null = False, but false does not work here
- MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=0
#Could be set when opening port 3306 to the hosts network and disallow root when connecting from other hosts
#is only set the first time for user creation - after that its fixed and may only be changed after login or internally in the container
#wenn leer, dann wird als host % genommen (also alle)if not set, % = allow all is taken
# - MARIADB_ROOT_HOST='%'
security_opt: # see https://github.com/MariaDB/mariadb-docker/issues/434#issuecomment-1136151239
- seccomp:unconfined
- apparmor:unconfined
#can be set to prevent some limitations. as long as the service is fine, don't change!
# ulimits:
# nproc: "262144"
# nofile:
# soft: "262144"
# hard: "262144"
# memlock: "262144"
healthcheck:
test: healthcheck.sh --connect --innodb_initialized
interval: 20s
start_period: 10s
timeout: 10s
retries: 3
#This opens the IP- Port of MariaDB - i won't do this as the socket /run/mysqld/mysqld.sock is more efficient
# ports:
# - 3306:3306
volumes:
- mariadb_data:/var/lib/mysql
- mariadb_log:/var/log/mysql
- ./my.cnf:/etc/mysql/conf.d/my.cnf
- /run/mysqld:/run/mysqld
#This won't work as mariadb does not get lock to that file when managed by docker (don't now why exactly)
# - /run/mysqld/mysqld.sock:/var/run/mysqld/mysqld.sock
tmpfs:
- /tmp
networks:
- mariadb
phpmyadmin:
image: phpmyadmin:latest
platform: linux/amd64
restart: always
ports:
- 8081:80
environment:
- PMA_ARBITRARY=1
- PMA_ABSOLUTE_URI=http://pcserver2023:8081/
- PMA_HOSTS=localhost
# - PMA_PORTS=/run/mysqld/mysqld.sock
- PMA_PMADB=phpmyadmin
volumes:
# Own Config in local config.user.inc.php
- ./config.user.inc.php:/etc/phpmyadmin/config.user.inc.php:ro
- /run/mysqld:/run/mysqld
healthcheck:
test: "curl localhost:80"
interval: "60s"
timeout: "3s"
start_period: "5s"
retries: 3
depends_on:
- mariadb
networks:
- mariadb
volumes:
mariadb_data:
driver_opts:
device: ""
type: ""
o: "umask=0007"
mariadb_log:
driver: local
driver_opts:
device: ""
type: ""
o: "umask=0007"
networks:
mariadb:
File my.cnf
[server] max_connections = 12 # skip_name_resolve = 1 #und Statistiken ausschalten innodb_stats_on_metadata = 0 # max_allowed_packet = 16M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #tmp-tables werden zum beispiel für update-select verwendet. wenn die hier nicht rein passen, werden sie auf der Platte #erzeugt. #Achtung: Warnungen, dass 0_TMPFILE nicht angelegt werden können, sind normal(!), hindern nicht an der Ausführung, solange mysql startet #Diese soll gleich sein mit max_heap_table_size tmp_table_size = 1024M max_heap_table_size = 1024M #Timeouts hochsetzen # 3600 ist wohl zu viel, sagt mysqltuner wait_timeout = 300 # damit wird dann nach 6 minuten inaktivität doch mal getrennt interactive_timeout = 360 #[Warning] 'innodb-buffer-pool-instances' was removed. It does nothing now and exists only for compatibility with old my.cnf files. #innodb_buffer_pool_instances = 1 # Standard 128 - wir haben 16GB Hauptspeicher und das soll eine große DB werden. Also diese auch etwas nutzen. #innodb_buffer_pool_size = 128M innodb_buffer_pool_size = 1024M #log_file_size sollen 25% des buffer-pools sein #sollen aber nicht mehr als 256M sein sagt der ratgeber... mal sehen innodb_log_file_size = 256M innodb_log_buffer_size = 32M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = fsync innodb_max_dirty_pages_pct = 90 query_cache_type = 1 query_cache_limit = 56M query_cache_min_res_unit = 2k query_cache_size = 64M low-priority-updates=on join_buffer_size = 256M sort_buffer_size = 4M read_rnd_buffer_size = 2M #Nur fuer MyISAM-Tabellen: war 128M und fast nicht benutzt. soll man runterschalten, zur Performancesteigerung key_buffer_size=64M #Bin-Log: sehr wichtig! log_bin=mysql-bin binlog_format = ROW expire_logs_days=3 #Slow Logs - Todos: Wie kann man das in Docker gut machen - also persistent, aber OHNE zuviel Datenmüll? #solange ich die rotation nicht an habe, würde das zuviel schreiben slow_query_log_file = /var/log/mysql/slow.log log-slow-verbosity=explain #slow_query_log = 0 # #Möglichkeit 1: herausfinden vieler disk-zugriffe #slow-query-log = 1 ## Alder - default=sp - aber keiner weiss, wofür sp steht! Also raus! #log-slow-disabled_statements=admin #Deprecated mit MariaDB11 - "use log_slow_filter without admin" #log-slow-admin-statements=off #long-query-time=0 #log-slow-filter=filesort_on_disk,tmp_table_on_disk #Möglichkeit 2: Lange Queries wegen fehlender Indizes slow-query-log = 1 long-query-time=1 #log-slow-filter=not_using_index [client] default-character-set = utf8mb4 socket = /run/mysqld/mysqld.sock [mysqld] character_set_server = utf8mb4 collation_server = utf8mb4_general_ci transaction_isolation = READ-COMMITTED innodb_file_per_table=1 #[Warning] 'innodb-large-prefix' was removed. It does nothing now and exists only for compatibility with old my.cnf files. #innodb_large_prefix=on #[Warning] 'innodb-defragment' was removed. It does nothing now and exists only for compatibility with old my.cnf files. #innodb-defragment=1
File config.user.inc.php
<?php declare(strict_types=1); # Settings for phpMyAdmin # $cfg['ShowPhpInfo'] = true; // Adds a link to phpinfo() on the home page $cfg['AllowArbitraryServer'] = false; // Array starts at 1 ... $i = 1; $cfg['Servers'][$i]['host'] = 'localhost'; $cfg['Servers'][$i]['socket'] = '/run/mysqld/mysqld.sock'; #$cfg['Servers'][$i]['auth_type'] = 'http';