Schlagwort-Archive: mysql

mysql_upgrade – prüft und aktualisiert MySQL-Tabellen

Vergangenes Wochenende hat KOFREZO unseren Server von Ubuntu Trusty Tahr auf die aktuelle LTS-Version Xenial Xerus aktualisiert. Einhergehend mit dem Release-Upgrade wurde MySQL auf Version 5.7 aktualisiert.

Beim ersten Versuch, ein Backup meiner Datenbank mit mysqldump zu erzeugen, wurde ich überraschend mit folgender Fehlermeldung konfrontiert:


mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682)

In der Folge wurde kein Backup erstellt.

Die Lösung für dieses Problem fand ich nach kurzer Internetrecherche im Werkzeug mysql_upgrade. Dieses Programm untersucht alle Datenbanken und Tabellen auf Kompatibilität mit der aktuell installierten Version des MySQL-Servers. Dabei werden die entsprechenden Datenbanken und Tabellen falls nötig automatisch aktualisiert. Die Manpage (MYSQL_UPGRADE(1)) empfiehlt die Ausführung des Programms nach jedem Upgrade von MySQL.

Achtung: Das im Folgenden gezeigte Kommando hat mein Problem zwar gelöst, jedoch erfolgt die Ausführung auf eigene Gefahr. Ich selbst habe das Kommando das erste Mal verwendet und bin alles andere als ein Experte dafür. Dieser Post dient mir vor allem zur Erinnerung, wie ich das aktuelle Problem lösen konnte.

Das aktuelle Problem wurde wie folgt gelöst:

  1. Sicherstellen, dass MySQL läuft mit sudo systemctl status mysql.service
  2. Ausführung von mysql_upgrade -u root --skip-sys-schema
  3. Neustart von MySQL mit sudo systemctl restart mysql.service

Fertig. Hoffentlich erinnere ich mich beim nächsten MySQL-Update an diesen Beitrag.

Häufig benötigte MySQL-Befehle

In diesem Artikel dokumentiere ich die von mir am häufigsten verwendeten und am schnellsten vergessenen MySQL-Befehle. So muss ich sie nicht jedes Mal in der offiziellen Dokumentation nachschlagen.1

Der Vollständigkeit halber beginne ich mit dem Befehl, mit dem man sich mit einem MySQL-Server verbindet, welcher auf dem localhost läuft.

$ mysql -u BENUTZERNAME -p
Password: 

Eine neue Datenbank kann mit dem folgenden Befehl erstellt werden2:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Beispiel:

CREATE DATABASE db_name;

Einen MySQL-Benutzeraccount erstellt man mit dem Befehl3:

CREATE USER user_specification [, user_specification] ...

user_specification:
    user [ identified_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

Beispiel:

CREATE USER 'pusemuckel'@'localhost' IDENTIFIED BY 'password';

Mit dem folgenden Kommando werden dem erstellten Benutzer Zugriffsrechte auf die erstellte Datenbank gewährt.4 Mit diesen Berechtigungen kann der Benutzer neue Tabellen in der Datenbank erstellen, Daten in Tabellen einfügen oder auch die gesamte Datenbank löschen.

GRANT ALL PRIVILEGES ON db_name.* TO 'pusemuckel'@'localhost';

Die letzten beiden Schritte können auch verkürzt mit folgendem Kommando ausgeführt werden:

GRANT ALL ON db_name.* TO 'pusemuckel'@'localhost' IDENTIFIED BY 'password';

Hat man neue Benutzer angelegt, oder die Berechtigungen bestehendender Benutzer geändert, werden die neuen Berechtigungen mit dem folgenden Kommando geladen:

FLUSH PRIVILEGES;

So, nun muss ich mich zukünftig nur noch daran erinnern, hier nachzuschauen, wenn mir mal wieder die Syntax entfallen ist. 😉

MySQL Relay log read failure beheben

Vor nicht allzu langer Zeit habe ich, mit Hilfe dieses Artikels, eine MySQL Master-/Slave-Replikation aufgebaut.

Heute ist es dann passiert. Nach einem Absturz des MySQL-Slave Servers funktionierte die Replikation nicht mehr. Die Ausgabe von

mysql> SHOW SLAVE STATUS\G

offenbarte folgende Fehlermeldung:

Relay log read failure: Could not parse relay log event entry. The possible reasons are:
the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on
the binary log), the slave's relay log is corrupted (you can check this by running
'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's
MySQL code. If you want to check the master's binary log or slave's relay log, you will
be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

In diesem Fall konnte die Replikation mit den folgenden Schritten repariert werden.

  1. STOP SLAVE;
  2. SHOW SLAVE STATUS\G
  3. Notiert die Einträge für ‚Relay_Master_Log_File‘ und ‚Exec_Master_Log_Pos‘
  4. RESET SLAVE;
  5. CHANGE MASTER TO MASTER_LOG_FILE=’Name_des_Master_Logfiles aus Schritt 3′, MASTER_LOG_POS=exec_master_log_pos aus Schritt 3;
  6. START SLAVE

Mit den oben beschriebenen Schritten werden alle Relay-Logs vom Slave-Server entfernt. Damit werden auch die evtl. korrupten Logs entfernt. Die Replikation beginnt anschließend genau an dem Punkt, an dem sie zuvor abgerissen ist, indem ein neues Binärlog vom Master-Server angefordert wird.

Und siehe da, ich hatte meine Replikation wieder am Fliegen.

MySQL Datenbanken auf neuen Server übertragen

Mit diesem Artikel möchte ich euch ein Tutorial bieten, dass erklärt wie Datenbanken von einem MySQL Server auf einen anderen übertragen werden können. Es kann euch damit bei der Konsolidierung oder Migration von MySQL-Servern gute Dienste leisten.

Um Inkonsistenzen zu vermeiden empfehle ich den MySQL-Quell-Server in den Read-Only Modus zu schalten. Dazu ist zuerst eine Anmeldung am MySQL Server erforderlich.

mysql -u root -p

Einschalten des Read-Only Modus:

mysql> set GLOBAL read_only = true;

Möchte man den Read-Only Modus später wieder abschalten, so meldet man sich wieder am Server an und führt den folgenden Befehl aus.

mysql> set GLOBAL read_only = false;

Es gibt im Prinzip mehrere Möglichkeiten die Datenbanken auf den neuen Server zu transferieren. Ich entscheide mich hier für den Weg der in meisten Fällen zum Ziel führen sollte. Wir können die gewünschten Datenbanken nun mit dem folgenden Befehl sichern. Um die Zeit für den Transfer über das Netzwerk zu reduzieren wird der Dump noch mit gzip gepackt.

shell> mysqldump -u root -p --databases db_name1 [db_name2 ...] >my_databases.sql
shell> gzip my_databases.sql
shell> scp my_databases.sql.gz user@zielserver:/zielverzeichnis/

Auf dem Zielsystem können die Datenbanken nun mit gunzip entpackt und in die Datenbank eingespielt werden.

shell> gunzip my_databases.sql.gz
shell> mysql -u root -p < my_databases.sql

Jetzt müssen wir noch dafür sorgen, dass auf dem neuen MySQL-Server die gleichen Benutzer wie auf dem Quellserver hinzugefügt werden und diese die gleichen Berechtigungen auf die Datenbanken erhalten wie sie sie auf dem Quellsystem besaßen. Ich habe mich dafür entschieden die benötigten Benutzer und Berechtigungszuordnungen mit dem folgenden Abfragemuster aus der mysql-Datenbank des Quellservers in eine Textdatei zu schreiben. Dazu muss ich die Tabellen user und db abfragen.

SELECT someColumn FROM `table`
WHERE foo LIKE '%foo%'
INTO OUTFILE '/tmp/dbdump.txt'

Wie genau obige Abfrage aussieht ist von Fall zu Fall unterschiedlich. In meinem Fall habe ich in zwei Abfragen die Dateien mysql_user.txt und mysql_db.txt erstellt. Diese sind nun auf den Zielserver zu übertragen.

Auf dem Zielserver müssen die Inhalte dieser Dateien nun wieder in die Tabellen user und db der Datenbank mysql eingespielt werden. Wer auf Nummer sicher gehen möchte testet den Import mit der Datenbank test, welche auf jedem MySQL Server vorhanden sein sollte. Das Einlesen der Dateien erledigt man mit dem LOAD DATA INFILE Kommando.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

Bei einigen Ubuntusystemen, auf denen AppArmor läuft kann es zu einem Fehler beim Einlesen der Datei kommen. Wer von diesem Problem betroffen ist, kann hier eine Lösung finden.

Damit ist es geschafft. Unsere Datenbanken sind nun auf dem neuen Server angekommen und wir können den alten im besten Fall abschalten.

MySQL: Passwort in der Datenbank ändern

Meist werden Passwörter als MD5-Hash in einer Tabelle gespeichert. Möchte man nun einem Benutzer ein neues Passwort über die Datenbank vergeben, so geht dies mit dem folgenden Befehl.

update TABLENAME set password=MD5('PASSWORD') where COLUMNAME='USERNAME';

TABLENAME ist der Name der Tabelle, die Benutzername und Passwort enthält. PASSWORD ist das neue Passwort im Klartext, COLUMNAME der Name der Spalte, welche die Benutzernamen enthält und USERNAME ist der Name des Benutzers, dessen Passwort wir setzen wollen.

Werden die Passwörter hingegen im Klartext in der Datenbank gespeichert, lautet der Befehl wie folgt:
update TABLENAME set password=PASSWORD('PASSWORD') where COLUMNAME='USERNAME';

Anschließend führt man noch das folgende Kommando aus und beendet die Sitzung:

FLUSH PRIVILEGES;
quit

MySQL Server manuell starten und stoppen

Auf meinem Notbook (Thinkpad R61 mit Ubuntu 11.10) läuft ein MySQL Server. Diesen nutze ich für gelegentliche Spielereien und wenn ich verschiedene Aufgaben für mein Studium bearbeite.

Da ich diesen Server nicht jedes Mal benötige wenn ich mein Laptop starte möchte ich nicht, dass er automatisch mit Ubuntu mitgestartet wird. Um das Start-/Stop-Verhalten von MySQL zu verändern öffnet man die Datei /etc/init/mysql.conf mit dem Editor seiner Wahl. Im folgenden Abschnitt wird definiert in welchen Runleveln der MySQL Daemon automatisch gestartet wird.

start on (net-device-up
and local-filesystems
and runlevel [2345])
stop on runlevel [016]

Hier löscht man nun einfach die Zahlen aus der [ ]-Klamme heraus, dass der Codeblock wie folgt aussieht:

start on (net-device-up
and local-filesystems
and runlevel [])
stop on runlevel [016]

Und schon wird MySQL nicht mehr automatisch mit Ubuntu gestartet und man kann den Dienst manuell durch eingabe von

sudo service mysql start/stop

starten und beenden.

Kommt gut in die neue Woche.