Dies ist eine alte Version des Dokuments!
Inhaltsverzeichnis
MySQL/MariaDB Backup - Skript
HINWEIS - Das nachfolgende Backup-Skript setzt eine lauffähige Installation von MySQL oder MariaDB voraus, wie unter nachfolgendem internen Link beschrieben !!!
MySQL oder MariaDB sind beide Open-Source-Datenbank-Server.
MySQL wird von Oracle entwickelt.
MariaDB wird von The MariaDB Foundation entwickelt.
Beschreibung | Externer Link |
---|---|
Homepage | MySQL |
Dokumentation | MySQL Dokumentation |
Beschreibung | Externer Link |
---|---|
Homepage | MariaDB |
Dokumentation | MariaDB Dokumentation |
Ab hier werden root
-Rechte zur Ausführung der nachfolgenden Befehle benötigt. Um root
zu werden geben Sie bitte folgenden Befehl ein:
$ su - Password:
Backup:
Skript: mysqldump_backup_full.sh
Nachfolgende Parameter sind in der aktuellen Version konfigurierbar: (Nur relevanter Ausschnitt)
... ############################################################################## # >>> Please edit following lines for personal settings and custom usages. ! # ############################################################################## # CUSTOM - Script-Name. SCRIPT_NAME='mysqldump_backup_full' # CUSTOM - Backup-Files. DIR_BACKUP='/srv/backup/mysqldump_backup_full' FILE_BACKUP=mysqldump_backup_`date '+%Y%m%d_%H%M%S'`.sql FILE_DELETE='*.tar.gz' BACKUPFILES_DELETE=7 # CUSTOM - mysqldump Parameter. DUMP_HOST='127.0.0.1' DUMP_USER='root' DUMP_PASS='geheim' # CUSTOM - Binary-Logging active. Example: ('Y'(my.cnf|log_bin=bin-log), 'N') DUMP_BIN_LOG_ACTIVE='N' # CUSTOM - Depends on the database engine. Example: ('Y'(MyISAM), 'N'(InnoDB)) DUMP_LOCK_ALL_TABLE='Y' # CUSTOM - Mail-Recipient. MAIL_RECIPIENT='you@example.com' # CUSTOM - Status-Mail [Y|N]. MAIL_STATUS='N' ############################################################################## # >>> Normaly there is no need to change anything below this comment line. ! # ############################################################################## ...
Parametername | Beschreibung |
---|---|
SCRIPT_NAME | Name des Skriptes |
DIR_BACKUP | Verzeichnis in dem die Backup-Dateien erstellt werden sollen |
FILE_BACKUP | Allgemeiner Teil der Bezeichnung für die Backup-Dateien |
FILE_DELETE | Endung zur Löschung von älteren Archivdateien |
BACKUPFILES_DELETE | Anzahl der Backup-Dateien pro Benutzer die gespeichert bleiben sollen |
DUMP_BIN_LOG_ACTIVE | Angabe ob beim Backup-Prozesses ein –master-data=1 –flush-logs berücksichtigt werden soll |
DUMP_LOCK_ALL_TABLE | Angabe ob beim Backup-Prozesses ein –lock-all-tables durchgeführt werden soll |
#!/bin/bash ############################################################################## # Script-Name : mysqldump_backup_full.sh # # Description : Script to backup the --all-databases of a MySQL/MariaDB. # # On successful execution only a LOG file will be written. # # On error while execution, a LOG file and a error message # # will be send by e-mail. # # # # Last update : 07.02.2018 # # Version : 1.00 # # # # Author : Klaus Tachtler, <klaus@tachtler.net> # # DokuWiki : http://www.dokuwiki.tachtler.net # # Homepage : http://www.tachtler.net # # # # +----------------------------------------------------------------------+ # # | This program is free software; you can redistribute it and/or modify | # # | it under the terms of the GNU General Public License as published by | # # | the Free Software Foundation; either version 2 of the License, or | # # | (at your option) any later version. | # # +----------------------------------------------------------------------+ # # # # Copyright (c) 2018 by Klaus Tachtler. # # # ############################################################################## ############################################################################## # H I S T O R Y # ############################################################################## # -------------------------------------------------------------------------- # # Version : x.xx # # Description : <Description> # # -------------------------------------------------------------------------- # # -------------------------------------------------------------------------- # # Version : x.xx # # Description : <Description> # # -------------------------------------------------------------------------- # ############################################################################## ############################################################################## # >>> Please edit following lines for personal settings and custom usages. ! # ############################################################################## # CUSTOM - Script-Name. SCRIPT_NAME='mysqldump_backup_full' # CUSTOM - Backup-Files. DIR_BACKUP='/srv/backup' FILE_BACKUP=mysqldump_backup_`date '+%Y%m%d_%H%M%S'`.sql FILE_DELETE='*.tar.gz' BACKUPFILES_DELETE=7 # CUSTOM - mysqldump Parameter. DUMP_HOST='127.0.0.1' DUMP_USER='root' DUMP_PASS='geheim' # CUSTOM - Binary-Logging active. Example: ('Y'(my.cnf|log_bin=bin-log), 'N') DUMP_BIN_LOG_ACTIVE='N' # CUSTOM - Depends on the database engine. Example: ('Y'(MyISAM), 'N'(InnoDB)) DUMP_LOCK_ALL_TABLE='Y' # CUSTOM - Mail-Recipient. MAIL_RECIPIENT='you@example.com' # CUSTOM - Status-Mail [Y|N]. MAIL_STATUS='N' ############################################################################## # >>> Normaly there is no need to change anything below this comment line. ! # ############################################################################## # Variables. MYSQLDUMP_COMMAND=`command -v mysqldump` TAR_COMMAND=`command -v tar` TOUCH_COMMAND=`command -v touch` RM_COMMAND=`command -v rm` PROG_SENDMAIL=`command -v sendmail` CAT_COMMAND=`command -v cat` DATE_COMMAND=`command -v date` MKDIR_COMMAND=`command -v mkdir` FILE_LOCK='/tmp/'$SCRIPT_NAME'.lock' FILE_LOG='/var/log/'$SCRIPT_NAME'.log' FILE_LAST_LOG='/tmp/'$SCRIPT_NAME'.log' FILE_MAIL='/tmp/'$SCRIPT_NAME'.mail' FILE_MBOXLIST='/tmp/'$SCRIPT_NAME'.mboxlist' VAR_HOSTNAME=`uname -n` VAR_SENDER='root@'$VAR_HOSTNAME VAR_EMAILDATE=`$DATE_COMMAND '+%a, %d %b %Y %H:%M:%S (%Z)'` # Functions. function log() { echo $1 echo `$DATE_COMMAND '+%Y/%m/%d %H:%M:%S'` " INFO:" $1 >>${FILE_LAST_LOG} } function retval() { if [ "$?" != "0" ]; then case "$?" in *) log "ERROR: Unknown error $?" ;; esac fi } function movelog() { $CAT_COMMAND $FILE_LAST_LOG >> $FILE_LOG $RM_COMMAND -f $FILE_LAST_LOG $RM_COMMAND -f $FILE_LOCK } function sendmail() { case "$1" in 'STATUS') MAIL_SUBJECT='Status execution '$SCRIPT_NAME' script.' ;; *) MAIL_SUBJECT='ERROR while execution '$SCRIPT_NAME' script !!!' ;; esac $CAT_COMMAND <<MAIL >$FILE_MAIL Subject: $MAIL_SUBJECT Date: $VAR_EMAILDATE From: $VAR_SENDER To: $MAIL_RECIPIENT MAIL $CAT_COMMAND $FILE_LAST_LOG >> $FILE_MAIL $PROG_SENDMAIL -f $VAR_SENDER -t $MAIL_RECIPIENT < $FILE_MAIL $RM_COMMAND -f $FILE_MAIL } # Main. log "" log "+-----------------------------------------------------------------+" log "| Start backup of --all-databases of database server............. |" log "+-----------------------------------------------------------------+" log "" log "Run script with following parameter:" log "" log "SCRIPT_NAME...: $SCRIPT_NAME" log "" log "DIR_BACKUP....: $DIR_BACKUP" log "" log "MAIL_RECIPIENT: $MAIL_RECIPIENT" log "MAIL_STATUS...: $MAIL_STATUS" log "" # Check if command (file) NOT exist OR IS empty. if [ ! -s "$MYSQLDUMP_COMMAND" ]; then log "Check if command '$MYSQLDUMP_COMMAND' was found................[FAILED]" sendmail ERROR movelog exit 11 else log "Check if command '$MYSQLDUMP_COMMAND' was found................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$TAR_COMMAND" ]; then log "Check if command '$TAR_COMMAND' was found......................[FAILED]" sendmail ERROR movelog exit 12 else log "Check if command '$TAR_COMMAND' was found......................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$TOUCH_COMMAND" ]; then log "Check if command '$TOUCH_COMMAND' was found....................[FAILED]" sendmail ERROR movelog exit 13 else log "Check if command '$TOUCH_COMMAND' was found....................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$RM_COMMAND" ]; then log "Check if command '$RM_COMMAND' was found.......................[FAILED]" sendmail ERROR movelog exit 14 else log "Check if command '$RM_COMMAND' was found.......................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$CAT_COMMAND" ]; then log "Check if command '$CAT_COMMAND' was found......................[FAILED]" sendmail ERROR movelog exit 15 else log "Check if command '$CAT_COMMAND' was found......................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$DATE_COMMAND" ]; then log "Check if command '$DATE_COMMAND' was found.....................[FAILED]" sendmail ERROR movelog exit 16 else log "Check if command '$DATE_COMMAND' was found.....................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$MKDIR_COMMAND" ]; then log "Check if command '$MKDIR_COMMAND' was found....................[FAILED]" sendmail ERROR movelog exit 17 else log "Check if command '$MKDIR_COMMAND' was found....................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$PROG_SENDMAIL" ]; then log "Check if command '$PROG_SENDMAIL' was found................[FAILED]" sendmail ERROR movelog exit 18 else log "Check if command '$PROG_SENDMAIL' was found................[ OK ]" fi # Check if LOCK file NOT exist. if [ ! -e "$FILE_LOCK" ]; then log "Check if script is NOT already runnig .....................[ OK ]" $TOUCH_COMMAND $FILE_LOCK else log "Check if script is NOT already runnig .....................[FAILED]" log "" log "ERROR: The script was already running, or LOCK file already exists!" log "" sendmail ERROR movelog exit 20 fi # Check if DIR_BACKUP Directory NOT exists. if [ ! -d "$DIR_BACKUP" ]; then log "Check if DIR_BACKUP exists.................................[FAILED]" $MKDIR_COMMAND -p $DIR_BACKUP log "DIR_BACKUP was now created.................................[ OK ]" else log "Check if DIR_BACKUP exists.................................[ OK ]" fi # Start backup. log "" log "+-----------------------------------------------------------------+" log "| Run backup $SCRIPT_NAME .............................. |" log "+-----------------------------------------------------------------+" log "" # Start backup process via mysqldump. cd $DIR_BACKUP if [ $DUMP_LOCK_ALL_TABLE = 'Y' ]; then DUMP_LOCK_ALL_TABLE='--lock-all-tables' else DUMP_LOCK_ALL_TABLE='--single-transaction' fi if [ $DUMP_BIN_LOG_ACTIVE = 'Y' ]; then log "Dump data with bin-log data ..." $MYSQLDUMP_COMMAND --host=$DUMP_HOST --user=$DUMP_USER --password=$DUMP_PASS --all-databases --flush-privileges $DUMP_LOCK_ALL_TABLE --master-data=1 --flush-logs --triggers --routines --events --hex-blob > $FILE_BACKUP else log "Dump data ..." $MYSQLDUMP_COMMAND --host=$DUMP_HOST --user=$DUMP_USER --password=$DUMP_PASS --all-databases --flush-privileges $DUMP_LOCK_ALL_TABLE --triggers --routines --events --hex-blob > $FILE_BACKUP fi log "" log "Packaging to archive ..." $TAR_COMMAND -cvzf $FILE_BACKUP.tar.gz $FILE_BACKUP --atime-preserve --preserve-permissions log "" log "Delete archive files ..." (ls $FILE_DELETE -t|head -n $BACKUPFILES_DELETE;ls $FILE_DELETE )|sort|uniq -u|xargs rm if [ "$?" != "0" ]; then log "Delete old archive files $DIR_BACKUP .....[FAILED]" else log "Delete old archive files $DIR_BACKUP ........[ OK ]" fi log "" log "Delete dumpfile ..." $RM_COMMAND $FILE_BACKUP # Delete LOCK file. if [ "$?" != "0" ]; then retval $? log "" $RM_COMMAND -f $FILE_LOCK sendmail ERROR movelog exit 99 else log "" log "+-----------------------------------------------------------------+" log "| End backup $SCRIPT_NAME .............................. |" log "+-----------------------------------------------------------------+" log "" fi # Finish syncing. log "+-----------------------------------------------------------------+" log "| Finish......................................................... |" log "+-----------------------------------------------------------------+" log "" # Status e-mail. if [ $MAIL_STATUS = 'Y' ]; then sendmail STATUS fi # Move temporary log to permanent log movelog exit 0
Skript - Log: mysqldump_backup_full
Nachfolgende Log-Datei entsteht im Verzeichnis
/var/log/
- hier z.B./var/log/mysqldump_backup_full.log
2018/02/07 03:32:05 INFO: 2018/02/07 03:32:05 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:05 INFO: | Start backup of --all-databases of database server............. | 2018/02/07 03:32:05 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:05 INFO: 2018/02/07 03:32:05 INFO: Run script with following parameter: 2018/02/07 03:32:05 INFO: 2018/02/07 03:32:05 INFO: SCRIPT_NAME...: mysqldump_backup_full 2018/02/07 03:32:05 INFO: 2018/02/07 03:32:05 INFO: DIR_BACKUP....: /srv/backup/mysqldump_backup_full 2018/02/07 03:32:05 INFO: 2018/02/07 03:32:05 INFO: MAIL_RECIPIENT: you@example.com 2018/02/07 03:32:05 INFO: MAIL_STATUS...: N 2018/02/07 03:32:05 INFO: 2018/02/07 03:32:05 INFO: Check if command '/bin/mysqldump' was found................[ OK ] 2018/02/07 03:32:05 INFO: Check if command '/bin/tar' was found......................[ OK ] 2018/02/07 03:32:05 INFO: Check if command '/bin/touch' was found....................[ OK ] 2018/02/07 03:32:05 INFO: Check if command '/bin/rm' was found.......................[ OK ] 2018/02/07 03:32:05 INFO: Check if command '/bin/cat' was found......................[ OK ] 2018/02/07 03:32:05 INFO: Check if command '/bin/date' was found.....................[ OK ] 2018/02/07 03:32:05 INFO: Check if command '/bin/mkdir' was found....................[ OK ] 2018/02/07 03:32:05 INFO: Check if command '/sbin/sendmail' was found................[ OK ] 2018/02/07 03:32:05 INFO: Check if script is NOT already runnig .....................[ OK ] 2018/02/07 03:32:05 INFO: Check if DIR_BACKUP exists.................................[ OK ] 2018/02/07 03:32:05 INFO: 2018/02/07 03:32:05 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:05 INFO: | Run backup mysqldump_backup_full .............................. | 2018/02/07 03:32:05 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:05 INFO: 2018/02/07 03:32:05 INFO: Dump data ... 2018/02/07 03:32:07 INFO: 2018/02/07 03:32:07 INFO: Packaging to archive ... 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: Delete archive files ... 2018/02/07 03:32:09 INFO: Delete old archive files /srv/backup/mysqldump_backup_full ........[ OK ] 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: Delete dumpfile ... 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:09 INFO: | End backup mysqldump_backup_full .............................. | 2018/02/07 03:32:09 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:09 INFO: | Finish......................................................... | 2018/02/07 03:32:09 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:09 INFO:
Anmerkung zur Ausführung:
HINWEIS - Falls nachfolgende Fehlermeldung auftreten sollte:
... INFO: Delete old archive files /srv/backup ......................[FAILED]
bedeutet dies nur, dass noch nicht genug alte Archivdateien vorhanden sind, damit diese gelöscht werden können!
Skript: mysqldump_backup_schema.sh
Nachfolgende Parameter sind in der aktuellen Version konfigurierbar: (Nur relevanter Ausschnitt)
... ############################################################################## # >>> Please edit following lines for personal settings and custom usages. ! # ############################################################################## # CUSTOM - Script-Name. SCRIPT_NAME='mysqldump_backup_schema' # CUSTOM - Backup-Files. DIR_BACKUP='/srv/backup/mysqldump_backup_schema' FILE_BACKUP=mysqldump_backup_`date '+%Y%m%d_%H%M%S'`.sql FILE_DELETE='*.tar.gz' BACKUPFILES_DELETE=77 # 7 backup files * 10 schemas = 77 # CUSTOM - mysqldump Parameter. DUMP_HOST='127.0.0.1' DUMP_USER='root' DUMP_PASS='geheim' # CUSTOM - Binary-Logging active. Example: ('Y'(my.cnf|log_bin=bin-log), 'N') DUMP_BIN_LOG_ACTIVE='N' # CUSTOM - Depends on the database engine. Example: ('Y'(MyISAM), 'N'(InnoDB)) DUMP_LOCK_ALL_TABLE='Y' # CUSTOM - Mail-Recipient. MAIL_RECIPIENT='you@example.com' # CUSTOM - Status-Mail [Y|N]. MAIL_STATUS='N'' ############################################################################## # >>> Normaly there is no need to change anything below this comment line. ! # ############################################################################## ...
Parametername | Beschreibung |
---|---|
SCRIPT_NAME | Name des Skriptes |
DIR_BACKUP | Verzeichnis in dem die Backup-Dateien erstellt werden sollen |
FILE_BACKUP | Allgemeiner Teil der Bezeichnung für die Backup-Dateien |
FILE_DELETE | Endung zur Löschung von älteren Archivdateien |
BACKUPFILES_DELETE | Anzahl der Backup-Dateien pro Benutzer die gespeichert bleiben sollen |
DUMP_BIN_LOG_ACTIVE | Angabe ob beim Backup-Prozesses ein –master-data=1 –flush-logs berücksichtigt werden soll |
DUMP_LOCK_ALL_TABLE | Angabe ob beim Backup-Prozesses ein –lock-all-tables durchgeführt werden soll |
#!/bin/bash ############################################################################## # Script-Name : mysqldump_backup_schema.sh # # Description : Script to backup the --all-databases of a MySQL/MariaDB. # # On successful execution only a LOG file will be written. # # On error while execution, a LOG file and a error message # # will be send by e-mail. # # # # Last update : 07.02.2018 # # Version : 1.00 # # # # Author : Klaus Tachtler, <klaus@tachtler.net> # # DokuWiki : http://www.dokuwiki.tachtler.net # # Homepage : http://www.tachtler.net # # # # +----------------------------------------------------------------------+ # # | This program is free software; you can redistribute it and/or modify | # # | it under the terms of the GNU General Public License as published by | # # | the Free Software Foundation; either version 2 of the License, or | # # | (at your option) any later version. | # # +----------------------------------------------------------------------+ # # # # Copyright (c) 2018 by Klaus Tachtler. # # # ############################################################################## ############################################################################## # H I S T O R Y # ############################################################################## # -------------------------------------------------------------------------- # # Version : x.xx # # Description : <Description> # # -------------------------------------------------------------------------- # # -------------------------------------------------------------------------- # # Version : x.xx # # Description : <Description> # # -------------------------------------------------------------------------- # ############################################################################## ############################################################################## # >>> Please edit following lines for personal settings and custom usages. ! # ############################################################################## # CUSTOM - Script-Name. SCRIPT_NAME='mysqldump_backup_schema' # CUSTOM - Backup-Files. DIR_BACKUP='/srv/backup/mysqldump_backup_schema' FILE_BACKUP=mysqldump_backup_`date '+%Y%m%d_%H%M%S'`.sql FILE_DELETE='*.tar.gz' BACKUPFILES_DELETE=77 # CUSTOM - mysqldump Parameter. DUMP_HOST='127.0.0.1' DUMP_USER='root' DUMP_PASS='geheim' # CUSTOM - Binary-Logging active. Example: ('Y'(my.cnf|log_bin=bin-log), 'N') DUMP_BIN_LOG_ACTIVE='N' # CUSTOM - Depends on the database engine. Example: ('Y'(MyISAM), 'N'(InnoDB)) DUMP_LOCK_ALL_TABLE='Y' # CUSTOM - Mail-Recipient. MAIL_RECIPIENT='you@example.com' # CUSTOM - Status-Mail [Y|N]. MAIL_STATUS='N' ############################################################################## # >>> Normaly there is no need to change anything below this comment line. ! # ############################################################################## # Variables. MYSQLDUMP_COMMAND=`command -v mysqldump` MYSQL_COMMAND=`command -v mysql` SED_COMMAND=`command -v sed` TAR_COMMAND=`command -v tar` TOUCH_COMMAND=`command -v touch` RM_COMMAND=`command -v rm` PROG_SENDMAIL=`command -v sendmail` CAT_COMMAND=`command -v cat` DATE_COMMAND=`command -v date` MKDIR_COMMAND=`command -v mkdir` FILE_LOCK='/tmp/'$SCRIPT_NAME'.lock' FILE_LOG='/var/log/'$SCRIPT_NAME'.log' FILE_LAST_LOG='/tmp/'$SCRIPT_NAME'.log' FILE_MAIL='/tmp/'$SCRIPT_NAME'.mail' FILE_MBOXLIST='/tmp/'$SCRIPT_NAME'.mboxlist' VAR_HOSTNAME=`uname -n` VAR_SENDER='root@'$VAR_HOSTNAME VAR_EMAILDATE=`$DATE_COMMAND '+%a, %d %b %Y %H:%M:%S (%Z)'` # Functions. function log() { echo $1 echo `$DATE_COMMAND '+%Y/%m/%d %H:%M:%S'` " INFO:" $1 >>${FILE_LAST_LOG} } function retval() { if [ "$?" != "0" ]; then case "$?" in *) log "ERROR: Unknown error $?" ;; esac fi } function movelog() { $CAT_COMMAND $FILE_LAST_LOG >> $FILE_LOG $RM_COMMAND -f $FILE_LAST_LOG $RM_COMMAND -f $FILE_LOCK } function sendmail() { case "$1" in 'STATUS') MAIL_SUBJECT='Status execution '$SCRIPT_NAME' script.' ;; *) MAIL_SUBJECT='ERROR while execution '$SCRIPT_NAME' script !!!' ;; esac $CAT_COMMAND <<MAIL >$FILE_MAIL Subject: $MAIL_SUBJECT Date: $VAR_EMAILDATE From: $VAR_SENDER To: $MAIL_RECIPIENT MAIL $CAT_COMMAND $FILE_LAST_LOG >> $FILE_MAIL $PROG_SENDMAIL -f $VAR_SENDER -t $MAIL_RECIPIENT < $FILE_MAIL $RM_COMMAND -f $FILE_MAIL } # Main. log "" log "+-----------------------------------------------------------------+" log "| Start backup of --all-databases of database server............. |" log "+-----------------------------------------------------------------+" log "" log "Run script with following parameter:" log "" log "SCRIPT_NAME...: $SCRIPT_NAME" log "" log "DIR_BACKUP....: $DIR_BACKUP" log "" log "MAIL_RECIPIENT: $MAIL_RECIPIENT" log "MAIL_STATUS...: $MAIL_STATUS" log "" # Check if command (file) NOT exist OR IS empty. if [ ! -s "$MYSQLDUMP_COMMAND" ]; then log "Check if command '$MYSQLDUMP_COMMAND' was found................[FAILED]" sendmail ERROR movelog exit 11 else log "Check if command '$MYSQLDUMP_COMMAND' was found................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$MYSQL_COMMAND" ]; then log "Check if command '$MYSQL_COMMAND' was found....................[FAILED]" sendmail ERROR movelog exit 12 else log "Check if command '$MYSQL_COMMAND' was found....................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$SED_COMMAND" ]; then log "Check if command '$SED_COMMAND' was found......................[FAILED]" sendmail ERROR movelog exit 13 else log "Check if command '$SED_COMMAND' was found......................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$TAR_COMMAND" ]; then log "Check if command '$TAR_COMMAND' was found......................[FAILED]" sendmail ERROR movelog exit 14 else log "Check if command '$TAR_COMMAND' was found......................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$TOUCH_COMMAND" ]; then log "Check if command '$TOUCH_COMMAND' was found....................[FAILED]" sendmail ERROR movelog exit 15 else log "Check if command '$TOUCH_COMMAND' was found....................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$RM_COMMAND" ]; then log "Check if command '$RM_COMMAND' was found.......................[FAILED]" sendmail ERROR movelog exit 16 else log "Check if command '$RM_COMMAND' was found.......................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$CAT_COMMAND" ]; then log "Check if command '$CAT_COMMAND' was found......................[FAILED]" sendmail ERROR movelog exit 17 else log "Check if command '$CAT_COMMAND' was found......................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$DATE_COMMAND" ]; then log "Check if command '$DATE_COMMAND' was found.....................[FAILED]" sendmail ERROR movelog exit 18 else log "Check if command '$DATE_COMMAND' was found.....................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$MKDIR_COMMAND" ]; then log "Check if command '$MKDIR_COMMAND' was found....................[FAILED]" sendmail ERROR movelog exit 19 else log "Check if command '$MKDIR_COMMAND' was found....................[ OK ]" fi # Check if command (file) NOT exist OR IS empty. if [ ! -s "$PROG_SENDMAIL" ]; then log "Check if command '$PROG_SENDMAIL' was found................[FAILED]" sendmail ERROR movelog exit 20 else log "Check if command '$PROG_SENDMAIL' was found................[ OK ]" fi # Check if LOCK file NOT exist. if [ ! -e "$FILE_LOCK" ]; then log "Check if script is NOT already runnig .....................[ OK ]" $TOUCH_COMMAND $FILE_LOCK else log "Check if script is NOT already runnig .....................[FAILED]" log "" log "ERROR: The script was already running, or LOCK file already exists!" log "" sendmail ERROR movelog exit 30 fi # Check if DIR_BACKUP Directory NOT exists. if [ ! -d "$DIR_BACKUP" ]; then log "Check if DIR_BACKUP exists.................................[FAILED]" $MKDIR_COMMAND -p $DIR_BACKUP log "DIR_BACKUP was now created.................................[ OK ]" else log "Check if DIR_BACKUP exists.................................[ OK ]" fi # Start backup. log "" log "+-----------------------------------------------------------------+" log "| Run backup $SCRIPT_NAME ............................ |" log "+-----------------------------------------------------------------+" log "" # Start backup process via mysqldump. cd $DIR_BACKUP if [ $DUMP_LOCK_ALL_TABLE = 'Y' ]; then DUMP_LOCK_ALL_TABLE='--lock-all-tables' else DUMP_LOCK_ALL_TABLE='--single-transaction' fi for DB in $($MYSQL_COMMAND --user=$DUMP_USER --password=$DUMP_PASS --execute='show databases \G' | grep -i Database: | grep -v -e information_schema -e performance_schema -e sys | sed 's/Database:\ //'); do if [ $DUMP_BIN_LOG_ACTIVE = 'Y' ]; then log "Dump data with bin-log data ..." log "$DB-$FILE_BACKUP" $MYSQLDUMP_COMMAND --host=$DUMP_HOST --user=$DUMP_USER --password=$DUMP_PASS --databases $DB --flush-privileges $DUMP_LOCK_ALL_TABLE --master-data=1 --triggers --routines --events --hex-blob --quick > $DB-$FILE_BACKUP else log "Dump data ..." log "$DB-$FILE_BACKUP" $MYSQLDUMP_COMMAND --host=$DUMP_HOST --user=$DUMP_USER --password=$DUMP_PASS --databases $DB --flush-privileges $DUMP_LOCK_ALL_TABLE --triggers --routines --events --hex-blob --quick > $DB-$FILE_BACKUP fi log "" log "Packaging to archive ..." $TAR_COMMAND -cvzf $DB-$FILE_BACKUP.tar.gz $DB-$FILE_BACKUP --atime-preserve --preserve-permissions log "" log "Delete archive files ..." (ls $FILE_DELETE -t|head -n $BACKUPFILES_DELETE;ls $FILE_DELETE )|sort|uniq -u|xargs rm if [ "$?" != "0" ]; then log "Delete old archive files $DIR_BACKUP .....[FAILED]" else log "Delete old archive files $DIR_BACKUP ........[ OK ]" fi log "" log "Delete dumpfile ..." $RM_COMMAND $DB-$FILE_BACKUP done # Delete LOCK file. if [ "$?" != "0" ]; then retval $? log "" $RM_COMMAND -f $FILE_LOCK sendmail ERROR movelog exit 99 else log "" log "+-----------------------------------------------------------------+" log "| End backup $SCRIPT_NAME ............................ |" log "+-----------------------------------------------------------------+" log "" fi # Finish syncing. log "+-----------------------------------------------------------------+" log "| Finish......................................................... |" log "+-----------------------------------------------------------------+" log "" # Status e-mail. if [ $MAIL_STATUS = 'Y' ]; then sendmail STATUS fi # Move temporary log to permanent log movelog exit 0
Skript - Log: mysqldump_backup_schema
Nachfolgende Log-Datei entsteht im Verzeichnis
/var/log/
- hier z.B./var/log/mysqldump_backup_schema.log
2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:09 INFO: | Start backup of --all-databases of database server............. | 2018/02/07 03:32:09 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: Run script with following parameter: 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: SCRIPT_NAME...: mysqldump_backup_schema 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: DIR_BACKUP....: /srv/backup/mysqldump_backup_schema 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: MAIL_RECIPIENT: you@example.com 2018/02/07 03:32:09 INFO: MAIL_STATUS...: N 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: Check if command '/bin/mysqldump' was found................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/bin/mysql' was found....................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/bin/sed' was found......................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/bin/tar' was found......................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/bin/touch' was found....................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/bin/rm' was found.......................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/bin/cat' was found......................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/bin/date' was found.....................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/bin/mkdir' was found....................[ OK ] 2018/02/07 03:32:09 INFO: Check if command '/sbin/sendmail' was found................[ OK ] 2018/02/07 03:32:09 INFO: Check if script is NOT already runnig .....................[ OK ] 2018/02/07 03:32:09 INFO: Check if DIR_BACKUP exists.................................[ OK ] 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:09 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:09 INFO: | Run backup mysqldump_backup_schema ............................ | 2018/02/07 03:32:09 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:09 INFO: 2018/02/07 03:32:14 INFO: Delete dumpfile ... 2018/02/07 03:32:14 INFO: Dump data ... 2018/02/07 03:32:14 INFO: mysql-mysqldump_backup_20180207_033209.sql 2018/02/07 03:32:14 INFO: 2018/02/07 03:32:14 INFO: Packaging to archive ... 2018/02/07 03:32:14 INFO: 2018/02/07 03:32:14 INFO: Delete archive files ... 2018/02/07 03:32:14 INFO: Delete old archive files /srv/backup/mysqldump_backup_schema ........[ OK ] 2018/02/07 03:32:14 INFO: 2018/02/07 03:32:14 INFO: Delete dumpfile ... 2018/02/07 03:32:14 INFO: Dump data ... 2018/02/07 03:32:14 INFO: phpmyadmin-mysqldump_backup_20180207_033209.sql 2018/02/07 03:32:14 INFO: 2018/02/07 03:32:14 INFO: Packaging to archive ... 2018/02/07 03:32:14 INFO: 2018/02/07 03:32:14 INFO: Delete archive files ... 2018/02/07 03:32:14 INFO: Delete old archive files /srv/backup/mysqldump_backup_schema ........[ OK ] 2018/02/07 03:32:14 INFO: 2018/02/07 03:32:14 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:14 INFO: | End backup mysqldump_backup_schema ............................ | 2018/02/07 03:32:14 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:14 INFO: 2018/02/07 03:32:14 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:14 INFO: | Finish......................................................... | 2018/02/07 03:32:14 INFO: +-----------------------------------------------------------------+ 2018/02/07 03:32:14 INFO:
Anmerkung zur Ausführung:
HINWEIS - Falls nachfolgende Fehlermeldung auftreten sollte:
... INFO: Delete old archive files /srv/backup ......................[FAILED]
bedeutet dies nur, dass noch nicht genug alte Archivdateien vorhanden sind, damit diese gelöscht werden können!
Skript - Logrotate
Damit die LOG-Datei des Skriptes nicht ins unendliche wächst, sollte diese ebenfalls, wie auch die Log-Dateien von hier z.B. MariaDB selbst, rotiert werden.
Um die LOG-Dateien des Skriptes ebenfalls wie die LOG-Dateien von hier z.B. MariaDB selbst zu rotieren ist nachfolgende Ergänzung in der Konfigurationsdatei
/etc/logrotate.d/mariadb
erforderlich:
VORHER: (Komplette Konfigurationsdatei)
# This logname can be set in /etc/my.cnf # by setting the variable "log-error" # in the [mysqld_safe] section as follows: # # [mysqld_safe] # log-error=/var/log/mariadb/mariadb.log # # If the root user has a password you have to create a # /root/.my.cnf configuration file with the following # content: # # [mysqladmin] # password = <secret> # user= root # # where "<secret>" is the password. # # ATTENTION: This /root/.my.cnf should be readable ONLY # for root ! # Then, un-comment the following lines to enable rotation of mysql's log file: # Tachtler - uncommented following lines - /var/log/mariadb/mariadb.log { create 640 mysql mysql notifempty daily rotate 3 missingok compress postrotate # just if mysqld is really running if test -x /usr/bin/mysqladmin && \ /usr/bin/mysqladmin ping &>/dev/null then /usr/bin/mysqladmin flush-logs fi endscript }
NACHHER: (Komplette Konfigurationsdatei)
# This logname can be set in /etc/my.cnf # by setting the variable "log-error" # in the [mysqld_safe] section as follows: # # [mysqld_safe] # log-error=/var/log/mariadb/mariadb.log # # If the root user has a password you have to create a # /root/.my.cnf configuration file with the following # content: # # [mysqladmin] # password = <secret> # user= root # # where "<secret>" is the password. # # ATTENTION: This /root/.my.cnf should be readable ONLY # for root ! # Then, un-comment the following lines to enable rotation of mysql's log file: # Tachtler - uncommented following lines - /var/log/mariadb/mariadb.log /var/log/mysqldump_backup_full.log /var/log/mysqldump_backup_schema.log { create 640 mysql mysql notifempty daily rotate 3 missingok compress postrotate # just if mysqld is really running if test -x /usr/bin/mysqladmin && \ /usr/bin/mysqladmin ping &>/dev/null then /usr/bin/mysqladmin flush-logs fi endscript }
Nachfolgend die geänderte Zeile (Nur relevanter Ausschnitt):
... /var/log/mariadb/mariadb.log /var/log/mysqldump_backup_full.log /var/log/mysqldump_backup_schema.log { ...
Skript - cron-Job
Nachfolgend kann auch ein cron
-Job zur z.B. nächtlichen Ausführung eingerichtet werden. Dazu können nachfolgende zwei Konfigurationszeilen an die bestehende Konfigurationsdatei
/etc/crontab
angehängt werden:
# Backup mariadb mysqldump 5 03 * * * root /usr/local/bin/mysqldump_backup_full.sh > /dev/null 2>&1 5 04 * * * root /usr/local/bin/mysqldump_backup_schema.sh > /dev/null 2>&1
* Ausführung nächtlich um 03:05 Uhr und um 04:05
Alternativ, kann auch eine Verknüpfung im Verzeichnis
/etc/cron.daily/
wie nachfolgend konfiguriert, erstellt werden, um die Ausführung „täglich“ durchzuführen:
# ls -l /etc/cron.daily/ total 8 -rwx------ 1 root root 219 Aug 2 2017 logrotate -rwxr-xr-x. 1 root root 618 Mar 17 2014 man-db.cron lrwxrwxrwx 1 root root 34 Dec 6 05:00 mysqldump_backup_full.sh -> /usr/local/bin/mysqldump_backup_full.sh lrwxrwxrwx 1 root root 36 Dec 6 09:25 mysqldump_backup_schema.sh -> /usr/local/bin//mysqldump_backup_schema.sh
Das erstellen von Links, kann mit nachfolgenden Befehlen durchgeführt werden:
# ln -s /usr/local/bin/mysqldump_backup_full.sh /etc/cron.daily/mysqldump_backup_full.sh
# ln -s /usr/local/bin/mysqldump_backup_schema.sh /etc/cron.daily/mysqldump_backup_full.sh
Skript - Dateien
Nachfolgend sollten nachfolgende Dateien im Backup-Verzeichnis, welches im Skript definiert ist, erstellt worden sein, nachdem das Skript zur Ausführung gekommen ist. Hier z.B. /srv/backup/
, was mit nachfolgendem Befehl überprüft werden kann:
# ls -la /srv/backup/ total 5520 drwxr-xr-x 2 mysql mysql 56 May 24 18:42 . drwxr-xr-x. 4 mysql mysql 52 May 24 18:27 .. -rw-r--r-- 1 root root 5320616 Feb 7 03:32 mysqldump_backup_20180207_033204.sql.tar.gz -rw-r--r-- 1 root root 140471 Feb 7 03:32 mysql-mysqldump_backup_20180207_033209.sql.tar.gz -rw-r--r-- 1 root root 2924 Feb 7 03:32 phpmyadmin-mysqldump_backup_20180207_033209.sql.tar.gz
Wobei das Skript
mysqldump_backup_full.sh
→ die Backup-Datei(en)mysqldump_backup_20180207_033204.sql.tar.gz
und das Skript
mysqldump_backup_schema.sh
→ die Backup-Dateimysql-mysqldump_backup_20180207_033209.sql.tar.gz
phpmyadmin-mysqldump_backup_20180207_033209.sql.tar.gz
hier erzeugt hat.
Recovery
Die Erstellung eines Backups (Sicherung) ist eine Sache, jedoch wie können im Fall eines Datenverlusts, die Daten wiederhergestellt werden.
Entpacken
Nachfolgendes Beispiel zeigt, wie zuerst die im Backup enthaltenen Daten in eine Verzeichnis unterhalb von
/tmp
wiederhergestellt werden können.
Dazu kann mit nachfolgendem Befehl ein Verzeichnis mit dem Namen recovery
unterhalb des Verzeichnisses /tmp
angelegt werden:
# mkdir /tmp/recovery
Anschließend kann dann mit nachfolgendem Befehl die komplette Datenbank/Schema Sicherung wiederhergestellt werden:
Als Beispiel sind hier nachfolgende Gegebenheiten angenommen:
- Das Backup befindet sich unter
/srv/backup
# tar -xvzf mysqldump_backup_20170524_184224.sql.tar.gz -C /tmp/recovery --atime-preserve --preserve-permissions
Nach erfolgreicher Ausführung des oben gezeigten Befehls, kann mit nachfolgendem Befehl überprüft werden, ob alle Daten aus der Backup-Datei erfolgreich extrahiert werden konnten:
# ls -la /tmp/recovery/ total 45936 -rw-r--r-- 1 root root 47034466 May 24 11:42 mysqldump_backup_20170524_184224.sql
Widerherstellen