[[Vorlage(Getestet, bionic, focal)]] {{{#!vorlage Wissen [:Pakete installieren: Installation von Programmen] [:Editor: Einen Editor öffnen] [:Terminal: Ein Terminal öffnen] [:sudo: Root-Rechte] }}} [[Inhaltsverzeichnis()]] 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. {{{#!vorlage Hinweis Für den Vorgang der Datensicherung ist es wichtig, dass während dieser Zeit __keine__ Daten geschrieben werden. Das heißt 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 [wikipedia:SQL#Dateninkonsistenz: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. {{{#!vorlage 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 == Falls irgend möglich, sollte man versuchen, ein [#Logisches_Backup Logisches Backup] zu machen, wenn man Datenbanken von einem System auf ein anderes umziehen möchte. Wenn dies nicht möglich ist, zum Beispiel wenn der Ursprungsserver unwiderruflich funktionsuntüchtig ist, die Dateien im Ordner '''/var/lib/mysql/''' aber unbeschädigt sind, kann man wie folgt vorgehen (getestet unter Ubuntu 18.04 mit MySQL 5.7): * MySQL auf dem neuen System ganz normal wie im [:MySQL:Hauptartikel] beschrieben installieren. * Dort den MySQL-Server stoppen und überprüfen, dass er auch tatsächlich heruntergefahren ist.{{{#!vorlage Befehl sudo systemctl stop mysql sudo systemctl status mysql }}} * Vom Ordner '''/var/lib/mysql''' auf dem neuen System eine Sicherungskopie erstellen. {{{#!vorlage Befehl sudo cp -r /var/lib/mysql /var/lib/mysql.orig.bak }}} * Nun __nur__ die Datenbank-Verzeichnisse vom alten ins neue System kopieren, also sämtliche Unterordner des Verzeichnisses '''/var/lib/mysql'''. * Die für InnoDB unerlässlichen Dateien ebenfalls vom alten System ins neue kopieren, am besten so:{{{#!vorlage Befehl sudo rsync -r ALTER_MYSQL_ORDNER/ib* /var/lib/mysql/ }}} * Eigentümer ändern. {{{#!vorlage Befehl sudo chown -R mysql:mysql /var/lib/mysql }}} * Den [https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html Mysql InnoDB Recovery Modus] {en} aktivieren. Dazu die Datei '''/etc/mysql/my.cnf''' in einem Editor öffnen[2] und unter dem Bereich `[mysqld]` (falls nicht vorhanden, hinzufügen) wie folgt editieren: {{{#!code ini [mysqld] innodb_force_recovery=5 }}} * Zum Schluss den MySQL-Server wieder starten. {{{#!vorlage Befehl sudo systemctl start mysql }}} * Wenn alles geklappt hat, kann man den Sicherungsordner '''/var/lib/mysql.orig.bak''' löschen und den Eintrag `innodb_force_recovery` in der '''/etc/mysql/my.cnf''' wieder entfernen. Damit diese Änderung greift, MySQL neu starten. Die Chancen, dass diese Vorgehensweise zum Erfolg führt, sind umso größer, je identischer das alte und das neue System sind. Also am besten die gleichen Ubuntu- und MySQL-Versionen verwenden. Die MySQL-Installation im neuen System sollte frisch und unangetastet sein (noch ohne Datenbanken, etc.). Gegebenenfalls könnte man sich als Zwischenlösung einer [:Virtualisierung:] bedienen, in der man den alten Server so identisch als möglich nachbildet. Von dort kann man dann nach einer erfolgreichen Wiederherstellung ein [#Logisches_Backup Logisches Backup] erstellen, das man dann wiederum auf das endgültige System einspielt. Lässt es sich nicht vermeiden, dass auf dem neuen System eine neuere Version von MySQL zum Einsatz kommt, 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: {{{#!vorlage Befehl 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 [:Editoren:Editor] einsehbar ist. Ein logisches Backup erfolgt unter MySQL mit Hilfe des Programms ``mysqldump``, welches standardmäßig mit installiert wird. {{{#!vorlage 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 {{{#!vorlage 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 {{{#!vorlage Befehl mysqldump -u root -p foobar > sicherung.sql }}} Möchte man den Dump zurücksichern, so geschieht dies mit dem Befehl {{{#!vorlage 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) {{{#!vorlage Befehl 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 [:Cron:Cronjob] gestartet wird. Das im Folgenden vorgestellte Skript sichert alle Datenbanken und speichert diese in einem Verzeichnis, das mit der Versionskontrolle [:Git:] 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. {{{#!vorlage Experten Statt Git kann auch ein anderes entsprechendes Werkzeug genutzt werden. }}} == Backup-Verzeichnis vorbereiten == Folgendes Paket muss installiert [1] werden: {{{#!vorlage Paketinstallation git }}} Das Backup-Verzeichnis wird im Terminal wie folgt erstellt {{{#!vorlage Befehl sudo mkdir -p /var/backup/mysql sudo git init /var/backup/mysql }}} {{{#!vorlage Hinweis Will man nur den jeweils aktuellen Stand speichern, können die git-Befehle einfach weggelassen werden. }}} == Das Skript == {{{#!vorlage Experten Statt des nachfolgend beschriebenen Skripts kann auch [sourceforge:automysqlbackup:AutoMySQLBackup] {en} 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 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. {{{#!code bash #!/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 --single-transaction $DB > $TARGET/$DB.sql done if [ -x /usr/bin/git ] && [ -d ${TARGET}/.git ]; then cd $TARGET /usr/bin/git add . /usr/bin/git commit -m "$NOW" else /usr/bin/logger "$0 - git 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 {{{#!vorlage Befehl sudo chmod +x /root/bin/mysql_backup }}} ausführbar gemacht werden. Meldungen des Skriptes werden in '''/var/log/messages''' protokolliert. {{{#!vorlage Experten 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. {{{#!vorlage Befehl 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 {{{#!vorlage Befehl 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 [:Git:] auch '''.sql'''-Dateien älteren Datums wiederherstellen. Dies geht mit diesem Befehl: {{{#!vorlage Befehl git show $(git log --oneline | grep YY-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 [wikipedia:Replikation_(Datenverarbeitung):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 [http://dev.mysql.com/doc/refman/5.1/en/replication.html beschrieben] {en}. 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. = Links = * [github:maxbube/mydumper:mydumper:] {en} - Kommandozeilenwerkzeug für Backup und Restore * [http://www.phpmybackuppro.net/ phpMyBackupPro] {de} - webbasierte Datenbank-Sicherung (PHP-Skript) * [:MySQL:] - Hauptartikel * [:MySQL/Werkzeuge:] - Programmübersicht #tag: Netzwerk, Internet, System, Sicherheit, Server