In laufende DBLog Datenbank (Mariadb), vorhandene Sicherung (sql-Dump) einlesen

[Rewe2000]

Hallo,

ich stehe derzeit etwas auf dem Schlauch und es breitet sich langsam Nervosität aus.
Ich hatte diese Woche Probleme mit eine Speicherkarte am Raspi3, deshalb musste ich Fhem mit der DBLog Datenbank auf eine neue SD-Card "umziehen". Der Umzug hat, wegen regelmäßiger Backups ganz gut geklappt, lediglich mit den Daten der SQL-Datenbank habe ich noch Probleme. Ich erstelle regelmäßige Backups mit "set DBReport dumpMySQL clientSide", deshalb habe ich tägliche sql-Dumpfiles vorliegen.

Wie ist nun der korrekte Weg, ein vorhandenes sql-Dumpfile in eine laufende sql-Datenbank zu integrieren?
Da ich kein Testsystem habe, kann ich mir keine großartigen Experimente erlauben, da ich dann eventuell noch mehr Daten verliere.
Auch sollte während des Einlesens der Daten, Fhem noch mit vernünftiger Geschwindigkeit und ohne längere Freezes weiterlaufen.

Ich wollte eine "Lücke" in der Datenbank (ca. 3 Monate) mit Daten vom sql-Dump wieder befüllen

Verstehe ich das richtig.
Wenn ich über DBRep ein Restore ausführe, wird meine aktuelle Datenbank komplett mit dem sql-Dump überschrieben und die Datenbank enthält nur noch die Daten vom Dump, die aktuellen Daten würden überschrieben.
Welche Vorgehensweise kann ich jetzt noch verwenden um die fehlenden Daten vom Backup zu verwenden?

Wäre es der richtige Weg gewesen, sofort nach den Umzug auf die neue Karte ein Restore vom sql-Dump auszuführen?
Wie macht ihr das in so einem Fall, oder hattet ihr noch nie solche Probleme.

Als SQL-Werkzeug habe ich HeidiSQL unter Windows installiert, meine SQL Kenntnisse sind allerdings nicht sehr ausgeprägt.
Gerne freue ich mich auf Links, wo ich mich zu dieser Thematik einlesen kann, habe leider selbst nichts passendes in der Hektik gefunden.

Schön wäre ein SQL-Befehl, welcher aus einem Backup alle Datensätze in einem anzugebenden Zeitraum in die aktuelle Tabelle schreibt und dies, ohne Fhem lahm zu legen.

Gruß Reinhard

[DS_Starter]

Hallo Reinhard,

Verstehe ich das richtig.
Wenn ich über DBRep ein Restore ausführe, wird meine aktuelle Datenbank komplett mit dem sql-Dump überschrieben und die Datenbank enthält nur noch die Daten vom Dump, die aktuellen Daten würden überschrieben.

Ja, das ist richtig.

Wäre es der richtige Weg gewesen, sofort nach den Umzug auf die neue Karte ein Restore vom sql-Dump auszuführen?

Das wäre am einfachsten gewesen.

Welche Vorgehensweise kann ich jetzt noch verwenden um die fehlenden Daten vom Backup zu verwenden?

Wie immer gibt es verschiedene Wege mit unterschiedlichem Aufwand.
Ich würde dir folgendes vorschlagen:

1. definiere dir eine neu Datenbank parallel zu deiner produktiven DB. Die soll nicht loggen, nur für die Datenübertragung.
    Also anlegen der DB, des Index und der Tabellen so wie immer. Sagen wir die heißt "oldlog".

2. Definiere dir dann ein DBlog-Device dafür. Achte darauf das nichts geloggt wird. Dazu im DEF einfach den Regex aaaaa:bbbbb setzen.
   Das sollte reichen. Stelle den asynch-Mode ein.

3. Dazu nun ein neues DbRep-Device welches sich damit verbindet. Das kennst du ja.

4. Jetzt kannst du den Restore in die neue DB machen. Damit das DbRep das File findet musst du die Attribute richtig setzen. Kannst du
   aus deinem
    DbRep fürs Backup übernehmen. Das Sicherungsfile musst du auch umbenennen, oder besser kopieren. Der alte DB-Name muss durch
   "oldlog" ersetzt werden. Dann restore mit:
    "set <DbRep> restoreMySQL <Filename>
    -> Vorgehensweise nur für SERVERSIDE-BACKUPS !

5. Ist der Restore erledigt, kannst du in aller Ruhe die benötigten Daten aus der oldlog-DB in deine produktive DB per "exportToFile" und
    "ImportFromFile" übertragen. Du kannst z.B. monatsweise vorgehen indem du die timestamp Attribute entsprechnd setzt, oder
     Device/Readings überträgst.

Dazu habe ich mal einen Wiki-Beitrag geschrieben: https://wiki.fhem.de/wiki/DbRep_-_Reporting_und_Management_von_DbLog-Datenbankinhalten#Datens.C3.A4tze_.28Devices.29_von_einer_Datenbank_in_eine_andere_umziehen_.28Export.2FImport.29

Noch einfacher als Export/Import ist es, beim Punkt 5 die DbRep-Funktion "syncStandby" zu verwenden. Dann werden alle Daten aus deiner oldlog-DB in die produktive DB "on the fly" übertragen. Du musst nur im DbRep deine Attribute "timestamp_begin", "timestamp_end" richtig setzen damit nur die gewünschten 3 Monate übertragen werden.

Die DbLog-Devices auf jeden Fall asynchron betreiben, damit alles schön weiterläuft.

Grüße
Heiko

[Rewe2000]

Hallo Heiko,

zunächst mal vielen Dank für deine ausführliche Anleitung.
Ich wollte nun auf einer anderen Speicherkarte das SQL-Restore unter DBRep testen, um in Zukunft für ähnliche Fälle gewappnet zu sein.
Ich habe aber Probleme, ein mit DBRep erstelltes Dump in eine laufende DBLog Datenbank als Restore zurückzuspielen.

Bei mir kommt keine Dropdown Auswahlliste, der (bei mir) 3 verfügbaren SQL-Dump Dateien. Gebe ich den Dateinamen in der Fhem Statuszeile direkt ein, so kommt folgende Fehlermeldung.

Code Auswählen Erweitern

2018.06.30 16:35:15 3: DbRep DBReport - Starting restore of database 'fhem', table 'history'.
2018.06.30 16:35:15 2: DbRep DBReport - DBD::mysql::st execute failed: Access denied for user 'Reinhard'@'%' (using password: YES) at ./FHEM/93_DbRep.pm line 6972.
Muss ich da noch etwas bezüglich der Benutzerrechte/Passwort einstellen, damit ich das Restore wieder zurückspielen kann?
Als user ist bei mir "Reinhard" eingestellt, eventuell wird das Restore ja unter user "Fhem" getätigt?
Sollte dies der Grund sein, so wundert mich nur, weshalb das Backup Fehlerfrei ausgeführt wird.

Die sql-Dump Dateien liegen im gleichen Verzeichnis, wo auch durch DBRep die Dumps abgelegt werden, der Pfad ist auch mit dem Eintrag unter dumpDirLocal identisch. Die Rechte der eingespielten sql-Dump Dateien sollten eigentlich auch passen (siehe Anhang).

Ich hänge das List der beiden Module noch mit an.

Code Auswählen Erweitern

Internals:
   DATABASE   fhem
   DEF        DBLogging
   LASTCMD    restoreMySQL fhem_2018_06_28_03_05.sql
   MODEL      Client
   NAME       DBReport
   NOTIFYDEV  global,DBReport
   NR         273
   NTFY_ORDER 50-DBReport
   ROLE       Client
   STATE      error
   TYPE       DbRep
   UTF8       1
   VERSION    7.18.1
   HELPER:
     DBLOGDEVICE DBLogging
     MINTS      2016-11-03 18:17:51
   READINGS:
     2018-06-30 16:35:15   errortext       DBD::mysql::st execute failed: Access denied for user 'Reinhard'@'%' (using password: YES) at ./FHEM/93_DbRep.pm line 6972.

     2018-06-30 16:35:15   state           error
   dbloghash:
     COLUMNS    field length used for Device: 64, Type: 64, Event: 512, Reading: 64, Value: 128, Unit: 32
     CONFIGURATION /opt/fhem/contrib/dblog/db.conf
     DEF        /opt/fhem/contrib/dblog/db.conf .*:.*
     MODE       asynchronous
     MODEL      MYSQL
     NAME       DBLogging
     NR         163
     NTFY_ORDER 50-DBLogging
     PID        25893
     REGEXP     .*:.*
     STATE      connected
     TYPE       DbLog
     UTF8       1
     VERSION    3.10.9
     dbconn     mysql:database=fhem;host=localhost;port=3306
     dbuser     Reinhard
     HELPER:
       COLSET     1
       DEVICECOL  64
       EVENTCOL   512
       OLDSTATE   connected
       READINGCOL 64
       TYPECOL    64
       UNITCOL    32
       VALUECOL   128
       REDUCELOG:
         DBLogging
         reduceLogNbl
         30
         average
     READINGS:
       2018-06-30 16:58:33   CacheUsage      0
       2018-06-30 16:58:33   NextSync        2018-06-30 16:59:03 or if CacheUsage 500 reached
       2018-01-20 19:32:06   background_processing_time 0.2007
       2017-01-04 15:51:17   countCurrent    167
       2017-01-04 15:51:17   countHistory    6148
       2018-06-30 02:08:00   reduceLogState  reduceLogNbl finished. Rows processed: 527856, deleted: 0, updated: 6, time: 267.64sec
       2018-01-20 19:32:06   sql_processing_time 0.1572
       2018-06-30 16:58:33   state           connected
     cache:
       index      7742
Attributes:
   DbLogExclude .*
   dumpDirLocal /opt/fhem/backup
   dumpFilesKeep 8
   optimizeTablesBeforeDump 1
   room       Logging
   verbose    2

Code Auswählen Erweitern

Internals:
   COLUMNS    field length used for Device: 64, Type: 64, Event: 512, Reading: 64, Value: 128, Unit: 32
   CONFIGURATION /opt/fhem/contrib/dblog/db.conf
   DEF        /opt/fhem/contrib/dblog/db.conf .*:.*
   MODE       asynchronous
   MODEL      MYSQL
   NAME       DBLogging
   NR         163
   NTFY_ORDER 50-DBLogging
   PID        25893
   REGEXP     .*:.*
   STATE      connected
   TYPE       DbLog
   UTF8       1
   VERSION    3.10.9
   dbconn     mysql:database=fhem;host=localhost;port=3306
   dbuser     Reinhard
   HELPER:
     COLSET     1
     DEVICECOL  64
     EVENTCOL   512
     OLDSTATE   connected
     READINGCOL 64
     TYPECOL    64
     UNITCOL    32
     VALUECOL   128
     REDUCELOG:
       DBLogging
       reduceLogNbl
       30
       average
   READINGS:
     2018-06-30 17:00:28   CacheUsage      1
     2018-06-30 17:00:03   NextSync        2018-06-30 17:00:33 or if CacheUsage 500 reached
     2018-01-20 19:32:06   background_processing_time 0.2007
     2017-01-04 15:51:17   countCurrent    167
     2017-01-04 15:51:17   countHistory    6148
     2018-06-30 02:08:00   reduceLogState  reduceLogNbl finished. Rows processed: 527856, deleted: 0, updated: 6, time: 267.64sec
     2018-01-20 19:32:06   sql_processing_time 0.1572
     2018-06-30 17:00:03   state           connected
   cache:
     index      7744
Attributes:
   DbLogExclude .*
   DbLogSelectionMode Exclude/Include
   DbLogType  Current/History
   asyncMode  1
   room       Logging
   shutdownWait 4
   verbose    2

[DS_Starter]

Hallo Reinhard,

Muss ich da noch etwas bezüglich der Benutzerrechte/Passwort einstellen, damit ich das Restore wieder zurückspielen kann?
Als user ist bei mir "Reinhard" eingestellt, eventuell wird das Restore ja unter user "Fhem" getätigt?

Sorry, das war mein Fehler, habe dir was falsches geschrieben. Der integrierte Restore funktioniert nur mit serverside-Backups.
Bei clientside-Backups muss man leider selbst Hand anlegen.
Habe ich sogar in der Commandref geschrieben    :

Code Auswählen Erweitern

Das erzeugte Dumpfile kann z.B. mit:

    mysql -u <user> -p <dbname> < <filename>.sql

auf dem MySQL-Server ausgeführt werden um die Datenbank aus dem Dump wiederherzustellen.

Das heißt du machst das Restore in deine neue DB auf der Kommandoebene deines MySQL-Serverrechners.
Ist das Restore drin, machst du weiter wie von mir beschrieben.

Grüße
Heiko

[Rewe2000]

Hallo Heiko,

danke nochmals für die Tipps und die Hilfestellung, bei mir ist die Datenbank nun wieder komplett, dank deinem tollen Modul.

Was ich daraus gelernt habe, ich werde zukünftig sofort bei Problemen ein Restore der vorhandenen Sicherung zurückspielen und nicht mehr so lange warten wie in der Vergangenheit.

Gruß Reinhard

[DS_Starter]

Freut mich Reinhard 
Wenn ich mich wieder mit DbRep beschäftige, will ich dem Modul noch die integrierte Restoremöglichkeit für Clientside-Backups spendieren.
Steht auf meiner ToDo.

Grüße
Heiko

[Rewe2000]

Hallo Heiko,

ich habe da noch einige Verständnisfragen, damit ich es beim nächsten Mal nicht falsch mache.

Situation:
fhem = aktuelle SQL-Datenbank
old_fhem = alte Backup SQL-Datenbank

DBLog = aktuelle SQL-Logdatei
old_DBLog = alte Sicherungsdatei

DBRep = aktuelle Reportdatei
old_DBRep = Reportdatei der alten Sicherungsdatei

Vor dem define des old_DBLog Moduls, muss ich doch noch zwingend eine weitere db.conf Datei (anderer Name natürlich) anlegen, welche die old_fhem SQL-Datei einstellt. Das hat bei mir so prima geklappt.

Ich hatte die Eingrenzung mit den "timestamp_begin" und "timestamp_end" Attributen in beiden DBRep Modulen gesetzt. Ich denke aber es sollte ausreichen, diese nur in dem einen DBRep Modul zu setzen, in welchem auch "syncStandby" ausgeführt wird. Nach dem erfolgten Übertrag habe ich diese Attribute wieder gelöscht.

Damit ich mein fhem nicht unnötig belaste, habe ich die beiden nicht mehr benötigten Module old_DBLog und old_DBRep auf attr auf disable gesetzt. Ich denke auch dies sollte so ausreichen und ich muss diese nicht wieder aus fhem entfernen. Wahrscheinlich wird dies nicht das letzte Mal gewesen sein dass ich diese benötige.

Die old_fhem SQL-Datenbank, welche ich zusätzlich zur "Standard" SQL-fhem Datenbank angelegt hatte kann ich auch sicherlich belassen, da diese ja nicht beschrieben wird. Es wird nicht schaden wenn ich diese wieder leere.

Sind wahrscheinlich blöde Fragen für SQL und Fhem Profis, aber einen einfachen User wie mich, beschäftigen solche Fragen schon.
Ich habe die Commandref und dein wirklich sehr gut und verständlich geschriebenes Wiki mehrmals gelesen, ich konnte mir aber diese Fragen damit nicht beantworten.

Danke Heiko für deine Geduld
Gruß Reinhard

[DS_Starter]

Hallo Reinhard,

Sind wahrscheinlich blöde Fragen für SQL und Fhem Profis, aber einen einfachen User wie mich, beschäftigen solche Fragen schon.

Manchmal gibts schon blöde Fragen, aber deine sind es ganz bestimmt nicht.
Es geht mir ja selber so dass ich nach ein paar Wochen oder gar Monaten nicht mehr so recht weiß wie ich etwas im Modul umgesetzt habe, was mir während des Schreibens sonnenklar war. Eine gute Doku ist also ein Muss und immer hilfreich.

Zu Beginn würde ich aber gern ein paar Begriffe korrigieren:

DBLog = aktuelles Logdevice (statt aktuelle SQL-Logdatei)
old_DBLog = altes Logdevice zum Restore (statt alte Sicherungsdatei)

DBRep = aktuelles Reportingdevice (statt aktuelle Reportdatei)
old_DBRep = Reportingdevice zum Restore (statt Reportdatei der alten Sicherungsdatei)

Das trifft es so besser.

Ich hatte die Eingrenzung mit den "timestamp_begin" und "timestamp_end" Attributen in beiden DBRep Modulen gesetzt. Ich denke aber es sollte ausreichen, diese nur in dem einen DBRep Modul zu setzen, in welchem auch "syncStandby" ausgeführt wird.

Da hast du völlig recht.
Es reicht die timestamp-Attribute in dem selelektierenden DbRep-Device, also old_DBRep, zu setzen. Man muss sich einfach nur vor Augen halten dass es immer die Quelle ist, aus der selektiert wird, um in das Ziel, in dem Fall DBLog bzw. der dazu gehörigen Datenbank zu schreiben. Die empfangende DB nimmt alles entgegen was sie bekommt und schaut nach keinem Datum.
Also die Datenbank ist mit dem DbLog-Device assoziiert (über die db.conf), und das DbRep-Device über das DbLog-Device (angegeben im DEF) ist ebenfalls mit dieser Datenbank assoziiert.

Damit ich mein fhem nicht unnötig belaste, habe ich die beiden nicht mehr benötigten Module old_DBLog und old_DBRep auf attr auf disable gesetzt. Ich denke auch dies sollte so ausreichen und ich muss diese nicht wieder aus fhem entfernen.

Das reicht völlig aus, bzw. sofern du z.B. in deinem DbLog das Attr

Code Auswählen Erweitern

excludeDevs = .*

setzt, wird generell nichts mit diesem DbLog-Device geloggt und belastet ebenfalls nicht. DbRep brauchst du nicht disablen. Solange du nichts startest, tut DbRep im System überhaupt nichts und ist einfach nur vorhanden.
Ich würde die Definitionen auch nicht löschen und würde sie eventuell benutzen um ein bisschen zu üben. Du kannst dir DB auch mal kaputt machen und wieder neu aufbauen usw.  Das schafft einfach etwas mehr Sicherheit und Vertrauen in die eigenen Fähigkeiten.

Die old_fhem SQL-Datenbank, welche ich zusätzlich zur "Standard" SQL-fhem Datenbank angelegt hatte kann ich auch sicherlich belassen, da diese ja nicht beschrieben wird. Es wird nicht schaden wenn ich diese wieder leere.

Genau ... siehe oben.

Also wenn du noch Fragen hast, kannst du die gerne stellen und ich beantworte sie solange ich kann.
Ich beginne aber in Kürze einen Urlaub 

LG
Heiko