Benutzer-Werkzeuge

Webseiten-Werkzeuge


tachtler:mysql_mariadb_backup_-_skript

MySQL/MariaDB Backup - Skript

Diese Dokumentation ist nach einem „persönlichen“ Workshop von: mit dem Referenten Oli Sennhauser entstanden. Hier noch einmal meinen Dank für die Informationen.

:!: HINWEIS - Die nachfolgenden Backup-Skripte setzen 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.

Beschreibung Externer Link
Homepage MySQL
Dokumentation MySQL Dokumentation

MariaDB wird von The MariaDB Foundation entwickelt.

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

Nachfolgendes Skript ermöglichen die Erstellung einer *.tar.gz-Datei

  • pro Tag eine eigene *.tar.gz-Datei
  • auf Dateiebene
  • mit konfigurierbaren Parametern
  • und automatischer Löschung von älteren Sicherungsdateien
  • und Erstellung einer fortlaufenden Log-Datei unter /var/log/

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

Nachfolgendes Skript ermöglichen die Erstellung einer *.tar.gz-Datei

  • pro Tag und schema eine eigene *.tar.gz-Datei
    • Nachfolgende schema werden dabei nicht gesichert:
      1. information_schema
      2. performance_schema
      3. sys
  • auf Dateiebene
  • mit konfigurierbaren Parametern
  • und automatischer Löschung von älteren Sicherungsdateien
  • und Erstellung einer fortlaufenden Log-Datei unter /var/log/

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, welche im jeweiligen Skript definiert sind, erstellt worden sein, nachdem das jeweilige 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-Datei
    • mysql-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:

  1. 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

Wiederherstellen

Mit nachfolgendem Befehl, kann die so aus dem Backup entpackte Datei zur Wiederherstellung der Datenbank/Tabelle/Schema wie folgt verwendet werden.

Wobei das Skript

  • mysqldump_backup_20170524_184224.sql.tar.gz → die Backup-Datei
    • mysqldump_backup_20170524_184224.sql

und das Skript (hier als Beispiel)

  • phpmyadmin-mysqldump_backup_20180207_033209.sql.tar.gz → die Backup-Datei
    • phpmyadmin-mysqldump_backup_20180207_033209.sql

hier die gezeigten Backup-Dateien jeweils in sich trägt.

Egal, ob ein Wiederherstellung aus einem full, oder schema-Backup-Datei erfolgt, mit dem nachfolgendem Befehl werden folgende Schritte zur Wiederherstellung durchgeführt:

  1. Anlage der Datenbank/Tabelle/Schema, (falls dies nicht (mehr) vorhanden sein sollte)
  2. Nutzung der bezeichneten Datenbank/Tabelle/Schema, welche wiederherstellt werden soll (auch mehrere nacheinander, bei einer full-Backup-Datei)
  3. Löschen des Inhalts der Datenbank/Tabelle/Schema, (falls ein Inhalt vorhanden sein sollte)
  4. Sperren der wiederherzustellenden Datenbank/Tabelle/Schema, während des Wiederherstellungsprozesses
  5. Wiederherstellung der in der Backup-Datei enthaltenen Daten
  6. Entsperren der wiederherzustellenden Datenbank/Tabelle/Schema, nach dem Wiederherstellungsprozesse
  7. Bei der Wiederherstellung aus einer full-Backup-Datei, fortsetzen des Prozesses mit der nächsten Datenbank/Tabelle/Schema

Nachfolgender Befehl, führt nun die Wiederehestellung tatsächlich aus.

:!: WICHTIG - Es wird die Kenntnis des root-Passworts der Datenbank benötigt !!!

(full)

/usr/bin/mysql -u root -p < /tmp/recovery/mysqldump_backup_20170524_184224.sql
bzw.

(schema)

/usr/bin/mysql -u root -p < /tmp/recovery/phpmyadmin-mysqldump_backup_20180207_033209.sql

Anschließend befindet sich die Datenbank/Tabelle/Schema auf dem Stand der Backup-Datei.

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
tachtler/mysql_mariadb_backup_-_skript.txt · Zuletzt geändert: 2018/03/26 09:28 von klaus