content:serverbasics:docker-mariadb [OBEL1X.de]

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== Docker: MariaDB ======

This Guide will briefly show how to setup MariaDB on Docker in rootless- mode as written here [[.:docker|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|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:

  * Tuned mariadb for Nextcloud
  * Using Sockets, not TCP/IP on the host
  * Added some tweaks from the net

====== STOP ======

This - it turned out is a very bad idea. MariaDB on Docker performs VERY bad. So i would strongly not advise to use Docker for this. Use native mariadb on your host and adept what is written here.

When you link the native mariadb- Socket to the services like this, you will be able to use Mariadb:
<file>

  volumes:
#Bind mount: Socketfile needs to be definied by full filename, not only path!
    - /run/mysql/mysql.sock:/run/mysql/mysql.sock

</file>

Or you can use Port 3306 and as host your servername (NOT localhost).

===== 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

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 ''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>

===== Backup =====

Backing up MariaDB on Docker works like this:

<file>
#!/bin/bash
# This would be Postgres - i don't like postgres, so see beneath for mariadb:
# rm /backup/nextcloud_pgdumpall.zstd
# docker exec -t postgresql-pgsql_db-1 pg_dumpall -c -U postgres  | zstd -19 -o /backup/nextcloud_pgdumpall.zstd --no-progress
# For mariadb use mariadb-dump and root
# For mysql use mysqldump and admin
BACKUPFILE=/backup/nextcloud_mariadbdump.zstd
MARIADB_PASS='verysecretpassword'
echo "Backup of mariadb to ${BACKUPFILE}"
rm ${BACKUPFILE}
docker exec -t mariadb-mariadb-1 mariadb-dump --all-databases --single-transaction --quick --lock-tables=false -u root -p${MARIADB_PASS}  | zstd -19 -o ${BACKUPFILE} --no-progress
echo 'Backup done.'

</file>

==== No Docker Service ====

If you have MariaDB as native Host- Service installed, use:
<file>

#!/bin/bash
# Makes a Backup of the whole Mariadb
BACKUPFILE=/root/backup/mariadb_dump_all.zstd
echo "Fullbackup of MariaDB into ${BACKUPFILE}..."
# Keep one Copy of the old Backup
if [ -f ${BACKUPFILE} ]; then
    if [ -f ${BACKUPFILE}.back ]; then
        rm ${BACKUPFILE}.back
    fi
    mv ${BACKUPFILE} ${BACKUPFILE}.back
else
    echo "File ${BACKUPFILE} was not found, not removing ${BACKUPFILE}.back"
fi
# for mysql use mysqldump...
/usr/bin/mariadb-dump --all-databases --single-transaction --quick --lock-tables=true -u root -p'SECRETPASSPHRASE' | zstd -19 -o ${BACKUPFILE} --no-progress
if [ ! -f ${BACKUPFILE} ]; then
    echo "ERROR: File ${BACKUPFILE} was not created - NO BACKUP PERFORMED. Please check the Backup."
    exit 1
fi

</file>

===== Optimizing Tables =====

Doing this will remove defragmentation and repair some stuff, so maybe you want to do this once a month or so ''mariadb_optimize.sh'':
<file>

#!/bin/bash
#Optimize Database
# 28.11.2023: Docker-Version
# 13.12.2023: Replaced mysql by mariadb
#
echo "Check and optimize DB Mysql"

RUN_SQL=/srv/backupscripts/backupfiles/check_all_tables.sql
RUN_LOG=/srv/backupscripts/backupfiles/check_all_tables.log

#SQL="SELECT CONCAT('ANALYZE LOCAL TABLE \`',table_schema,'\`.\`',table_name,'\`;')"
SQL="SELECT CONCAT('CHECK TABLE \`',table_schema,'\`.\`',table_name,'\` EXTENDED;')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql','sys','innodb')"
SQL="${SQL} AND engine IS NOT NULL"

MARIADB_USER='root'
MARIADB_PASS='verysecretpassword'
CONTAINERNAME='mariadb_mariadb_1'

RUN_CMD="docker exec -i ${CONTAINERNAME} mariadb -u${MARIADB_USER} -p${MARIADB_PASS}"

# Create SQL Commands to run
${RUN_CMD} -ANe"${SQL}" --raw --silent | grep TABLE> ${RUN_SQL}
# Execute CHECK TABLE Commands
${RUN_CMD} --raw --silent --table <${RUN_SQL}> ${RUN_LOG} 2>&1

#cat "${RUN_LOG}" | grep check
#RUN_ERROR=(`cat "${RUN_LOG}" | grep check`)
RUN_ERROR=(`cat "${RUN_LOG}" | grep error`)
if [[ ! -z "${RUN_ERROR}" |]]; then
  echo "***********************************************"
  echo ""
  echo "!!!! ERROR- STOP OPTIMIZE: Some MySQL Databases are corrupt, please check output in ${RUN_LOG}:"
  cat "${RUN_LOG}"
  echo ""
  echo "***********************************************"
  exit 1
fi
echo "Check MysqlDB was sucessful, no errors found"

exit 0

#For me, i choose to run this only every 6th of the month
ifStart=`date '+%d'`
if [ $ifStart == 06 ]
then
  echo "Optimize DB Mysql"
  RUN_SQL=/srv/backupscripts/backupfiles/optimize_all_tables.sql
  RUN_LOG=/srv/backupscripts/backupfiles/optimize_all_tables.log

  SQL="SELECT CONCAT('OPTIMIZE TABLE \`',table_schema,'\`.\`',table_name,'\`;')"
  SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
  SQL="${SQL} ('information_schema','performance_schema','mysql','sys','innodb')"
  SQL="${SQL} AND engine IS NOT NULL"

  # Create SQL Commands to run
  ${RUN_CMD} -ANe"${SQL}" --raw --silent | grep TABLE> ${RUN_SQL}

  # Execute Commands
  ${RUN_CMD} --raw --silent --table <"${RUN_SQL}"> ${RUN_LOG} 2>&1
  cat "${RUN_LOG}"
  RUN_ERROR=(`cat "${RUN_LOG}" | grep error`)
  if [[ ! -z "${RUN_ERROR}" |]]; then
    echo "***********************************************"
    echo ""
    echo "!!!! ERROR- STOP OPTIMIZE: Some MySQL Databases are corrupt, please check output in ${RUN_LOG}:"
    echo ""
    echo "***********************************************"
    exit 1
  fi
  # Useless for Docker...
  #  /sbin/btrfs filesystem defragment /srv/faststorage/mysql -r
fi
echo "Optimize finished sucessfully"
exit 0

#Diese Version geht leider nicht, weil die KOmmandos im Docker nicht vorhanden sind...
#docker exec -t mysql-mysqldb-1 mysqlcheck -u admin -p'Ps.xz!)6JLn/YoGL' --check --auto-repair --all-databases 1>/dev/null
#docker exec -t mysql-mysqldb-1 mysqlcheck -u admin -p'Ps.xz!)6JLn/YoGL' --check --auto-repair --all-databases

</file>