====== SQL-SSH-Backup ======
Mit SQL-SSH-Backup lassen sich MySQL-, PostgreSQL- oder MongoDB-Pull-Sicherungen erstellen. Der Backupserver loggt sich dazu per SSH ein und führt die Befehle zur Sicherung auf dem zu sichernden Server aus. Der Backupserver benötigt dabei keinerlei Datenbank-Module oder -Programme. Jede Datenbank kann einzeln gesichert, was eine Rücksicherung erleichtert.

===== Copyright / Lizenz =====
SQL-SSH-Backup entstand hauptsächlich durch das Webserver-Team im Rahmen des Webhostings der FeM e.V. und steht unter der [[http://www.gnu.de/documents/gpl-2.0.de.html|GPL-2]].

===== Features (der aktuellen Version) =====

  * Dump für MySQL, PostgreSQL oder MongoDB via SSH
    * benötigt kein MySQL/PostgreSQL auf dem Backup-Server
    * sichere Übertragung/Nutzung der Datenbank-Zugangsdaten möglich
    * Einzel- oder Gesamtsicherung aller Datenbanken
    * Sicherung einzelner Datenbanken
  * Kompression der Dumps mit bzip2/xz (alle DBs) oder PosgreSQL-Dump-Format möglich
  * mehrere Backups täglich möglich (Verzeichnisstruktur enthält dann Uhrzeit)
  * automatische Bereinigung von alten Backups

===== Sourcecode / Tarballs =====

  * [[https://github.com/fem/sql-ssh-backup]]
  * Gentoo-FeM-Overlay (**//app-backup/sql-ssh-backup//**)

===== Installation =====

==== Backupserver ====
Auf dem Backupserver wird neben SQL-SSH-Backup nur SSH (meist vorhanden) und ggf. bzip2/xz (für komprimierte MySQL-Backups) benötigt.

Versionen:
  * 1.3 - erstes kombiniertes MySQL/PgSQL-Release
  * 1.4 - Zeitangabe in Sicherungsordner integrieren (erlaubt Mehrfach-Sicherungen an einem Tag)
  * 1.5 - Konfigurationsdatei für Benutzer/Passwort bei MySQL (verhindert auslesen des Passworts via ps), SSH-Port-Angabe
  * 1.6 - Konfigurationsdatei für Benutzer/Passwort bei PgSQL (verhindert auslesen des Passworts via ps)
  * 1.7 - Gesamtdump aller Datenbanken statt Einzeldump ermöglichen
  * 1.8 - Sicherung einer einzelnen Datenbank möglich
  * 1.9 - Wiederholung von Dumps bei Fehlern ermöglichen (Retry), bereinigte Exit-Codes, kleinere Fehlerkorrekturen
  * 2.0 - Backup von MongoDB (ohne Datenbankauthentifizierung)
  * 2.1 - Max-allowed-packet für MySQL

==== zu sichernder Server ====

Auf dem zu sichernden Server müssen nur SSH (meist vorhanden) und MySQL / PostgreSQL / MongoDB (sowieso vorhanden) zur Verfügung stehen.

===== Konfiguration =====
==== zu sichernder Server ====
=== Benutzer und SSH-Key anlegen ===
Der Schlüssel muss __ohne__ Passwort (Passphrase) erstellt werden, sonst macht das automatische Backup wenig Spaß.

  * **//useradd -m backup//**
  * **//ssh-keygen -f /home/backup/.ssh/id_rsa//**

<code|Ausgabe>
Generating public/private rsa key pair.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/backup/.ssh/id_rsa.
Your public key has been saved in /home/backup/.ssh/id_rsa.pub.
The key fingerprint is:
3e:65:ec:fd:ba:92:15:94:18:da:74:26:d4:fc:b9:f0 root@server1
The key's randomart image is:
+--[ RSA 2048]----+
|         .==o.   |
|         +.+=    |
|        . .. . . |
|         .  o o  |
|        S +  + . |
|       . + .. E  |
|        o .o.    |
|         .o  .   |
|           .oo.  |
+-----------------+
</code>

  * **//<nowiki>echo "no-port-forwarding,no-X11-forwarding $(cat /home/backup/.ssh/id_rsa.pub)" >> /home/backup/.ssh/authorized_keys</nowiki>//**
  * **//chown backup:backup /home/backup/.ssh/*//**

Der Private Schlüssel für den Backup-Nutzer (/home/backup/.ssh/id_rsa) muss nun sicher (z.B. per SCP) auf den Backup-Server übertragen werden. Anschließend kann er auf dem zu sichernden Server gelöscht werden.

  * **//rm /home/backup/.ssh/id_rsa//**

=== Datenbank-Benutzer anlegen ===
== MySQL ==
Der Backup-Benutzer benötigt lediglich folgende Rechte:

  * SELECT
  * SHOW DATABASES
  * LOCK TABLES
  * SHOW VIEW

<code|Benutzer anlegen - passendes PASSWORT wählen>
CREATE USER 'backup'@'localhost' IDENTIFIED BY '<PASSWORT>';
GRANT USAGE ON * . * TO 'backup'@'localhost' IDENTIFIED BY '<PASSWORT>' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

REVOKE ALL PRIVILEGES ON * . * FROM 'backup'@'localhost';
REVOKE GRANT OPTION ON * . * FROM 'backup'@'localhost';

GRANT SELECT, SHOW DATABASES, LOCK TABLES, SHOW VIEW, PROCESS ON * . * TO 'backup'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
</code>

Beim Einsatz von MySQL 5.7 / MariaDB 10.6 (oder neuer) und der Sicherung aller Datenbanken inkl. Sys-Schema werden folgende Rechte zusätzlich benötigt:

<code|Zugriff auf das Sys-Schema>
GRANT EXECUTE ON FUNCTION `sys`.`extract_table_from_file_name` TO 'backup'@'localhost';
GRANT EXECUTE ON FUNCTION `sys`.`format_path` TO 'backup'@'localhost';
GRANT EXECUTE ON FUNCTION `sys`.`format_statement` TO 'backup'@'localhost';
GRANT EXECUTE ON FUNCTION `sys`.`extract_schema_from_file_name` TO 'backup'@'localhost';
GRANT EXECUTE ON FUNCTION `sys`.`ps_thread_account` TO 'backup'@'localhost';
GRANT EXECUTE ON FUNCTION `sys`.`format_time` TO 'backup'@'localhost';
GRANT EXECUTE ON FUNCTION `sys`.`format_bytes` TO 'backup'@'localhost';
</code>
== PostgreSQL ==
Der Backup-Benutzer benötigt folgende Rechte __nicht__:

  * Datenbanken anlegen
  * Rollen anlegen

Er benötigt die Rolle:

  * Superuser

Folgendes als root auf dem zu sichernden Server ausführen und erst das Passwort (und Wiederholung) für den neuen Nutzer und dann ggf. das Passwort für den postgres-Benutzer eingeben:
  * **//su - postgres//**
  * **//createuser -D -R -s backup -P //**

==== Backupserver ====
Annahmen:
  * Server: server1.example.org (Port 1022)
  * Backup-Verzeichnis: /mnt/backup/server1.example.org/backupsql/
  * SSH-Key: /mnt/backup/server1.example.org/identity

=== SSH-Hostkey speichern ===
Zu allererst muss man sich einmal per SSH mit dem zu sichernden Server als der Benutzer auf dem Backupserver verbinden unter dem dann das Backup läuft. Der SSH-Key muss dem Nutzer gehören und "rw-------" (600) als Benutzerrechte haben.

  * //**chmod 600 /mnt/backup/server1.example.org/identity**//
  * //**ssh backup@server1.example.org -i /mnt/backup/server1.example.org/identity**//
    * Are you sure you want to continue connecting (yes/no)? //**yes**//
<code|Ausgabe>
The authenticity of host 'server1.example.org (10.200.2.211)' can't be established.
RSA key fingerprint is c9:08:84:ea:1c:2c:84:5a:0f:cd:6e:1f:a1:bf:fe:4a.
Are you sure you want to continue connecting (yes/no)?
</code>

Nun sollte man als Nutzer Backup auf dem genannten Server eingeloggt sein.

=== Backup ===
Auf dem Backupserver kann nun das Backup anstoßen werden. Anschließend kann man dies auch per Cronjob erledigen lassen. Der Parameter "-e" gibt an, wieviele Tage Backups vorgehalten werden - im Beispiel sind das die letzten 120 Tage (Standard: 90).

== MySQL ==

  * //**<nowiki>sql-ssh-backup -T mysql -l backup -s server1.example.org -P 1022 -d /mnt/backup/server1.example.org/backupsql -i /mnt/backup/server1.example.org/identity -e 120 -u backup -p PASSWORT</nowiki>**//

== PostgreSQL ==

  * //**<nowiki>sql-ssh-backup -T pgsql -l backup -s server1.example.org -P 1022 -d /mnt/backup/server1.example.org/backupsql -i /mnt/backup/server1.example.org/identity -e 120 -u backup -p PASSWORT</nowiki>**//

== MongoDB ==

  * //**<nowiki>sql-ssh-backup -T mongodb -l backup -s server1.example.org -P 1022 -d /mnt/backup/server1.example.org/backupsql -i /mnt/backup/server1.example.org/identity -e 120 </nowiki>**//