weidner/computer/sysadmin/

Checkliste MySQL-Backup

Das Thema Backup von MySQL-Datenbanken kommt bei mir in größeren Abständen auf und ich fange jedesmal an zu überlegen, wie ich es mache. Zeit, das Thema mit einer Checkliste abzuhandeln.

Generell muss ich diese fünf Dinge beim Einrichten des Backups für eine MySQL-Datenbank beachten:

Schauen wir uns diese der Reihe nach an.

MySQL-Benutzerrechte

Für das Backup verwende ich bei MySQL das Programm mysqldump. Dieses benötigt mindestens die folgenden Privilegien:

Für einige Optionen benötige ich zusätzlich noch weitere Privilegien, diese sind bei den Optionen in der Dokumentation angegeben.

Nenne ich den MySQL-Benutzer mysqldump und möchte, dass er alle Datenbanken sichern kann, kann ich ihn mit dem folgenden Befehl anlegen:

GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES \
      ON *.* \
      TO 'mysqldump'@'%' \
      IDENTIFIED BY 'mysqldump';

Natürlich verwende ich bei Produktivdatenbanken ein anderes Kennwort.

Bei Stackoverflow hat man sich auch schon Gedanken über die minimal notwendigen Privilegien gemacht. Demnach benötige ich EXECUTE Privilegien, falls es Views gibt, die eine Funktion ausführen. Da sich die benötigten Privilegien mit der Weiterentwicklung von MySQL hin und wieder ändern, ist es ohnehin sinnvoll, die konkreten Privilegien anhand der aktuellen Dokumentation zu ermitteln und zu testen.

Um eine Datenbanksicherung wieder einzuspielen, benötige ich Privilegien, um die SQL-Statements, die im Backup stehen, ausführen zu können. Das sind meist die entsprechenden CREATE Statements für die Objekte im Backup.

Falls die Sicherung ALTER DATABASE Statements enthält, benötige ich auch dieses Privileg.

Falls mysqldump weitere Privilegien benötigt, wird es mich darauf hinweisen, so dass ich das GRANT Statement anpassen kann.

Im Referenzhandbuch kann ich weitere Details zu mysqldump nachlesen.

Plattenplatz

Über den Plattenplatz gibt es nicht viel zu sagen. Um herauszufinden, auf welcher Partition noch Platz ist, reicht der folgende Befehl:

df -h

Das Backup lege ich, wenn möglich, auf eine andere Partition als die Datenbank selbst.

Ich kann allerdings auch die Ausgabe von mysqldump zu einem anderen Rechner schicken - zum Beispiel mit ssh - und brauche mir dann keine Gedanken über den Platz auf dem Datenbank-Rechner machen.

Dabei habe ich zwei Möglichkeiten: entweder ich starte auf dem Datenbank-Rechner mysqldump und schicke dessen Ausgabe via Pipe und ssh an den anderen Rechner, oder ich starte vom anderen Rechner aus in einer SSH-Sitzung mysqldump auf dem Datenbank-Rechner und leite die Ausgabe auf dem anderen Rechner um.

Dateirechte

Ein paar Dinge muss ich beachten, wenn ich das Backup auf das Dateisystem schreiben will.

Ich vermeide nach Möglichkeit, als root auf das Dateisystem zu schreiben. Dateisysteme unter Unix/Linux halten oft Speicherplatz in Reserve, wenn sie voll werden. Dieser Speicherplatz steht nur dem Benutzer root zur Verfügung. Läuft das Backup unter dessen Benutzerkennung, kann es passieren, dass damit der Plattenplatz restlos aufgebraucht wird. Bei einer anderen Benutzerkennung (mit einer UID != 0) passiert das nicht.

Je nach Situation verwende ich für das Backup meist einen dedizierten Benutzer. Will ich, das auch andere Benutzer das Backup lesen können, dann füge ich diese zur selben Gruppe wie den Backup-Nutzer hinzu und sorge mit umask dafür, dass diese Gruppe die Backups lesen kann.

umask 027

Ein Anwendungsfall dafür ist zum Beispiel das Kopieren des Backups via FTP oder HTTP auf einen anderen Server.

Skript für das Backup

Der Bequemlichkeit halber verwende ich meist das folgende Skript für die Sicherung von Datenbanken:

#!/bin/sh
# vim: sw=4 ts=4 tw=78 si et:
#
# Script sichert Datenbanken
#
# grant select,lock tables
#    on *.*
#    to mysqldump@localhost identified by 'mysqldump';
#

# zu sichernde Datenbanken
#
DATABASES="mysql"

BACKUPDIR=/var/local/mysql.backup
DUMPUSER=mysqldump
DUMPPASS=mysqldump
CONFFILE=/etc/mysql/backup-mysql.conf

# in $CONFFILE brauchen nur die Variable $DATABASES und eventuell die Variablen
# $DUMPUSER und $DUMPPASS angepasst werden
#
if [ -r $CONFFILE ]; then
    . $CONFFILE
fi

umask 07
for db in $DATABASES; do
    [ ! -d $BACKUPDIR/$db ] && mkdir -p $BACKUPDIR/$db
    cd $BACKUPDIR/$db
    [ -f $db.mysql ] && mv $db.mysql $db.mysql.bak
    mysqldump -u $DUMPUSER -p$DUMPPASS --add-drop-table $db > $db.mysql
done

Dieses Skript liest die Datei /etc/mysql/backup-mysql.conf ein, wenn sie vorhanden ist. Damit kann ich in dieser Datei über die folgenden Shell-Variablen das Skript beeinflussen:

Natürlich überschreibe ich DUMPUSER und DUMPPASS mit dem Benutzer und Kennwort, das ich bei der Vergabe der Backup-Privilegien gewählt habe.

Außerdem sorge ich dafür, dass /etc/mysql/backup-mysql.conf nur von dem Systemnutzer, der die Sicherung macht (also mysqldump aufruft), gelesen werden kann.

Eintrag in crontab

Bleibt als letztes noch der Eintrag in der crontab, damit die Datenbank regelmäßig gesichert wird. Meist lasse ich die Datenbanken in der Stunde vor Mitternacht sichern:

# Datenbank-Backup nach /var/local/mysql.backup
13 23 * * * backup /usr/local/sbin/backup-mysql

Falls auch andere Daten auf dem Rechner, z.B. mit duply gesichert werden, kann das Skript statt von cron auch vom Sicherungsskript aufgerufen werden. In diesem Fall muss ich gegebenenfalls mit den Dateirechten aufpassen und das Skript via su oder sudo mit einer anderen UID ausführen.

Posted 2017-06-03
Tags: