[MySQL] Herstellen verwijderde data

[gavro]

Het is iedereen wel eens gebeurd: er moet verwijderde data hersteld worden uit een MySQL database.

Nu zou je denken: gebruik backups! Helaas genoeg zijn de backups die er zijn onbruikbaar. Backups werden als tar gepiped (mysqlhotcopy van alle db's voordat er getarred/gepiped wordt) via ftp naar een externe server die blijkbaar voor mijn account vol was (quota)... al een hele poos ... waardoor de backup cronjobs nooit volledig afgemaakt werden en de pipe afgebroken werd voordat actie al aagekomen was bij de MySQL db's.

Details over de server en de database:
CentOS 4.6, MySQL 5.0.27, db storage type: MyISAM, db grootte < 1MB.
Nu draait de database op een productieserver waar geen binary logs worden weggeschreven, dus helaas kan ik de dabase niet reconstrueren met behulp van dit soort logbestanden.

Verder heb ik ook een tool gevonden voor MySQL InnoDB databases: http://code.google.com/p/innodb-tools/
Deze tools kunnen behoorlijk wat data terugkrijgen ... wanneer je dus InnoDP gebruikt. Helaas kwam ik later erachter dat de gebruikte storage engine MyISAM was en geen InnoDB....

Heeft iemand nog opties voor mij? Is het uberhaupt mogelijk om verwijderde records terug te halen? Zouden deze records/tabellen nog misschien als een soort 'residu' aanwezig blijven in de overhead van de DB en zoja: hoe krijg je dit dan terug?

[b12e]

Bij ons op school zegt onze lerares databanken "als het weg is, dan is het weg". De methoden die je net omschreef, ik heb ze ook allemaal getest toen ik m'n data kwijt was. Uiteindelijk was alles weg vanwege een typfout, typfout herstelt in hup! back.

In jouw geval, denk ik dat het eigenlijk hopeloos is.

edit: ik geef je 1 hint: werk in een backupdatabase, zodat je de normale niet aanraakt tijdens het testen. Veel veiliger

[ Voor 17% gewijzigd door b12e op 10-08-2009 00:21 ]

[Remus]

Ik heb geen idee of je iets kan herstellen, maar ik verwacht eerlijkgezegd niet.

Ik hoop wel dat je hier van hebt geleerd dat het noodzakelijk is om te controleren of je backup strategie werkt, en dat je ook met enige regelmaat controleert of je terug kunt vallen op een backup (dus of een restore überhaupt werkt).

[cariolive23]

Tja, met MyISAM kun je toch al geen betrouwbare database maken, wanneer je daarvoor kiest, heb je blijkbaar geen waardevolle data. MyISAM controleert helemaal niets, kent geen transacties en kan dus nooit vaststellen of data nu wel of niet correct is opgeslagen. Data later terughalen is dan ook onmogelijk, er is geen oude data.

Tip: Verwijder nooit data, geef de data gewoon een andere status: verwijderd. Trek dus van alle databaserollen de rechten op DELETE en TRUNCATE in, dan kan niemand meer de data verwijderen. Alleen de superuser zou dat dan nog kunnen doen, maar ik mag toch hopen dat hij/zij voldoende kennis heeft om te weten wat hij/zij uitspookt. Zorg wel voor voldoende schijfruimte en controleer dit regelmatig, MySQL heeft er de nodige problemen mee wanneer er onvoldoende ruimte is. Daarmee kun je nog meer schade aanrichten....

En ja, backupprocedures werken vrijwel nooit en daar kom je pas achter op het moment dat je ze nodig hebt. Er zijn maar weinig bedrijven/organisaties die regelmatig de backups en backupprocedures testen.

[gavro]

Hmm, bedankt voor de replies... Ik vrees dat de data onherstelbaar weg is. Apart dat MyISAM nog standaard gebruikt wordt binnen MySQL tenzij je dat zelf anders aangeeft, terwijl InnoDB naar mijn idee toch stukken robuuster is...

Backups, backups, backups; je hebt er blijkbaar niet zo veel aan indien je niet 'on top of it' bent! Damnit, beter bijhouden en blijven controleren

Zat er ook aan te denken om even wat LVM truukjes uit te halen, maar ik vrees dat dat ook nergens op uit zou draaien

[r0b]

Hmm, daar zeg je wat, zou je geen filesystem restore uit kunnen voeren? Voorwaarde is dan wel dat die server gewoon *niet* meer gebruikt wordt en dat je de schijf aan een andere pc / server kan knopen, maar mijn ervaring daarmee (maar dan niet met een MySQL db) is dat - indien de blocks nog niet overschreven zijn - je vaak wel 100% terughaalt

edit:

Oh, shit, dat gaat natuurlijk niet op voor ext3.. http://www.xs4all.nl/~carlo17/howto/undelete_ext3.html

gavro schreef op zondag 09 augustus 2009 @ 22:09:
Het is iedereen wel eens gebeurd: er moet verwijderde data hersteld worden uit een MySQL database.

Nou nee, eigenlijk niet.

[ Voor 61% gewijzigd door r0b op 10-08-2009 21:52 ]

[Verwijderd]

Ik denk ook dat je geen data terug gaat krijgen.

Maar de server waar het op was, is die in eigen beheer? Zo ja, waarom krijg je geen mail oid op het moment dat een backup mislukt?

Zo nee, zoek een andere host..

/me is ooit eens een filmdatabase met zo'n 1200 films, gesorteerd in koffers, kwijt door een blunder van de hosting provider..

[gavro]

Server is in eigen beheer, backup server niet. Backup wordt mbv lftp (en een achterliggend script) gedaan en de backup gaat in feite niet fout.. alleen de tgz werd al een tijd niet volledig weggeschreven door het volgelopen quota.... waar ik geen notificatie van heb gekregen! %^#$!*%

[Verwijderd]

gavro schreef op dinsdag 11 augustus 2009 @ 02:43:
Server is in eigen beheer, backup server niet. Backup wordt mbv lftp (en een achterliggend script) gedaan en de backup gaat in feite niet fout.. alleen de tgz werd al een tijd niet volledig weggeschreven door het volgelopen quota.... waar ik geen notificatie van heb gekregen! %^#$!*%

De backup gaat in feite wel fout, want je bent al je data kwijt.

Op zoek naar een beter gecontroleerde backup zou ik zeggen / zo aan (laten) passen dat hij een mailtje doet op het moment dat hij niet weggeschreven kan worden..

[cariolive23]

Op zoek naar een beter gecontroleerde backup zou ik zeggen / zo aan (laten) passen dat hij een mailtje doet op het moment dat hij niet weggeschreven kan worden..

En dan:
- op een slecht moment werkt je emailscriptje niet meer, merk je niks van, je krijgt toch al geen email wanneer er niks fout gaat...
- de backup loopt in het honderd, merk je ook niks van, je emailscriptje werkt al een paar maanden niet meer...
- er gaat iets heel erg fout en je hebt een backup nodig...

En ziedaar, de situatie die nu ook al speelt. Je controleert ALTIJD of de backups zijn gelukt of je maakt backups voor de grap. Niet omdat je zonodig een backup nodig hebt. Zonder controle of de backups wel zijn gelukt, sta je toch al met lege handen. Het liefste kijk je zelf even of de backups op de gewenste locatie staan, kost je niet meer dan 1 a 2 minuten per dag, of anders stuur je zowel bij een gelukte als mislukte backup een emailtje.

[alt-92]

En test een keer een restore
Leuk, een backup, maar als je 'm niet eens kunt restoren is het niet bijster zinvol.

[gavro]

Allemaal valide argumenten over de backups... draait al jaren goed, vaak genoeg getest, maar zodra je de aandacht laat slacken kan het blijkbaar direct fout gaan.

Ik zei "vaak genoeg", ik hou het niet vol om het elke week, laat staan elke dag te blijven controleren... Tis niet mijn fulltime baan en alles uitbesteden heb ik altijd mijn twijfels over, zelfs wanneer je een gerenoveerde partij kiest (en veel geld ervoor neerlegd!). Maargoed, eigen beheer zegt ook niet alles blijkbaar...

[Verwijderd]

Als je geluk hebt: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Certain data recovery operations require use of the binary log. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 6.2.2, “Using Backups for Recovery”.

Ik heb er nog nooit mee gewerkt en zal denk ik alleen voor REPAIR zijn, maar er staat zeker data in!

[ Voor 12% gewijzigd door Verwijderd op 11-08-2009 21:41 ]

[alt-92]

dat gaat er wel vanuit dat je innoDB gebruikt (binary logs is een stevige hint) - en dat was hier niet het geval.
Wellicht komt dat omdat het een upgraded 4.0 > 4.1 > 5.0 db is bijvoorbeeld?

[gavro]

Yup, binary logs had ik al gevonden; ook al genoemd in de openingspost dat binary-logs niet worden weggeschreven... Of dat door de vele upgrades afgelopen jaren op de server zo is gekomen kan ik niet zien, my.cnf en my.cnf.orig hebben geen hele oude datum, dus die kunnen al wel een paar keer overschreven zijn;
het staat nu in ieder geval weer aan

[Olaf van der Spek]

cariolive23 schreef op dinsdag 11 augustus 2009 @ 19:06:
- op een slecht moment werkt je emailscriptje niet meer, merk je niks van, je krijgt toch al geen email wanneer er niks fout gaat...

Email script?
Output van cronjobs wordt standaard gemaild, daar heb je geen custom script voor nodig.
Ik ga echt niet elke dag alle servers controleren hoor, ik heb wel betere dingen te doen.

gavro schreef op dinsdag 11 augustus 2009 @ 21:31:
Ik zei "vaak genoeg", ik hou het niet vol om het elke week, laat staan elke dag te blijven controleren...

Dat is toch ook niet nodig?
Je moet het wel zo configureren dat fouten gemeld worden (via email).

[ Voor 41% gewijzigd door Olaf van der Spek op 11-08-2009 22:00 ]

[cariolive23]

Output van cronjobs wordt standaard gemaild, daar heb je geen custom script voor nodig.

Doet niet ter zake, je zult altijd moeten controleren of de backup is gelukt. En of je dat nu doet door te in de emailbox te kijken of daar een bericht staat dat de boel is goed- of afgekeurd of dat je even in de backupfiles gaat zoeken, dat maakt niet uit. Maar zonder controle mag je aannemen dat er géén bruikbare backup is, je hebt tenslotte niets gecontroleerd. Voor veel zaken zal dat niet uitmaken, maar er zijn databases waar wel belangrijke data in staat.

En backups heb je altijd nodig op een moment dat het heel slecht uitkomt, dat is wettelijk vastgelegd: Murphy.

[Olaf van der Spek]

cariolive23 schreef op dinsdag 11 augustus 2009 @ 22:43:
[...]
Doet niet ter zake, je zult altijd moeten controleren of de backup is gelukt. En of je dat nu doet door te in de emailbox te kijken of daar een bericht staat dat de boel is goed- of afgekeurd of dat je even in de backupfiles gaat zoeken, dat maakt niet uit. Maar zonder controle mag je aannemen dat er géén bruikbare backup is, je hebt tenslotte niets gecontroleerd.

Met dat soort redenaties is een mailtje natuurlijk ook geen garantie op een succesvolle backup.

[Remus]

gavro schreef op dinsdag 11 augustus 2009 @ 21:31:
een gerenoveerde partij

offtopic:
gerenoveerd betekent niet wat jij denkt dat het betekent

[Bryan Tong Minh]

gavro schreef op dinsdag 11 augustus 2009 @ 21:31:
Allemaal valide argumenten over de backups... draait al jaren goed, vaak genoeg getest, maar zodra je de aandacht laat slacken kan het blijkbaar direct fout gaan.

Ik zei "vaak genoeg", ik hou het niet vol om het elke week, laat staan elke dag te blijven controleren... Tis niet mijn fulltime baan en alles uitbesteden heb ik altijd mijn twijfels over, zelfs wanneer je een gerenoveerde partij kiest (en veel geld ervoor neerlegd!). Maargoed, eigen beheer zegt ook niet alles blijkbaar...

Je zou bijvoorbeeld een script kunnen maken dat wekelijks een lijst van alle backups + filesize mailt. Het gaat dan in ieder geval opvallen als er veel data mist uit een bepaalde backup.

[alt-92]

gavro schreef op dinsdag 11 augustus 2009 @ 21:55:
Yup, binary logs had ik al gevonden; ook al genoemd in de openingspost dat binary-logs niet worden weggeschreven... Of dat door de vele upgrades afgelopen jaren op de server zo is gekomen kan ik niet zien, my.cnf en my.cnf.orig hebben geen hele oude datum, dus die kunnen al wel een paar keer overschreven zijn;
het staat nu in ieder geval weer aan

Gebruik je nu ook gelijk InnoDB als storage engine, en zorg je ervoor dat je zowel lokaal als remote een (gecontroleerde) backup hebt ?
Zodat je desnoods op twee lokaties een offsite backup hebt? Die ook te restoren is?
Qua performance zal het wel meevallen met een 1MB database, da's geen reden om niet een steviger storage engine te gebruiken

[gavro]

Gaan straks inderdaad gewoon over op InnoDB en zal de manier van backup weer onder handen gaan nemen; in ieder geval meer informatie verschaffing dagelijks over de gang van zaken...

Eens kijken of andere backupoplossingen wat zijn zoals virtualbackup oid.

offtopic:
erm Remus, inderdaad... gerenommeerd is wat ik zoek ;-)