Zusätzliche Backups mit dem Binary Log (mysqlbinlog)

[select name from me;]

Hallo zusammen,

ich würde gerne in Zukunft alle paar Stunden die Binary Logs der MariaDB sichern, damit ich zusätzlich zu den normalen umfangreichen Backups den Zeitraum eines potentiellen Datenverlusts reduzieren kann.

Zudem kann man damit beliebige Zwischenstände reproduzieren.

Hat jemand von euch damit Erfahrungen?

[Jolinar]

Hat jemand von euch damit Erfahrungen?

Erfahrungen womit? Dem Anlegen einer Backupstrategie oder dem Verarbeiten von Binlog-Files?

ich würde gerne in Zukunft alle paar Stunden die Binary Logs der MariaDB sichern, damit ich zusätzlich zu den normalen umfangreichen Backups den Zeitraum eines potentiellen Datenverlusts reduzieren kann.

Ich frage mich gerade, welche Intention du verfolgst...
Warum verkürzt du nicht einfach das Backupintervall des DB-Backups? Denn vom Ergebnis her macht es doch keinen Unterschied, ob du z.B. stündlich ein Backup der Datenbanken machst oder einmal täglich ein DB-Backup und stündlich ein Binlog-Backup, welche du dann im Fall der Fälle in das DB-Backup einspielst.
Wenn du mittels der Binlog-Files Datenverlust vorbeugen willst, dann müßtest du schon eine Master/Slave Replikation etablieren, damit die Änderungen der Master-DB (fast) in Echtzeit in der Slave-DB "gesichert" werden würden.

[select name from me;]

Hat jemand von euch damit Erfahrungen?

Erfahrungen womit? Dem Anlegen einer Backupstrategie oder dem Verarbeiten von Binlog-Files?

Mit dem Verarbeiten der Binlog-Files.

Ich frage mich gerade, welche Intention du verfolgst...
Warum verkürzt du nicht einfach das Backupintervall des DB-Backups? Denn vom Ergebnis her macht es doch keinen Unterschied, ob du z.B. stündlich ein Backup der Datenbanken machst oder einmal täglich ein DB-Backup und stündlich ein Binlog-Backup, welche du dann im Fall der Fälle in das DB-Backup einspielst.

Gute Frage. Ich hätte das etwas ausführlicher beschreiben sollen.

Die Datenbanken in einem konsistenten Zustand zu sichern erfordert einen Write Lock und ist tagsüber Last, die ich gerne vermeiden würde. Die Binary Logs liegen ja schon auf dem Server und müssen nur noch kopiert werden.

Der - imho - große Vorteil der Binary Logs ist, dass man auch selektiv auf Daten zugreifen kann. Wenn z.B. ein Datensatz zwischen zwei Sicherungen angelegt und gelöscht/geändert wurde, wäre das mit den Binary Logs wieder herzustellen.

In unserem Fall könnten wir ziemlich viele Fehler der Anwender sehr einfach korrigieren.

Wenn du mittels der Binlog-Files Datenverlust vorbeugen willst, dann müßtest du schon eine Master/Slave Replikation etablieren, damit die Änderungen der Master-DB (fast) in Echtzeit in der Slave-DB "gesichert" werden würden.

Master/Slave ist gut gegen einen Ausfall der Hardware. Nützt aber leider nicht, wenn ein Anwender (oder Entwickler ) Fehler macht.

[Jolinar]

Vorneweg (nur meine private Meinung^^):

Nützt aber leider nicht, wenn ein Anwender (oder Entwickler ) Fehler macht.

Anwender sollten sich gefälligst selber um die Sicherheit ihrer Daten kümmern und Entwickler haben nichts an produktiven Datenbanken verloren, dafür gibt es Testdatenbanken.

Aber unabhängig davon:

Mit dem Verarbeiten der Binlog-Files.

Ich bin noch nicht selber in die Verlegenheit gekommen, Daten selektiv aus den Binlog-Files zu ziehen. Ich bin bisher auch nur während der Ausbildung mal mit dem Thema in Bezug auf Replikationen in Kontakt gekommen.
Wenn ich dein Vorhaben umsetzen sollte, würde ich vermutlich ein Script mit folgenden Funktionen schreiben:

• Auswerten, in welches Binlog-File gerade geschrieben wird (müßte über den MySQL-Query show master status; gehen)
• Wenn das nächste Binlog-File angelegt wird, das letzte Binlog-File mit mysqlbinlog in ein *.sql-File umwandeln
• Das *.sql-File zippen und wegsichern
• Das eben verarbeitete Binlog-File löschen

Dann hast du alle durchgeführten SQL-Queries in den gesicherten Files und kannst bei Bedarf darauf zurückgreifen.
Ist aber jetzt nur ein Denkspiel meinerseits, vermutlich gibt es da sogar schon fertige Lösungen für.

[select name from me;]

Vorneweg (nur meine private Meinung^^):

Nützt aber leider nicht, wenn ein Anwender (oder Entwickler ) Fehler macht.

Anwender sollten sich gefälligst selber um die Sicherheit ihrer Daten kümmern und Entwickler haben nichts an produktiven Datenbanken verloren, dafür gibt es Testdatenbanken.

Danke für Deine Meinung.

Fehler der Entwickler schlagen leider manchmal auch in Produktivsysteme durch.
Wir versuchen uns nicht nur gegen einen Crash zu sichern, denn wenn wir den Fehler eines Anwenders korrigieren können, bindet das den Kunden an uns und verhindert im schlimmsten Fall eine Kündigung, weil der Kunde ohne Daten den Dienst nicht mehr benötigt.

Wenn ich dein Vorhaben umsetzen sollte, würde ich vermutlich ein Script mit folgenden Funktionen schreiben:...

Gute Idee. Danke für den Ansatz.

Mein Plan bisher: Alle Binary Logs in ein anderes Verzeichnis rsyncen und dann auf einen anderen Server.

Man kann aus den gesamten Binary Logs mit --start-datetime sehr gut alle SQL Befehle ab dem Backup Zeitpunkt, oder einem anderen Zeitpunkt, exportieren.

[Jolinar]

Wir versuchen uns nicht nur gegen einen Crash zu sichern, denn wenn wir den Fehler eines Anwenders korrigieren können, bindet das den Kunden an uns und verhindert im schlimmsten Fall eine Kündigung, weil der Kunde ohne Daten den Dienst nicht mehr benötigt.

Kundenbindung ist natürlich ein Argument. Allerdings hab ich bei dem Gedanken, daß der Anbieter selektiv Kundendaten verändert, irgendwie ein ungutes Gefühl. Aber vielleicht denke ich auch einfach zu paranoid

Man kann aus den gesamten Binary Logs mit --start-datetime sehr gut alle SQL Befehle ab dem Backup Zeitpunkt, oder einem anderen Zeitpunkt, exportieren.

Mein Gedanke, das Binlog in ein sql-File zu konvertieren, hatte eigentlich zwei Gründe. Zum Einen hat man dann ein lesbares Format und könnte so jeden Query visuell auf mögliche Fehler prüfen. Zum Anderen bin ich davon ausgegangen, daß Textfiles (und nix anderes ist ja ein sql-File) meist besser komprimiert werden können als Binärfiles...Und der Platzverbrauch für die Binlogs auf einem Produktivsystem ist gerade bei vielen DB-Zugriffen nicht zu vernachlässigen. Da kommen schnell mal mehrere GB am Tag zusammen.

[select name from me;]

Vielen Dank, dass Du Deine Gedanken dazu teilst. Das hilft mir.

Kundenbindung ist natürlich ein Argument. Allerdings hab ich bei dem Gedanken, daß der Anbieter selektiv Kundendaten verändert, irgendwie ein ungutes Gefühl. Aber vielleicht denke ich auch einfach zu paranoid

Ja, kann ich verstehen.

Kommt natürlich auf das Verhältnis und die Art des Auftrags an. Bei uns rufen Kunden immer mal wieder an, wenn sie sich trotz Warnung verklickt haben. Kann man leider nie ausschließen.

Zum Einen hat man dann ein lesbares Format und könnte so jeden Query visuell auf mögliche Fehler prüfen. Zum Anderen bin ich davon ausgegangen, daß Textfiles (und nix anderes ist ja ein sql-File) meist besser komprimiert werden können als Binärfiles...

Ja, das könnte sein. Ich habe die Binary Logs mal direkt geöffnet. Die SQL Queries stehen direkt als Text drin. Keine Binäre Codierung oder so.

Und mit mysqlbinlog lässt sich das auch flott wandeln.

Und der Platzverbrauch für die Binlogs auf einem Produktivsystem ist gerade bei vielen DB-Zugriffen nicht zu vernachlässigen. Da kommen schnell mal mehrere GB am Tag zusammen.

Bei den Keyweb Servern die wir haben, ist das Binary Log im Auslieferungszustand immer aktiv.
Die Files rotieren. Über den Parameter expire_logs_days in der my.cnf lässt sich das einstellen. Wir haben dort 10 Tage und auf dem Server mit der meisten "Last" sind es nur 1,5 GiB. Komprimiert 168 MiB.

Da ja nur Queries eingetragen werden die Daten ändern, scheint das doch relativ wenig zu sein.

Irgendwo muss da doch der Haken sein.

[Jolinar]

Wir haben dort 10 Tage und auf dem Server mit der meisten "Last" sind es nur 1,5 GiB. Komprimiert 168 MiB.

Hmm...okay, da hätte ich jetzt größere Werte vermutet. Aber da fehlen mir wohl tatsächlich die praktischen Erfahrungen.
Ich war vor ein paar Jahren mal als Co-Admin mit zuständig für einen DB-Cluster mit Master/Master-Replikation aus dem Bereich Warenwirtschaft. Da hatten wir täglich ca. 40-50 GB an Binlogs. Aber da wurde eben auch exzessiv in der DB geschrieben...

[select name from me;]

Wow.