# [svd]MySQL-Datenbank konsistent während des Betriebs sichern

## Mr. Anderson

Hallo,

vorweg: ich habe nicht viel Erfahrung mit Datenbanken. Vielleicht ist das also alles ganz anders als ich denk...

Um den Inhalt einer MySQL-Datenbank zu sichern, kann man die Dateien kopieren, in denen die Datenbank gespeichert ist, vorzugsweise, wenn man den MySQL-Server gerade ausgeschaltet hat. Müsste ja funktionieren, aber sonderlich elegant erscheint mir das nicht, ganz davon abgesehen, dass es sehr ärgerlich ist, den Server runterzufahren. Wenn man den Server dabei aber nicht herunterfährt - vermutlich ist die Datenbank dann möglicherweise nicht konsistent, oder?

Es geht auch mit mysqldump, aber das dauert bei großen Datenbanken ne ganze Weile. Wie bekomme ich da einen konsistenten Zustand aller Datenbanken und Tabellen von einem bestimmten Zeitpunkt (sofern es so etwas bei Datenbanken überhaupt gibt) ohne alles zu blockieren?

Vor Kurzem hatte ich hier im Forum was gelesen davon, dass es die Technik gibt, während eines Backups, ein Logilfe mitzuschreiben, das alle Änderungen der Datenbank aufzeichnet und später in das Backup einarbeitet. Weiß da jemand Genaueres?Last edited by Mr. Anderson on Mon Jun 11, 2007 1:35 pm; edited 1 time in total

----------

## Beforegod

Normal sichert mysqldump alle Daten konsistent.

Die Daten so zu sichern (aus /var/mysql raus) finde ich nicht wirklich sinnvoll.

Versuche den Dump einfach, evt. erfüllt er ja schon all Deine Wünsche (Testsystem aufsetzen  :Smile: )

----------

## Raimund

Schau Dir mal mysqlhotcopy an.

----------

## musv

 *Mr. Anderson wrote:*   

> Um den Inhalt einer MySQL-Datenbank zu sichern, kann man die Dateien kopieren, in denen die Datenbank gespeichert ist, vorzugsweise, wenn man den MySQL-Server gerade ausgeschaltet hat.

 

Ja, das kann man so machen. Ich hatte mal vor 5 Jahren in einer kleinen Firma einen Nebenjob als Linux-Admin (hatte damals null Plan). Und die haben jede Nacht 'nen Cronjob laufen lassen, der die Datenbank runtergefahren und danach die Datenbankdateien per tar auf ein tape gesichert hat. Die Dateien findest du übrigens in /var/lib/mysql.

ABER: Damit das funktioniert, mußt du das MySQL erst beenden, da sonst Inkonsistenzen auftreten können. Und das Backup sollte theoretisch auch nur auf demselben Rechner wieder funktionieren.

 *Mr. Anderson wrote:*   

> 
> 
> Es geht auch mit mysqldump, aber das dauert bei großen Datenbanken ne ganze Weile. Wie bekomme ich da einen konsistenten Zustand aller Datenbanken und Tabellen von einem bestimmten Zeitpunkt (sofern es so etwas bei Datenbanken überhaupt gibt) ohne alles zu blockieren?
> 
> Vor Kurzem hatte ich hier im Forum was gelesen davon, dass es die Technik gibt, während eines Backups, ein Logilfe mitzuschreiben, das alle Änderungen der Datenbank aufzeichnet und später in das Backup einarbeitet. Weiß da jemand Genaueres?

 

Das könnte ich geschrieben haben. Wonach du suchst, sind transaktionsloggesteuerte Backups. Ich glaub aber nicht, daß MySQL sowas kann. Hab aber nicht weiter danach gesucht.

mysql-dump sollte schon für Deine Zwecke ausreichend sein.

----------

## ChrisM87

Hi,

für kleinere Datenbanken ist mysqldump genau das was du suchst. Für größere geht es natürlich auch, wird allerdings ziemlich langsam (einfach testen).

Was man beachten sollte: mysqldump garantiert Konsistenz nur innerhalb von einer Datenbank auf dem MySQL-Server, weil immer nur eine separat gelockt und gedumpt wird. Das ist aber in der Praxis kein Problem, weil ja normalerweile jede Anwendung (genau) eine Datenbank benutzt.

ChrisM

----------

## Mr. Anderson

Danke euch allen. Das hat mir schon sehr weitergeholfen. 

 *Quote:*   

> Normal sichert mysqldump alle Daten konsistent. 
> 
> Die Daten so zu sichern (aus /var/mysql raus) finde ich nicht wirklich sinnvoll. 
> 
> Versuche den Dump einfach, evt. erfüllt er ja schon all Deine Wünsche (Testsystem aufsetzen )

 

Ein Testsystem hatte ich bereits versucht zu erstellen. Allzu realistisch ist das aber nicht, denn weder habe ich die Datenbanken mit einigen GB Größe vor Ort noch habe ich die zahlreichen gleichzeitigen Zugriffe darauf. Ich wollte mir trotzdem natürlich ein Testsystem aufbauen, aber ich musste dann schmerzlich feststellen, dass meine Debian-DVDs hinüber sind.  :Sad:  Muss mir erst Ersatz holen.

 *Quote:*   

> Schau Dir mal mysqlhotcopy an.

 

In der MySQL-Dokumentation steht:

 *Quote:*   

> mysqlhotcopy works only for backing up MyISAM and ISAM tables, and ARCHIVE tables.

 

Ich habe aber auch Tabellen, bei denen als Engine "HEAP" eingetragen ist. Diese Strukturen müsste ich dann separat sichern? Das Schlimmste kommt noch: der Zugriff auf einige Tabellen führt zu Fehlern, weil die zugehörigen Dateien nicht mehr existieren (es gibt viel zu tun ^^). Ob das mysqlhotcopy aus dem Tritt bringen könnte?

 *Quote:*   

> Ja, das kann man so machen. Ich hatte mal vor 5 Jahren in einer kleinen Firma einen Nebenjob als Linux-Admin (hatte damals null Plan). Und die haben jede Nacht 'nen Cronjob laufen lassen, der die Datenbank runtergefahren und danach die Datenbankdateien per tar auf ein tape gesichert hat. Die Dateien findest du übrigens in /var/lib/mysql.

 

Dann hast Du ja eine Vorstellung davon, was ich gerade mache. ^^

 *Quote:*   

> ABER: Damit das funktioniert, mußt du das MySQL erst beenden, da sonst Inkonsistenzen auftreten können. Und das Backup sollte theoretisch auch nur auf demselben Rechner wieder funktionieren.

 

Mache ich ja auch. Da das mit dem Dumpen wohl etwas komplizierter wird, habe ich das bestehende Backup-Skript noch etwas umgeschrieben, unter Anderem um die Zeit, in der der MySQL-Server down ist, zu verkürzen.

 *Quote:*   

> Das könnte ich geschrieben haben. Wonach du suchst, sind transaktionsloggesteuerte Backups. Ich glaub aber nicht, daß MySQL sowas kann. Hab aber nicht weiter danach gesucht.

 

Ja, ich glaube, das kam von Dir.  :Smile: 

 *Quote:*   

> für kleinere Datenbanken ist mysqldump genau das was du suchst. Für größere geht es natürlich auch, wird allerdings ziemlich langsam (einfach testen). 
> 
> Was man beachten sollte: mysqldump garantiert Konsistenz nur innerhalb von einer Datenbank auf dem MySQL-Server, weil immer nur eine separat gelockt und gedumpt wird. Das ist aber in der Praxis kein Problem, weil ja normalerweile jede Anwendung (genau) eine Datenbank benutzt.

 

Die Datenbanken werde ich mir unter diesem Aspekt mal etwas genauer ansehen. Ich fürchte, dass es da Abhängigkeiten über Datenbankgrenzen hinweg gibt...

----------

## Anarcho

 *ChrisM87 wrote:*   

> Was man beachten sollte: mysqldump garantiert Konsistenz nur innerhalb von einer Datenbank auf dem MySQL-Server, weil immer nur eine separat gelockt und gedumpt wird. Das ist aber in der Praxis kein Problem, weil ja normalerweile jede Anwendung (genau) eine Datenbank benutzt.

 

Stimmt nicht immer, mysqldump bietet mit "-x" eine globalen Lock an. Ich mache meine Backups täglich so:

```
mysqldump -F -A -x -c -u root --password=geheimespwd | bzip2 > ${COMPLETE_BACKUP_PATH}/all_databases.sql.bz2
```

----------

## b3cks

Scheinbar bewahrheitet es sich auch hier, dass die meisten einfach root als Backup-User nutzen und somit das root-Passwort (für die DBs) in den Backup-Scripts oder spätestens in der ~/.bash_history im Klartext steht. Warum wird kein Backup-User erstellt, der lediglich die nötigen (Lese-)Rechte hat!?

----------

## sirro

Da ich bei einem Shared-Hoster bin kann ich nur mit meinem schreibenden User zugreifen.

Aber gerade da ist ein Passwort in der Aufrufzeile eine ganz schlechte Idee. Darum ist meins in der ~/.my.cnf, die natuerlich nur fuer mich lesbar ist.

Wenn man die Moeglichkeit hat, dann sollte man aber deine Strategie befolgen.

EDIT: ein globaler Lock bedeutet aber auch, dass in der Zeit des Backups die Datenbank readonly ist, oder?

----------

## musv

Was man nicht alles so beim Aufräumen der Festplatte findet:

Anlegen eines MySQL-Backups

```

#!/bin/bash

# Das Script dient zum Anlegen eines Backups der Mysql-Datenbank

mysqldump \

  -uroot \

  --password \

  -hlocalhost \

  --all-databases \

  --opt \

  --allow-keywords \

  --flush-logs \

  --hex-blob \

  --master-data \

  --max_allowed_packet=16M \

  --quote-names \

  --result-file=BACKUP_MYSQL_`date +%Y%m%d%H%M`.SQL

```

Zurückspielen des Backups

```

#!/bin/bash

# Script dient zum Zurückspielen des Backups

echo "Backup zurückspielen: "

cat $1 \

     | mysql \

     -uroot \

     --password \

     -hlocalhost \

     --max_allowed_packet=16M

echo "Rechte korrigieren: "

mysql_fix_privilege_tables \

     --defaults-file=/etc/mysql/my.cnf \

     --user=root \

     --password

```

----------

## Marlo

 *Mr. Anderson wrote:*   

> Hallo,
> 
> vorweg: ich habe nicht viel Erfahrung mit... 

 

einem "n".

Nimm es dir.

Ma

----------

## musv

 *Marlo wrote:*   

>  *Mr. Anderson wrote:*   vorweg: ich habe nicht viel Erfahrung mit...  
> 
> einem "n".
> 
> 

 

Wenn du "vorweg" meinen solltest:

http://www.duden-suche.de/suche/abstract.php?shortname=fx&artikel_id=181346

Ich glaub, da gibt es hier im Forum wesentlich lernresistentere Schreiberlinge im Bezug auf deutsche Orthografie. Ich sag nur: vorraus, Standart, Apostrophitis (http://www.deppenapostroph.de/) und das Verlernen zusammengesetzter Substantive (http://www.deppenleerzeichen.de).

Aber das Thema wurde schon zu oft (leider erfolglos) hier durchgekaut. Also belassen wir es dabei.

----------

## Anarcho

 *musv wrote:*   

>  *Marlo wrote:*    *Mr. Anderson wrote:*   vorweg: ich habe nicht viel Erfahrung mit...  
> 
> einem "n".
> 
>  
> ...

 

Nein, er meinte das "kosistent" aus dem Titel.

----------

## Mr. Anderson

Danke, jetzt hab ich's endlich kapiert. Hab die ganze Zeit nach einem fehlenden einzelnen 'n' in dem Satz gesucht. ^^

----------

## sschlueter

Kurz noch zwei Dinge, die bislang noch nicht genannt worden sind:

Bei InnoDB-Tabellen kann mysqldump ein konsistentes Backup erstellen, ohne die Tabellen zu sperren.

Ein konsistentes dateisystembasiertes Backup ist leicht realisierbar, wenn Replikation verwendet wird: Zum Erstellen des Backups einfach den Slave herunterfahren und anschließend ganz simpel ein Backup der Dateien des Slaves erstellen. Der Master läuft währendessen unbeeinträchtigt weiter. Der Slave kann durchaus auf demselben System laufen.

----------

## Anarcho

 *sschlueter wrote:*   

> Ein konsistentes dateisystembasiertes Backup ist leicht realisierbar, wenn Replikation verwendet wird: Zum Erstellen des Backups einfach den Slave herunterfahren und anschließend ganz simpel ein Backup der Dateien des Slaves erstellen. Der Master läuft währendessen unbeeinträchtigt weiter. Der Slave kann durchaus auf demselben System laufen.

 

Ehrlich gesagt halte ich von Dateisystembackups bei MySQL nichts. Ich habe es schon erlebt das ein Rückspielen nicht erfolgreich war. Dazu kommen noch mögliche Probleme mit Charsets usw.

----------

## sschlueter

 *Anarcho wrote:*   

> Ehrlich gesagt halte ich von Dateisystembackups bei MySQL nichts. Ich habe es schon erlebt das ein Rückspielen nicht erfolgreich war. Dazu kommen noch mögliche Probleme mit Charsets usw.

 

Jo, ist aber geeignet, ein Backup zu erstellen, das im Falle eines Falles auf genau demselben System mit genau derselben MySQL-Versionsnummer und genau derselben Konfiguration wieder eingesetzt werden kann. Und dann ist es unter Umständen schneller, die Dateien zu ersetzen als nen Dump einzuspielen. Im allgemeinen ist ein Dump besser, das stimme ich voll zu.

----------

