MySQL-Dumps mit Namens-Patterns

Um "eben mal schnell" ein Backup einer Datenbank zu machen, hat sich das mitgelieferte Tool "mysqldump" bewährt. Zwar brauchen die erzeugten Dumps etwas mehr Speicherplatz als die Datenbankdateien, aber dafür lassen sich die Files auch lesen und editieren, denn sie enthalten die SQL-Kommandos im Klartext. "mysqldump" kann einen Dump aller Datenbanken erzeugen (--all-databases) oder nur einen Dump derjenigen Datenbank(en), die nach dem Befehl aufgeführt werden. 

Was "mysqldump" aber nicht kann, ist, einen Dump von Datenbanken anzulegen, die einem bestimmten Namensmuster folgen, also beispielsweise mit demselben Präfix starten. Dies ist vielleicht nicht so häufig gefragt, weil üblicherweise eine Datenbank zu einer Anwendung gehört, aber manche Anwendungen handhaben das eben anders. So legt das Projektmanagement-Tool Phabricator insgesamt 58 Datenbanken an, die alle mit "phabricator_" starten.

Nun gibt es sicher auch in MySQL einen SQL-Befehl, der Datenbanken- und Tabellennamen ausgibt. Diese Ausgabe lässt sich dann hoffentlich so weiterverarbeiten, dass die als Eingabe für "mysqldump" dienen kann. Üblicherweise schauen sich Datenbank-Admins die Datenbanken und Tabellen mit den Befehlen "SHOW DATABASES" respektive "SHOW TABLES" an. Dies sind aber keine richtigen SQL-Befehle, die sich mit dem vollständigen SQL-Sprachumfang kombinieren lassen. Der richtige Weg führt also über die Datenbank "information_schema", in der die Metadaten der verwalteten Datenbanken und Tabellen gespeichert sind. Ein brauchbarer Befehl, der die oben erwähnten Phabricator-Datenbanken ausgibt, lautet beispielsweise (hier im MySQL-Monitor):

mysql> use information_schema;
mysql> select distinct(table_schema) from tables where table_schema like 'phabricator%';

Das Prozentzeichen dient hier, wie bei SQL üblich, als Wildcard, also als Platzhalter für beliebige Zeichen. Diese Syntax muss mit dem davor stehenden "like" kombiniert werden. Das MySQL-Tool erlaubt ebenso die Abfrage auf der Commandline mit der Option "-e":

mysql -u root -p -N -s -e "select distinct(table_schema) from tables where table_schema like 'phabricator%'" information_schema

MySQL-Administratorrechte werden gebraucht, weil nur der Admin alle Schema-Informationen lesen darf (in der Default-Konfiguration). Danach die beiden Flags "-N" und "-s", die die tabellarische Ausgabe und den Tabellenkopf der jeweiligen Spalte abschalten. Die Ausgabe sieht in etwa so aus:

phabricator_almanac
phabricator_audit
phabricator_auth
phabricator_badges
phabricator_cache
phabricator_calendar
phabricator_chatlog
phabricator_conduit
...

Damit könnte man nun schon arbeiten und gegebenfalls noch die Linebreaks durch Leerzeichen ersetzen, um die Datenbanken an das Dump-Tool zu übergeben. Aber MySQL bringt auch selbst schon die dafür nötigen Werkzeuge mit. Hier hilft die Funktion "group_concat" weiter, die das Ergebnis einer "Gruppe" zu einem String zusammenfasst. Per Default verwendet die Funktion das Komma als Trennzeichen, aber es lässt sich als Argument auch durch ein Leerzeichen ersetzen. Die passende Funktion sieht dann so aus:

$ mysql -u root -p -N -s -e "select group_concat(distinct(table_schema) separator ' ') from tables where table_schema like 'phabricator%'" information_schema

Wer jetzt die beiden Befehle zusammensetzt, erlebt unter Umständen ein blaues Wunder:

$ mysqldump -u root -p --databases $(mysql -u root -p -N -s -e "select group_concat(distinct(table_schema) separator ' ') from tables where table_schema like 'phabricator%'"  information_schema) > phabricator.sql
Enter password: 
Enter password: 
mysqldump: Got error: 1049: Unknown database 'phabricator_sl' when selecting the database

Was soll denn das nun wieder? Die Datenbank "phabricator_sl" gibt es tatsächlich nicht, und wer etwas genauer hinsieht, stellt fest, dass sie in Wirklichkeit "phabricator_slowvote" und auch die dahinter folgenden Datenbanken fehlen. Was hier passiert, ist, dass MySQL den Output des Befehls abschneidet. Und zwar gibt es für "group_concat" eine solche Begrenzung, die in den MySQL-Servervariablen zu finden ist:

mysql> show variables like group_concat%;

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.00 sec)

Erhöhen wir also den Wert auf 16 MByte:
mysql> set global group_concat_max_len=16384;
Query OK, 0 rows affected (0.00 sec)

Nach dieser Anpassung lassen sich nun sämtliche 58 Phabricator-Datenbanken in einem Rutsch in einen SQL-Dump schreiben:

$ mysqldump -u root -p --databases $(mysql -u root -p -N -s -e "select group_concat(distinct(table_schema) separator ' ') from tables where table_schema like 'phabricator%'" information_schema) > phabricator.sql

$ grep "CREATE DATABASE" phabricator.sql | wc -l
58

Unschön an der Lösung ist jetzt nur noch, dass man das MySQL-Root-Passwort zweimal eingeben muss. Dies lässt sich lösen, indem man etwa das Passwort als Umgebungsvariable setzt und an die Skript übergibt – wenn man sicher ist, dass es kein Problem ist, wenn das Passwort zeitweise in der Prozesstabelle und dauerhaft in der Shell-History auftaucht.

(export PW=supersecret; mysqldump -u root -p$PW --databases $(mysql -u root -p$PW -N -s -e "select group_concat(distinct(table_schema) separator ' ') from tables where table_schema like 'phabricator%'" information_schema) > phabricator.sql)