Shell-Script zum Exportieren von MySQL-Datenbanken inklusive Benutzer und Berechtigungen

Wer Webentwicklung betreibt und MySQL/MariaDB als Datenbanksystem einsetzt, der wird irgendwann einmal mit der Aufgabe betreut werden, MySQL-Datenbanken inkl. der Benutzer, Passwörter und Berechtigungen (Grants) auf einen anderen Server zu kopieren. Die Gründe für eine MySQL-Datenbank-Migration können unterschiedlich sein. Vielleicht möchte man die Webanwendung zu einen anderen Provider wechseln. Vielleicht ist man für einen Webserver verantwortlich und man muss im Worst Case Scenario einen Webserver auf einen anderen migrieren. Der wahrscheinlch häufigste Anwendungsfall einer MySQL-Datenbank-Migration ist, die Webanwendung für die lokale Entwicklung verfügbar zu machen.

In allen genannten Szenarien müssen folgende Schritte manuell und teilweise aufwendig durchgeführt werden: Datenbanken sichten, mit mysqldump exportieren, alle Datenbank-Benutzer und -Passwörter finden, Datenbanken auf dem neuen Server importieren, Benutzer einrichten, Passwörter setzen und Berechtigungen einrichten. Gerade letzterer Schritt kann schnell in einer "Klickmanie" enden, wenn man in PHPMyAdmin die Rechte für die einzelnen Datenbanken zusammenklickt.

MySQL-Datenbanken inkl. Benutzer, Passwörter und Berechtigungen migrieren

In diesem Artikel wird ein Linux-Shell-Skript zur Verfügung gestellt, welches uns die Arbeit abnimmt und alle MySQL-Datenbanken inkl. der Benutzer, Passwörter und Berechtigungen automatisiert exportiert, um diese anschließend auf einem neuen Server zu importieren. Das Skript arbeitet wie folgt:

  • Auslesen aller installierten Datenbanken. Ausnahme Systemdatenbanken wie mysql oder performance_schema.
  • Für jede einzelne Datenbank wird eine Export-Datei (SQL-Dump) erstellt.
  • Auslesen aller MySQL-Benutzer. Ausnahme Systemuser wie root oder debian-sys-maint.
  • Für jeden Benutzer werden die Grant-Statements, welche die Berechtigungen auf die Datenbanken steuern, erzeugt.
  • Die Grants enthalten das Passwort in Hash-Form. Es ist also nicht in Klartext, sprich nicht "lesbar".
  • Die Grant-Statements werden ebenfalls in einem SQL-Dump gespeichert.
  • Durch das Ausschließen der System-Datenbanken und -Benutzer können die Exports problemlos auf jedem beliebigen Server importiert werden.
  • Abschließend enthält das Skript eine Import-Funktion, um die erzeugten MySQL-Dumps zu importieren

Voreilige finden das Linux-Skript am Ende dieses Artikels und können die ausführlichen Erläuterung gerne überspringen.

Hinweis zur MySQL-Authentifizierung:
In diesem Artikel werden alle Mysql-Aufrufe ohne Authentifizierung durchgeführt (Parameter -u und -p fehlen). Möglich ist dies, da Benutzername und -Passwort in ~/.my.cnf hinterlegt wurden. Dies ist praktisch, für die MySQL-Authentifizierung kein Passwort eingegeben werden muss und das Shell-Skript somit einfacher anzuwenden ist. Wem dies zu unsicher ist, muss alle MySQL-Aufrufe mit den Parametern -u und -p ergänzen.

Schritt 1: MySQL-Datenbanken exportieren

Starten wir mit dem einfacheren Teil, dem Export der einzelnen Datenbanken. Wie zu Beginn erwähnt, sollen alle installierten Datenbanken mit Ausnahme der Systemdatenbanken in jeweils einzelnen SQL-Dumps exportiert werden. Für das Auslesen der Datenbanken nutzen wir das bekannte SHOW DATABASES, welches wir noch für die Shell aufbereiten müssen (bspw. Leerzeichen und Pipe-Zeichen | entfernen).

 databases=`mysql -e "SHOW DATABASES;" | tr -d "| " | egrep -v 'Database|mysql|information_schema|performance_schema'`

Die Zeile in kürze erklärt:

  • mysql -e "SHOW DATABASES;": durch den Parameter -e kann der MySQL-Befehl direkt in der Linux-Shell ausgeführt werden
  • tr -d "| ": wir entfernen alle Pipes aus der Ausgabe von SHOW DATABASES;
  • egrep -v '{PATTERN}': hierdurch entfernen wir alle Zeilen, die nicht erwünscht sind. In diesem Fall die Systemdatenbanken und Überschrift

Das Ergebnis ist eine Liste aller Datenbanken, die wir in einer For-Schleife durchgehen und für jeden Eintrag die einzelne MySQL-Datenbank exportieren:

 for db in $databases; do
echo "... $db"
mysqldump --add-drop-database --add-drop-table --lock-all-tables --events --force --opt --databases $db | gzip > SQL_$db.sql.gz
done

Auch hier eine kurze Erläuterung:

  • --add-drop-database und -add-drop-table sind nützlich, wenn man wiederholt einen Import durchführt. Vor jedem Import werden die Datenbanken und Tabellen gelöscht, so dass man einen sauberen Stand zur Verfügung hat.
  • | gzip: der SQL-Dump wird automatisch komprimiert
  • > SQL_$db.sql.gz: Speichern nach der Namenskonvention SQL_db001.sql.gz

Schritt 2: Benutzer, Passwörter und Berechtigungen exportieren

In nächsten Schritt kümmern wir uns um den Export der Benutzer inklusive Passwörter und Berechtigungen. MySQL bietet hierfür den GRANT-Befehl an, um für einen Benutzer alle Berechtigungen auszugeben:

SHOW GRANTS FOR testuser@localhost;
+--------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost'
| IDENTIFIED BY PASSWORD '*91122375E3E43157B1FF3A9C6B392D7FFA5697BB';
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db\_001`.* TO 'sqlPCE'@'localhost'
| GRANT ALL PRIVILEGES ON `db\_002`.* TO 'testuser'@'localhost' WITH GRANT OPTION
+--------------------------------------------------------------------------------+

Das folgende Beispiel gibt es folgende Erkenntnisse:

  • GRANT USAGE ON *.* TO 'testuser'@'localhost': damit wird ein User MySQL "bekannt" gemacht. D.h.: existiert der User nicht, wird dieser erstellt.
  • IDENTIFIED BY PASSWORD: hierdurch wird das Passwort gesetzt. In diesem Beispiel in Hash-Form, so dass es niemand sofort lesen kann.
  • GRANT ALL oder GRANT SELECT, UPDATE ON db001 usw: das eigentliche setzen der Berechtigungen auf die Datenbanken. Mit GRANT ALL werden alle Operation für eine Datenbank erlaubt.

MySQL GRANT - Berechtigungen setzen
Die Ausgabe von GRANT entspricht der folgenden Eingabemaske von PHPMyAdmin

Weitere Informationen zum Thema Berechtigungen findet man hier auf der MySQL-Homepage.

An dieser Stelle können wir die Berechtigungen für einen bestimmten Benutzer exportieren. Wir möchten aber alle vorhandenen MySQL-User exportieren. Die User findet man in der Tabelle user in der Datenbank mysql

SELECT 
DISTINCT CONCAT(
'SHOW GRANTS FOR ', user, '@', host, ';'
)
FROM
mysql.user AS q
WHERE
user != 'debian-sys-maint'
AND user != 'root'
AND user != ''

Der Query liefert uns die Show-Grants-Statements für jeden einzelnen MySQL-Benutzer aus der Tabelle mysql.user. Die Where-Klausel ist optional und ignoriert in diesem Fall u.a. die System-Benutzer.

+------------------------------------+
| q
+------------------------------------+
| SHOW GRANTS FOR testuser@localhost;
| SHOW GRANTS FOR fritz@localhost;
| SHOW GRANTS FOR peter@localhost;
| SHOW GRANTS FOR sqluser1@localhost;
+------------------------------------+

Ähnlich wie bei den Datenbanken können wir nun für jeden einzelnen User die Grants exportieren, was wir im folgenden vollständigen Shell-Script wiederfinden.

Das fertige MySQL-Migrations-Skript für Datenbanken, Benutzer und Grants

Das folgende Shell-Script fasst die Erkenntnisse aus diesem Artikel zusammen. Den Inhalt in eine Textdatei (bspw. unter dem Namen mysql-migration.sh) speichern und mit chmod +x ausführbar machen.

  • ./mysql-migration.sh export
    Exportiert Datenbanken und Berechtigungen. Wird auf dem Quellserver ausgeführt.
  • ./mysql-migration.sh import
    Importiert die zuvor erstellten Export-Dateien. Wird auf dem Zielserver ausgeführt
#!/bin/bash
#
# Skript zum Importieren/Exportieren von MySQL-Benutzern, Berechtigungen und
# und Datenbanken
# Quelle: www.pc-erfahrung.de
#

if [ "$#" -ne 1 ]; then
SCRIPT_NAME=$(basename $0)
echo "Benutzung: ./$SCRIPT_NAME export ODER ./$SCRIPT_NAME import"
exit
fi

#
# Exportiere alle MySQL-Datenbanken
# mit Ausnahme von der System-Datenbanken (mysql, information_schema, usw.)
#
function exportMysqlDatabases() {
# Exportiere nur "normale" Datenbanken
databases=`mysql -e "SHOW DATABASES;" | tr -d "| " | egrep -v 'Database|mysql|information_schema|performance_schema'`

for db in $databases; do
echo "... $db"
mysqldump --add-drop-database --add-drop-table --lock-all-tables --events --force --opt --databases $db | gzip > SQL_$db.sql.gz
done
}

#
# Exportiere alle MySQL-Benutzer und -Berechtigungen
# mit Ausnahme der System-User (bspw. root)
#
function exportMysqlGrants() {
host=`hostname`

# SHOW GRANTS Statements für alle User generieren
# Ergebnis ist etwa "SHOW GRANTS FOR 'peter'@'localhost'; "
mysql -B -N -e \
"SELECT
DISTINCT CONCAT(
'SHOW GRANTS FOR ', user, '@', host, ';'
)
FROM
mysql.user AS q
WHERE
user != 'debian-sys-maint'
AND user != 'root'
AND user != ''; " | \

# SQL-Query durch MySQL ausführen
mysql | \

# Ausgabe mit Titeln erweitern, Semikolon am Ende setzen
# Ergebnis in Datei GRANT_HOSTNAME.sql speichern
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' > GRANT_$host.sql

}

#
# Importiere alle Datenbanken, Benutzer und Berechtigungen
#
function importMysqlDatabasesAndGrants() {
echo ""
echo "+++ Entpacke & importiere Datenbank-Backups"
echo ""

db_backups=`ls SQL_*.sql.gz`
for db in $db_backups; do
echo "...$db"
zcat $db | mysql
done

# Wichtig: BErechtigungen müssen nach den Datenbanken importiert werden
echo ""
echo "+++ Entpacke & importiere Datenbank-User und -Berechtigungen"
echo ""
db_backups=`ls GRANT_*.sql.gz`
for db in $db_backups; do
echo "...$db"
zcat $db | mysql
done
}


########################
#
#
# RUN THE SCRIPT
#
#
########################
if [ $1 == 'export' ]; then
echo ""
echo "+++ Step 1: MySQL-Datenbanken exportieren"
echo ""
exportMysqlDatabases

echo ""
echo "+++ Step 2: MySQL-Berechtigungen exportieren"
echo ""
exportMysqlGrants
fi

if [ $1 == 'import' ]; then
echo ""
echo "+++ Step 3: Alle Berechtigungen und Datenbanken importieren"
echo ""
importMysqlDatabasesAndGrants
fi


Abschließende Erklärung:

  • Es gibt drei Funktionen exportMysqlDatabases, exportMysqlGrants und importMysqlDatabasesAndGrants, deren Funktionsweise selbsterklärend sind
  • Abhängig von dem Parameter export bzw. import werden die entsprechenden Methoden ausgeführt

Wir wünschen viel Spaß beim Ausprobieren und hoffen, dem einen oder anderen etwas Arbeit abgenommen zu haben.