Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen der Seite angezeigt.

--- content:serverbasics:docker-mariadb [2023/12/29 17:29] – obel1x
+++ content:serverbasics:docker-mariadb [2025/06/05 23:40] (aktuell) – [STOP] obel1x
@@ Zeile 12: / Zeile 12: @@
   * 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).
+So stop reading here, maybe only adjusting your local mariadb with these values beneath, thats all.
 ===== Create Socket in tmpfs =====
@@ Zeile 18: / Zeile 36: @@
 Create the File ''/etc/tmpfiles.d/docker-mariadb.conf''  with content
-<file>
+<file>
 #Type Path            Mode UID      GID    Age Argument
 d     /run/mysqld     0755 docker   docker -   -
 </file>
 ===== Yaml and .env, configuration of mariadb and phpMyAdmin =====
@@ Zeile 76: / Zeile 95: @@
 #      memlock: "262144"
    healthcheck:
-     test: healthcheck.sh --su-mysql --connect --innodb_initialized
+     test: healthcheck.sh --connect --innodb_initialized
      interval: 20s
      start_period: 10s
@@ Zeile 249: / Zeile 268: @@
 $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>