MySQL - Eine EinführungDatenchaosvon Wolfgang Kruck |
MySQL ist ein Multi-User-Datenbankserver, der auf der zur Zeit wohl populärsten Datenbanksprache SQL beruht. MySQL ist eine Client/Server-Implementation und besteht aus einem Datenbankserver mysqld, verschiedenen Client-Programmen und Bibliotheken. Dieser Artikel soll eine Einführung in die Konfiguration und Anwendung von MySQL sein. Es werden dabei die grundlegenden Basiskonfigurationen behandelt, die für den Betrieb des Datenbankservers und den Aufbau einer Datenbank notwendig sind. Ich beziehe mich hier ausschließlich auf die MySQL-Version 3.22.25.
Bei der von mir verwendeten SuSE 6.2 Linux-Distribution kann MySQL bequem über yast installiert werden. Wer eine Distribution verwendet, bei der MySQL nicht dabei ist, fängt am besten mit einen Download von MySQL an [1]. Bei der anschließenden Installation sind die Installationshinweise des MySQL-Pakets zu beachten. Nach erfolgreicher Installation kann die Datenbank gestartet werden. Ich mache nach einer Installation (zumindest auf nicht produktiven Systemen) einen reboot, um zu sehen, ob beim Booten auch alle gewünschten Server starten und ich somit nichts vergessen habe. Um herauszufinden ob die Datenbank läuft, kann man sich nach dem Reboot mit ps ax | greq -i mysql die Prozessliste anschauen. Bei erfolgreicher Installation müssen Einträge der Form wie in Listing 1 vorhanden sein.
| Listing 1: Läuft der mysql-Daemon? |
Nikita:~ # ps ax | grep -i mysql 221 ? SW 0:00 (safe_mysqld) 231 ? S N 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/mysql --skip- 251 ? S N 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/mysql --skip- 252 ? S N 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/mysql --skip- 1887 ? S 0:00 grep -i mysql Nikita:~ # |
Nach der Installation muß erst einmal dem root-User des Datenbankservers (dieser hat nichts mit dem Superuser root von Linux zu tun!) ein Paßwort vergeben werden. Dies geschieht durch einen Aufruf des Programms mysqladmin. Die allgemeine Syntax für einen Aufruf von mysqladmin lautet dabei
mysqladmin [Optionen] Befehl [Befehl ...]
Die hier verwendeten Optionen sind -h, -u und -p. Die Option -h dient zu Angabe eines Hosts (Rechners). Wird -h nicht angegeben, so wird localhost verwendet. Mit Hilfe des Parameters -u kann ein alternativer User angegeben werden, ohne den Parameter -u wird der aktuell angemeldete Linux-User verwendet. Der Parameter -p sorgt dafür, daß mysqladmin vor dem Ausführen des Befehls nach dem Paßwort fragt. Der folgende Befehl setzt das Paßwort des Users root des Datenbankservers auf StrengGeheim.
Nikita:~ # mysqladmin -u root password 'StrengGeheim'
Diese erste Konfiguration kann nun z.B. durch einen Ping geprüft werden.
Nikita:~ # mysqladmin -u root -p ping Enter password: mysqld is alive Nikita:~ #
Die Version des laufenden Datenbankservers kann ebenfalls leicht mit Hilfe von mysqladmin ermittelt werden (siehe Listing 2).
| Listing 2: Welche MySQL-Version läuft auf dem System? |
Nikita:~ # mysqladmin -u root -p version Enter password: mysqladmin Ver 7.11 Distrib 3.22.25, for pc-linux-gnu on i686 TCX Datakonsult AB, by Monty Server version 3.22.25 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 22 min 0 sec Threads: 1 Questions: 135 Slow queries: 0 Opens: 13 Flush tables: 1 Open tables: 9 Nikita:~ # |
Die bisher durchgeführten Befehle wurden am lokalen System abgesetzt. D.h. auf dem System, auf dem auch der Datenbankserver mysqld läuft. Ein Versuch, von einem anderen System aus auf die Datenbank zuzugreifen, wird zu diesem Zeitpunkt vom Datenbankserver abgelehnt. Dies liegt daran, daß der zur Zeit einzige User root des Datenbankservers keine Rechte hat, um von einem anderen System aus auf den Datenbankserver zuzugreifen. Zum Anlegen neuer User sowie zur Vergabe der Rechte der einzelnen User kann der Client mysql verwendet werden.
mysql ist ein interaktives Client-Programm, mit welchem es möglich ist, eine Verbindung zu einem MySQL-Server aufzubauen, Abfragen abzusetzen und die Resultate der Abfragen anzuschauen. mysql kann auch in einem Batch-Modus verwendet werden, der es erlaubt, die Abfragen an die Datenbank aus einer vorbereiteten Datei heraus einzulesen. Die hier verwendeten Parameter -h, -u und -p von mysql haben die gleiche Bedeutung wie bei mysqladmin. Nachfolgend wird eine Verbindung zum Datenbankserver des lokalen Systems aufgebaut.
Nikita:~ # mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 3.22.25 Type 'help' for help. mysql>
Die Verbindung zum Datenbankserver kann mit QUIT, EXIT oder durch Drücken der Tastenkombination Control-d wieder beendet werden. Tabelle 1 zeigt die verschiedenen Promptzeichen, die mysql in den verschiedenen Modi einer Abfrage zeigt.
| Tabelle 1: Promptzeichen von mysql | |
| mysql> | Startklar für einen neuen Befehl |
| -> | Wartet auf die nächste Zeile, bei einem mehrere Zeilen umfassenden Befehl | '> | Wartet auf die nächste Zeile beim Aufsammeln eines Strings, der mit einem einfachen Anführungszeichen (') begonnen hat. |
| "> | Wartet auf die nächste Zeile beim Aufsammeln eines Strings, der mit einem doppelten Anführungszeichen (") begonnen hat. |
Jeder Befehl muß mit einem Semikolon (;) abgeschlossen werden. Die einzigen Ausnahmen sind die Befehle EXIT und QUIT zum Beenden der Verbindung sowie, der Befehl USE. Tabelle 2 zeigt die hier verwendeten MySQL-spezifischen Befehle.
| Tabelle 2: MySQL-spezifische Befehle | |
| EXIT | Beendet die Verbindung zum Datenbankserver und das Programm mysql |
| QUIT | Beendet die Verbindung zum Datenbankserver und das Programm mysql |
| SHOW | Liefert Informationen über Datenbanken und Tabellen |
| USE | Dient zur Auswahl einer Datenbank |
Nach einer Neuinstallation von MySQL sind normalerweise zwei Datenbanken (Databases), mysql und test, vorhanden. Dies kann man mit Hilfe des Befehls SHOW DATABASES; überprüfen.
mysql> SHOW DATABASES; +--------------+ | Database | +--------------+ | mysql | | test | +--------------+ 2 rows in set (0.01 sec) mysql>Die Datenbank test ist eine leere Datenbank, d.h. sie enthält keine Tabellen. In der Datenbank mysql sind unter anderem die User des Datenbankservers und ihre Rechte gespeichert. Mit USE mysql wird dem Datenbank-Server mitgeteilt, daß sich alle folgenden Befehle auf die Datenbank mysql beziehen. Ein anschließendes SHOW TABLES; zeigt die in der Datenbank mysql enthaltenen Tabellen (siehe Listing 3).
| Listing 3: Alle in mysql enthaltenen Tabellen |
mysql> USE mysql Database changed mysql> SHOW TABLES; +-----------------+ | Tables in mysql | +-----------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +-----------------+ 6 rows in set (0.00 sec) mysql> |
Uns interessiert im Folgenden die Tabelle user. In dieser Tabelle sind die Benutzer des Datenbankservers und ihre Rechte gespeichert. Das Verwalten der Tabellen erfolgt natürlich durch SQL-Befehle. Einen ersten Überblick über den aktuellen Inhalt der Tabelle user verschafft man sich durch den SQL-Befehl SELECT * FROM user;. Dieser Befehl liefert ein Listing des kompletten Inhalts der Tabelle user (siehe Listing 4).
| Listing 4: Die Struktur der user-Tabelle |
mysql> SELECT * FROM user; +---------------+------+------------------+-------------+-------------+ | Host | User | Password | Select_priv | Insert_priv | +---------------+------+------------------+-------------+-------------+ | localhost | root | 7de697f809c79bb2 | Y | Y | | Nikita | root | 7de697f809c79bb2 | Y | Y | | localhost | | | N | N | | Nikita | | | N | N | +---------------+------+------------------+-------------+-------------+ ------------+-------------+-------------+-----------+-------------+ Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | ------------+-------------+-------------+-----------+-------------+ Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | ------------+-------------+-------------+-----------+-------------+ ---------------+--------------+-----------+------------+-----------------+ Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | ---------------+--------------+-----------+------------+-----------------+ Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | N | N | N | Y | N | N | N | N | Y | ---------------+--------------+-----------+------------+-----------------+ -----------+------------+ Index_priv | Alter_priv | -----------+------------+ Y | Y | Y | Y | Y | Y | Y | Y | -----------+------------+ 4 rows in set (0.01 sec) mysql> |
Das Feld Host gibt an, von welchem System aus ein User auf den Datenbankserver zugreifen darf. In diesem Beispiel gibt es nur den User root, er darf vom lokalen System (localhost) und vom System Nikita aus auf den Datenbankserver zugreifen. Beides sind lokale Zugriffe, da Nikita der Name des Systems ist, auf dem der Datenbankserver läuft. Alle *_priv-Felder beschreiben die Rechte (Privileges) des jeweiligen Benutzers. Eine Liste der Rechte, mit einer Beschreibung worauf das Recht wirkt, ist in Tabelle 3 zu finden.
| Tabelle 3: Die Rechte (Privileges) der User | ||
| Privilege | Spalte | Wirkt auf |
| select | Select_priv | Tabellen |
| insert | Insert_priv | Tabellen |
| update | Update_priv | Tabellen |
| delete | Delete_priv | Tabellen |
| index | Index_priv | Tabellen |
| alter | Alter_priv | Tabellen |
| create | Create_priv | Datenbanken, Tabellen, Indizes |
| drop | Drop_priv | Datenbanken, Tabellen |
| grant | Grant_priv | Datenbanken, Tabellen |
| reload | Reload_priv | Server-Administration |
| shutdown | Shutdown_priv | Server-Administration |
| process | Process_priv | Server-Administration |
| file | File_priv | File-Zugriff auf dem Server |
Um zusätzliche Datenbankbenutzer einzurichten, müssen neue Einträge in der Tabelle user erzeugt werden. Diese werden mit dem SQL-Befehl INSERT eingefügt. INSERT trägt dabei die Daten zeilenweise in eine Tabelle ein und prüft die einzutragenden Werte auf ihre Konsistenz mit der Tabellendefinition. Werden keine Fehler gefunden, dann wird die neue Zeile in die Tabelle aufgenommen. Nachfolgend wird der neue User wolfgang eingerichtet. Er darf von jedem Rechner aus auf den Datenbankserver zugreifen (Host = '%'), hat die gleichen Rechte wie root und erhält das Paßwort test. Das Paßwort muß mit Hilfe der Funktion PASSWORD() eingegeben werden. Diese Funktion sorgt dafür, daß das Paßwort verschlüsselt in die Datenbank eingetragen wird.
mysql> INSERT INTO user
-> VALUES('%','wolfgang',PASSWORD('test'),
-> 'Y','Y','Y','Y','Y','Y','Y',
-> 'Y','Y','Y','Y','Y','Y','Y');
Query OK, 1 row affected (0.01 sec)
mysql>
Beim Einrichten neuer User ist Folgendes zu beachten:
Für eine detaillierte Beschreibung der Steuerung des Zugriffs auf den Datenbankserver und auf die einzelnen Datenbanken verweise ich auf die MySQL-Dokumentation Kapitel 6: The MySQL access privilege system. Die Dokumentation ist z.B. bei der SuSE-Distribution unter /usr/doc/packages/mysql/html/index.html oder Online bei [1] zu finden.
Nachdem die Benutzer der Datenbank eingerichtet und ihre Rechte vergeben sind, kann dazu übergegangen werden, neue Datenbanken aufzubauen. Dazu wird zuerst eine neue Datenbank angelegt und anschließend als aktuelle Datenbank für die folgenden Befehle festgelegt.
mysql> CREATE DATABASE MyDB; Query OK, 1 row affected (0.00 sec) mysql> USE MyDB Database changed mysql>Um Daten in der Datenbank MyDB speichern zu können, wird noch mindestens eine Tabelle benötigt. Dazu wird als Beispiel eine Tabelle Adressen mit den Spalten Vorname, Name, Strasse, PLZ, Ort und Geburtstag angelegt. Siehe Tabelle 4 für eine Liste der Typen, die für die Spalten der Tabellen verwendet werden können.
mysql> CREATE TABLE Adressen (
-> Vorname VARCHAR(25),
-> Name VARCHAR(25),
-> Strasse VARCHAR(40),
-> PLZ INT(5),
-> Ort VARCHAR(40),
-> Geburtstag DATE );
Query OK, 0 rows affected (0.00 sec)
mysql>
| Tabelle 4: Die wichtigsten MySQL-Datentypen | |
| BLOB | Ein Binär-String mit einer maximalen Länge von 65535 Zeichen. |
| CHAR(N) | Ein String mit der festen Länge N, wobei für N Werte von 1 bis 255 möglich sind. |
| DATE | Ein Datum in Bereich von '1000-01-01' bis '9999-12-31'. Das Format eines Datums in MySQL ist 'YYYY-MM-DD'. |
| INT[(N)] [UNSIGNED] | Eine Integer-Zahl im Bereich von -2147483648 bis 2147483647. Der unsigned-Bereich geht von 0 bis 4294967295 |
| TEXT | Ein String mit einer maximalen Länge von 65535 Zeichen. |
| TIME | Eine Zeit im Bereich von '-838:59:59' bis '838:59:59'. Das Format eines TIME-Wertes ist 'HH:MM:SS'. |
| VARCHAR(N) | Ein String mit einer variablen Länge. Die maximale Länge des Strings ist N, wobei für N Werte von 1 bis 255 möglich sind. |
Der letzte Schritt im Erstellen einer Datenbank ist das Einfügen von Daten in die Tabelle. Dies kann manuell erfolgen oder aber durch vorbereitete Dateien, in denen die entsprechenden SQL Befehle stehen. Ich habe z.B. eine Datei Adressen.txt vorbereitet (siehe Listing 5), in welcher die notwendigen Schritte zum Erstellen einer Tabelle Adressen und das Einfügen der Daten vorbereitet sind. Mit EXIT wird an dieser Stelle das Client-Programm mysql verlassen, um anschließend die Datei Adressen.txt durch Verwenden des Batch Modus von mysql einzulesen. Im folgenden Aufruf von mysql bezeichnet MyDB dabei die von uns zuvor angelegte Datenbank, in die die Tabelle und die Daten eingefügt werden sollen.
mysql> exit Bye Nikita:~ # mysql -u root -p MyDB < Adressen.txt Enter password: Nikita:~ #
| Listing 5: Die Batchdatei Adressen.txt |
# Anlegen der Tabelle Adressen
#
CREATE TABLE Adressen (
Vorname VARCHAR(25),
Name VARCHAR(25),
Strasse VARCHAR(50),
PLZ INT(5),
Ort VARCHAR(50),
Geburtstag DATE
);
# Daten in der Tabelle Adressen speichern
#
INSERT INTO Adressen VALUES("Hugo", "Müller", "Lindenstraße 25",
12345, "Oberdorf", "1970-01-25");
INSERT INTO Adressen VALUES("Tina", "Maier", "Gartenweg 18",
12345, "Oberdorf", "1969-11-09");
INSERT INTO Adressen VALUES("Claus", "Smith", "Baumstraße 44",
12345, "Oberdorf", "1973-06-17");
INSERT INTO Adressen VALUES("Kerstin", "Müller", "Hauptstraße 2",
12345, "Oberdorf", "1968-08-15");
|
Nach dem Anlegen der Tabelle in der Datenbank MyDB kann man sich durch einige Abfragen an die Datenbank vom Erfolg der Aktion überzeugen. Nach dem Anmelden am Datenbank Server mit mysql und dem Wechsel zur Datenbank MyDB (USE MyDB;) können die verfügbaren Tabellen der Datenbank mit dem Befehl SHOW TABLES; angezeigt werden (siehe Listing 6).
| Listing 6: Die neu erzeugten Tabellen in MyDB |
Nikita:~ # mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 40 to server version: 3.22.25 Type 'help' for help. mysql> USE MyDB Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +----------------+ | Tables in MyDB | +----------------+ | Adressen | +----------------+ 1 row in set (0.00 sec) mysql> |
Nicht nur das Vorhandensein der Tabelle Adressen ist von Interesse, auch die Struktur der Tabelle gilt es zu prüfen. Ob eine Tabelle Felder enthält die nicht leer sein dürfen kann mit dem SQL Befehl DESCRIBE geprüft werden (siehe Listing 7).
| Listing 7: Ausgabe des DESCRIBE-Befehls |
mysql> DESCRIBE Adressen; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | Vorname | varchar(25) | YES | | NULL | | | Name | varchar(25) | YES | | NULL | | | Strasse | varchar(50) | YES | | NULL | | | PLZ | int(5) | YES | | NULL | | | Ort | varchar(50) | YES | | NULL | | | Geburtstag | date | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) mysql> |
Als letztes kann man sich noch davon überzeugen, daß alle in der Datei Adressen.txt enthaltenen Adressen in die Tabelle übernommen wurden (siehe Listing 8).
| Listing 8: Alle Adressdaten imÜberblick |
mysql> SELECT * FROM Adressen; +---------+--------+-----------------+-------+----------+------------+ | Vorname | Name | Strasse | PLZ | Ort | Geburtstag | +---------+--------+-----------------+-------+----------+------------+ | Hugo | Müller | Lindenstraße 25 | 12345 | Oberdorf | 1970-01-25 | | Tina | Maier | Gartenweg 18 | 12345 | Oberdorf | 1969-11-09 | | Claus | Smith | Baumstraße 44 | 12345 | Oberdorf | 1973-06-17 | | Kerstin | Müller | Hauptstraße 2 | 12345 | Oberdorf | 1968-08-15 | +---------+--------+-----------------+-------+----------+------------+ 4 rows in set (0.00 sec) mysql> |
An dieser Stelle ist die neue Datenbank MyDB vollständig angelegt und geprüft. Es kann dazu übergegangen werden, die Datenbank zu nutzen. Es folgt nun ein kleiner Exkurs durch einige der möglichen Abfragen, die an eine Datenbank gestellt werden können.
Um ein wenig Nutzen aus unserer kleinen Datenbank zu ziehen kann z.B. das Alter der Personen in der Tabelle Adressen ermittelt werden (siehe Listing 9).
| Listing 9: Ermitteln des Alters mit SQL |
mysql> SELECT Vorname,Name,(TO_DAYS(NOW())-TO_DAYS(Geburtstag))/365
-> FROM Adressen;
+---------+--------+------------------------------------------+
| Vorname | Name | (TO_DAYS(NOW())-TO_DAYS(Geburtstag))/365 |
+---------+--------+------------------------------------------+
| Hugo | Müller | 29.61 |
| Tina | Maier | 29.82 |
| Claus | Smith | 26.21 |
| Kerstin | Müller | 31.05 |
+---------+--------+------------------------------------------+
4 rows in set (0.00 sec)
mysql>
|
Dies läßt sich noch etwas besser lesbar gestalten, indem man der Berechnung einen Namen gibt, und das Ganze dann noch nach dem Alter sortiert ausgibt (siehe Listing 10). Am liebsten hätte ich die dritte Spalte 'Alter' genannt, doch ALTER ist ein SQL-Schlüsselwort und kann nicht verwendet werden. Ich weiche deshalb auf das Englische 'age' aus.
| Listing 10: Neue Namen durch AS-Umbenennungen |
mysql> SELECT Vorname,Name,((TO_DAYS(NOW())-TO_DAYS(Geburtstag))/365) AS age
-> FROM Adressen ORDER BY age;
+---------+--------+-------+
| Vorname | Name | age |
+---------+--------+-------+
| Claus | Smith | 26.21 |
| Hugo | Müller | 29.61 |
| Tina | Maier | 29.82 |
| Kerstin | Müller | 31.05 |
+---------+--------+-------+
4 rows in set (0.00 sec)
mysql>
|
Eine klassische Datenbankanwendung ist natürlich das Suchen nach Begriffen. Das folgende Beispiel zeigt eine solche Suche nach allen Namen, welche mit der Buchstabenfolge 'er' enden (siehe Listing 11).
| Listing 11: Einfacher SELECT-Befehl |
mysql> SELECT * FROM Adressen WHERE Name LIKE '%er'; +---------+--------+-----------------+-------+----------+------------+ | Vorname | Name | Strasse | PLZ | Ort | Geburtstag | +---------+--------+-----------------+-------+----------+------------+ | Hugo | Müller | Lindenstraße 25 | 12345 | Oberdorf | 1970-01-25 | | Tina | Maier | Gartenweg 18 | 12345 | Oberdorf | 1969-11-09 | | Kerstin | Müller | Hauptstraße 2 | 12345 | Oberdorf | 1968-08-15 | +---------+--------+-----------------+-------+----------+------------+ 3 rows in set (0.02 sec) mysql> |
Das Prozent-Zeichen (%) ist ein SQL Wildcard-Zeichen und steht für eine beliebige Anzahl von Zeichen. Ein weiteres Wildcard-Zeichen ist der Unterstrich (_). Es steht für exakt ein Zeichen.
Es geht! MySQL bietet eine Vielzahl von Schnittstellen z.B. für C/C++, Perl, Eiffel, Java, PHP, ODBC, Python/Zope und TCL. Dies ermöglicht das Einbinden der Datenbank in eigene Anwendungen, die den jeweiligen Bedürfnissen exakt angepaßt und leicht zu bedienen sind. Auch ohne eigene Programmierung ist ein komfortabler Umgang mit der Datenbank möglich. Ein bekannter Client für MySQL ist z.B. phpMyAdmin [4]. Diese Datenbankoberfläche nutzt, wie schon der Name sagt, die Schnittstelle von MySQL zu PHP und ist eine Web-basierte Anwendung, d.h., zum Betrieb von phpMyAdmin ist ein laufender Apache-WebServer [2] und PHP3 [3] notwendig. Bei der SuSE-Distribution muss man dazu die Pakete apache und mod_php (beide Serie n) installieren. phpMyAdmin kann man sich bei [4] downloaden.
Natürlich gibt es auch jede Menge KDE- und GNOME-Applikationen, die den bequemen Umgang mit MySQL und anderen Datenbanken erlauben, dazu vermutlich mehr in einer späteren Ausgabe. Und wer nach dem Lesen dieses Artikels Lust auf mehr erhalten hat, sollte unbedingt einen Blick in das neue O'Reilly-Buch [6] werfen, das sehr ausführlich auf die Datenbanken MySQL und mSQL eingeht.
| Infos |
|
[1] MySQL: http://www.mysql.org
|
| Der Autor |
|
Dipl. Ing. (FH) Wolfgang Kruck ist Software-Entwickler. Wenn er nicht vor seinen Computern sitzt und Programme schreibt oder Konfigurationen testet, dann kann man ihn beim Mountainbikefahren in den Alpen oder in Südfrankreich treffen. |
Copyright © 1999 Linux-Magazin Verlag