KeyHelp: Große Datenbanken importieren und exportieren ohne Zeitüberschreitung

Ein Shop mit einigen hundert Megabyte Datenbank zieht auf einen KeyHelp-Server um, und der Import im Browser bricht mit einer knappen Fehlermeldung ab. Diese Anleitung zeigt, welche Grenzen KeyHelp für Import und Export setzt, wo sie konfiguriert sind und wie Sie große Datenbanken trotzdem zuverlässig übertragen: per mariadb-dump und mariadb auf der Kommandozeile oder, bis zu einer bestimmten Größe, in phpMyAdmin. Alle Zahlen stammen aus einem Test mit einer rund 420 MiB großen Datenbank und einer zweiten Tabelle mit rund 2,5 GB SQL. Grundlage ist ein KeyHelp-Server wie in KeyHelp installieren und absichern.

Voraussetzungen

• KeyHelp-Server, getestet mit KeyHelp 26.1.1 (Build 3698) auf Debian 12.15 mit MariaDB 10.11.18.
• Kundenkonto mit Datenbank, im Beispiel kunde1 mit der Datenbank kunde1_db und dem gleichnamigen Datenbankbenutzer.
• Kommandozeile auf dem Server für den Weg über mariadb-dump. Wir haben alle Befehle als root ausgeführt, bis auf SET GLOBAL mit den Zugangsdaten des Datenbankbenutzers. Kunden brauchen dafür einen SSH-Zugang, den der Admin unter den Berechtigungen des Kunden freigibt.
• Testdaten: Wir haben die Tabelle bestellungen mit einer Million Zeilen Zufallsdaten gefüllt, Größe laut information_schema:

table_name	table_rows	MiB
bestellungen	951938	418

Schritt 1: Die Grenzen von KeyHelp kennen

phpMyAdmin und Adminer laufen in KeyHelp nicht im PHP-Pool des Kunden, sondern im Pool der KeyHelp-Oberfläche. Laut KeyHelp-Wissensdatenbank ist fpm/pool.d/keyhelp.conf die „PHP-FPM Konfiguration für die KeyHelp-Oberfläche und die verbundenen Dienste (Webmail, PhpMyAdmin)“. Dort fanden wir:

grep -rE "upload_max|post_max|max_execution|memory_limit|max_input_time" /etc/php/8.2/fpm/pool.d/ | head -40

/etc/php/8.2/fpm/pool.d/keyhelp.conf:php_admin_value[memory_limit] = 256M
/etc/php/8.2/fpm/pool.d/keyhelp.conf:php_admin_value[post_max_size] = 144M
/etc/php/8.2/fpm/pool.d/keyhelp.conf:php_admin_value[upload_max_filesize] = 128M
/etc/php/8.2/fpm/pool.d/keyhelp.conf:php_admin_value[max_execution_time] = 240

Die PHP-Einstellungen, die Sie pro Kunde im Panel setzen, wirken also nicht auf phpMyAdmin. Die zweite Grenze sitzt im Apache. Die Weiterleitung an diesen PHP-Pool wartet höchstens 120 Sekunden auf eine Antwort:

sed -n 60,72p /etc/apache2/keyhelp/keyhelp.conf

    <Proxy fcgi://keyhelp_keyhelp.socket>
        ProxySet connectiontimeout=5 timeout=120
    </Proxy>

Die dritte Grenze gilt für jeden Weg, auch für die Kommandozeile: MariaDB nimmt keine einzelne Anweisung an, die größer als max_allowed_packet ist. KeyHelp setzt den Wert in einer eigenen Datei:

head -12 /etc/mysql/mariadb.conf.d/90-tuning.cnf

# KeyHelp Tuning Settings
max_allowed_packet      = 16M

Verifizieren: Die Importseite von phpMyAdmin zeigt die Upload-Grenze direkt an, im Test „Durchsuchen Sie Ihren Computer: (Maximal: 128MiB)“.

Schritt 2: Datenbank auf der Kommandozeile exportieren

Damit das Passwort nicht in der Befehlszeile und im Verlauf steht, legen Sie eine Datei mit den Zugangsdaten an, im Test /root/k1.cnf. Die Datei sollte nur für Sie lesbar sein (Rechte 600):

[client]
user=kunde1_db
password=IHR_DATENBANKPASSWORT

Den Export haben wir mit --single-transaction und --quick ausgeführt:

time mariadb-dump --defaults-extra-file=/root/k1.cnf --single-transaction --quick kunde1_db > /root/kunde1_db.sql
ls -lh /root/kunde1_db.sql

real	0m5.537s
-rw-r--r-- 1 root root 470M Oct  1 19:20 /root/kunde1_db.sql

Für die Übertragung auf einen anderen Server lohnt sich die Kompression in einem Schritt:

time (mariadb-dump --defaults-extra-file=/root/k1.cnf --single-transaction --quick kunde1_db | gzip > /root/export.sql.gz)
ls -lh /root/export.sql.gz

real	0m18.875s
-rw-r--r-- 1 root root 260M Oct  1 19:35 /root/export.sql.gz

Unsere Zufallsdaten ließen sich schlecht komprimieren, aus 470 MiB wurden 260 MiB.

Verifizieren: Ein vollständiger Dump beginnt mit einem Kopf wie -- MariaDB dump 10.19 Distrib 10.11.18-MariaDB und enthält je Tabelle DROP TABLE IF EXISTS und CREATE TABLE.

Schritt 3: Datenbank auf der Kommandozeile importieren

Der Import liest die Datei über die Standardeingabe ein. Weil der Dump DROP TABLE IF EXISTS enthält, ersetzt er gleichnamige Tabellen in der Zieldatenbank vollständig:

time mariadb --defaults-extra-file=/root/k1.cnf kunde1_db < /root/kunde1_db.sql
mariadb --defaults-extra-file=/root/k1.cnf kunde1_db -e "SELECT COUNT(*) FROM bestellungen"

real	0m22.484s
COUNT(*)
1000000

Komprimierte Dumps müssen Sie nicht erst entpacken. Im Test war /root/kunde1_db.sql.gz mit gzip -k /root/kunde1_db.sql entstanden:

time (gunzip -c /root/kunde1_db.sql.gz | mariadb --defaults-extra-file=/root/k1.cnf kunde1_db)

real	0m25.213s

Auch die zweite Testtabelle mit sechs Millionen Zeilen und rund 2,5 GB SQL lief so ohne Abbruch durch:

time (gunzip -c /root/protokoll.sql.gz | mariadb --defaults-extra-file=/root/k1.cnf kunde1_db)
mariadb --defaults-extra-file=/root/k1.cnf kunde1_db -e "SELECT COUNT(*) FROM protokoll"

real	1m46.720s
COUNT(*)
6000000

Verifizieren: Vergleichen Sie die Zeilenzahl wichtiger Tabellen mit SELECT COUNT(*) auf Quell- und Zielserver.

Schritt 4: Import in phpMyAdmin bis 128 MiB

Im Kundenbereich öffnen Sie unter „Ressourcen“, „Datenbanken“ in der Zeile der Datenbank die Aktion „Datenbank-Weboberfläche“. Im Test öffnete sich damit phpMyAdmin. Wählen Sie dort die Datenbank und den Reiter „Importieren“.

Laut Seite darf die Datei „komprimiert (gzip, zip) oder unkomprimiert“ sein. Wir haben drei Dateien hochgeladen:

• 65 MiB gzip mit 250.000 Zeilen: „Der Import wurde erfolgreich abgeschlossen, 146 Abfragen wurden ausgeführt. (teil.sql.gz)“
• 146 MiB gzip: „Möglicherweise wurde eine zu große Datei hochgeladen. Bitte lesen Sie die Dokumentation zur Lösung dieses Problems.“ Im Fehlerprotokoll von Apache stand dazu PHP Warning: POST Content-Length of 152297925 bytes exceeds the limit of 150994944 bytes in Unknown on line 0, also die 144 MiB aus post_max_size.
• 27 MB gzip mit rund 2,5 GB SQL (sechs Millionen kurze Zeilen): Der Upload ging durch, der Import nicht. Nach rund zwei Minuten zeigte der Browser „Gateway Timeout“.

Die Grenze von 128 MiB gilt also für die hochgeladene Datei. Die gut komprimierbare Testtabelle passte gezippt durch, scheiterte dann aber an der Laufzeit.

Verifizieren: Nach dem Import zeigt phpMyAdmin die Zahl der ausgeführten Abfragen. Prüfen Sie zusätzlich die Zeilenzahl mit SELECT COUNT(*).

Schritt 5: Was bei einem Abbruch in der Datenbank bleibt

Bei der dritten Datei war in phpMyAdmin „Teilweiser Import“ eingeschaltet. Die Option soll laut Oberfläche den Import unterbrechen, „falls das Skript erkennt, dass es sich dem PHP-Timeout-Limit nähert“. Das PHP-Limit liegt aber bei 240 Sekunden, Apache gab schon nach 120 Sekunden auf. Im Zugriffsprotokoll von KeyHelp steht die Anfrage mit Status 504:

"POST /db/index.php?route=/import HTTP/2.0" 504 9867

Im Fehlerprotokoll folgt rund zwei Minuten später:

[proxy_fcgi:error] [pid 665:tid 679] (70007)The timeout specified has expired:

Die Tabelle war danach nicht leer, sondern unvollständig gefüllt. Bei zwei Kontrollen um 19:43 und 19:44 Uhr Serverzeit, der Import war um 19:38 Uhr gestartet, standen dort unverändert 4.361.744 von 6.000.000 Zeilen:

COUNT(*)
4361744

Ein abgebrochener Import ist damit gefährlicher als eine Fehlermeldung vor dem Start: Eine Website kann mit unvollständigen Daten weiterlaufen. Löschen Sie die betroffenen Tabellen und importieren Sie neu, im Zweifel auf der Kommandozeile.

Verifizieren: Prüfen Sie nach jedem Abbruch die Zeilenzahl. Das Zugriffsprotokoll der Oberfläche liegt unter /var/log/apache2/keyhelp/access.log.

Schritt 6: Export in phpMyAdmin

Der Export hat die Upload-Grenze nicht. Im Reiter „Exportieren“ haben wir die Datenbank mit der Exportmethode „Schnell“ im Format „SQL“ exportiert. Der Download mit 497.589.534 Byte war nach rund 30 Sekunden vollständig, gemessen in 3-Sekunden-Schritten, und endete mit der letzten Zeile des Dumps. Größere Datenbanken haben wir im Browser nicht exportiert. Ob die 120-Sekunden-Grenze dort ebenfalls greift, ist deshalb offen.

Verifizieren: Die letzte Zeile eines vollständigen Exports lautete im Test /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;.

Schritt 7: Weboberfläche für Datenbanken wählen (Admin)

Als Admin legen Sie unter „Konfiguration“, „Datenbank-Administration“ fest, welches Werkzeug Kunden bekommen. Zur Auswahl standen „phpMyAdmin 5.2.3“ und „Adminer 6.0.1“. Über „Benutzerdefinierte Zugriffs-URL“ verweisen Sie auf eine eigene Installation, laut Hinweis gilt die URL dann „im gesamten Control Panel“.

Beide Werkzeuge liegen unter /home/keyhelp/www/. Den Wechsel auf Adminer haben wir nicht getestet.

Verifizieren: Im Kundenbereich führte der Menüpunkt „Datenbank-Weboberfläche“ unter „Tools“ im Test auf /db/ und damit zu phpMyAdmin.

Typische Fehler

„Möglicherweise wurde eine zu große Datei hochgeladen.“ Die Datei ist größer als die Grenze aus dem PHP-Pool der Oberfläche. Komprimieren Sie den Dump mit gzip oder weichen Sie auf die Kommandozeile aus.

„Gateway Timeout“ beim Import. Der Import lief länger als die 120 Sekunden aus ProxySet timeout=120. „Teilweiser Import“ verhinderte das im Test nicht. Die Tabelle ist danach unvollständig, siehe Schritt 5.

„ERROR 2006 (HY000) at line 1: Server has gone away“. Diese Meldung bekamen wir beim Import einer einzelnen Anweisung mit 20 MB. Das Journal von MariaDB nennt den Grund:

[Warning] Aborted connection 355 to db: 'kunde1_db' user: 'kunde1_db' host: 'localhost' (Got a packet bigger than 'max_allowed_

Nur den Client mit --max-allowed-packet=64M zu starten, reichte im Test nicht, die Meldung kam erneut. Erst als zusätzlich der Wert im Server als root mit SET GLOBAL angehoben war, stand die Zeile mit 20.000.000 Zeichen in der Tabelle:

mariadb -e "SET GLOBAL max_allowed_packet=67108864"
mariadb --defaults-extra-file=/root/k1.cnf --max-allowed-packet=64M kunde1_db < /root/zeile.sql
mariadb -e "SET GLOBAL max_allowed_packet=16777216"

Der dritte Befehl setzt den Wert danach wieder auf 16 MiB zurück. Eine dauerhafte Änderung in 90-tuning.cnf haben wir nicht getestet. Laut KeyHelp-Wissensdatenbank können manuelle Änderungen in von KeyHelp erstellten Dateien überschrieben werden.

Häufige Fragen

Gelten die PHP-Einstellungen des Kunden für phpMyAdmin?

Nein. Im Test galten für phpMyAdmin die Werte aus /etc/php/8.2/fpm/pool.d/keyhelp.conf, der Pool läuft als Benutzer keyhelp. Ein Kunden-Pool ist dort nicht beteiligt.

Wie lange darf ein Import im Browser dauern?

Im Test endete die Anfrage nach rund 120 Sekunden mit 504. Der Import von 1.000.000 Zeilen (470 MiB SQL) dauerte auf der Kommandozeile 22,5 Sekunden.

Hilft es, den Dump in mehrere Dateien zu teilen?

Nur bedingt. Jede Datei muss unter 128 MiB bleiben und in unter zwei Minuten importiert sein. Mit --where lässt sich ein Teil einer Tabelle exportieren, im Test so:

mariadb-dump --defaults-extra-file=/root/k1.cnf --single-transaction --quick --where="id<=250000" kunde1_db bestellungen | gzip > /root/teil.sql.gz

Die Datei mit 250.000 Zeilen (65 MiB gzip) lief in phpMyAdmin ohne Fehler durch. Sie ersetzte aber die ganze Tabelle: Vorher standen dort 1.000.000 Zeilen, danach 250.000. Jeder so erzeugte Teil-Dump legt die Tabelle mit DROP TABLE IF EXISTS neu an, wie in Schritt 3 gezeigt. Einen Import mehrerer Teile nacheinander haben wir nicht getestet.

Testumfang

Export und Import mit mariadb-dump und mariadb sowie Import und Export in phpMyAdmin haben wir in KeyHelp 26.1.1 mit 470 MiB und rund 2,5 GB SQL ausgeführt. Auffällig: Ein Import mit Zeitüberschreitung hinterließ eine unvollständig gefüllte Tabelle. Nicht geprüft: Adminer, Uploads über 146 MiB in phpMyAdmin (ein vorgeschalteter Proxy unseres Testaufbaus ließ sie nicht bis zum Server durch), große Einzelanweisungen in phpMyAdmin, der Import mehrerer Teil-Dumps und eine dauerhafte Änderung von max_allowed_packet. Große Datenbanken übertragen Sie auf der Kommandozeile.

Fazit

Für kleine und mittlere Datenbanken reicht phpMyAdmin in KeyHelp: Bis 128 MiB Dateigröße und rund zwei Minuten Laufzeit funktioniert der Import, komprimierte Dumps sparen Upload. Darüber hinaus ist die Kommandozeile der verlässliche Weg, im Test lief dort auch der Import von 2,5 GB SQL ohne Abbruch durch. Prüfen Sie nach jedem Import die Zeilenzahl, denn ein Abbruch im Browser hinterließ im Test keine leere, sondern eine unvollständig gefüllte Tabelle.

Weiterführende Anleitungen und Quellen

• KeyHelp installieren und absichern
• KeyHelp: Berechtigungen für Kunden festlegen
• MySQL- und PostgreSQL-Backups mit cron automatisieren
• KeyHelp-Wissensdatenbank: Konfigurationsdateien und Speicherpfade
• Keyweb: KeyHelp, alle Funktionen im Überblick