MySQL Datenbank migrieren (sehr groß)

[ch.eick]

Hallo zusammen,
wer hat schon mal eine sehr große MySQL Datenbank migriert?
Das Verzeichnis vom Docker Container ist insgesamt 34G groß und beinhaltet Daten ab 10/2019.
Natürlich habe ich immer sehr stark aufgeräumt, jedoch kommt man mit den Werten einer PV-Anlage und
vielen einzel Messungen der Verbraucher dann doch mal so an die ein oder andere Grenze.

- Quelle: RPI4 mit SSD
- Ziel: RPI5 mit SSD
- MySQL 8.0.28 => 8.0.32

- Backup wurde mit SQLBackupAndFTP erstellt
  Es wurde nach Jahren unterteilt, jedoch sind die Dateien immernoch recht groß.
  Der Restore läuft leider nicht, obwohl ich das auch schon getestet hatte.

- MySQL WorkBench
  Hier wäre ein Export nur ein extrem großes File,
  was jedoch selbst das selbe Tool nicht mehr laden kann.
  Die Datenbank Struktur mit allen Tabellen und Proceduren, ohne Daten lief gut.
  Ein Run SQLFile wirft einen Windows Fehler mit Temp File in use.

  Kleine Backups vom SQLBackupAndFTP lassen sich jedoch verarbeiten.

  Die Migrations Funktionalität werde ich noch testen.

- UNIX Bord Mittel
  Akuell läuft ein tar der gestoppten MySQL Datenbank => done 2 Stunden 35,5 GB
  Das tar File wird dann auf den neuen Rechner übertragen => done 42,7 MB/s
  Dort entpacke ich dann das File => done 2,5 Minuten - ups, der RPI5 ist etwas schneller
  und werde den Docker Container damit starten => done
  Die Datenbank wirde auf die neue 8.0.32 migriert => Start mit Migration hat 14 Sekunden gedauert

Nun ist noch das Delta mit einem Backup/Restore zu migrieren und eventuell kleine Anpassungen, falls
es kritische Berechnungen gibt, zu korrigieren.
Da es eine cold Migration war sind nun leider die 2 h für das tar File verloren gegangen.

Das wir in Zunkunft sicherlich noch den ein oder anderen beschäftigen.
Ich werde das tar File jetzt mal noch liegen lassen, denn damit könnte ich ja jederzeit nocheinen
Docker Container befüllen.

Welche Tools verwendet Ihr für den Backup, oder solche Migrationen?

Gibt es noch einen Tip, wie ich FHEM am besten Starte, da dann ja Zeitweise beide Instancen die selben
Geräte im Haus bedienen würden?
Ich würde erstmal das Netzwerk vom neuen FHEM weg lassen und nach und nach die Devices checken.

Fortsetzung folgt

VG  Christian

[Prof. Dr. Peter Henning]

Hm, unter einer "sehr großen Datenbank" verstehe ich hundert Terabyte...

Aber auch bei 34 GB würd eich niemals Windows verwenden, sondern die von Dir verwendeten Linux-Tools.

LG

pah

[ch.eick]

Okay,
Für nen RPI4 eine große DB und man merkt halt, dass die genannten Tools in der kostenlosen Version schon das nicht bewältigen können. Beim RPI5 wäre ja ein cold Backup wieder okay. Auf dem alten RPI4 wären mir da die 2h downtime zuviel.
Ich denke ich fixe mal das Sqlbackupandftp für den Restore und mache noch kleinere Häppchen von 1/Monate.

Hat ansonsten noch jemand nen Tip?

VG Christian

[ch.eick]

Hm, unter einer "sehr großen Datenbank" verstehe ich hundert Terabyte...

Das ist in unseren RZs bei den Kunden auch noch klein , aber mit der Smarthome HW und im kostenlosen Bereich muss man schon tricksen.

VG Christian

[RalfRog]

Hi
Ich bin hiermit
SyncStandbx
vom Raspi in Zeitabschnitten auf Proxmox umgezogen. Im laufenden Betrieb.
Allerdings mit ner kleinen DB, ob 34GB zu handeln sind...
Gruß Ralf

[ch.eick]

Hi
Ich bin hiermit
SyncStandbx
vom Raspi in Zeitabschnitten auf Proxmox umgezogen. Im laufenden Betrieb.
Allerdings mit ner kleinen DB, ob 34GB zu handeln sind...
Gruß Ralf

Hallo Ralf,

=> schockiert
Wieso ist das schockierend? Ich habe auch noch massiv aufgeräumt und mache mir wirklich gedanken, was in die DB geht.
Bei einer PV-Anlage kommt da selbst im Minuten Takt einiges zusammen. Meine openWB ist da jedoch echt erbarmungslos, da kommen Watt mit sinnlosen 9 Stellen nach dem Komma und das teilweise im Sekunden Takt. Da bin ich aber noch dran und habe schon vieles auf null Nachkommastellen gekürzt. Ich habe nur noch nichts gefunden, um mqtt auf einen 60s Takt bei der openWB zu reduzieren
Beim Aufräumen würde ich gerne auch noch auf den Max Wert von 5 oder auch 15 Minuten nach einigen Tagen ausdünnen, aber da fehlt mir noch was im DbRep, denn ich sehe nur pro Minute oder Stunde.

[Prof. Dr. Peter Henning]

Es gibt eine sehr schöne Software, mit der ich seit ungefähr 20 Jahre arbeite. Nennt sich Navicat, es gibt eine kostenlose Testversion ohne funktionale Einschränkungen (aber nur 14 Tage Laufzeit) https://www.navicat.com/de/download/navicat-premium

Damit sollte eine Migration problemlos möglich sein.

LG

pah

[RalfRog]

Naja trifft vielleicht nicht ganz...
wieviel Datensätze sind sowas?
Vielleicht doch eher erstaunt, dass das mit nem RPI4 klappt.

[Guybrush]

34 GB ist nicht groß. Ich weiß auch nicht warum du dafür unbedingt tools nimmst. ein mysqldump --single-transaction DB | gz > db.sql.gz fuktioniert doch auch.

wenn du die datendateien unbedingt kopieren willst, dann mach das mit rsync zwischen beiden servern im Betrieb, also ohne mysqld zu beenden. Wenn das durch ist, dann beendest du mysqld auf dem alten server und machst das rsync direkt nochmal. das geht dann meist binnen sekunden. im anschluss kannst du dann den neuen mysqld starten. so hast du dann eine downtime von nur wenigen sekunden und nicht 2h...

[kadettilac89]

schau dir / schaut euch mal RBRep an, damit kannst du eine "StandbyDB" syncen. Dabei ist es möglich Kriterien (bei dir das Alter des Events) zu filtern. Quelle und Ziel bleiben online, dauert sicher länger aber man hat keine Downtime. Bevor mal endgültig auf das neue System geht macht man einen letzten Sync und gut ist. Ähnlich wie man auf Unix OS mit rscnc arbeitet.

https://fhem.de/commandref_DE.html#DbRep

[ch.eick]

34 GB ist nicht groß. Ich weiß auch nicht warum du dafür unbedingt tools nimmst. ein mysqldump --single-transaction DB | gz > db.sql.gz fuktioniert doch auch.

wenn du die datendateien unbedingt kopieren willst, dann mach das mit rsync zwischen beiden servern im Betrieb, also ohne mysqld zu beenden. Wenn das durch ist, dann beendest du mysqld auf dem alten server und machst das rsync direkt nochmal. das geht dann meist binnen sekunden. im anschluss kannst du dann den neuen mysqld starten. so hast du dann eine downtime von nur wenigen sekunden und nicht 2h...

Hallo Guybrush,

ich habe jetzt mal einen mysqldump im MySQL Workbench angestoßen.

Code Auswählen Erweitern

08:56:19 Dumping fhem (all tables)
Running: mysqldump.exe --defaults-file="C:\Users\ce\AppData\Local\Temp\tmptlvwbofu.cnf"  --host=192.168.178.40 --port=3306 --default-character-set=utf8 --user=fhemroot --protocol=tcp --column-statistics=FALSE --single-transaction=TRUE --routines --skip-triggers "fhem"
Könnte ich das direkt in dem MySQL Container starten?
- Das scheint zu gehen, bedarf jedoch noch einiger Berechtigungen.
- Leider gibt es in dem original Oracle MySQL Container nicht sonderlich viele basis Kommandos
Würde rsync dann nur das Delta der Dump Datei übertragen? Es ist doch nur eine geänderte Datei, dann müsste rsync ja einen inhaltlichen Vergleich machen.
Der mysqldump hat nun in 15 Minuten rund 8,3 GB geschrieben, da müsste ich wohl die MySQL mal wieder defragmentieren, wenn die Files in dem Verzeichnis in Summe 34 GB belegen.
Kann ich den neuen mysqldump nun einfach in die neue MySQL einlesen, obwohl dort der größte Teil der Daten bereits drin ist? Bekomme ich da nicht einen Abbruch beim INSERT wegen duplicate key?


VG  Christian

[Guybrush]

mysqldump und rsync schließen sich aus. mysqldump erstellt einen lesbaren Export mit SQL Statements. Rsync kopiert binär. und ja, mysqldump kannst du direkt in der shell ausführen. die ganzen tools die es gibt machen ja nichts anderes als mysqldump mit passenden parametern zu starten.

ich würde aber immer die mysqldump variante bevorzugen, zumal man das eh jede nacht einmal laufen lassen sollte um ein backup zu haben

und defragmentieren bringt dir nichts. das ist das thema bei innodb, dass die datenfiles immer nur wachsen und der speicher für gelöschte tabellen nicht automatisch freigegeben wird. das einfachste hilfsmittel dagegen ist die db zu löschen und neu einzuspielen. bringt aber auch nur was wenn du das setting one_file_per_table aktiviert hast. wenn du die datengröße selbst meinst heißt das nichts, da die indexe erst beim import erstellt werden und daher der dump regelmäßig kleiner ist als die fertige tabelle. backups solltest du immer in leere tabellen/dbs einspielen. da kann sonst zuviel daneben gehen und inkonsistenzen verursachen

[ch.eick]

mysqldump und rsync schließen sich aus. mysqldump erstellt einen lesbaren Export mit SQL Statements. Rsync kopiert binär. und ja, mysqldump kannst du direkt in der shell ausführen. die ganzen tools die es gibt machen ja nichts anderes als mysqldump mit passenden parametern zu starten.

ich würde aber immer die mysqldump variante bevorzugen, zumal man das eh jede nacht einmal laufen lassen sollte um ein backup zu haben

und defragmentieren bringt dir nichts. das ist das thema bei innodb, dass die datenfiles immer nur wachsen und der speicher für gelöschte tabellen nicht automatisch freigegeben wird. das einfachste hilfsmittel dagegen ist die db zu löschen und neu einzuspielen. bringt aber auch nur was wenn du das setting one_file_per_table aktiviert hast. wenn du die datengröße selbst meinst heißt das nichts, da die indexe erst beim import erstellt werden und daher der dump regelmäßig kleiner ist als die fertige tabelle. backups solltest du immer in leere tabellen/dbs einspielen. da kann sonst zuviel daneben gehen und inkonsistenzen verursachen

Puh, dass ist viel Stoff

Das mit den Inkonsistenzen sollte sich bei FHEM ja im Ramen halten, da es ein sehr flaches Datenmodel ist und ja eigentlich nur aus einzelnen Daten Zeilen besteht. Meine Frage dazu ziehlt deshalb darauf ab, was mysqldump beim Import macht. Datensätze mit identischem Key müssten ja mit einem UPDATE bearbeitet werden und fehlende Zeilen mit einem INSERT dazu kommen.
Dein Vorschlag wäre ja den Dump in eine neue Tabelle zu schreiben und später die Alte mit einem DROP zu löschen, bevor die Wiederhergestellt dann mit einem ALTER umbenannt wird.

Das mit dem initialen Laden sollte mit mysqldump dann jetzt kein Problem sein, wenn root zuvor die nötigen Berechtigungen bekommen hat.
Dann würden die innodb Files auch wieder kleiner sein. Werden dabei denn auch die User und Berechtigungen wiederhergestellt?

Wo wird one_file_per_table denn aktiviert? FHEM hat ja eh nur eine Tabelle

Bei meinem bisherigen Backup war ich davon ausgegangen, dass die erzeugten INSERT einfach wieder mit WorkBench eingespielt werden könnten, jedoch bricht das bereits beim Laden wegen der schieren Menge ab. Kleinere Häppchen lassen sich so jedoch wieder herstellen.

Hast Du mal ein mysqldump Kommande als docker Beispiel?

So eine Platform Migration hat echt einen guten Lerneffekt und man merkt seine eigene Unzulänglichkeit

VG  Christian

UPDATE: Statistiken

Code Auswählen Erweitern

Der mysqldump load hat nun auf dem RPI5 2:26 h benötigt.

Der Größenvergleich vom Transport der Dateien der alten DB über ein tar File zum Laden in eine neue MySQL Datenbank

Transport der docker data files:
root@raspberrypi:/docker/fhem_2025# du -h mysql_20250416
4,0K mysql_20250416/log
4,0K mysql_20250416/mycustom.cnf
4,0K mysql_20250416/data/#innodb_temp
116K mysql_20250416/data/sys
36K mysql_20250416/data/mysql
101M mysql_20250416/data/#innodb_redo
1,7M mysql_20250416/data/performance_schema
32G mysql_20250416/data/fhem
33G mysql_20250416/data
8,0K mysql_20250416/init.sql
33G mysql_20250416

mysqldump in leere Datenbank:
root@raspberrypi:/docker/fhem_2025# du -h mysql
4,0K mysql/log
4,0K mysql/mycustom.cnf
820K mysql/data/#innodb_temp
116K mysql/data/sys
36K mysql/data/mysql
101M mysql/data/#innodb_redo
1,7M mysql/data/performance_schema
29G mysql/data/fhem
34G mysql/data
4,0K mysql/init.sql
34G mysql

Sofern die Datenbank mit OPTIMIZE TABLE gepflegt wurde gibt es da keinen wirklichen Unterschied.


[ch.eick]

Nach dem mysqldump ist nun natürlich eine Lücke in den Daten, da die alte DB ja bereits weitere Daten aufgenommen hat.

Hierfür habe ich nun im alten FHEM das syncStandby eingerichtet

Code Auswählen Erweitern

defmod syncDB DbLog ./db_2.conf aaaaaa:bbbbbb
attr syncDB DbLogExclude .*
attr syncDB DbLogSelectionMode Exclude/Include
attr syncDB DbLogType History
attr syncDB asyncMode 1
attr syncDB cacheEvents 2
attr syncDB devStateIcon .*active:10px-kreis-gelb connected:10px-kreis-gruen .*disconnect:10px-kreis-rot
attr syncDB disable 0
attr syncDB insertMode 1
attr syncDB room System
attr syncDB showNotifyTime 1
attr syncDB showproctime 1
attr syncDB syncEvents 1
attr syncDB verbose 0

defmod LogDBRep_syncStandby DbRep LogDB
attr LogDBRep_syncStandby DbLogExclude .*
attr LogDBRep_syncStandby aggregation no
attr LogDBRep_syncStandby comment Version 2025.04.16 15:00\
Sync DbLog in eine weitere Datenbank
attr LogDBRep_syncStandby event-on-update-reading state
attr LogDBRep_syncStandby fastStart 1
attr LogDBRep_syncStandby role Client
attr LogDBRep_syncStandby room System
attr LogDBRep_syncStandby showproctime 1
attr LogDBRep_syncStandby stateFormat { (ReadingsVal($name,"state", ""))."<br>SQL-Zeit: ".(ReadingsVal($name,"sql_processing_time", "n/a")) }
attr LogDBRep_syncStandby timeDiffToNow d:6
attr LogDBRep_syncStandby verbose 3

Der erste syncStandby ist nun gestartet und schreibt schon ins Log File

Code Auswählen Erweitern

2025.04.16 15:42:15.398 3: DbRep LogDBRep_syncStandby - get initial structure information of database "fhem", remaining attempts: 3
2025.04.16 15:42:15.400 3: DbRep LogDBRep_syncStandby - Connectiontest to database mysql:database=fhem;host=192.168.178.40;port=3306 with user fhemuser
2025.04.16 15:42:15.862 3: DbRep LogDBRep_syncStandby - Index Report_Idx exists. Check ok
2025.04.16 15:42:15.882 3: DbRep LogDBRep_syncStandby - Initial data information retrieved - total time used: 0.3198 seconds
2025.04.16 15:42:15.963 3: DbRep LogDBRep_syncStandby - Connectiontest to db mysql:database=fhem;host=192.168.178.40;port=3306 successful

Entgegen der Wiki Doku sind bei verbose 3 keine Meldungen im FHEM Log erschiene, die readings wurden jedoch gesetzt:
background_processing_time 1067.5642 2025-04-16 16:00:03
number_lines_inserted_Standby 22101 2025-04-16 16:00:03
sql_processing_time 1067.5454 2025-04-16 16:00:03

Es ergibt sich somit eine Laufzeit von 18 Minuten für diesen syncStandby.

Die Lücke in der Datenbank ist auch schön im Grafana zu sehen.
Leider habe ich den ersten syncStandby mit d:6 gestartet, obwohl ich ja wuste, dass es nur einige Stunden sind, aber schauen wir mal was da passiert
Du darfst diesen Dateianhang nicht ansehen.

Und schon sind die fehlenden Daten drüben Ich bin echt Dankbar für die Lehrstunde, die Ihr mir habt zuteil kommen lassen.
Du darfst diesen Dateianhang nicht ansehen.