Interner SQL-Server der QNAP: Migration und Datensicherung

Der SQL-Server in der QNAP-Firmware hat sicherlich schon manchen Nutzer zur Weißglut und/oder Verzweiflung getrieben. Vor allem bei Versionssprüngen bleiben gern mal die Daten auf der Strecke, weil die Migration "suboptimal" gelöst ist. Außerdem ist es im Auslieferungszustand über die Weboberfläche nicht möglich, eine verlässliche Datensicherung zu erstellen.

Aber mal der Reihe nach:

Welcher SQL-Server werkelt eigentlich im QNAP?

Bis QTS 4.x war das relativ einfach zu beantworten. Der SQL-Server war ein MariaDB5.x-Server, der auf dem Port 3306 und dem Unix-Socket /tmp/mysql gelauscht hat. Sein Datenverzeichnis war standardmäßig /share/CACHEDEV1_DATA/.@qmariadb.

Bei QTS 5.x wird es interessanter:

Hier wurde der interne SQL-Server in das App-Center ausgelagert, was grundsätzlich keine schlechte Idee ist, da er dann unabhängig von der Firmware aktualisiert werden kann. ABER: Unter der Haube wurde dadurch viel verändert, was zu einigen Problemen führen kann... Dazu später mehr...

Es sind nun zwei Versionen des SQL-Servers verfügbar: Die MariaDB5 (die dem gleichen Hauptversionszweig wie der bisherige SQL-Server entspringt) und die deutlich modernere und performantere MariaDB10.

Grundsätzlich wird bei einem Upgrade von QTS4.x auf QTS5.x der MariaDB5-Server installiert und es *sollten* die Daten des bisherigen SQL-Servers übernommen werden. ABER: Wenn man - wie ich - auf dem SQL-Server jede Menge umfangreiche Datenbanken liegen hat, läuft das Migrationsscript in einen Timeout und der MariaDB5-Server startet nicht.. Die Fehlermeldung ist dabei oft nicht sehr aussagekräftig.

Noch schöner wird es, wenn man dann den MariaDB10-Server installiert und dort auf "MariaDB5-Datenbank konvertieren" klickt, um seine bisherigen Daten in den neuen Server zu übernehmen. Auch hier schlägt dann gern der Timeout zu, mit den bereits oben beschriebenen Folgen. Noch "besser" ist allerdings, dass der MariaDB10-Server standardmäßig auf Port 3307 und Socket /var/run/mariadb10.sock lauscht. Somit ist zwar theoretisch ein Parallelbetrieb der beiden Server möglich, der aber in der Praxis nicht viel Sinn macht. Außerdem funktioniert damit die Anbindung an den internen Webserver der QNAP nicht mehr, da dieser den SQL-Server hart verdrahtet auf Port 3306 und Socket /tmp/mysql.sock erwartet.

Hier noch mal die Unterschiede der Versionen im Vergleich:

Zum Glück lässt die alten Datenverzeichnisse bei einer Migration auf eine andere Version bestehen.

Bei keiner der genannten Versionen hat QNAP eine Datensicherung vorgesehen - d.h. man kann weder über die alte Backup-Anwendung noch über HBS3 von Haus aus eine Sicherung der Daten vornehmen. Das ist natürlich aus IT-Sicherheitsgesichtspunkten großer Mist!

Also schauen wir uns jetzt mal die Möglichkeiten der Datensicherung an:

Sinnvoll sind zwei verschiedene Wege zur Sicherung:

1. Regelmäßiger (täglicher) DB-Dump aller Datenbanken (!!) in ein Share auf dem NAS, um dieses dann regulär wegzusichern. Das ist auch der von mir empfohlene Weg!

2. Verschieben des Datenverzeichnisses des SQL-Servers, um es in einen Share zu legen, der über HBS3 gesichert werden kann. Dieser Weg ist möglich, birgt aber das Risiko, dass keine Transaktionssicherheit gewährleistet ist und somit bei der Rücksicherung Probleme auftreten können.

Also machen wir es richtig und sichern die Datenbank korrekt und transaktionssicher über mysqldump. Damit können auch problemlos MariaDB5-Daten in MariaDB10 und vice versa überspielt werden. Außerdem können die Datenbanken auch ohne Anpassung auf ein anderes NAS oder eine andere MariaDB-Instanz (z.B. in einer VM unter Linux oder Windows) übertragen werden.

Was brauchen wir dazu?

1. Ein Sicherungsverzeichnis, welches für HBS3 erreichbar ist.

Dazu muss lediglich ein neuer Share in der Systemsteuerung des NAS unter Systemsteuerung --> Rechte --> Freigabeordner angelegt werden.

Nennen wir es für diese Anleitung db_dumps.

2. Ein Sicherungsscript.

Jetzt muss das Sicherungsscript erstellt werden. Dies geht wahlweise über die unter Windows gemappte Freigabe db_dumps oder direkt per SSH mit vi. Das Script nennen wir backup.sh

Für jede zu sichernde Datenbank muss eine separate Zeile nach folgendem Schema eingetragen werden:

/share/CACHEDEV1_DATA/.qpkg/MariaDB10/bin/mysqldump --single-transaction -h localhost -u root -pHIERSTEHTDASROOTPWDERMARIADB mysql> /share/db_dumps/mysql-DB_`date +"%Y%m%d"`.sql

Das obige Beispiel funktioniert für eine MariaDB10. Bei einem anderen Server muss der Pfad zu mysqldump anhand der Tabelle oben angepasst werden. Weiterhin muss hinter dem Parameter -p das Root-Passwort der MariaDB eingetragen werden. Hierbei ist wichtig, dass kein Leerzeichen und auch kein Steuerzeichen von SQL verwendet wird. Ist dies doch der Fall, muss das Passwort in einfache Anführungszeichen (') gesetzt werden!

Nach dem Parameter mit dem Passwort kommt dann der Name der zu sichernden Datenbank. Hier bitte beachten, dass jede Datenbank gesichert wird! Am besten die Datenbanken mit folgendem Befehl abfragen:

/share/CACHEDEV1_DATA/.qpkg/MariaDB10/bin/mysql -u root -pHIERSTEHTDASROOTPWDERMARIADB
SHOW DATABASES;

Dann bitte für jede Datenbank einen separaten Eintrag im Script vornehmen. Angepasst werden muss der Datenbankname an zwei Stellen: Einmal nach dem Passwort-Parameter und einmal im Pfad der Sicehrungs-SQL-Datei (im obigen Beispiel /share/CACHEDEV1_DATA/.qpkg/MariaDB10/bin/mysqldump --single-transaction -h localhost -u root -pHIERSTEHTDASROOTPWDERMARIADB mysql> /share/db_dumps/mysql-DB_`date +"%Y%m%d"`.sql)

3. Nun muss das Script noch als ausführbar gekennzeichnet und in der crontab verankert werden.

Dazu im SSH folgenden Befehlen ausführen:

chmod +x /share/db_dumps/backup.sh

Jetzt mal das Script per Hand im SSH ausführen und schauen, ob die Sicherungs-SQL-Dateien im Verzeichnis angelegt werden:

/share/db_dumps/backup.sh

Wenn alles geklappt hat, noch den Eintrag in der Crontab vornehmen (hier im Beispiel lasse ich den Dump täglich um 3 Uhr erstellen - das sollte natürlich vor dem täglichen Backup sein!)

crontab -e

Dann in der Crontab folgende Zeile am Ende einfügen:

0 3 * * * /share/db_dumps/backup.sh

Und damit das ganze rebootsicher ist, nochmals diese Zeile mit vim in der Standardcrontab eintragen:

vi /mnt/HDA_ROOT/.config/crontab

Jetzt haben wir eine tägliche Datensicherung aller Datenbanken, die wir dann regulär über HBS3 wegsichern können.

Hinweis:

Das Script erzeugt für jeden Tag eine neue Sicherungsdatei. D.h., die alten Dateien müssen regelmäßig gelöscht werden, da sonst das Verzeichnis vollläuft. Sichert Ihr täglich das db_dump-Verzeichnis über HBS3, dann kann auch gleich die Vortagessicherung überschrieben werden. Dazu muss einfach der Teil `date +"%Y%m%d"` im Befehl entfernt werden.

Rücksicherung einer Datenbank:

Haben wir die Dump-Dateien, ist die Rücksicherung ganz einfach:

/share/CACHEDEV1_DATA/.qpkg/MariaDB10/bin/mysql -u root -pHIERSTEHTDASROOTPWDERMARIADB datenbankname </share/db_dump/datenbankname_datum.sql

Auch hier muss natürlich wieder der Pfad zur jeweils genutzten MariaDB-Version und der Datenbankname angepasst werden.

Exkurs: Die Datenbank mysql.

Eine besondere Rolle spielt die Datenbank mysql. Diese beinhaltet die Systemeinstellungen der MariaDB inklusive der User und Passwörter. Daher muss bei einer WIederherstellung oder beim Neuaufsetzen des DB-Servers diese Datenbank als erste wiederhergestellt werden. Danach ist zwingend ein Neustart der MariaDB erforderlich, damit die Einstellungen neu eingelesen werden!

Performanceoptimierung:

Ein Datenbankserver hängt immer stark vom Datenträgerdurchsatz ab. Wenn er auf einem Volume läuft, welches aus Festplatten besteht, auf denen vielleicht auch noch VMs liegen, dann geht natürlich die Performance in den Keller. Daher sollte das Datenverzeichnis möglichst auf dem schnellsten verfügbaren Volume (SSD oder SSD-gecachtes Volume) liegen.

Hierzu legen wir einen neuen Share in der Systemsteuerung des NAS unter Systemsteuerung --> Rechte --> Freigabeordner an.

Wer die Möglichkeit hat, diesen Share auf ein SSD-Volume zu legen, sollte das tunlichst nutzen, da es der Performance zu Gute kommt!

pasted-from-clipboard.png

Hier bei mir habe ich eine Freigabe "datenbanken" auf meinem SSD-Volume anlegt.

Damit diese verwendet wird, muss in der Config-Datei des DB-Servers (siehe obige Tabelle) der Eintrag datadir angepasst werden.

Dazu zuerst den DB-Server beenden (entweder über die Weboberfläche oder per SSH-Befehl /share/CACHEDEV1_DATA/.qpkg/MariaDB10/etc/init.d/mariadb10.sh stop.

Danach die Config-Datei mit einem Editor (z.B. vim öffnen) vi /share/CACHEDEV1_DATA/.qpkg/MariaDB10/etc/mariadb.conf und unter der Rubrik [mysqld] folgenden Eintrag vornehmen:

datadir = /share/datenbanken/

Nun die Daten aus dem alten Datenverzeichnis in das neue Verzeichnis kopieren:

cp /share/CACHEDEV1_DATA/.mariadb10/ /share/datenbanken/ -R

Und danach den SQL-Server neu starten über die Weboberfläche oder per SSH-Befehl /share/CACHEDEV1_DATA/.qpkg/MariaDB10/etc/init.d/mariadb10.sh start.

Nutzen des MariaDB10-Servers als DB-Server für den internen Webserver:

Wie eingangs beschrieben, kann der MariaDB10-Server nicht standardmäßig für den internen Webserver der QNAP genutzt werden. Das ändern wir aber ganz schnell:

Dazu einfach die Config-Datei abändern:

vi /share/CACHEDEV1_DATA/.qpkg/MariaDB10/etc/mariadb.conf

und hier die Port-Einträge von 3307 auf 3306 und die Socket-Einträge von /var/run/mariadb10.sock auf /tmp/mysql.sock ändern (ACHTUNG: Mehrfach!)

Danach den SQL-Server über die Weboberfläche oder per SSH-Befehl neu starten:

/share/CACHEDEV1_DATA/.qpkg/MariaDB10/etc/init.d/mariadb10.sh stop
/share/CACHEDEV1_DATA/.qpkg/MariaDB10/etc/init.d/mariadb10.sh start

So, ich hoffe, dass mein erster Blogbeitrag nicht zu langweilig war und dass er möglichst vielen Usern hilft, mehr Sicherheit in den internen SQL-Server zu bekommen.

Ich freue mich über FEEDBACK!!!!