Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Nächste Überarbeitung		 Vorhergehende Überarbeitung
content:serverbasics:docker-mariadb [2023/12/29 16:08] – angelegt Danielcontent:serverbasics:docker-mariadb [2023/12/31 16:35] (aktuell) – [Yaml and .env, configuration of mariadb and phpMyAdmin] Daniel
Zeile 5: Zeile 5:
 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|https://github.com/phpmyadmin/docker/issues/187#issuecomment-1872177089]] Currently you need to change the file docker-entrypoint.sh manually after downloading the images. 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|https://github.com/phpmyadmin/docker/issues/187#issuecomment-1872177089]] Currently you need to change the file docker-entrypoint.sh manually after downloading the images.
  
-===== Yaml and .env =====+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.
  
-i created the directory ''/srv/docker-compose/mariadb'' and put the following files and content in it:+Remarks to my setup:
  
-''.env'' +  * Tuned mariadb for Nextcloud 
-<code> +  * Using Sockets, not TCP/IP on the host 
-#Allgemeine Einstellungen für docker/mariadb+  * Added some tweaks from the net 
 + 
 +===== Create Socket in tmpfs ===== 
 + 
 +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 
 +<file> 
 + 
 +#Type Path            Mode UID      GID    Age Argument 
 +d     /run/mysqld     0755 docker   docker -   - 
 + 
 +</file> 
 +===== Yaml and .env, configuration of mariadb and phpMyAdmin ===== 
 + 
 +First, create the directory ''/srv/docker-compose/mariadb'' and put the following files and content in it: 
 + 
 +File ''.env'' 
 +<file>
 COMPOSE_HTTP_TIMEOUT=180 COMPOSE_HTTP_TIMEOUT=180
  
-#Diese Variabeln hier sind ganz anders, als die env-files: sie können als parameter in der yaml verwendet werden, nicht so die .env-files der dienste 
-#vgl: 
-# .env file which is used for variable substitution in the docker-compose.yaml file, and should resolve when you run docker-compose config 
-# env_file element in a service definition, which is just setting the environment file to send to docker engine when starting container, as a definition of runtime 
-environment. Compose will not parse this file which is opaque to him. 
-# 
-#Also die globalen Werte müssen hier rein! 
-#Mariadb immer dreistellig, möglichst stable 
 MARIADB_TAG=11.2.2 MARIADB_TAG=11.2.2
  
 +</file>
 +
 +File ''docker-compose.yml''
 +<file>
 +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> </file>
 +
 +File ''my.cnf''
 +<file>
 +[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>
 +
 +File ''config.user.inc.php''
 +<file>
 +<?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';
 +
 +</file>
 +
  
  • content/serverbasics/docker-mariadb.txt
  • Zuletzt geändert: 2023/12/31 16:35
  • von Daniel