12.4 MySQL-Administration

Ebenso wichtig wie die Pflege der im RDBMS gespeicherten Daten ist die Administration des Datenbankservers selbst. Dazu gehören unter anderem Benutzerverwaltung, Datensicherung und Konfiguration. In diesem Abschnitt erhalten Sie eine Einführung in die Aufgaben der Datenbankadministration, wieder am Beispiel von MySQL.

Für die MySQL-Administration stehen im Wesentlichen drei Arten von Werkzeugen zur Verfügung:

• mit dem Server gelieferte Konsolentools wie mysql, mysqladmin und mysqldump
• MySQL Administrator, ein grafisches Administrationstool der MySQL-Entwickler. Sie können es von der MySQL-Website herunterladen. Es wird hier nicht beschrieben, da es weitgehend intuitiv und selbsterklärend ist – erst recht, wenn Sie den folgenden Informationen zur manuellen Administration folgen.
• grafische Clients, die nicht auf die Administration spezialisiert sind (zum Beispiel phpMyAdmin)

12.4.1 mysqladmin

Einige Verwaltungsaufgaben lassen sich mithilfe des Kommandozeilentools mysqladmin erledigen. Genau wie den Konsolenclient mysql müssen Sie es mit -u Benutzername (in der Regel root) und -p für die Passwortanforderung aufrufen, sodass seine Syntax so aussieht:

mysqladmin Befehl -u Benutzername -p

Die wichtigsten Befehle, die Sie eingeben können, sind:

• create Datenbank – erzeugt die angegebene Datenbank wie die SQL-Anweisung CREATE DATABASE.
• drop Datenbank – löscht die gewünschte Datenbank.
• extended-status – erweiterte Statusinformationen. Da die Liste sehr lang ist, sollten Sie sie durch |less (Unix) oder |more (Windows) filtern.
• ping – überprüft, ob der MySQL-Server läuft.
• reload – lädt die Benutzerinformationen neu (siehe nächsten Abschnitt).
• shutdown – beendet den MySQL-Server.
• version – gibt die Version des MySQL-Servers aus.

Hier ein Beispiel, das den Server beendet:

$ mysqladmin shutdown -u root -p

12.4.2 Benutzerverwaltung

Die Benutzeridentifikation in MySQL erfolgt anhand der drei Komponenten Host, Benutzername und Passwort. Die entsprechenden Daten werden in der Verwaltungsdatenbank mysql gespeichert. Hier dienen insbesondere folgende Tabellen der Verwaltung von Benutzerrechten:

• user – enthält Benutzernamen und verschlüsselte Passwörter zur Überprüfung der Anmeldung sowie globale Benutzerrechte
• host – hostbasierte Berechtigungen
• db – Berechtigungen an einzelnen Datenbanken
• tables_priv – Rechte an einzelnen Tabellen
• columns_priv – Benutzerrechte an einzelnen Tabellenspalten

Sie können sich die Inhalte und Strukturen dieser Tabellen mithilfe der zuvor vorgestellten Abfragen in Ruhe anschauen. Wie Sie sehen, besitzt in user, host und db jedes Recht eine eigene ENUM-Spalte mit den möglichen Werten 'Y' (Recht gewährt) oder 'N' (Recht verweigert – dies ist aus naheliegenden Gründen der Standardwert). In tables_priv und columns_priv gibt es dagegen für alle Rechte je eine einzelne SET-Spalte; die darin für den einzelnen User aufgelisteten Rechte werden gewährt, alle anderen verweigert.

Die Überprüfung jedes Datenbankzugriffs erfolgt in zwei Stufen:

1. Prüfung, ob der fragliche Benutzer sich vom entsprechenden Host aus anmelden darf (Tabellen user und host). Scheitert dies aus einem der möglichen Gründe (unbekannter Benutzer, unberechtigter Host, falsches Passwort), bricht der Anmeldeversuch mit einer Fehlermeldung ab. Beispiel:

   $ mysql -u wronguser -p
   ERROR 1045 (28000): Access denied for 'wronguser'@'localhost' (using
   password: YES)
   
   

2. War die grundlegende Legitimationsprüfung erfolgreich, wird als Nächstes getestet, ob der nunmehr angemeldete Benutzer die gewünschte Operation durchführen darf. Dies geht schrittweise von oben nach unten: Hat er das entsprechende Recht global (in der Tabelle user), dann ist bereits alles klar. Wenn nicht, wird in der Tabelle db überprüft, ob der Benutzer die entsprechende Berechtigung für die gesamte aktuelle Datenbank besitzt. Ist auch dies nicht der Fall, dann geht es mit der Tabelle und schließlich mit den einzelnen Spalten weiter. Erst wenn alle diese Prüfungen versagen sollten, erhalten Sie eine Fehlermeldung wie diese:

   mysql> SHOW TABLES FROM test;
   ERROR 1044 (42000): Access denied for user 'darfnix'@'localhost' to
   database 'test'
   
   

Es ist sehr wichtig, dieses Verfahren richtig zu verstehen: Wenn ein Recht auf einer übergeordneten Ebene besteht, ist die Berechtigungsüberprüfung unwiderruflich abgeschlossen. Es gibt also keine Möglichkeit, einem Benutzer zuerst eine allgemeine Erlaubnis zu erteilen und diese dann im Einzelnen wieder einzuschränken. Es ist also umso unerlässlicher, hier »geizig« zu sein und Benutzern stets nur die unbedingt notwendigen Berechtigungen zu erteilen.

Um einen neuen Benutzer zu erzeugen, starten Sie zunächst den Kommandozeilen-Client als User root:

$ mysql -u root -p

Wie es nun weitergeht, hängt von der MySQL-Version ab. Seit Version 5.0 verwendet MySQL die Anweisung CREATE USER, um ein Benutzerkonto anzulegen. Die allgemeine Syntax lautet:

CREATE USER username@hostname IDENTIFIED BY 'Passwort'

Hier ein konkretes Beispiel:

mysql> CREATE USER someuser@localhost
    -> IDENTIFIED BY 'AnyPa55';

Diese Anweisung erzeugt einen neuen Datensatz in der Verwaltungstabelle mysql.user, in der User- und Hostname, verschlüsseltes Passwort und keinerlei Rechte (alles auf 'N') eingetragen sind:

mysql> SELECT * from mysql.user WHERE user='someuser'\G
********************** 1. row **********************
       Host: localhost
       User: someuser
   Password: *426A9DFF6005EE6609101D651C4E70F51F52E12B
Select_priv: N
Insert_priv: N
Update_priv: N
[...]

In MySQL 4.1 und älteren Versionen steht CREATE USER nicht zur Verfügung. Stattdessen wird wie zum Erteilen von Rechten eine GRANT-Abfrage verwendet. Benutzen Sie dazu das spezielle »Recht« USAGE; es erstellt den Eintrag in der Tabelle user, erlaubt die grundsätzliche Anmeldung am MySQL-Server und setzt alle globalen Privilegien auf 'N'. Die allgemeine Schreibweise sieht so aus:

GRANT USAGE ON *.* TO username@hostname
IDENTIFIED BY 'Passwort'

Für den neuen Benutzer someuser@localhost sieht die entsprechende Abfrage so aus:

mysql> GRANT USAGE ON *.* TO someuser@localhost
    -> IDENTIFIED BY 'AnyPa55';

Sollten Sie mit relativ alten Clients oder Programmierschnittstellen arbeiten (beispielsweise PHP-Versionen vor 4.4), funktioniert die Anmeldung dieses neuen Benutzers nicht. Das Problem besteht darin, dass das Standardverfahren zur Passwortverschlüsselung in MySQL 4.1 geändert wurde; die alten MySQL-Client-Bibliotheken verstehen das neue Verfahren nicht. In einem solchen Fall müssen Sie IDENTIFIED BY 'Passwort' im jeweiligen Befehl weglassen – was zunächst einen passwortlosen Benutzer erzeugt – und das Passwort danach folgendermaßen festlegen:

mysql> SET PASSWORD FOR someuser@localhost =
    -> OLD_PASSWORD('AnyPa55');

SET PASSWORD kann auch verwendet werden, um ein Passwort nachträglich zu ändern. Im Normalfall kommt dann allerdings die Verschlüsselungsfunktion PASSWORD() statt OLD_PASSWORD() zum Einsatz.

Um bestehenden Benutzern neue Rechte zuzuweisen, wird in jedem Fall die Anweisung GRANT verwendet, deren allgemeine Syntax so aussieht:

GRANT Recht [(Spalte)][, Recht [(Spalte)] ...]
ON Datenbank.Tabelle
TO username@hostname

Die wichtigsten Rechte, die Sie Benutzern erteilen können, werden in Tabelle 12.8 aufgelistet. Eine Liste aller möglichen Privilegien erhalten Sie übrigens mit folgender MySQL-Anweisung:

mysql> SHOW PRIVILEGES;

Das folgende Beispiel erteilt dem zuvor erstellten Benutzer someuser@localhost das Recht, Daten aus beliebigen Datenbanken und Tabellen auszuwählen:

mysql> GRANT SELECT ON *.* TO someuser@localhost;

Zusätzlich soll er die Berechtigung erhalten, Datensätze in beliebige Tabellen der Datenbank supermarkt einzufügen:

mysql> GRANT INSERT ON supermarkt.*
    -> TO someuser@localhost;

In der Tabelle artikel soll er auch Datensätze löschen dürfen:

mysql> GRANT DELETE ON supermarkt.artikel
    -> TO someuser@localhost;

Schließlich soll er auch noch das Recht haben, den Inhalt der Spalte artname in der Tabelle artikel (den Artikelnamen) zu ändern:

mysql> GRANT UPDATE (artname) ON supermarkt.artikel
    -> TO someuser@localhost;

Angenommen, dieser Benutzer meldet sich an und führt folgende Abfrage durch:

mysql> UPDATE artikel SET artname="PC"
    -> WHERE artname="Computer";

Dann überprüft der MySQL-Server nacheinander folgende Werte:

• Besitzt der User someuser@localhost das globale Recht zum Ändern (Update_priv) in der Tabelle user? – Nein.
• Besitzt er das allgemeine Änderungsrecht (Update_priv) für die Datenbank supermarkt in der Tabelle db? – Nein.
• Besitzt er das Recht, beliebige Spalten in der Tabelle artikel zu ändern (Wert Update in der Spalte Table_priv der Tabelle Tables_priv)? – Nein.
• Besitzt er die Berechtigung, die Spalte artname in der Tabelle artikel zu ändern (Wert Update in der Spalte Column_priv – sowohl in der Tabelle Tables_priv als auch in Columns_priv; in Letzterer mit dem Spaltennamen)? – Ja.

Um einem User dieselben Privilegien wie root zu erteilen (wovon Sie in der Praxis in der Regel absehen sollten), genügt die folgende Anweisung übrigens nicht:

mysql> GRANT ALL PRIVILEGES ON *.*
    -> TO verwalter@localhost;

Dieser Benutzer besitzt nämlich nicht das Recht, seinerseits mithilfe von GRANT Benutzerrechte zu erteilen. Soll dies der Fall sein, ist folgende Variante erforderlich:

mysql> GRANT ALL PRIVILEGES ON *.*
    -> TO verwalter@localhost WITH GRANT OPTION;

Um einem Benutzer ein zuvor erteiltes Recht wieder zu entziehen, wird eine REVOKE-Anweisung verwendet. Ihre allgemeine Syntax lautet:

REVOKE Recht [(Spalte)][, Recht [(Spalte)] ...]
ON datenbank.tabelle FROM username@hostname

Das folgende Beispiel entzieht dem Benutzer someuser@localhost das zuvor erteilte Recht, in alle Tabellen der Datenbank supermarkt Daten einzufügen:

mysql> REVOKE INSERT ON supermarkt.*
    -> FROM someuser@localhost;

Möchten Sie dagegen einen Benutzer mit allen seinen Berechtigungen entfernen, können Sie in MySQL 5 folgende Anweisung verwenden:

mysql> DROP USER ex_user@localhost;

In den 4er-Versionen von MySQL ist es dagegen am gründlichsten. Es ist daher zu empfehlen, Zeilen mit dem fraglichen Usernamen per SQL-Abfrage aus den Berechtigungstabellen zu löschen. Beispiel:

mysql> DELETE FROM user
    -> WHERE user=«ex_user« AND host=«localhost«;

Falls Sie den Client nach dem Ändern von Benutzerrechten nicht beenden (oder zur Sicherheit sogar auch dann), sollten Sie die folgende Anweisung ausführen:

mysql> FLUSH PRIVILEGES;

Dies lädt die Benutzerinformationen neu; alternativ funktioniert auch folgende Konsolenanweisung:

$ mysqladmin reload -u root -p

12.4.3 Import und Export von Daten, Backups

Für einfache, manuelle Backups von MySQL-Datenbanken und -Tabellen kann das Konsolenprogramm mysqldump verwendet werden. Es erzeugt SQL-Dumps der entsprechenden Strukturen und Daten, das heißt Dateien mit SQL-Anweisungen, die die entsprechenden Tabellen erstellen und die Daten einfügen.

Die Syntax dieser Anweisung ist etwas unterschiedlich, je nachdem, ob Sie einzelne Tabellen, einzelne Datenbanken oder alle Datenbanken Ihres Servers exportieren möchten. Für einzelne Tabellen lautet die Syntax:

mysqldump [Optionen] Datenbank [Tabelle ...]

Wenn Sie keine Tabellenbezeichnungen angeben, wird die gesamte Datenbank exportiert.

Falls Sie mehrere Datenbanken exportieren möchten, müssen Sie folgende Schreibweise verwenden:

mysqldump [Optionen] --databases Datenbank
          [Datenbank ...]

Für einen Export des gesamten Datenbestands des Servers gilt schließlich diese Syntax:

mysqldump [Optionen] --all-databases

Wie alle MySQL-Konsolenhilfsprogramme benötigt auch mysqldump Benutzername und Passwort, sodass Sie unter den Optionen auf jeden Fall -u Benutzername (hier meist root) und -p (Passworteingabeaufforderung) angeben müssen.

Angenommen, Sie möchten die Datenbank supermarkt exportieren. Dazu ist folgende Eingabe erforderlich:

$ mysqldump -u root -p supermarkt

Wie Sie feststellen werden, erfolgt die Ausgabe auf STDOUT, sodass Sie sie in eine Datei umleiten müssen, um etwas Sinnvolles damit anfangen zu können:

$ mysqldump -u root -p supermarkt >supermarkt.sql

Die Dateiendung .sql ist keine Bedingung, bietet sich aber an, da die entstehende Datei eben SQL-Anweisungen enthält.

Wenn Sie »Live-Datenbanken« betreiben, die in frequentierten Netzwerk- oder Webanwendungen eingesetzt werden, genügt der einfache Aufruf von mysqldump nicht mehr. Das Problem ist, dass sich noch während des Backup-Vorgangs Daten ändern können, was zu Inkonsistenzen führt. Hier müssen Sie dafür sorgen, dass der Backup-Vorgang vor Änderungen geschützt abläuft. Wie das im Einzelnen funktioniert, hängt davon ab, welche Storage Engine die Tabelle der entsprechenden Datenbank verwendet.

InnoDB-Tabellen sind transaktionsfähig und können daher innerhalb einer Transaktion exportiert werden, die komplett vor eventuellen gleichzeitigen Änderungen geschützt abläuft. Dazu wird die Option --single-transaction verwendet. Insgesamt hat ein solcher Aufruf folgende Syntax:

mysqldump -u root -p --single-transaction \
Datenbank >Datei

In älteren MySQL-Versionen (der Wechsel erfolgte im Verlauf der Weiterentwicklung von MySQL 5.0) war dies etwas komplizierter; es musste eine Zeile wie diese verwendet werden:

mysqldump -u root -p --skip-opt --single-transaction \
--add-drop-table --create-options --quick \
--extended-insert --set-charset --disable-keys \
Datenbank >Datei

Das Problem bestand darin, dass die Option --single-transaction mit den Standardoptionen von mysqldump inkompatibel war, sodass diese mithilfe von --skip-opt übersprungen und dann zum Teil wieder einzeln gesetzt werden mussten.

Ein korrektes Backup von MyISAM-Tabellen bei einem frequentierten Server benötigt dagegen zusätzliche Anweisungen, um während des Dump-Vorgangs Sperren (Locks) auf die entsprechenden Tabellen zu setzen:

mysql -e "flush tables with read lock" -u root -p
mysqldump -u root -p DATENBANK >DATEI
mysql -e "unlock tables" -u root -p

Die mysql-Option -e startet den mysql-Client nicht interaktiv, sondern führt die angegebene Anweisung aus. Die MySQL-Anweisung FLUSH TABLES WITH READ LOCK schreibt alle zurzeit »schwebenden« Änderungen ordnungsgemäß auf die Festplatte und sperrt dann alle Tabellen sämtlicher Datenbanken vollständig. UNLOCK TABLES setzt die Sperren wieder zurück.

Leider müssen Sie bei dieser Anweisungsfolge dreimal das root-Passwort des MySQL-Servers eingeben. Daher lässt es sich in dieser Form auch nicht per Cronjob automatisieren (siehe Kapitel 7, »Linux«). Zwar können Sie das Passwort notfalls im Klartext ohne Abstand hinter die Option -p schreiben – aber das sollten Sie auf keinen Fall mit dem root-Passwort machen! Dafür empfiehlt es sich eher, einen speziellen Backup-Benutzer mit eingeschränkten Rechten zu erstellen. Er benötigt die globalen Rechte RELOAD (für FLUSH TABLES), LOCK TABLES (für das Sperren und Entsperren) sowie SELECT für das Auslesen der Daten zum eigentlichen Backup:

mysql> CREATE USER backupuser@localhost
    -> IDENTIFIED BY '84ckUp2';
mysql> GRANT RELOAD, LOCK TABLES, SELECT ON *.*
    -> TO backupuser@localhost;

Anschließend können Sie die drei Anweisungen für MyISAM-Backups wie folgt als Shell-Skript speichern:

mysql -e "flush tables with read lock" \
-u backupuser -p84ckUp2
mysqldump -u backupuser -p84ckUp2 Datenbank >Datei
mysql -e "unlock tables" -u backupuser -p84ckUp2

Alternativ können Sie mysqldump auch gleich mit der zusätzlichen Option -x (Langform: --lock-all-tables) aufrufen, um alle Tabellen während des gesamten Backup-Vorgangs zu sperren. Dies sollte allerdings eher in Stunden mit relativ geringfügigem Zugriff geschehen.

Um eines der zuvor angelegten Backups wieder zurückzuspielen, gibt es zwei Möglichkeiten. Die erste wird auf der Konsole angewendet:

$ mysql -u root -p <Datei

Innerhalb des mysql-Clients können Sie dagegen die bereits besprochene Syntax

mysql> source Datei

oder kurz

mysql> \. Datei

verwenden.

Eine etwas andere Möglichkeit bietet das SQL-Anweisungs-Paar SELECT ... INTO OUTFILE und LOAD DATA INFILE: Hier werden die Daten in sogenannte CSV-Textdateien (Comma-separated Values) exportiert beziehungsweise aus diesen importiert. CSV ist ein beliebtes Datenaustauschformat für Tabellenkalkulationsprogramme wie Excel oder Open-Office.org Calc.

Um Daten in eine CSV-Datei zu exportieren, hängen Sie an eine beliebige SELECT-Abfrage INTO OUTFILE Dateiname an. Beispiel:

mysql> SELECT * FROM artikel INTO OUTFILE
    -> "supermarkt.csv";

Der Import aus einer entsprechenden Datei erfolgt dagegen so:

mysql> LOAD DATA INFILE "supermarkt.csv"
    -> INTO TABLE artikel;

Über diverse Parameter, die hier allerdings zu weit führen würden, können Sie jeweils den genauen Aufbau der CSV-Dateien bestimmen.

12.4.4 Konfigurationsdateien

Wie bereits erwähnt, kommt MySQL im Standardbetrieb recht gut ohne Konfigurationsdateien aus. Sollten Sie dennoch irgendwelche Aspekte seines Verhaltens ändern wollen, können Sie eine der folgenden Dateien anlegen:

• /etc/my.cnf – globale Konfigurationsdatei
• ~/.my.cnf – benutzerspezifische Konfigurationsdatei

Der Aufbau dieser Datei entspricht den bekannten Windows-INI-Dateien (ähnlich wie smb.conf oder php.ini, siehe Kapitel 7, »Linux«, beziehungsweise Kapitel 13, »Server für Webanwendungen«): Abschnitte in eckigen Klammern kennzeichnen die Themen (in diesem Fall die einzelnen MySQL-Programme); die einzelnen Konfigurationsanweisungen werden im Format Parameter = Wert angegeben.

Der wichtigste Abschnitt ist [mysqld] mit Parametern für den Server selbst. Aber auch alle anderen mit MySQL gelieferten Programme lesen die für sie geschriebenen Abschnitte in diesen Dateien aus, zum Beispiel [mysqladmin], [mysqldump] oder der Client [mysql].

Hier einige wenige Parameter für den Server im Überblick:

• port – der TCP-Port, an dem der Server lauscht (Standard 3306; kann geändert werden, um mehrere MySQL-Server auf einem Host zu betreiben)
• socket – das Unix-Domain-Socket (Dateipfad) für die lokale Kommunikation (Standard: /tmp/mysql.sock)
• character-set-server – der Zeichensatz des Servers selbst
• collation-server – die Sortierfolge des Servers
• language – die Sprache für Fehlermeldungen und Warnungen (Sie können beispielsweise auf german umschalten)
• sql-mode – der SQL-Kompatibilitätsmodus; zum Beispiel mysql (Standard) oder ansi (ändert etwa die zuvor diskutierte Bedeutung der verschiedenen Anführungszeichen)

In den Konfigurationsdateien können Sie jeden Wert ändern, dessen aktuelle Einstellung die folgende MySQL-Anweisung zeigt:

mysql> SHOW VARIABLES;

MySQL wird mit einigen Vorlagen für mögliche Konfigurationsdateien geliefert, von my-small.cnf (optimiert für sehr kleine Datenbanken) bis hin zu my-huge.cnf für gigantische Datenbanken. Sie können bei Bedarf eine dieser Dateien nach /etc/my.cnf kopieren und noch etwas an Ihre Bedürfnisse anpassen.

12.4.5 Log-Dateien

MySQL ist in der Lage, verschiedene Log-Dateien anzulegen, die eine wichtige Basis zur Wiederherstellung verlorener Daten, für die Replikation (siehe nächsten Abschnitt) oder zur Fehlersuche bilden.

Standardmäßig wird nur eine Error-Log-Datei angelegt. Sie befindet sich – wie alle Log-Dateien – im MySQL-Datenverzeichnis (weil das mysql-Systembenutzerkonto nur dort Schreibrechte besitzt) und trägt den aktuellen Hostnamen und die Endung .log (Beispiel: tux.log). Mit folgendem Eintrag unter dem Abschnitt [mysqld] in der Datei my.cnf können Sie ihren Ort ändern:

log-error = Pfad

Eine weitere sehr wichtige Log-Datei ist das binäre Update-Log. Es protokolliert alle Änderungsabfragen und dient somit als Basis für die Replikation oder auch für die Wiederherstellung seit dem letzten richtigen Backup.

Der entsprechende Eintrag in /etc/my.cnf lautet:

[mysqld]
...
log-bin = mylog

Dies legt im MySQL-Datenverzeichnis die Dateien mylog.000001 und mylog.index an (statt mylog können Sie auch einen beliebigen anderen Namen eingeben). Bei jedem Neustart des Servers, bei explizitem Flush oder wenn die Datei eine bestimmte Größe überschreitet, wird die nächste Datei (mylog.000002 und so weiter) angelegt.

Um geplant die nächste Log-Datei zu beginnen, können Sie im mysql-Client Folgendes eingeben:

mysql> FLUSH LOGS;

Eine Konsolenalternative ist:

$ mysqladmin flush-logs -u root -p

Wenn Sie eine automatische Log-Rotation per Cronjob (siehe Kapitel 7, »Linux«) durchführen möchten, legen Sie zunächst einen speziellen MySQL-User an, der nur das globale Recht RELOAD besitzt. Danach können Sie ein Skript mit folgender Zeile erstellen:

mysqladmin -u reloaduser flush-logs -p Reloadpasswort

Da die Update-Log-Datei binär ist, können Sie sie nicht in einem Texteditor lesen. Wenn Sie sie im Klartext einsehen möchten, müssen Sie das mit MySQL gelieferte Tool mysqlbinlog verwenden. Beispiel:

$ mysqlbinlog -u root -p mylog.000001

Es gibt noch einige andere mögliche Logs, die Sie durch folgende Einträge in /etc/my.cnf erzeugen können (die Bedeutung wird jeweils durch #-Kommentare beschrieben):

[mysqld]
...
# Zu langsame Abfragen unter HOSTNAME-slow.log
# protokollieren:
log-slow-queries
# Dazu muss definiert werden, wie lange ZU LANGE ist
# (variiert stark je nach Datenbankgröße):
long_query_time = SEKUNDEN
# Ganz allgemein Abfragen protokollieren, die
# keinen Index verwenden (und daher oft optimierbar sind)
log-queries-not-using-indexes

12.4.6 Replikation

Die MySQL-Replikation ermöglicht die automatische Übernahme aller Datenbankänderungen von einem MySQL-Server, dem Master, auf beliebig viele andere MySQL-Server, die Replikations-Slaves. Dies dient zum einen der Ausfallsicherheit – Sie haben stets ein oder gar mehrere sekundenaktuelle Backups zur Hand –, zum anderen auch dem Load-Balancing (Lastverteilung) frequentierter Datenbankanwendungen: Schreibvorgänge müssen zwar weiterhin auf den Master erfolgen, aber Auswahlabfragen können auf die Slaves verteilt werden.

Zur Einrichtung der Replikation sind einige Schritte erforderlich, aber danach läuft diese vollautomatisch. Selbst wenn ein Slave vorübergehend ausfällt, bringt er sich nach einem Neustart des MySQL-Servers selbstständig wieder auf den neuesten Stand.

Auf dem Rechner, der Master werden soll (dieser kann seinerseits auch durchaus Slave eines anderen Masters sein), müssen Sie folgende Schritte ausführen:

• Erstellen Sie einen User für den Replikations-Slave:

  mysql> CREATE USER repl_user@Slave-Host
      -> IDENTIFIED BY "Passwort";
  
  

  Ob Sie den Slave-Host als einfachen Hostname (etwa heartofgold) angeben können oder ob Sie seinen FQDN (heartofgold.test.local) benötigen, hängt von Ihrer Netzwerkkonfiguration ab; im Zweifelsfall funktioniert Letzteres immer.

• Erteilen Sie dem neuen User das Recht REPLICATION SLAVE:

  mysql> GRANT REPLICATION SLAVE ON *.*
      -> TO repl_user@Slave-Host;
  
  

• Richten Sie eine Update-Log-Datei ein, falls Sie noch keine haben. Führen Sie in beiden Fällen folgende Schritte durch, um sich die aktuelle Datei und deren Position zu notieren:

  mysql> FLUSH TABLES WITH READ LOCK;
  mysql> SHOW MASTER STATUS;
  
  

  Merken Sie sich die Angaben File (zum Beispiel mylog.000003) und Position (etwa 345).

• Stoppen Sie den MySQL-Server, zum Beispiel so:

  # mysqladmin shutdown -u root -p
  
  

• Erstellen Sie einen Snapshot des gesamten Datenverzeichnisses; hier ein Unix-Beispiel:

  # cd <MySQL-Datenverzeichnis>
  # tar czvf snapshot.tgz */* ibdata*
  
  

  Die ibdata*-Dateien existieren übrigens nur, wenn Sie InnoDB-Tabellen verwenden.

• Führen Sie die folgende Änderung in /etc/my.cnf durch:

  [mysqld]
  ...
  server-id = 1
  
  

  Statt der 1 ist auch eine beliebige andere Nummer möglich, solange sie sich von den IDs aller Slaves unterscheidet; beim ersten Master ist 1 aber Standard.

• Zum Schluss müssen Sie den MySQL-Server wieder starten.

Auf dem Slave sind dagegen folgende Vorbereitungen erforderlich:

• Stoppen Sie den MySQL-Server.
• Sichern Sie Ihre bisherigen Daten; unter Unix beispielsweise wie folgt:

  # mv <MySQL-Datenverzeichnis> <NeuerVerzeichnisName>
  
  

• Spielen Sie den Snapshot des Masters ein (den Sie zuvor per SCP, NIS, FTP, E-Mail oder wie auch immer auf den Slave kopiert haben):

  # cd <Verzeichnis-über-MySQL-Datenverzeichnis>
  # tar xzvf snapshot.tgz
  
  

• Passen Sie die Rechte des neuen Datenverzeichnisses an (nur Unix):

  # chown -R mysql:mysql <MySQLDaten>
  
  

• Führen Sie die folgende Änderung in /etc/my.cnf durch:

  [mysqld]
  ...
  server-id = 2
  
  

  Auch hier kann die ID im Grunde beliebig sein; sie muss sich nur vom Master und von allen anderen Slaves unterscheiden.

• Nun können Sie den MySQL-Server wieder starten.
• Geben Sie im MySQL-Kommandozeilen-Client schließlich noch folgende Anweisungen ein:

  mysql> CHANGE MASTER TO
      -> MASTER_HOST =     'Master-Host',
      -> MASTER_USER =     'repl_user',
      -> MASTER_PASSWORD = 'Passwort',
      -> MASTER_LOG_FILE = 'ermittelte Logdatei'
      -> MASTER_LOG_POS =  ermittelte Position;
  mysql> START SLAVE;
  
  

Natürlich müssen Sie die Platzhalter wie 'Master-Host' oder 'ermittelte Logdatei' durch konkrete Werte ersetzen.

Ob die Replikation nun funktioniert, können Sie leicht überprüfen, indem Sie auf dem Master Daten ändern und dann versuchen, diese auf dem Slave zu lesen. Wenn Sie alles richtig gemacht haben, müsste es funktionieren. Falls nicht, überprüfen Sie noch einmal alle Schritte; gegebenenfalls müssen Sie auch nachschauen, ob die Firewall auf einem der beteiligten Hosts den MySQL-Port (standardmäßig 3306) blockiert.

Ihr Kommentar

Wie hat Ihnen das <openbook> gefallen? Wir freuen uns immer über Ihre freundlichen und kritischen Rückmeldungen.