komplettes Backup mit mysqldump

Hi,
ich möchte gerne einen kompletten MySQL-Server backuppen. Ist es
richtig, dass sämtliche Informationen über eingerichtete User und deren
Rechte in der DB 'mysql' stehen? Reicht es also aus, diese mit mysqldump
zu backuppen oder muss ich noch etwas aus dem eigentlichen System
hinzuziehen?

Welche Optionen sind beim Backup mit mysqldump sinnvoll, wenn ich zum
Beispiel Problemen mit charsets vorbeugen möchte?

Zurzeit verwende ich
-e für extended-inserts
-f damit er bei Fehlern nicht abbricht und natürlich
-u -p -h für die notwendigen Daten.

Über Tipps und Hinweise würde ich mich sehr freuen,
viele Grüße
Thomas

Re: komplettes Backup mit mysqldump

Thomas Müller wrote:
> ich möchte gerne einen kompletten MySQL-Server backuppen. Ist es
> richtig, dass sämtliche Informationen über eingerichtete User und deren
> Rechte in der DB 'mysql' stehen? Reicht es also aus, diese mit mysqldump
> zu backuppen oder muss ich noch etwas aus dem eigentlichen System
> hinzuziehen?

Die Informationen aus den Tabellen in `mysql` reichen aus, um die
Berechtigungen innerhalb von MySQL wiederherzustellen (sofern es nicht
um Stored Procedures geht).

> Welche Optionen sind beim Backup mit mysqldump sinnvoll, wenn ich zum
> Beispiel Problemen mit charsets vorbeugen möchte?
>
> Zurzeit verwende ich
> -e für extended-inserts
> -f damit er bei Fehlern nicht abbricht und natürlich
> -u -p -h für die notwendigen Daten.

--opt
Mehr braucht's eigen*lich nicht. Bei MySQL>=4.1 ist das auch der Default.

Bist du dir sicher, dass du -f brauchst? Welche Fehler erwartest du?

Grüße
Kai

--
This signature is left as an exercise for the reader.

Re: komplettes Backup mit mysqldump

Hi,
vielen Dank für die Antwort.

>> Welche Optionen sind beim Backup mit mysqldump sinnvoll, wenn ich zum
>> Beispiel Problemen mit charsets vorbeugen möchte?
>>
>> Zurzeit verwende ich
>> -e für extended-inserts
>> -f damit er bei Fehlern nicht abbricht und natürlich
>> -u -p -h für die notwendigen Daten.
>
> --opt
> Mehr braucht's eigen*lich nicht. Bei MySQL>=4.1 ist das auch der Default.
>
> Bist du dir sicher, dass du -f brauchst? Welche Fehler erwartest du?

Ich weiß nicht, welche Fehler auftreten könnten. Was ist zum Beispiel
mit Tabellen, die gerade beschrieben werden oder mit solchen, die sich
nicht locken lassen? Ich dachte mir: Bevor irgendwelche Fehler
auftauchen, setze ich lieber -f, dann macht er auf jeden Fall weiter.
Oder inwiefern kann -f schaden?

Bezüglich möglicherweise verschiedenlicher charsets sollte ich keine
Vorkehrungen treffen?

Viele Grüße
Thomas

Re: komplettes Backup mit mysqldump

Thomas_Müller wrote:

>> Bist du dir sicher, dass du -f brauchst? Welche Fehler erwartest du?
>
> Ich weiß nicht, welche Fehler auftreten könnten.

Um so schlimmer. "Ich weiß zwar nicht, was passieren kann, aber ich
mache in jedem Fall einfach weiter"

> Was ist zum Beispiel
> mit Tabellen, die gerade beschrieben werden oder mit solchen, die sich
> nicht locken lassen?

mysqldump ist aus Sicht des Servers ein gewöhnlicher Client. Der
bekommt seine Locks also u.U. sehr spät oder möglicherweise auch
gar nicht, wenn es ein Timeout gibt.

Wenn du InnoDB-Tabellen sichern willst, empfiehlt sich die Option
--single-transaction.

> Ich dachte mir: Bevor irgendwelche Fehler
> auftauchen, setze ich lieber -f, dann macht er auf jeden Fall weiter.
> Oder inwiefern kann -f schaden?

Im Falle von Fehlern ist dein Dump so oder so unvollständig. Mit
-f bekommst du es u.U. aber gar nicht mit.

Wie bei jedem Backup gilt übrigens auch hier: der Backup-Vorgang
ist nur die halbe Miete. Wichtiger ist jedoch der Restore-Vorgang.
Du solltest das in jedem Fall testen.

> Bezüglich möglicherweise verschiedenlicher charsets sollte ich keine
> Vorkehrungen treffen?

Ab 4.1 schreibt mysqldump zu jedem Objekt (db, table, field) die
jeweiligen Charset-Einstellungen in den Dump. Die Daten selber sind
im Dump UTF-8 codiert abgelegt. Ein 'SET NAMES UTF-8' am Anfang des
Dumps sorgt dafür, daß ein Re-Import des Dumps per 'mysql' korrekt
verläuft.

Problematisch ist der Import eines pre-4.1 Dumps in ein 4.1+ MySQL,
weil in einem alten Dump *ueberhaupt keine* Informationen über das
Encoding abgelegt sind. Für die Gegenrichtung hat mysqldump die
--compatible Option.


noch was bezüglich "Sichern von MySQL-Accounts":

Ich habe vor längerer Zeit mal ein kleines Skript gebastelt, das
MySQL-Accounts in Form von GRANT Statements dumpt. Du findest es
hier: http://24days.de/~schwenke/MySQL/get-grants.pl

Ein ähnliches Skript mit noch mehr Features gibts hier:
http://forge.mysql.com/snippets/view.php?id=12


XL

Re: komplettes Backup mit mysqldump

Hi!

>>>Bist du dir sicher, dass du -f brauchst? Welche Fehler erwartest du?
>>
>>Ich weiß nicht, welche Fehler auftreten könnten.
>
> Um so schlimmer. "Ich weiß zwar nicht, was passieren kann, aber ich
> mache in jedem Fall einfach weiter"

Naja, ich gehe davon aus, dass sämtliche Backupvorgänge fehlerfrei
verlaufen. Allerdings will ich der Möglichkeit vorbeugen, dass ich mich
geirrt habe und doch Unvorhergesehenes eintritt. :)

Naja, im Ernst, auf der Manpage lese ich:
"-f|--force
Continue even if we get an sql-error."

Nun dachte ich, dass er sonst, wenn ein schwerwiegender Fehler auftritt,
den Dump abbricht und genau das wollte ich mit -f verhindern. Wieso
bekomme ich mögliche Fehler denn dann nicht mehr mit? Wird das errorlog
dadurch ausgeschaltet?

> Im Falle von Fehlern ist dein Dump so oder so unvollständig. Mit
> -f bekommst du es u.U. aber gar nicht mit.
>
> Wie bei jedem Backup gilt übrigens auch hier: der Backup-Vorgang
> ist nur die halbe Miete. Wichtiger ist jedoch der Restore-Vorgang.
> Du solltest das in jedem Fall testen.

Jaja, mache ich ja. Aber ich möchte Szenarien, die in meinen Tests nicht
auftraten, gerne vorbeugen... :)

> Ab 4.1 schreibt mysqldump zu jedem Objekt (db, table, field) die
> jeweiligen Charset-Einstellungen in den Dump. Die Daten selber sind
> im Dump UTF-8 codiert abgelegt. Ein 'SET NAMES UTF-8' am Anfang des
> Dumps sorgt dafür, daß ein Re-Import des Dumps per 'mysql' korrekt
> verläuft.

Achso! Nun, ich verwende noch eine etwas betagte "MySQL - 4.0.24". Es
scheint also sinnvoll, dass ich die auf 4.1 update? Im Debian stable
repository ist noch eine "4.1.11a-4sarge2". Wenn ich mich dabei hieran

http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.ht ml

halte, sollte es doch keine größeren Schwierigkeiten geben, oder? Über
die Datenbank läuft noch ein vpopmail. Da habe ich Gerüchte gehört, dass
es Probleme mit 4.1 geben soll? Doch wenn charsets ab 4.1 endlich 'mal
vernünftig definiert werden können, nehme ich den Umstand des Upgrades
gerne auf mich. Ich hatte damit schön öfter Probleme.

> Problematisch ist der Import eines pre-4.1 Dumps in ein 4.1+ MySQL,
> weil in einem alten Dump *ueberhaupt keine* Informationen über das
> Encoding abgelegt sind. Für die Gegenrichtung hat mysqldump die
> --compatible Option.

Hmm... :) Hast du Tipps, wie ich das halbwegs geschickt durchführen kann?

> noch was bezüglich "Sichern von MySQL-Accounts":
>
> Ich habe vor längerer Zeit mal ein kleines Skript gebastelt, das
> MySQL-Accounts in Form von GRANT Statements dumpt. Du findest es
> hier: http://24days.de/~schwenke/MySQL/get-grants.pl

Ah, vielen Dank. Aber wenn ich das richtig sehe, dann dumpt das nur
Informationen aus der Tabelle "user", für mich wären aber noch
"tables_priv" sehr wichtig. Mit einem kompletten Dump der DB "mysql"
sollte ich doch alle Informationen, die für das Benutzermanagement
wichtig sind, haben, oder?

Vielen Dank, viele Grüße
Thomas

Re: komplettes Backup mit mysqldump

Thomas Müller wrote:
> Naja, im Ernst, auf der Manpage lese ich:
> "-f|--force
> Continue even if we get an sql-error."
> Nun dachte ich, dass er sonst, wenn ein schwerwiegender Fehler auftritt,=
=20
> den Dump abbricht und genau das wollte ich mit -f verhindern. Wieso=20
> bekomme ich mögliche Fehler denn dann nicht mehr mit? Wird das errorlo=
g=20
> dadurch ausgeschaltet?

Typischerweise willst Du in Shell-Scripten, die mysqldump aufrufen,
dessen Returncode und seine stderr-Ausgabe auswerten, damit Du Dich
(via eMail oder wie auch immer) zeitnah benachrichtigen lassen
kannst, wenn etwas schieflaeuft.

-f liefert Dir als Backup dann _irgendwas_, aber Du weisst nicht was.
Fehlt eventuell Deine wichtigste Tabelle und Du hast nichts davon
mitbekommen? Du kannst es nicht sagen, weil mysqldump ja alle Fehler
einfach ignoriert hat. Das boese Erwachen kommt dann erst beim
Rueckspielen des Backups, wenn die wichtigsten Daten fehlen. Wenn ein
Fehler bei mysqldump auftaucht, gibt es nur eine einfache richtige
Vorgehensweise: Zeitnah notifiziert werden und zeitnah die
Fehlerursache beseitigen.

Logfiles sind schoen und gut, aber im realen Alltag werden sie
zumeist nicht beachtet, ausser, wenn Du eine ordentliche
Auswertesoftware drauf hast, die harmlose Eintraege von nicht so
harmlosen unterscheidet und dann ihrerseits notifiziert. Kleiner
Haken: mysqldump hat kein Logfile.

> Jaja, mache ich ja. Aber ich möchte Szenarien, die in meinen Tests nic=
ht=20
> auftraten, gerne vorbeugen... :)

Das geht nicht. Es gibt keine richtigen Patentrezepte gegen Fehler,
die man nicht kennt. Das A&O im produktiven Betrieb ist aber, dass
man derartige Fehler mitbekommt und dann fallweise untersucht und
beseitigt. Wenn man Systeme im Blindflug betreibt, wiegt man sich in
Sicherheit, die man gar nicht hat.

Re: komplettes Backup mit mysqldump

Thomas_Müller wrote:

>> Ab 4.1 schreibt mysqldump zu jedem Objekt (db, table, field) die
>> jeweiligen Charset-Einstellungen in den Dump. Die Daten selber sind
>> im Dump UTF-8 codiert abgelegt.
>
> Achso! Nun, ich verwende noch eine etwas betagte "MySQL - 4.0.24". Es
> scheint also sinnvoll, dass ich die auf 4.1 update?

Wenn du damit real existierende Probleme behoben bekommst, solltest du
ein Upgrade in Betracht ziehen.

> Über
> die Datenbank läuft noch ein vpopmail. Da habe ich Gerüchte gehört, dass
> es Probleme mit 4.1 geben soll?

Kenne ich nicht, kann ich nix zu sagen. Eine vernünftig geschriebene
Applikation sollte den Sprung nach 4.1 ohne größere Probleme schaffen.
Schlimmstenfalls muß man Anpassungen an der Applikation vornehmen.
Gerade von 4.0 zu 4.1 hat sich vieles geändert in MySQL.

>> Problematisch ist der Import eines pre-4.1 Dumps in ein 4.1+ MySQL,
>> weil in einem alten Dump *ueberhaupt keine* Informationen über das
>> Encoding abgelegt sind. Für die Gegenrichtung hat mysqldump die
>> --compatible Option.
>
> Hmm... :) Hast du Tipps, wie ich das halbwegs geschickt durchführen kann?

Das beste ist, die default-charset Einstellung aus der 4.0er my.cnf
in die 4.1er my.cnf zu übernehmen und den 4.1er Server einfach mit
den 4.0er Daten zu starten. Dann initialisiert 4.1 die charsets für
Tabellen und Spalten damit. Das geht nur dann in die Hose, wenn man
in 4.0 zwar default-charset=german1 gesetzt hatte, die Daten aber
trotzdem als latin2 oder utf8 abgelegt hatte.

Die Probleme mit mysqldump umgeht man so, indem man es nicht verwendet.
Alternativ kann man sich ein 4.1er mysqldump besorgen und die Daten
vom laufenden 4.0er Server damit abziehen.

>> Ich habe vor längerer Zeit mal ein kleines Skript gebastelt, das
>> MySQL-Accounts in Form von GRANT Statements dumpt.
>
> Ah, vielen Dank. Aber wenn ich das richtig sehe, dann dumpt das nur
> Informationen aus der Tabelle "user", für mich wären aber noch
> "tables_priv" sehr wichtig.

Das Skript dumpt *GRANTs*. Das schließt Privilegien für einzelne
Tabellen ein.

> Mit einem kompletten Dump der DB "mysql"
> sollte ich doch alle Informationen, die für das Benutzermanagement
> wichtig sind, haben, oder?

Ja, das hat aber Nachteile:

1. GRANTs sind viel besser lesbar. Eine Anwendung o.g. Skripts besteht
darin, die GRANTs für eine MySQL-Datenbank versionierbar zu machen
indem man die erzeugten Files in z.B. CVS eincheckt.

2. Die GRANTs funktionieren auch mit zukünftigen Versionen von MySQL
noch. Die Struktur der Privileg-Tabellen ändert sich typischerweise
mit jeder Major-Release von MySQL.


XL

Re: komplettes Backup mit mysqldump

Hi,
vielen Dank für die ausführliche Antwort.

>>Naja, im Ernst, auf der Manpage lese ich:
>>"-f|--force
>> Continue even if we get an sql-error."
>>Nun dachte ich, dass er sonst, wenn ein schwerwiegender Fehler auftritt,
>>den Dump abbricht und genau das wollte ich mit -f verhindern. Wieso
>>bekomme ich mögliche Fehler denn dann nicht mehr mit? Wird das errorlog
>>dadurch ausgeschaltet?
>
> Typischerweise willst Du in Shell-Scripten, die mysqldump aufrufen,
> dessen Returncode und seine stderr-Ausgabe auswerten, damit Du Dich
> (via eMail oder wie auch immer) zeitnah benachrichtigen lassen
> kannst, wenn etwas schieflaeuft.

Ja, genau. Das stderr wandert in meine Logfile. Die Zeile sieht hier so aus:

mysqldump -u$MySQLUSER -p$MySQLPASS -h $MySQLHOST -ef $db | gzip >
$DUMPFILE 2>> $LOGFILE

Aber aus der manpage ersehe ich nur, dass bei -f nicht abgebrochen wird.
Wo steht denn, dass er die stderr nicht mehr weiter ausgibt?

> -f liefert Dir als Backup dann _irgendwas_, aber Du weisst nicht was.
> Fehlt eventuell Deine wichtigste Tabelle und Du hast nichts davon
> mitbekommen? Du kannst es nicht sagen, weil mysqldump ja alle Fehler
> einfach ignoriert hat.

Hm, da hast du natürlich vollkommen recht. Das wäre ganz schlecht. Aber
so hatte ich die Beschreibung in der manpage nicht interpetiert.

Viele Grüße
Thomas

Re: komplettes Backup mit mysqldump

Hi,
danke für die nochmalige Antwort!

>>>Ab 4.1 schreibt mysqldump zu jedem Objekt (db, table, field) die
>>>jeweiligen Charset-Einstellungen in den Dump. Die Daten selber sind
>>>im Dump UTF-8 codiert abgelegt.
>>
>>Achso! Nun, ich verwende noch eine etwas betagte "MySQL - 4.0.24". Es
>>scheint also sinnvoll, dass ich die auf 4.1 update?
>
> Wenn du damit real existierende Probleme behoben bekommst, solltest du
> ein Upgrade in Betracht ziehen.

Ich habe das gestern Abend auf 4.1 migiriert - bisher ohne Probleme. Die
Umlaute in allen Foren, die darauf laufen, scheinen korrekt
dargestellt zu werden. Und ich kann nun endlich Dumps mit
charset-Informationen anlegen.

>>Über die Datenbank läuft noch ein vpopmail. Da habe ich Gerüchte gehört, dass
>>es Probleme mit 4.1 geben soll?

Auch hier gag es glücklicherweise keine Schnwierigkeiten.

> Das beste ist, die default-charset Einstellung aus der 4.0er my.cnf
> in die 4.1er my.cnf zu übernehmen und den 4.1er Server einfach mit
> den 4.0er Daten zu starten. Dann initialisiert 4.1 die charsets für
> Tabellen und Spalten damit. Das geht nur dann in die Hose, wenn man
> in 4.0 zwar default-charset=german1 gesetzt hatte, die Daten aber
> trotzdem als latin2 oder utf8 abgelegt hatte.

Hm, seltsamerweise gab es in der alten my.conf keine default-charset
Einstellungen und auch in der neuen konnte ich im Standard nichts
finden. Ich dachte, dass MySQL 4.0 standardmäßig in latin1 schreibt,
darum habe ich das für die Imports der Dumps als default-character-set
gesetzt. Offenbar hat das gereicht.

> Die Probleme mit mysqldump umgeht man so, indem man es nicht verwendet.

Hm, womit empfiehlt es sich denn, MySQL-Backups anzufertigen?

> Alternativ kann man sich ein 4.1er mysqldump besorgen und die Daten
> vom laufenden 4.0er Server damit abziehen.

Oh, das ist interessant... Das werde ich mir 'mal für den Fall merken,
dass alles andere schief geht. :)

>>>Ich habe vor längerer Zeit mal ein kleines Skript gebastelt, das
>>>MySQL-Accounts in Form von GRANT Statements dumpt.
>>
>>Ah, vielen Dank. Aber wenn ich das richtig sehe, dann dumpt das nur
>>Informationen aus der Tabelle "user", für mich wären aber noch
>>"tables_priv" sehr wichtig.
>
> Das Skript dumpt *GRANTs*. Das schließt Privilegien für einzelne
> Tabellen ein.

Sorry, das Hatte ich wohl übersehen.

>>Mit einem kompletten Dump der DB "mysql"
>>sollte ich doch alle Informationen, die für das Benutzermanagement
>>wichtig sind, haben, oder?
>
>
> Ja, das hat aber Nachteile:
>
> 1. GRANTs sind viel besser lesbar. Eine Anwendung o.g. Skripts besteht
> darin, die GRANTs für eine MySQL-Datenbank versionierbar zu machen
> indem man die erzeugten Files in z.B. CVS eincheckt.
>
> 2. Die GRANTs funktionieren auch mit zukünftigen Versionen von MySQL
> noch. Die Struktur der Privileg-Tabellen ändert sich typischerweise
> mit jeder Major-Release von MySQL.

OK, das klingt logisch. Ich werde in meinem Backupsystem also zusäzlich
noch GRANTS einbirngen.

Viele Grüße
Thomas

Re: komplettes Backup mit mysqldump

Thomas_Müller wrote:

>> Das beste ist, die default-charset Einstellung aus der 4.0er my.cnf
>> in die 4.1er my.cnf zu übernehmen und den 4.1er Server einfach mit
>> den 4.0er Daten zu starten.
>
> Hm, seltsamerweise gab es in der alten my.conf keine default-charset
> Einstellungen und auch in der neuen konnte ich im Standard nichts
> finden.

Dann gilt der MySQL-Default: latin1 mit schwedischer ;-) collation

>> Die Probleme mit mysqldump umgeht man so, indem man es nicht verwendet.
>
> Hm, womit empfiehlt es sich denn, MySQL-Backups anzufertigen?

Schon mysqldump. Aber es ist kaum tauglich, wenn man Daten von 4.0
auf 4.1 migrieren will. Normalerweise macht man den Dump aber nur
zur Sicherheit und startet den neuen Server einfach mit dem alten
Daten-Verzeichnis.

>> Alternativ kann man sich ein 4.1er mysqldump besorgen und die Daten
>> vom laufenden 4.0er Server damit abziehen.
>
> Oh, das ist interessant... Das werde ich mir 'mal für den Fall merken,
> dass alles andere schief geht. :)

Das ist ganz generell die empfohlene Vorgehensweise; wenn du von
Version X auf Version Y umsteigen willst, solltest du das mysqldump
Version Y zum dumpen der Daten verwenden.

>> 1. GRANTs sind viel besser lesbar.
>>
>> 2. Die GRANTs funktionieren auch mit zukünftigen Versionen von MySQL
>
> OK, das klingt logisch. Ich werde in meinem Backupsystem also zusäzlich
> noch GRANTS einbirngen.

Brav! :-)


XL