sicherung, welche variante ist besser ?

MySQL, PostgreSQL, SQLite
torti0013
Posts: 15
Joined: 2004-10-25 14:25

sicherung, welche variante ist besser ?

Post by torti0013 » 2004-10-25 15:24

hallo,

ich habe mal mysqldump ausprobiert und funktioniert recht gut.

was mir jetzt nicht ganz klar ist (habe mal in verschiedenen anderen foren geschaut). mache kopieren die datenbank im betrieb woanders und packen diese dann. ich war bis jetzt der meinung das man eine laufenden datenbank nicht kopieren kann.
ist dies doch möglich ?
funktioniert das zurück spielen auch ohne probleme ?
warum sollte man dann mysqldump verwenden ?

vielleicht kann mir jemand meine fragen beantworten.

danke

outofbound
Posts: 470
Joined: 2002-05-14 13:02
Location: Karlsruhe City

Re: sicherung, welche variante ist besser ?

Post by outofbound » 2004-10-25 15:35

torti0013 wrote:hallo,

ich habe mal mysqldump ausprobiert und funktioniert recht gut.
Sehr schön. ;)
was mir jetzt nicht ganz klar ist (habe mal in verschiedenen anderen foren geschaut). mache kopieren die datenbank im betrieb woanders und packen diese dann. ich war bis jetzt der meinung das man eine laufenden datenbank nicht kopieren kann.
Doch, kann man, sollte man aber auf keinen Fall machen. Vor allem im laufenden Betrieb
kann das zu Inkonsistenzen führen == Datenverlust.
ist dies doch möglich ?
Ja, siehe oben.
funktioniert das zurück spielen auch ohne probleme ?
Nein, nicht unbedingt.
warum sollte man dann mysqldump verwenden ?
Datenkonsistenz.


Gruss,

Out

torti0013
Posts: 15
Joined: 2004-10-25 14:25

Re: sicherung, welche variante ist besser ?

Post by torti0013 » 2004-10-25 20:45

hab mal selber versucht was aus ein paar schnipsel zusammen zu basteln (so lernt man am besten):

Code: Select all

#! /bin/sh
NAME1=`date +%d%m%Y`
NAME2=`date +%Hh%Mm`
ZIEL=/sicherung/

mysqldump --opt usr_web2_1 -pPASSWORT  > ${ZIEL}/usr_web2_1_${NAME1}_${NAME2}.sql
tar cvpzPf ${ZIEL}usr_web2_1_${NAME1}_${NAME2}.tgz ${ZIEL}*sql
rm ${ZIEL}*sql
was haltet ihr von dieser lösung ?

vielleicht kann man noch was verbessern ?

ist es eigentlich möglich aus einem kompletten dump (alle datenbanken) auch nur eine datenbak einzuspielen ? denn es muß ja nicht immer sein das alle einen schaden haben.

danke mal

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: sicherung, welche variante ist besser ?

Post by Joe User » 2004-10-25 21:26

Kleine Anregung:

Code: Select all

mysqlcheck --analyze --check --auto-repair --extend --optimize --all-databases -uroot
mysqldump -a -c -i --allow-keywords --delayed-insert --opt -A > /path/to/full_db_backup.sql
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

outofbound
Posts: 470
Joined: 2002-05-14 13:02
Location: Karlsruhe City

Re: sicherung, welche variante ist besser ?

Post by outofbound » 2004-10-26 12:00

Joe User wrote:Kleine Anregung:

Code: Select all

mysqlcheck --analyze --check --auto-repair --extend --optimize --all-databases -uroot
mysqldump -a -c -i --allow-keywords --delayed-insert --opt -A > /path/to/full_db_backup.sql
Bist du sicher, dass das reparieren der Datenbank VOR dem Backup eine so gute Idee ist? ;)

Dann würde ich auf jeden Fall gescycelte Backups vornehmen... (so etwa 7 Tage) und vor
allem das Wiedereinspielen der Backups auf einer TestDB prüfen, damit auch alles klappt.

Ich hatte es schon oft genug, dass irgendwelche Leute ungültige Zeichen in Tabellennamen
verwendet haben... (geht mit create table z.B.), die lassen sich auch ohne Stress dumpen,
nur wiedereinspielen iss nich ;)

Ebenfalls lustig: Tabelle füllen, ID- Column erst nach ein paar Einträgen auf AI setzen...
knallt auch ganz lustig beim wiedereinspielen... das dumpen funzt ohne Probleme ;)

Gruss,

Out



Gruss,

Out

eagle2k
Userprojekt
Userprojekt
Posts: 34
Joined: 2003-06-11 21:09

Re: sicherung, welche variante ist besser ?

Post by eagle2k » 2005-04-08 14:51

ja, das problem habe ich auch... mit mysqldump erstellte dumps lassen sich unter umständen nicht einspielen... wenn man das aber mit phpmyadmin macht, klappt es ohne weiteres. gibt es nicht bessere methoden ein backup zu machen?

alexander newald
Posts: 1117
Joined: 2002-09-27 00:54
Location: Hannover

Re: sicherung, welche variante ist besser ?

Post by alexander newald » 2005-04-08 19:20

Ich habe mal meine Lösung auf http://linux.newald.de/new_design/backup.html gepackt.

Das Script kann locale und remote Backups (per FTP) und unterschiedliche Backup Sets sowie Backup auf einem optionalen 2. remote Server

krusty007
Posts: 28
Joined: 2006-02-23 15:00
Location: Eifel

Re: sicherung, welche variante ist besser ?

Post by krusty007 » 2006-02-23 17:09

Irgendwie wurde die Frage nicht richtig beantwortet, für meinen Geschmack zumindest. Naja, will noch was hinzufügen.

Ist es nicht möglich im laufenden Datenbankbetrieb ein Backup zu machen? Muss ich den Datenbankprozess zuvor beenden, aber dann läuft doch auch mysqldump nicht mehr. Wie sieht es aus, wenn ständig also rund um die Uhr Daten in die Datenbank geschrieben werden. Kann ich dann auch das mysqldump verwenden? Was haltet ihr von dem Prog mysqldumper? Diese Tool bring ein Script in Perl mitsich, welches man prima über cronjob starten lassen kann.

Sorry das ich den alten Thread wiederbelebe aber ein neuer muss ja auch nicht sein.

golloza
Posts: 23
Joined: 2005-01-03 17:27

Re: sicherung, welche variante ist besser ?

Post by golloza » 2006-02-23 20:49

Ich halte das hier für die beste Lösung:

1) datadir auf ein LVM Logical Volume legen
2) FLUSH TABLES WITH READ LOCK
3) Snapshot vom LV erstellen
4) UNLOCK TABLES
5) In aller Ruhe vom Snapshot aus das Zeug sichern
6) Snapshot entfernen

Schritt 3 ist sofort fertig, so dass der Schreibzugriff nur minimal unterbrochen wird (SELECTs gehen ja sowieso durchgehend). Solange das Snapshot-LV existiert ist natürlich die Performance etwas beeinträcht, ausserdem muss man darauf achten, dass es groß genug ist, um die Ã?nderungen aufzunehmen.

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: sicherung, welche variante ist besser ?

Post by flo » 2006-02-23 21:18

Bei mir läuft täglich ein Bericht durch, der die Tabellen durchcheckt.

Etwa drei-vier Stunden später kommt dann der eigentliche Backup-Lauf, der die Datenbanken einzeln dumpt.

Snapshots sind eine ganz nette Sache, wenn das Backup zeitnah passieren muß, aber das Problem inkonsistenter Daten kriegt man damit auch nicht in den Griff, oder?

flo.

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: sicherung, welche variante ist besser ?

Post by isotopp » 2006-02-24 09:28

golloza wrote:2) FLUSH TABLES WITH READ LOCK
Funktioniert nur mit MyISAM, insbesondere bei InnoDB funktioniert das nicht.

Ansonsten ist das mit dem LVM Snapshot eine sehr gute Methode.

Auch sehr bequem ist einen Slave aufzusetzen (auf derselben Maschine, Puffergrößen 1/10 vom Master), und dann den Slave anzuhalten und ein Backup vom Datadir vom Slave zu machen.

krusty007
Posts: 28
Joined: 2006-02-23 15:00
Location: Eifel

Re: sicherung, welche variante ist besser ?

Post by krusty007 » 2006-02-24 09:33

Also ist es doch am besten, wenn ich einmal ein mysqldump mache. Nur leider habe ich eine recht große DB, ca. 7 GB groß. Wenn ich dann ein Dump mache, dauert das so seine Zeit. Und in dieser Zeit können ja dann keine Daten geschrieben werden. Das ist sehr schlecht, denn dann fehlen mir Daten um Graphen zu erzeugen. Gibt es keine Möglichkeit die Datenbank zu sichern und gleichzeitig den Betrieb aufrecht zu erhalten, sowas wie Online-Backup.
Habe letztens im Internet eine gegenüberstellung von verschiedenen Datenbanken gesehen. Dabei habe ich festgestellt, das MySQL keine Online-Sicherung unterstütz, ist das richtig? MaxDB hingegen soll so was unterstützen. Nur so als Beispiel. Wird denn in der 5er Version diese Backupmöglichkeit unterstützt?
Dann fällt mir gleich die nächste Frage ein, wie sieht es bei MySQL mit inkrementellen Backups aus, geht sowas?

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: sicherung, welche variante ist besser ?

Post by isotopp » 2006-02-24 10:06

krusty007 wrote:Nur leider habe ich eine recht große DB, ca. 7 GB groß.
Das ist eher klein. :)

Wie auch immer, mit dem lokalen Backup Slave bekommst Du genau das Online-Backup.
Dann fällt mir gleich die nächste Frage ein, wie sieht es bei MySQL mit inkrementellen Backups aus, geht sowas?
Das sind Binlogs. Die kann man einfach so wegsichern.

krusty007
Posts: 28
Joined: 2006-02-23 15:00
Location: Eifel

Re: sicherung, welche variante ist besser ?

Post by krusty007 » 2006-02-24 10:39

Und wie soll das praktisch funktionieren. Kann ich dann z.b. alle 12h die Datei sicheren. Wenn jetzt die DB abraucht und ich die neu aufspielen muss. Wie kann ich dann die Daten wieder herstellen. In der my.cnf gibt es doch eine Option, glaube bin-log die man einfügen muss, damit diese bin-log Datei angelegt wird. Oder???

Wenn ich sowas jetzt machen möchte und ich habe bereits eine Datenbank mit (wenigen) Einträgen (7Mio), kann ich dann den Server einfach stoppen die my.cnf mit dem Parameter ändern und den Server neu starten? Zuvor wäre es sicherlich sinnvoll ein mysqldump zu machen und diesen sehr gut zu sichern. Ab da muss ich doch eigentlich nur noch die bin-log sichern und fertig ist mein BackupSystem. Oder habe ich mal wieder was in den falschen Hals bekommen? Wird die bin-log eigentlich immer weiter geschrieben, oder werden hier vielleicht automatisch nach einer gewissen Zeit oder einer bestimmten Größe eine neue erzeugt? Wie sieht eine Wiederherstellung aus, mit dieser Datei?

Du siehst ich bin nicht wirklich fit in Sachen MySQL aber ich habe so viele Fragen. :) Hoffe du kannst mir helfen. Auf jeden Fall will ich mich schon mal für die Mühe bedanken.

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: sicherung, welche variante ist besser ?

Post by isotopp » 2006-02-24 11:10

krusty007 wrote:Und wie soll das praktisch funktionieren.
Du machst ein Vollbackup und zwar in einer Weise, daß die Binlog-Position zum Zeitpunkt des Backups atomar ist. Alle Binlogs, die älter als das Vollbackup sind, kannst Du löschen.

Du machst vor dem inkrementellen Backup ein "FLUSH LOGS". Dadurch wird ein neues Binlog angefangen.

Du sicherst in den inkrementellen Backups alle Binlogs.


Zum Restore restaurierst Du die Datenbank vom Vollbackup, und spielst dann alle Binlogs ab der Position des Vollbackups bis zum gewünschten Zeitpunkt des Wiederaufsetzens mit "mysqlbinlog ... | mysql ..." wieder ab. Auf diese Weise kannst Du jeden Zeitpunkt zwischen dem Vollbackup und dem Zeitpunkt des Inkrementellen Backup ansteuern.

Das ist auch genau das, was ein Slave macht. Nur daß Du beim Slave ein datadir vorfindest, das bereits auf Stand ist, weil es das Binlog ständig vom Master zieht und abspielt. Von daher ist es für das Restore in den meisten Fällen einfacher und schneller, einen Slave zu haben (und den zu sichern) statt den Aufwand mit den manuellen Binlog-Sicherungen zu treiben. Insbesondere wenn man noch nicht mal 10GB wegschaffen muss.
In der my.cnf gibt es doch eine Option, glaube bin-log die man einfügen muss, damit diese bin-log Datei angelegt wird. Oder???
Das ist korrekt. Du setzt das in die [mysqld]-Sektion der my.cnf, machst ein "rcmysql stop; sleep 2; rcmysql start" und dann bekommst Du ein Binlog.

Danach machst Du ein "mysqldump --master-data=2" und bekommst einen Dump mit Binlog-Position drin (als Kommentar, wenn Du das Statement stattdessen aktiv haben willst, setze --master-data=1).

Wichtig ist, daß der Dump gemacht wird, NACHDEM das Binlog an ist (sonst kriegst Du kein Backup mit Binlog-Position drin).
Wird die bin-log eigentlich immer weiter geschrieben, oder werden hier vielleicht automatisch nach einer gewissen Zeit oder einer bestimmten Größe eine neue erzeugt? Wie sieht eine Wiederherstellung aus, mit dieser Datei?
MySQL hat ein Handbuch-Kapitel zu diesem Thema, http://dev.mysql.com/doc/refman/5.0/en/binary-log.html. Das erklärt Dir den Mechanismus und alle wesentlichen Variablen, darunter auch max_binlog_size, und auch binlog_cache_size und die dazu gehörenden SHOW STATUS Zähler. Du willst auch über sync_binlog Bescheid wissen.

krusty007
Posts: 28
Joined: 2006-02-23 15:00
Location: Eifel

Re: sicherung, welche variante ist besser ?

Post by krusty007 » 2006-02-24 12:45

hi isotopp,

gilt das ganze auch für Mysql Version 4.1? Werde mir das Thema mal genauer anschauen. Danke schonmal für deine Hilfe.

[edit]Sag mal kannst du mir ein wirklich gutes Mysql Buch empfehlen, welches nicht nur auf Syntax eingeht sonder sich auch mit der Konfiguration des Servers auseinandersetzt? Der Preis ist zweitrangig.

krusty007
Posts: 28
Joined: 2006-02-23 15:00
Location: Eifel

Re: sicherung, welche variante ist besser ?

Post by krusty007 » 2006-02-24 13:55

Vielen Dank für die vielen Buchtips. Werde dann mal ne größere Bestellung bei Amazon in Auftrag geben. :P

oxygen
Posts: 2138
Joined: 2002-12-15 00:10
Location: Bergheim

Re: sicherung, welche variante ist besser ?

Post by oxygen » 2006-02-24 14:03

torti0013 wrote: ist es eigentlich möglich aus einem kompletten dump (alle datenbanken) auch nur eine datenbak einzuspielen ? denn es muß ja nicht immer sein das alle einen schaden haben.
Die Dumps die von mysqldump erzeugt werden, haben eine einfach Zusatz Syntax in Form von Kommentaren wie
--
-- Current Database: `mysql`
--
(ähnlich auch für Tabellenstruktur und Tabellendaten)

Daran lassen sich die Files sehr leicht dran aufspalten. Das macht es für mich überflüssig, für jede Datenbank einen Dump anzulegen. Zumindest im MB-Bereich...

stanglwirt
Posts: 48
Joined: 2006-01-10 14:44

Re: sicherung, welche variante ist besser ?

Post by stanglwirt » 2006-11-08 10:06

sorry wenn ich dieses alte thema wieder aufgreife aber irgendwie kann keiner das problem so richtig lösen.

- ich brauch ein fast-online-backup, die datenbank darf max. 5 sek. nicht verwendbar sein. bei meiner db-größe dauert das auf jeden fall länger, wenn ich meine applikation anhalte und dann nen dump von der db ziehe.
- ich will ein dump als backup haben, weil ich die versionsfreiheit will bei der wiederherstellung. die mysql-dateien sind dafür ungeeignet.

wie kann ich vorgehen?


welche vor- und nachteile gibt es denn bei der LVM-Variante? Wie funktioniert das überhaupt, dass ein Snapshot, der die Daten zu einem bestimmten Zeitpunkt beinhaltet, in so kurzer Zeit gezogen werden kann?
Und inwiefern ist die Performance beeinträchtigt, solang der Snapshot existiert? wie viel macht das aus?
kann man bei der variante nicht so vorgehen, dass man die binär-dateien in lvm sichert, dann einen zweiten mysql-server aufmacht der darauf zugreift und von dort dann den dump ziehen? um konsitente daten zu haben, muss ich die applikation stoppen während lvm das snapshot erzeugt? ich denke ja...

welche vor- und nachteile gibt es bei der Slave-Variante? Vorteil ist sicher, dass es ziemlich bequem ist. Als großen Nachteil sehe ich die große Resourcen-Verschwendung wenn das Teil "nur" zum backupen ständig mitläuft. RAM, CPU und vor allem auch doppelte Plattenzugriffe. Ich habe soviele Writes in die Datenbank dass es mir unmöglich erscheint, einen 2. server laufen zu lassen. Denke sogar darüber nach, das bin-log abzustellen um die plattenzugriffe zu senken, und den Datenverlust zwischen den Vollbackups hinzunehmen. Auch ein 2. dedizierter Server als Backup und Rescue-Server kommt nicht in Frage aus Kosten- und Geschwindigkeitsgründen (wenn aus ausgelasteten 100Mbit noch der Traffic für nen Repli-Server dazu kommt, wirds eng)

wie funktioniert das backup mit dem kommerziellen innodb hot backup programm? was machen die anders, dass sie ein online backup zur verfügung stellen können? das muss doch auch mit "bord-mitteln" funktionieren...?

ciao

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: sicherung, welche variante ist besser ?

Post by isotopp » 2006-11-12 11:28

stanglwirt wrote:Welche Vor- und Nachteile gibt es denn bei der LVM-Variante? Wie funktioniert das überhaupt, dass ein Snapshot, der die Daten zu einem bestimmten Zeitpunkt beinhaltet, in so kurzer Zeit gezogen werden kann?
http://mysqldump.azundris.com/archives/ ... f-LVM.html

Ein LVM Snapshot ist eine Copy-On-Write Kopie des originalen Volumes. Diese kann, wie bei COW üblich, in Nullzeit (Kopieren der Descriptortabellen) angefertigt werden. Das erste Write in einen jeden Block des Originals macht aus einer logischen Operation (Write neuer Block) drei physikalische Operationen (Read Old Block, Write Old Block to Snapshot, Write New Block to Original). Weitere Writes desselben Blocks im Original sind unbeeinträchtigt.
kann man bei der variante nicht so vorgehen, dass man die binär-dateien in lvm sichert, dann einen zweiten mysql-server aufmacht der darauf zugreift und von dort dann den dump ziehen?
Um einen mysqld mit MyISAM-Tabellen zu starten ist es ausreichend eine Partition r/o vorliegen zu haben. InnoDB braucht r/w Zugriff. Mit LVM ist ein Snapshop r/o, mit LVM2 kann ein Snapshot auch beschrieben werden.
um konsitente daten zu haben, muss ich die applikation stoppen während lvm das snapshot erzeugt? ich denke ja...
Mit MyISAM ist es ausreichend, FLUSH TABLES WITH READ LOCK zu machen. InnoDB braucht den Shutdown des Servers während des Snapshot.

Beachte, daß ein FLUSH TABLES WITH READ LOCK alle Tabellen mit Share Locks sperrt, bis entweder ein UNLOCK TABLES gemacht wird oder die Verbindung geschlossen wird. "mysql -u root -e 'FLUSH TABLES WITH READ LOCK'" funktioniert also nicht.
welche vor- und nachteile gibt es bei der Slave-Variante? Vorteil ist sicher, dass es ziemlich bequem ist. Als großen Nachteil sehe ich die große Resourcen-Verschwendung wenn das Teil "nur" zum backupen ständig mitläuft.
Ein Slave führt die Anweisungen aus dem Binlog im SQL_THREAD sequentiell aus. Es ist also nutzlos, im Slave mehr als eine CPU zu haben, wenn es sich um einen reinen Backup Slave handelt. Der übrige Ressourcenverbraucht im Slave entspricht dem einer Staging Area eines Backup-Servers für Fileserver. Für eine schnelle Recovery ist ein Slave ideal ("Niemand will Backup, alle brauchen Restore").
Ich habe soviele Writes in die Datenbank dass es mir unmöglich erscheint, einen 2. server laufen zu lassen.
Besorge Dir ein MSA 30 oder vergleichbar, oder einen Escalade xx12 oder so was. Writes kann man nur mit mehr Platten skalieren. Mehr Platten, nicht größere Platten.
wie funktioniert das backup mit dem kommerziellen innodb hot backup programm?
Genauso wie ein Backup einer InnoDB-Datenbank mit "mysqldump --single-transaction --master-data": Es wird ein Repeatable-Read Snapshot in der Datenbank erzeugt, der für die Dauer des Backups gehalten wird. Dabei wächst das Undo-Log der Datenbank um alle Writes aufzunehmen, die während der Dauer des Backups erfolgen. Nach dem Ende des Backups wird der InnoDB Purge Thread das Undo-Log kürzen.

Im Gegensatz zu "mysqldump --single-transaction", das ein logisches Backup erzeugt, generiert ibbackup ein physikalisches Backup.

stanglwirt
Posts: 48
Joined: 2006-01-10 14:44

Re: sicherung, welche variante ist besser ?

Post by stanglwirt » 2006-11-13 11:23

super. danke für die ausführungen.