MySQL-Dumps, GPG, SFTP-Download, MySQL-Import

Alles ohne manuelle Passworteingabe, „ready for cronjob“

Wer MySQL nutzt, möchte den Dump vielleicht mit einem Passwort verschlüsseln und zugleich komprimieren.

Weiterhin soll mysqldump in einem cronjob laufen, wobei das Passwort nicht eingeben werden kann.

Desweiteren soll auf einer lokalen Maschine der verschlüsselte Dump heruntergeladen und in MySQL importiert werden können – und das genauso ohne Passworteingabe.

Wie das funktioniert, wird im folgenden beschrieben. Nebenbei gibt es ein paar Hinweise.

Vorraussetzung sind

  • das Passwort für mysql in ~/.my.cnf
  • ein ssh-key für sftp mit ssh-keygen
  • das Passwort für gpg in ~/.gnupg/example-passwd

Die Einstellungen müssen auf dem Server und dem Client gemacht werden.


Server

Vorab: Auf dem Server gibt es einen User 2TH3oNPR (oder anders zu benennen). Dieser dient dazu, sich per ssh-key einloggen zu können und benötigt keine root-Rechte.

Nebenbei: Wer einen eigenen Root-Server hat, sollte möglichst wenig andere User anlegen. Und wenn, dann mit kryptischen Namen, da Bots Standardnamen wie “admin”, “joe“, “marcel” etc für Angriffe verwenden.


mysqldump

Damit mysqldump ohne User/Password in einem cronjob ausgeführt werden kann, muss in der Datei ~/.my.cnf ein MySQL-Benutzer und dessen Passwort eingetragen werden:

[mysqldump]
user=backup
password=password

Nebenbei: Es empfiehlt sich, in MySQL einen eigenen Benutzer mit minimalen Rechten anzulegen.

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';

Um einen Dump mit drei Datenbanken namens “database1” “database2” und “database3” zu erzeugen, wird folgender Befehl verwendet:

$ mysqldump --opt --databases database1 database2 database3 > databases.sql

Es wird eine Datei erzeugt, in der die Datenbanken jeweils mit “USE database1” usw angesprochen werden.


gpg

Um mit gpg ohne Passworteingabe zu arbeiten, muss das Passwort in einer Datei gespeichert und per “cat“ übergeben werden. Dazu wird im Verzeichnis .gnupg/ eine Datei (Name kann frei gewählt werden) mit dem Passwort gespeichert. Das wird anschließend per cat an gpg übergeben:

$ cat ~/.gnupg/example-passwd | gpg -c --batch --passphrase-fd 0 databases.sql

Die Parameter “–batch“ und “–passphrase-fd 0” stehen hier für “Ausführen im batch/script/cron” und „Passphrase aus dem Dateibezeichner 0”

Erzeugt wird eine komprimierte und verschlüsselte Datei namens databases.sql.gpg

Mit der folgenden Zeile wird der Dump anschließend gelöscht:

$ rm databases.sql

Alternative:

Statt den Dump zunächst zu sichern, zu komprimieren und löschen, kann er auch direkt an gpg gesendet werden. Allerdings geht das zu Lasten der Performance (Faktor 2-4), da die Daten zugleich komprimiert werden.

$ mysqldump --opt --databases database1 database2 database3 | cat ~/.gnupg/example-passwd | gpg -c --batch --passphrase-fd 0 databases.sql.gpg

cronjob

Während eines mysqldumps sind Tabellen “gelockt”, d.h. schreibgeschützt. Je nach Datenbankgröße und Rechnerperformance kann ein Dump 30 Sekunden bis mehrere Minuten dauern. Daher sollte man den Dump zu einer möglichst inaktiven Zeit durchführen. Die Einstellungen für täglich morgens um 5:00 Uhr:

0 5 * * * /home/2TH3oNPR/dump.sh

Client / Lokaler Rechner

sftp

Die Verbindung zum Server soll wieder ohne Passworteingabe erfolgen, was durch einen ssh-key möglich wird:

$ ssh-keygen -t ed25519

Der erzeugte Key wird auf den Server übertragen:

$ ssh-copy-id -i .ssh/id_ed25519 2TH3oNPR@myhost.com

Anschließend ist ein Login ohne Passwort möglich:

$ ssh 2TH3oNPR@myhost.com

Siehe auch hier

Es sollte besser ed25519 statt RSA verwendet werden. Der Schlüssel ist kürzer und sicherer.

Als letztes wird die Datei .ssh/config für alle ssh-basierenden Programme angelegt. Diese enthält folgende Informationen pro Host und User:

Host 192.168.178.46 myhost.com
    HostName myhost.com
    IdentityFile ~/.ssh/id_ed25519
    User 2TH3oNPR

Nebenbei: Sobald die Datei angelegt wurde und es nur einen User für den Host gibt, kann der ssh-Login etwas kürzer erfolgen:

$ ssh myhost.com

Nun ist es möglich, sftp per Kommandozeile ohne Passwort aufzurufen, da sftp Teil von ssh ist:

$ sftp myhost.com:/home/2TH3oNPR/.dumps/databases.sql.gpg /home/amalesh/sql/databases.sql.gpg

Nebenbei: Möchte man aus irgendeinem Grund die Datenbanken in separate Dumps ablegen, aber alle in einem sftp-Befehl downloaden, muss die Option “-batch” verwendet und eine Befehlsdatei (z.b. “ftpcmd.txt”) angelegt werden. ftpcmd.txt enthält dann folgende Zeile:

mget *.sql.gpg /home/amalesh/sql/

oder

get database1.sql.gpg /home/amalesh/sql/
get database2.sql.gpg /home/amalesh/sql/
get database3.sql.gpg /home/amalesh/sql/

Ein sftp-Befehl für beliebige Dateien zum Download per Kommandozeile:

$ sftp -b ftpcmd.txt myhost.com:/home/2TH3oNPR/.dumps/

gpg und mysql

Ebenso wie auf dem Server wird hier im Verzeichnis .gnupg/ eine Datei mit dem Passwort im Klartext angelegt.

In die ~/.my.cnf wird unter [mysql] einen DB-User mit globalen Rechten eingetragen, um später ebenfalls kein Passwort eingeben zu müssen.

Abschließend können wir den folgenden Befehl nutzen, um die komprimierte, verschlüsselte Datei direkt in MySQL einzulesen:

$ cat ~/.gnupg/example-passwd | gpg --passphrase-fd 0 --batch --decrypt /home/amalesh/sql/databases.sql.gpg | mysql

Und das war es dann.

Für unser lokales Script reicht das nun aus:

#!/usr/bin/env bash
$ sftp myhost.com:/home/2TH3oNPR/.dumps/databases.sql.gpg /home/amalesh/sql/databases.sql.gpg
$ cat ~/.gnupg/example-passwd | gpg --passphrase-fd 0 --batch --decrypt /home/amalesh/sql/databases.sql.gpg | mysql

Auf github.com finden sich zwei Scripte, die das zusammenfassen. Dort wird auch restic verwendet. Wer es nicht nutzen will, kommentiert die Zeile aus.

Noch ein kleiner Tipp: Das tool pv zeigt Dauer, aktuelle und durchschnittliche MiB/sec beim Import an:

$ cat ~/.gnupg/example-passwd | gpg --passphrase-fd 0 --batch --decrypt /home/amalesh/sql/databases.sql.gpg | pv -ptra | mysql