Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
content:serverbasics:docker-mariadb [2023/12/29 16:29] – Danielcontent:serverbasics:docker-mariadb [2025/03/29 19:46] (aktuell) – Daniel
Zeile 12: Zeile 12:
   * Using Sockets, not TCP/IP on the host   * Using Sockets, not TCP/IP on the host
   * Added some tweaks from the net   * 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 ===== ===== Create Socket in tmpfs =====
Zeile 18: Zeile 33:
  
 Create the File ''/etc/tmpfiles.d/docker-mariadb.conf''  with content Create the File ''/etc/tmpfiles.d/docker-mariadb.conf''  with content
-<file> 
  
 +<file>
 #Type Path            Mode UID      GID    Age Argument #Type Path            Mode UID      GID    Age Argument
 d     /run/mysqld     0755 docker   docker -   - d     /run/mysqld     0755 docker   docker -   -
  
 </file> </file>
 +
 ===== Yaml and .env, configuration of mariadb and phpMyAdmin ===== ===== Yaml and .env, configuration of mariadb and phpMyAdmin =====
  
Zeile 76: Zeile 92:
 #      memlock: "262144" #      memlock: "262144"
    healthcheck:    healthcheck:
-     test: healthcheck.sh --su-mysql --connect --innodb_initialized+     test: healthcheck.sh --connect --innodb_initialized
      interval: 20s      interval: 20s
      start_period: 10s      start_period: 10s
Zeile 249: Zeile 265:
 $cfg['Servers'][$i]['socket'] = '/run/mysqld/mysqld.sock'; $cfg['Servers'][$i]['socket'] = '/run/mysqld/mysqld.sock';
 #$cfg['Servers'][$i]['auth_type'] = 'http'; #$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> </file>
  
  
  • content/serverbasics/docker-mariadb.1703867356.txt.gz
  • Zuletzt geändert: 2023/12/29 16:29
  • von Daniel