Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen Revision Vorhergehende Überarbeitung | |||
docs:mysql:backup_dbserver [05.04.2020 - 16:19] Mischa |
docs:mysql:backup_dbserver [05.10.2023 - 09:45] (aktuell) Mischa [die Ausführung] |
||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ====== Datenbank Server Backup ====== | ||
+ | |||
+ | Die Informationen in Datenbanken sind zumeist doch sehr wichtig, weswegen für den Fall der Fälle diese gesichert seien sollten. Hierbei sind auch die Rechte der Datenbank-Nutzer wichtig, damit auch bei den Applikationen nicht immer gleich alle diesbezüglichen Einstellungen geändert werden müssen. | ||
+ | |||
+ | ===== das Skript ===== | ||
+ | |||
+ | Das folgende Skript soll die Aufgabe zur Sicherung, aber auch zum Restore eines kompletten Datenbank-Servers bewerkstelligen: | ||
+ | <file bash dbbackup.sh> | ||
+ | #!/bin/bash | ||
+ | |||
+ | ######################### | ||
+ | # | ||
+ | # DB-Backup-Script: MySQL-Servers-Backup | ||
+ | # | ||
+ | # Skript zum vollständigen Backup eines MySQL-Servers. | ||
+ | # | ||
+ | # (c) M.K. Hitzigrath - http://hitzigrath.de | ||
+ | # | ||
+ | # last change: 25.07.2014 | ||
+ | # | ||
+ | ######################### | ||
+ | |||
+ | owncmd="${0##*/}" | ||
+ | ownpath="${0%/*}" | ||
+ | ownext="${0##*.}" | ||
+ | ownname="${owncmd%.*}" | ||
+ | DBCSV=$ownpath/mydbs.csv | ||
+ | BUPATH=$ownpath/dbsvr | ||
+ | TMPpfx=/tmp | ||
+ | perms=0660 | ||
+ | group=webdev | ||
+ | alter=31 | ||
+ | |||
+ | helpmesg() { | ||
+ | cat <<EOF | ||
+ | Skript fuer Backup aller Datenbanken eines MySQL-Servers und der Benutzer-Rechte. | ||
+ | |||
+ | Usages: $owncmd -s | -i [-b <Backup-Dir>] [-f <CSV-Datei>] [-r | -z] [-o "mysql/dump Optionen"] <ServerID> | ||
+ | $owncmd -S [-b <Backup-Dir>] [-f <CSV-Datei>] [-r | -z] [-o "mysqldump Optionen"] | ||
+ | $owncmd -h | -? for help | ||
+ | |||
+ | Options: | ||
+ | -s Sichern der Datenbank | ||
+ | -i Import der Datenbank | ||
+ | <ServerID> ID (Kuerzel) des DB-Servers | ||
+ | |||
+ | -S Sichern aller Datenbanken in der CSV-Datei | ||
+ | |||
+ | -b <string> Angabe des Verzeichnis fuer die Backup-Dateien | ||
+ | Default: $BUPATH | ||
+ | -f <string> Angabe der CSV-Datei mit den Daten fuer die DB-Instanzen | ||
+ | Default: $DBCSV | ||
+ | -r SQL-Dump im RAW-Format | ||
+ | -z SQL-Dump als ZIP-Datei | ||
+ | -o <string> Optionen zu mysql (Import-Mode -i) bzw. mysqldump (Export-Mode -s) | ||
+ | Dadurch werden die Optionen in der csv-Datei ueberschrieben. | ||
+ | -u use sudo | ||
+ | -B Batch-Mode | ||
+ | |||
+ | Aufbau der CSV-Datei (mit Beispielen): | ||
+ | #ServerID,DBhost,DBuser,DBpwd,excluded User,excluded DBs,Export Options,Import Options | ||
+ | MyServer,dbserver,dbadmin,secretpw,root debian-sys-maint phpmyadmin dbadmin,mysql information_schema phpmyadmin performance_schema,--hex-blob --routines --events --skip-extended-insert --complete-insert | ||
+ | localDB,,dbadm,secretpw,root debian-sys-maint phpmyadmin dbadm,mysql information_schema phpmyadmin performance_schema | ||
+ | EOF | ||
+ | } | ||
+ | |||
+ | strtrim() { | ||
+ | if [ $# -ge 1 ] | ||
+ | then | ||
+ | local __retvar=$2 | ||
+ | local _var="$1" | ||
+ | local _retres="" | ||
+ | local _ferr=0 | ||
+ | |||
+ | _retres="$(echo "$_var" | sed 's/^[ \t]*//;s/[ \t]*$//')" | ||
+ | else | ||
+ | local _retres="Falscher Funktions-Aufruf - strtrim" | ||
+ | local _ferr=254 | ||
+ | fi | ||
+ | if [[ "$__retvar" ]] | ||
+ | then | ||
+ | eval $__retvar="'$_retres'" | ||
+ | else | ||
+ | echo "$_retres" | ||
+ | fi | ||
+ | return $_ferr; | ||
+ | } | ||
+ | |||
+ | myusrgrants() { | ||
+ | local grantuser="$1" | ||
+ | shift | ||
+ | $CMDsudo mysql -B -N $@ -e "SELECT DISTINCT CONCAT( | ||
+ | 'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';' | ||
+ | ) AS query FROM mysql.user" | grep "'$grantuser'@" | mysql $@ | \ | ||
+ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' | ||
+ | } | ||
+ | |||
+ | chknolst() { | ||
+ | local susr="$1" | ||
+ | shift | ||
+ | local exists=0 | ||
+ | for nusr in $@ | ||
+ | do | ||
+ | [ "$nusr" = "$susr" ] && exists=1 | ||
+ | done | ||
+ | return $exists | ||
+ | } | ||
+ | |||
+ | while getopts :b:f:o:rzisSuBh opt | ||
+ | do | ||
+ | case $opt in | ||
+ | b) | ||
+ | BUPATH="$OPTARG" | ||
+ | ;; | ||
+ | f) | ||
+ | DBCSV="$OPTARG" | ||
+ | ;; | ||
+ | o) | ||
+ | opt_cmd="$OPTARG" | ||
+ | ;; | ||
+ | r) | ||
+ | nocrypt="true" | ||
+ | ;; | ||
+ | z) | ||
+ | nocrypt="true" | ||
+ | opt_zip="true" | ||
+ | ;; | ||
+ | i) | ||
+ | opt_imp="true" | ||
+ | ;; | ||
+ | s) | ||
+ | opt_sic="true" | ||
+ | ;; | ||
+ | S) | ||
+ | opt_all="true" | ||
+ | opt_sic="true" | ||
+ | ;; | ||
+ | u) | ||
+ | CMDsudo="sudo" | ||
+ | ;; | ||
+ | B) | ||
+ | batchmode="true" | ||
+ | ;; | ||
+ | h|'?') | ||
+ | [ ! -n "$batchmode" ] && helpmesg | ||
+ | exit 1 | ||
+ | ;; | ||
+ | esac | ||
+ | done | ||
+ | |||
+ | shift $((OPTIND - 1)) | ||
+ | |||
+ | dbsvrIDs="$@" | ||
+ | if [ ! -n "$dbsvrIDs" ] | ||
+ | then | ||
+ | if [ ! -n "$opt_all" ] | ||
+ | then | ||
+ | if [ ! -n "$batchmode" ] | ||
+ | then | ||
+ | echo "Kein ServerID benannt!" | ||
+ | echo "" | ||
+ | helpmesg | ||
+ | fi | ||
+ | exit 2 | ||
+ | fi | ||
+ | fi | ||
+ | |||
+ | if [ ! -n "$opt_sic" -a ! -n "$opt_imp" ] | ||
+ | then | ||
+ | if [ ! -n "$batchmode" ] | ||
+ | then | ||
+ | echo "Fehlender Parameter! (-s -i)" | ||
+ | echo "" | ||
+ | helpmesg | ||
+ | fi | ||
+ | exit 3 | ||
+ | fi | ||
+ | |||
+ | if [ -n "$opt_sic" -a -n "$opt_imp" ] | ||
+ | then | ||
+ | if [ ! -n "$batchmode" ] | ||
+ | then | ||
+ | echo "Zuviele Parameter! (-s -i)" | ||
+ | echo "" | ||
+ | helpmesg | ||
+ | fi | ||
+ | exit 3 | ||
+ | fi | ||
+ | |||
+ | if [ ! -f "$DBCSV" ] | ||
+ | then | ||
+ | if [ ! -n "$batchmode" ] | ||
+ | then | ||
+ | echo "Keine Datei mit DB-Daten vorhanden! ($DBCSV)" | ||
+ | echo "" | ||
+ | helpmesg | ||
+ | fi | ||
+ | exit 4 | ||
+ | fi | ||
+ | |||
+ | if [ ! -d "$BUPATH" ] | ||
+ | then | ||
+ | mkdir $BUPATH | ||
+ | if [ "$?" -ne "0" ] | ||
+ | then | ||
+ | if [ ! -n "$batchmode" ] | ||
+ | then | ||
+ | echo "Kann Verzeichnis fuer Backups nicht erstellen! ($BUPATH)" | ||
+ | echo "" | ||
+ | helpmesg | ||
+ | fi | ||
+ | exit 5 | ||
+ | fi | ||
+ | fi | ||
+ | |||
+ | if [ -n "$nocrypt" ] | ||
+ | then | ||
+ | if [ -n "$opt_zip" ] | ||
+ | then | ||
+ | fext="sql.zip" | ||
+ | else | ||
+ | fext="sql" | ||
+ | fi | ||
+ | else | ||
+ | fext="sql.enc" | ||
+ | fi | ||
+ | |||
+ | exlev=0 | ||
+ | |||
+ | [[ "$opt_all" ]] && dbsvrIDs="$($CMDsudo cat "$DBCSV" | grep -v "^#" | strtrim "$(awk -F, '{ print $1 }')")" | ||
+ | |||
+ | for dbsID in $dbsvrIDs | ||
+ | do | ||
+ | [ ! -n "$batchmode" ] && echo "DB-Backup zu $dbsID" | ||
+ | csvline="$($CMDsudo cat "$DBCSV" | grep "^$dbsID,")" | ||
+ | if [ ! "$csvline" = "" ] | ||
+ | then | ||
+ | dbhost="$(strtrim "$(awk -F, '{ print $2 }' <<<$csvline)")" | ||
+ | [ ! -n "$dbhost" ] && dbhost="localhost" | ||
+ | dbuser="$(strtrim "$(awk -F, '{ print $3 }' <<<$csvline)")" | ||
+ | dbpwd="$(strtrim "$(awk -F, '{ print $4 }' <<<$csvline)")" | ||
+ | nouser="$(strtrim "$(awk -F, '{ print $5 }' <<<$csvline)")" | ||
+ | nodbs="$(strtrim "$(awk -F, '{ print $6 }' <<<$csvline)")" | ||
+ | if [[ "$opt_cmd" ]] | ||
+ | then | ||
+ | expopt="$opt_cmd" | ||
+ | else | ||
+ | expopt="$(strtrim "$(awk -F, '{ print $7 }' <<<$csvline)")" | ||
+ | fi | ||
+ | if [[ "$opt_cmd" ]] | ||
+ | then | ||
+ | impopt="$opt_cmd" | ||
+ | else | ||
+ | impopt="$(strtrim "$(awk -F, '{ print $8 }' <<<$csvline)")" | ||
+ | fi | ||
+ | |||
+ | dbcon="--host=$dbhost --user=$dbuser --password=$dbpwd" | ||
+ | tmpsql="$TMPpfx/dbsic-$dbsID-$$.sql" | ||
+ | sbase="$BUPATH/$dbsID" | ||
+ | dmpsql="$sbase.`date +%F`.$fext" | ||
+ | |||
+ | if [ -n "$opt_sic" ] | ||
+ | then | ||
+ | if [ -e "$dmpsql" ] | ||
+ | then | ||
+ | sb1="${sbase}-1.$fext" | ||
+ | if [ -e "$sb1" ] | ||
+ | then | ||
+ | sb2="${sbase}-2.$fext" | ||
+ | if [ -e "$sb2" ] | ||
+ | then | ||
+ | sb3="${sbase}-3.$fext" | ||
+ | if [ -e "$sb3" ] | ||
+ | then | ||
+ | rm -f $sb3 | ||
+ | fi | ||
+ | mv -f $sb2 $sb3 | ||
+ | fi | ||
+ | mv -f $sb1 $sb2 | ||
+ | fi | ||
+ | mv -f $dmpsql $sb1 | ||
+ | fi | ||
+ | |||
+ | echo "" > $tmpsql | ||
+ | for idb in $($CMDsudo mysql -B -N $dbcon -e "SHOW DATABASES;") | ||
+ | do | ||
+ | if (chknolst $idb $nodbs) | ||
+ | then | ||
+ | [ ! -n "$batchmode" ] && echo "Erstelle SQL-Dump zu $idb" | ||
+ | $CMDsudo mysqldump $dbcon -B $expopt $idb >> $tmpsql | ||
+ | if [ "$?" -ne "0" ] | ||
+ | then | ||
+ | [ ! -n "$batchmode" ] && echo "Fehler beim SQL-Dump! ($idb)" | ||
+ | exlev=11 | ||
+ | [ -e "$tmpsql" ] && rm -f $tmpsql | ||
+ | fi | ||
+ | fi | ||
+ | done | ||
+ | for gdbuser in $($CMDsudo mysql -B -N $dbcon -e "SELECT DISTINCT user FROM mysql.user;") | ||
+ | do | ||
+ | if (chknolst $gdbuser $nouser) | ||
+ | then | ||
+ | [ ! -n "$batchmode" ] && echo "Erstelle Rechte-Dump zu $gdbuser" | ||
+ | myusrgrants $gdbuser $dbcon >> $tmpsql | ||
+ | if [ "$?" -ne "0" ] | ||
+ | then | ||
+ | [ ! -n "$batchmode" ] && echo "Fehler beim Rechte-Dump! (User: $gdbuser)" | ||
+ | exlev=12 | ||
+ | [ -e "$tmpsql" ] && rm -f $tmpsql | ||
+ | fi | ||
+ | fi | ||
+ | done | ||
+ | |||
+ | if [ $exlev -eq 0 ] | ||
+ | then | ||
+ | if [ -n "$nocrypt" ] | ||
+ | then | ||
+ | if [ -n "$opt_zip" ] | ||
+ | then | ||
+ | cat $tmpsql | gzip > $dmpsql | ||
+ | rm -f $tmpsql | ||
+ | else | ||
+ | mv -f $tmpsql $dmpsql | ||
+ | fi | ||
+ | else | ||
+ | cat $tmpsql | gzip | openssl enc -a -out $dmpsql | ||
+ | rm -f $tmpsql | ||
+ | fi | ||
+ | $CMDsudo chmod $perms $dmpsql | ||
+ | [[ "$group" ]] && $CMDsudo chgrp $group $dmpsql | ||
+ | $CMDsudo find $BUPATH/ -type f -name "${dbsID}*" -mtime +$alter -exec rm -f {} >/dev/null 2>&1 \; | ||
+ | fi | ||
+ | |||
+ | elif [ -n "$opt_imp" ] | ||
+ | then | ||
+ | dmpsql=$($CMDsudo ls -rt1 ${sbase}* 2>/dev/null | tail -1) | ||
+ | if [ -n "$dmpsql" ] | ||
+ | then | ||
+ | dumpext="${dmpsql##*.}" | ||
+ | if [ "$dumpext" = "sql" -o "$dumpext" = "zip" ] | ||
+ | then | ||
+ | nocrypt="true" | ||
+ | [ "$dumpext" = "zip" ] && opt_zip="true" || unset opt_zip | ||
+ | else | ||
+ | nocrypt="" | ||
+ | unset nocrypt | ||
+ | fi | ||
+ | |||
+ | if [ ! -f "$dmpsql" ] | ||
+ | then | ||
+ | [ ! -n "$batchmode" ] && echo "Backup-Datei zu $dbsID ist keine Datei oder nicht vorhanden!" | ||
+ | else | ||
+ | [ ! -n "$batchmode" ] && echo "Datenbank-Import zu $dbsID" | ||
+ | if [ -n "$nocrypt" ] | ||
+ | then | ||
+ | if [ -n "$opt_zip" ] | ||
+ | then | ||
+ | $CMDsudo zcat $dmpsql | $CMDsudo mysql $dbcon $impopt | ||
+ | else | ||
+ | $CMDsudo mysql $dbcon $impopt < $dmpsql | ||
+ | fi | ||
+ | else | ||
+ | $CMDsudo openssl enc -a -d -in $dmpsql | zcat | $CMDsudo mysql $dbcon $impopt | ||
+ | fi | ||
+ | fi | ||
+ | fi | ||
+ | fi | ||
+ | else | ||
+ | echo "Falsche ServerID! ($dbsID)" | ||
+ | exlev=6 | ||
+ | fi | ||
+ | done | ||
+ | exit $exlev | ||
+ | |||
+ | </file> | ||
+ | |||
+ | ==== die DB-Definitions-Datei ==== | ||
+ | |||
+ | Damit ein (oder auch mehrere) Server richtig gesichert werden kann, werden diverse Parameter benötigt, die in einer CSV-Datei hinterlegt werden: | ||
+ | |||
+ | <file conf mydbs.csv> | ||
+ | #ServerID,DBhost,DBuser,DBpwd,excluded User,excluded DBs,Export Options,Import Options | ||
+ | MyServer,dbserver,dbadmin,secretpw,root debian-sys-maint phpmyadmin dbadmin,mysql information_schema phpmyadmin performance_schema,--hex-blob --routines --events --skip-extended-insert --complete-insert | ||
+ | localDB,,dbadm,secretpw,root debian-sys-maint phpmyadmin dbadm,mysql information_schema phpmyadmin performance_schema | ||
+ | </file> | ||
+ | Diverse nicht benötigte Angaben können weg gelassen werden - abgesehen der ServerID (zur Identifikation des Eintrags) und der Benutzerdaten (DBuser und DBpwd) zum Zugriff auf den Server. | ||
+ | |||
+ | FIXME | ||
+ | |||
+ | ===== die Ausführung ===== | ||
+ | |||
+ | Das Script erstellt zunächst ein Backup-Verzeichnis (wenn nicht schon vorhanden) und exportiert dann die angegebenen Datenbanken jeweils in eine SQL-Datei. | ||
+ | |||
+ | TODO | ||
+ | |||
+ | {{description>Backup und Restore Skript kompletter MySQL-Datenbank-Server.}} | ||
+ | {{keywords>wartung,dokumentation}} | ||
+ | {{tag>mysql mariadb scripting backup}} | ||