MySQL/Backup

Dieser Artikel wurde für die folgenden Ubuntu-Versionen getestet:

Dieser Artikel ist mit keiner aktuell unterstützten Ubuntu-Version getestet! Bitte hilf mit, indem du diesen Artikel testest. Wiki/FAQ - häufig gestellte Fragen (Abschnitt „Wikiartikel-testen“).

Zum Verständnis dieses Artikels sind folgende Seiten hilfreich:

  1. Installation von Programmen

  2. Einen Editor öffnen

  3. Ein Terminal öffnen

  4. Root-Rechte

Inhaltsverzeichnis
  1. Datenbankdateien kopieren
    1. Datenbankdateien auf ein anderes System ...
  2. Logisches Backup
  3. Automatisches inkrementelles Backup
    1. Backup-Verzeichnis vorbereiten
    2. Das Skript
    3. Cronjob einrichten
    4. Daten wiederherstellen
  4. Replikation
  5. Links

Hat man kritische und/oder sensible Daten in einer MySQL-Datenbank, ist es - wie üblich - ratsam, regelmäßig ein Backup der Daten zu machen. Dazu gibt es mehrere Möglichkeiten.

Hinweis:

Für den Vorgang der Datensicherung ist es extrem wichtig, dass während dieser Zeit keine Daten geschrieben werden. D.h. es gilt, entweder alle Tabellen gegen Schreibzugriffe zu sperren, oder - besser - den MySQL-Server für die Zeit der Datensicherung herunterzufahren. Dabei ist auch zu beachten, dass der Server fehlerfrei herunter fährt.

Ist einer dieser Punkte nicht erfüllt, riskiert man Inkonsistenz in den Daten.

Datenbankdateien kopieren

Eine recht simple Methode ist, die Dateien zu kopieren, in denen MySQL die Daten speichert. Diese Daten liegen standardmäßig im Verzeichnis /var/lib/mysql. Dort gibt es für jede Datenbank ein Unterverzeichnis. Die Dateien tragen normalerweise den Namen der entsprechenden Tabelle in der Datenbank.

Hinweis:

Die Unterverzeichnisse für die Datenbanken sind nur mit Root-Rechten bzw. als root lesbar, normale Benutzer haben keine Leserechte.

Verwendet man MyISAM-Tabellen, sichert man alle Dateien mit den Endungen .myd, .frm und .myi.

Verwendet man InnoDB-Tabellen, kopiert man alle .frm-Dateien sowie alle Dateien der Typen .ib_logfileX, .ibdataX und .idb. X muss durch die entsprechende Zahl ersetzt werden, wobei Dateien auch mehrfach vorkommen können.

Unabhängig vom Tabellentyp sollte auch immer zusätzlich die Datei /etc/mysql/my.cnf mit gesichert werden.

Grundsätzlich hat diese Art der Datensicherung den Nachteil, dass man nie hundertprozentig sicher stellen kann, dass die kopierten (Binär-)Dateien fehlerfrei sind, also es keine Probleme beim Kopieren gab.

Datenbankdateien auf ein anderes System zurück spielen

Möchte man gesicherte Dateien auf ein anderes System einspielen, so kann man wie folgt vorgehen:

  1. MySQL auf dem neuen System ganz normal wie im Hauptartikel beschrieben installieren.

  2. Vom Ordner /var/lib/mysql auf dem neuen System zunächst ein Backup machen und dann löschen.

  3. Den wiederherzustellenden Ordner mysql aus dem alten System nach /var/lib/ auf dem neuen System kopieren.

  4. Die Datei mit der Endung .pid aus dem Ordner /var/lib/mysql mit der .pid-Datei aus dem unter Punkt 2 gesicherten Ordner ersetzen.

  5. Rechte anpassen:

     sudo chown -R mysql:mysql /var/lib/mysql/ 
  6. Ggf. phpmyadmin neuinstallieren

Nur so wird sichergestellt, dass auch InnoDB-Tabellen wiederhergestellt werden! Ein einfaches Kopieren der Datenbank-Ordner reicht zum Wiederherstellen von Datenbanken mit InnoDB-Tabellen nicht aus, da auch die Datei /var/lib/mysql/ibdata1 und weitere Dateien im Stammordner für InnoDB-Tabellen benötigt werden!

Kommt auf dem neuen System eine neuere Version von MySQL zum Einsatz, sollte mysql_upgrade ausgeführt werden. Dieses Programm untersucht alle Datenbanktabellen auf Inkompatibilitäten mit der neuen MySQL-Version und aktualisiert die Tabellen in der MySQL-eigenen Datenbank "mysql". Das ist nötig, damit Funktionen benutzt werden können, die in der MySQL-Version neu hinzugekommen sind. Standardmäßig benutzt das Programm den Datenbankbenutzer root. Falls dieser mit einem Passwort geschützt ist, muss es mittels der Option --password= angegeben werden:

sudo mysql_upgrade --password=PASSWORT 

Logisches Backup

Ein logisches Backup dauert zwar länger, ist aber dafür etwas eleganter. Weiterhin haben logische Backups den Vorteil, dass diese sich grundsätzlich auf andere SQL-Datenbanken (z.B. PostgreSQL, Oracle, SQL Server) zurück sichern lassen. Ein logisches Backup besteht außerdem aus einer Textdatei, welche - wenn nötig - mit einem konventionellen Editor einsehbar ist.

Ein logisches Backup erfolgt unter MySQL mit Hilfe des Programms mysqldump, welches standardmäßig mit installiert wird.

Hinweis:

Um einen konsistenten "dump" zu erzeugen, sperrt mysqldump die Datenbank. Je nach Größe und Aufwand ist der Server für normale Zugriffe somit eine gewisse Zeit nicht erreichbar.

Der Befehl

mysqldump -u root -p --all-databases > sicherung.sql 

sichert mit den Rechten des (SQL-Benutzers) "root" alle Datenbanken in die Datei sicherung.sql. Die Sicherung kann natürlich auch mit einem anderen Benutzer durchgeführt werden, sofern dieser die notwendigen Rechte in den zu sichernden Datenbanken hat.

Anstatt alle Datenbanken kann man auch einzelne Datenbanken sichern. Möchte man z.B. nur die Datenbank "foobar" sichern, so lautet der Befehl

mysqldump -u root -p foobar > sicherung.sql 

Möchte man den Dump zurücksichern, so geschieht dies mit dem Befehl

mysql -u root -p < sicherung.sql 

Beim zurücksichern des Dumps einer einzelnen Datenbank muss die Datenbank angegeben werden, der Befehl lautet somit (bei der "foobar" Datenbank von oben)

mysql -u root -p foobar < sicherung.sql 

Automatisches inkrementelles Backup

Das logische Backup kann man natürlich auch automatisiert von einem Skript erledigen lassen, welches täglich durch einen Cronjob gestartet wird. Das im Folgenden vorgestellte Skript sichert alle Datenbanken und speichert diese in einem Verzeichnis, das mit der Versionskontrolle Bazaar verwaltet wird. So kann man jeden beliebigen Tag wiederherstellen, spart durch Bazaar aber Speicherplatz. Im weiteren Verlauf wird davon ausgegangen, dass die Backups in /var/backup/mysql gespeichert werden.

Experten-Info:

Statt Bazaar kann auch Git verwendet werden.

Backup-Verzeichnis vorbereiten

Folgendes Paket muss installiert [1] werden:

Wiki/Vorlagen/Installbutton/button.png mit apturl

Paketliste zum Kopieren:

sudo apt-get install bzr 

sudo aptitude install bzr 

Das Backup-Verzeichnis wird im Terminal wie folgt erstellt

sudo mkdir -p /var/backup/mysql
sudo bzr init-repo /var/backup
sudo bzr init /var/backup/mysql 

Hinweis:

Will man nur den jeweils aktuellen Stand speichern, können die bzr-Befehle einfach weggelassen werden.

Das Skript

Experten-Info:

Statt des nachfolgend beschriebenen Skripts kann auch AutoMySQLBackup 🇬🇧 verwendet werden. Dabei handelt es sich um ein mysqldump-Skript in Kombination mit Cron-Einträgen, um automatisch tägliche, wöchentliche und monatliche Sicherungen anzulegen. Es ist unter dem Namen automysqlbackup in den offiziellen Paketquellen enthalten.

Weitere Alternativen sind im Abschnitt Links am Ende des Artikels zu finden.

Das folgende Skript kann in einem beliebigen Editor [2] erstellt werden und muss dann mit Root-Rechten [4] im Verzeichnis /root/bin/ unter dem Namen mysql_backup gespeichert und ausführbar gemacht werden.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#!/bin/bash
# TARGET: Backup-Ziel
# IGNORE: Liste zu ignorierender Datenbanken (durch | getrennt)
# CONF: MySQL Config-Datei, welche die Zugangsdaten enthaelt
TARGET=/var/backup/mysql
IGNORE="phpmyadmin|mysql|information_schema|performance_schema|test"
CONF=/etc/mysql/debian.cnf
if [ ! -r $CONF ]; then /usr/bin/logger "$0 - auf $CONF konnte nicht zugegriffen werden"; exit 1; fi
if [ ! -d $TARGET ] || [ ! -w $TARGET ]; then /usr/bin/logger "$0 - Backup-Verzeichnis nicht beschreibbar"; exit 1; fi

DBS="$(/usr/bin/mysql --defaults-extra-file=$CONF -Bse 'show databases' | /bin/grep -Evw $IGNORE)"
NOW=$(date +"%Y-%m-%d")

for DB in $DBS; do
    /usr/bin/mysqldump --defaults-extra-file=$CONF --skip-extended-insert --skip-comments $DB > $TARGET/$DB.sql
done

if [ -x /usr/bin/bzr ] && [ -d ${TARGET}/.bzr/branch ]; then
  cd $TARGET
  /usr/bin/bzr add .
  /usr/bin/bzr commit -m "$NOW"
else
  /usr/bin/logger "$0 - bzr nicht verfuegbar oder Backup-Ziel nicht unter Versionskontrolle"
fi

/usr/bin/logger "$0 - Backup von $NOW erfolgreich durchgefuehrt"
exit 0

Ist das Skript unter /root/bin/mysql_backup gespeichert, muss es noch mit

sudo chmod +x /root/bin/mysql_backup 

ausführbar gemacht werden. Meldungen des Skriptes werden in /var/log/messages protokolliert.

Experten-Info:

Das Skript muss nicht zwingend als root ausgeführt werden. Der ausführende Benutzer muss lediglich Zugriff auf eine in der Variable CONF definierte MySQL-Konfiguration haben. In dieser muss mindestens der Abschnitt [client] mit Benutzername und Passwort eines MySQL-Benutzers mit ausreichenden Rechten existieren. Außerdem muss der ausführende Benutzer Schreibrechte im Zielverzeichnis haben.

Cronjob einrichten

Damit jeden Morgen um 3 Uhr eine Sicherung stattfindet, legt man einen passenden Cronjob an. Dazu öffnet man ein Terminal [3] und gibt folgendes ein.

sudo crontab -e 

Es öffnet sich der Standardeditor [2]. Hier ergänzt man eine Zeile für das Skript und achtet darauf, dass am Ende eine Leerzeile bleibt. Mehr Informationen zu Cronjobs gibt es im Artikel Cron.

0 3 * * * /root/bin/mysql_backup > /dev/null 2>&1

Daten wiederherstellen

Im Zielverzeichnis befindet sich für jede Datenbank eine .sql-Datei mit dem jeweils aktuellsten Backup. Diese kann man mit

sudo mysql --defaults-extra-file=/etc/mysql/debian.cnf DATENBANK < /var/backup/mysql/DATENBANK.sql 

zurückspielen, wobei DATENBANK durch den tatsächlichen Namen der Datenbank zu ersetzen ist.

Durch die Versionskontrolle kann man mit Bazaar auch .sql-Dateien älteren Datums wiederherstellen. Dies geht mit diesem Befehl:

bzr cat -r  $(bzr log --line | grep YYYY-MM-DD | cut -f1 -d:) DATENBANK.sql > DATENBANK_YYYY-MM-DD.sql 

Diese kann man dann bei Bedarf wie oben gezeigt zurücksichern. YYYY-MM-DD entspricht dem gewünschten Datum.

Replikation

Der MySQL-Server unterstützt auch Datenreplikation, bei der quasi "live" die Daten eines Master SQL-Servers auf einen oder mehrere Slave-Server kopiert werden. Das Grundsetup hierfür ist nicht weiter schwierig und im MySQL-Benutzerhandbuch beschrieben 🇬🇧.

Wichtig ist aber, dass Replikation kein "echtes Backup" wie die anderen beschriebenen Methoden ist, da die replizierte Datenbank immer den aktuellen Stand der Master-Datenbank hat. Dies kann praktisch sein, wenn z.B. der Master-Datenbankserver unerwartet ausfällt. Allerdings ist es mit Hilfe der Replikation nicht möglich, den Stand einer Datenbank am Tag X zu sichern oder wieder herzustellen.