MySQL

MySQL ist eine sehr beliebte Datenbank vor allem im Internetbereich. Sie ist durch die offene Struktur von den wichtigen Serversprachen PERL und PHP leicht anspechbar.

Ursprünglich war MySQL nicht einmal mit einem Transaktionsmechanismus (COMMIT, ROLLBACK) ausgestattet. Der Hersteller begründet die niedrige Priorität dieses Features für die Weiterentwicklung damit, dass dies erhebliche Auswirkungen auf die Performance habe. Und bei dem typischen Einsatzgebiet als Datenbank für Webserver war diese Argumentation durchaus berechtigt. Inzwischen ist auch MySQL transaktionsfähig.

MySQL arbeitet über einen TCP/IP-Server namens mysqld auch dann, wenn die Zielmaschine lokal ist.

Initialisierungsarbeiten

Die Installation erfolgt unter Debian durch Installation des Pakets mysql-server.
apt-get install mysql-server
Dabei wird der Benutzer mysql angelegt, in dessen Namen die Datenbank gestartet wird.

Zum geregelten Herunterfahren des Services gibt es das Skript /etc/init.d/mysql, das mit Argumenten aufgerufen wird, um den Service zu starten (start), zu stoppen (stop) oder den Status zu ermitteln.

AufrufWirkung
/etc/init.d/mysql start Datenbank starten
/etc/init.d/mysql stop Datenbank stoppen
/etc/init.d/mysql restart Datenbank neu starten
/etc/init.d/mysql status Datenbankstatus ermitteln
Um Datenbankbefehle einzugeben, ruft man mit dem Befehl mysql den Interpreter von MySQL auf. Dabei wird hinter der Option -u der Benutzer angegeben. Die Option -p sorgt dafür, dass das Passwort des Benutzers angefordert wird. Als letzter Parameter kann die Datenbank angegeben werden, die verwendet werden soll.

# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.5.47-0+deb7u1 (Debian)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
Mit den Befehlen quit oder exit wird MySQL wieder verlassen.

Administrieren

Der Server erhält mit folgenden Befehlen ein root-Passwort.

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h onyx.willemer.edu password 'new-password'

Anlegen eines Benutzers

Der Administrator kann neue Benutzer anlegen. Hier wird ein User namens dbuser mit dem Passwort geheim für den lokalen Rechner angelegt.

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'geheim';
Mit dem Befehl DROP USER wird der Benutzer wieder gelöscht.
DROP USER 'dbuser'@'localhost';
Soll der Benutzer auch für den Zugriff von außen gelten, wird ein fremder Hostname angegeben oder ein Prozentzeichen als Wildcard.
CREATE USER 'dbuser'@'%' IDENTIFIED BY 'geheim';
Das Passwort kann durch den folgenden Aufruf geändert werden:
SET PASSWORD FOR 'oc_admin'@'localhost' = PASSWORD('geheim');
Soll eine Datenbank angelegt werden, in der der Benutzer arbeiten darf, werden die folgenden beiden Befehle dies tun:
create database dbname;
grant all on dbname.* to dbuser;
Das Tool mysql-administrator ermöglicht Adminstrationsarbeiten mit einer grafischen Benutzeroberfläche. Hier können beispielsweise auch Benutzer angelegt werden.

Um alle Benuter und deren Passwörter anzuzeigen werden die Spalten user, host und password aus der Tabelle mysql.user ausgelesen.

mysql> SELECT user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *ganzganzfurchtbargeheimundverschlüsselt  |
| root             | obelix    | *ganzganzfurchtbargeheimundverschlüsselt  |
| root             | 127.0.0.1 | *ganzganzfurchtbargeheimundverschlüsselt  |
| root             | ::1       | *ganzganzfurchtbargeheimundverschlüsselt  |
|                  | localhost |                                           |
|                  | obelix    |                                           |
| debian-sys-maint | localhost | *ganzganzfurchtbargeheimundverschlüsselt  |
| oc_admin         | localhost | *ganzganzfurchtbargeheimundverschlüsselt  |
| oc_admin         | %         | *ganzganzfurchtbargeheimundverschlüsselt  |
| dbuser           | %         | *ganzganzfurchtbargeheimundverschlüsselt  |
+------------------+-----------+-------------------------------------------+
10 rows in set (0.02 sec)
Die Passwörter sind natürlich verschlüsselt und sind normalerweise eine wilde Folge von hexadezimalen Zeichen. Hier wurde die Ausgabe ein wenig manipuliert.

Datensicherung und Restauration

$ mysqldump --single-transaction owncloud -u root -p >owncloud.sicher.mysql
Enter password: 
Sicherung der Datenbank owncloud. Die Sicherung aller Datenbanken kann so erfolgen:
$ mysqldump --single-transaction --all-databases -u root -p > sicher.sql
Enter password: 

Zugriff über das Netzwerk erlauben

In der Datei /etc/mysql/my.cnf wird unter bind-adress die IP-Adresse des Netzwerkadapters eingetragen, über dessen Netzwerkzugang Zugriffe erlaubt sein sollen. Zunächst steht dort localhost.
bind-address            = 192.168.109.142

Arbeiten mit MySQL

Zum Arbeiten mit der Datenbank steht neben den Programmierschnittstellen eine Client-Kommandozeile namens mysql zur Verfügung. Hier können SQL-Kommandos abgesetzt werden. Der SQL-Standard braucht hier nicht noch einmal erläutert zu werden, da er an
anderer Stelle bereits ausgeführt ist.

Verbindung aufnehmen

Zunächst schauen Sie nach, welche Datenbanken überhaupt angelegt wurden. Sie können sich bei einer Datenbank mit dem folgenden Befehl anmelden.

mysql -D dbname -u user -p

Anschließend wird das Passwort des Benutzers erfragt.

Danach können Sie die üblichen SQL-Befehle absetzen. Vergessen Sie nicht das Semikolon am Ende der Zeile!

Einige Befehle sind spezifisch für mysql. Der Befehl status (hier ohne Semikolon) zeigt den Status von mysql.

show databases; zeigt die eingerichteten Datenbanken.

show tables; zeigt die Tabellen.

Typen

TINYINT 8 Bit
SMALLINT 16 Bit
MEDIUMINT 24 Bit (kein Standard!)
INTEGER oder INT 32 Bit
BIGINT 64 Bit
FLOAT oder FLOAT(4) 4 Byte (max. ca. 10+-38
DOUBLE, REAL oder FLOAT(8) 8 Byte (max. ca. 10+-308
NUMERIC(m,d) oder DECIMAL(m,d) m Byte, sofern m>d, da Stringdarstellung
DATE '1000-01-01' bis '9999-12-31'
DATETIME '1000-01-01 00:00:00' bis '9999-12-31 23:59:59'
TIME '-838:59:59' bis '838:599:59'
CHAR(n) n von 1 bis 255
VARCHAR(n) n von 1 bis 255, nimmt nur soviel Platz wie benötigt ein
TEXT oder BLOB Länge max 216 - 1
MEDIUMTEXT oder MEDIUMBLOB Länge max 224 - 1
LONGTEXT oder LONGBLOB Länge max 232 - 1

Numerische Typen haben optional UNSIGNED. CHAR und VARCHAR können das Attribut BINARY haben. Dies führt dazu, dass eine Sortierung case sensitive ist.

Konstanten

Das Dezimalzeichen ist der Punkt. Strings können durch " und durch ' eingeschlossen werden. Die \-Escapes sind wie die unter C, hinzu kommt \% und \_, da dies ansonsten Wildcards in SQL-Ausdrücken sind.

Weitere Kommandos

CREATE DATABASE Datenbankname
DROP DATABASE Datenbankname
USE DATABASE Datenbankname

Mit CREATE wird eine Datenbank angelegt. In dieser werden die Tabellen erzeugt. Mit DROP wird die Datenbank und alle ihre Tabellen gelöscht. Mit USE wird die Datenbank gewechselt.

OPTIMIZE TABLE Tabellenname

Nach umfangreichem Löschen oder Ersetzen von variablen Feldern kann es sich lohnen, die Tabelle zu optimieren.

MySQL kennt den Befehl REPLACE, der eine Kombination aus DELETE und INSERT auf einer Tabelle mit einem UNIQUE KEY ist.

Import aus Textdateien

LOAD DATA [LOCAL] INFILE 'filename.txt
     INTO TABLE tablename
     [FIELDS [TERMINATED BY '\t'] [OPTIONALLY] ENCLOSED BY " [ESCAPED BY '\\']]
     [LINES TERMINATED BY '\n']
     [(columnname [,columname]*)]

LOCAL bedeutet, dass die Textdatei auf dem Clienthost steht. Fehlt die Angabe, befindet sie sich auf dem Server. Bei letzterem ist der Import natürlich schneller. Man braucht allerdings die entsprechenden Berechtigungen.

Die oben angegebenen Optionen sind der Standard. Ohne Angaben erwartet MySQL also die Felder optionall durch Anführungszeichen begrenzt, aber durch Tabulatorzeichen getrennt und alle Zeilen durch ein Linefeed getrennt.

LOCK und UNLOCK als Alternative zur Transaktion

LOCK TABLES tablename { READ | WRITE }
           [, tablename { READ | WRITE } ]*

READ bedeutet, dass alle Prozesse nur noch auf der Tabelle lesen dürfen. WRITE bedeutet, andere Prozesse dürfen weder Lesen noch Schreiben. Die Sperre wird aufgehoben durch UNLOCK TABLES

Die C-API als Programmierschnittstelle

Für den Zugriff auf die MySQL-Datenbank sind folgende Typen wichtig:
MYSQL Handle zu einer Datenbankverbindung
MYSQL_RES Ein Anfrageresultat, also quasi eine Ergebnistabelle
MYSQL_ROW Ein Ergebniszeile, kann als Array of Strings zugegriffen werden
Ein C-Programm muss zun¨chst <mysql/mysql.h> einbinden. Beim Übersetzen wird die Library mysqlclient angehängt. Je nach UNIX-Version kann dies das explizite Einbinden der socket erforderlich machen.

/* Demonstration fuer den Zugrff auf MySQL ueber die C-API.
 * (C) Arnold Willemer
 */

#include <mysql/mysql.h>

/* prototypes */
void dbInsert(MYSQL *dbHandle);
void dbSelect(MYSQL *dbHandle);

void main()
{
  MYSQL *     dbHandle;

  dbHandle = mysql_init(0);
  dbHandle = mysql_real_connect(dbHandle,
        "localhost",   /* on what host */
        "arnold",      /* the user */
        0,             /* no password */
        "test_arnold", /* the database */
        0,             /* don't change the port number */
        0,             /* don't change the UNIX socket */
        0);            /* client flag */
  if (dbHandle==0) {
    puts("no connect to database");
    return;
  }

  dbInsert(dbHandle);
  dbSelect(dbHandle);

  /* at last close the connection */
  mysql_close(dbHandle);
}

void dbInsert(MYSQL *dbHandle)
{
  if (0!=mysql_query(dbHandle, 
        "INSERT INTO customer(nr, name) VALUES (12, 'Hans Wurst')")) {
    puts("query INSERT was not successful");
  }
}

void dbSelect(MYSQL *dbHandle)
{
MYSQL_RES * dbResult;
MYSQL_ROW   dbRow;
unsigned long *fieldLengths;
unsigned int i, fieldNumbers;

  if (0!=mysql_query(dbHandle, "SELECT * FROM customer")) {
    puts("query was not successful");
  } else {
    /* now we fetch the results to a local buffer */
    dbResult = mysql_store_result(dbHandle);
    if (dbResult==0) {
      puts(" problem with the result ");
    } else {
      fieldNumbers = mysql_num_fields(dbResult);
      while ((dbRow = mysql_fetch_row(dbResult))) {
        fieldLengths = mysql_fetch_lengths(dbResult);
        for(i = 0; i < fieldNumbers; i++) {
          if (dbRow[i]==0) {
            printf(" (null) ");
          } else {
            printf("%s - ",  dbRow[i] );
          }
        }
        printf("\n");
      }
    }
    mysql_free_result(dbResult);
  }
}

Die Anmeldung und Abmeldung erfolgen mit kurzen klaren Schritten. Es wird der Zielhost, der Benutzer und sein Passwort benötigt. Auch die Datenbank wird angegeben.

Das Einfügen von Daten kann wie alle SQL-Befehle, die keine Antwort brauchen recht einfach bewerkstelligt werden, indem ein String konstruiert wird, der an die Datenbank gesandt wird.

Aufwenig wird es wenn ein SELECT eine Menge von Datensätzn liefert. Dies passt nicht zu den satzorientierten Systemen und Programmiersprachen. Dementsprechend wird hier immer ein recht umständlicher Aufwand betrieben, um dem Mengenparadigma von SQL zu genügen. Im Falle von MySQL wird zunächst ermittelt, Zeile für Zeile geholt und dann spaltenweise ausgewertet.

Eine vergleichbare Schnittstelle existiert auch zu PHP.

Java-Programmierung

Der Zugriff auf Datenbanken erfolgt von Java aus per JDBC. Ein typischer Beispielcode lautet:

import java.sql.*;

class testMySQL {
    public static void main(String argv[]) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection =
                DriverManager.getConnection(
                    "jdbc:mysql://localhost/meinedb",
                    "user",
                    "password");
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select * from kunde");
            while (rs.next()) {
                System.out.println("name = " + rs.getString("name"));
            }
        } catch (ClassNotFoundException e) {
            System.out.println("Couldn't load Driver: " + e.getMessage());
        } catch (SQLException e) {
            System.out.println("Couldn't get Connection: " + e.getMessage());
        } catch (Exception e) {
            System.out.println("Problem: " + e.getMessage());
        }
    }
}

Das Aktivieren des Treibers hat bei mir nicht geklappt. Erst als ich die jar-Datei mysql-connector-java-version-bin.jar in die Libraries meines Projekts einband, funktionierte der Zugriff.


Homepage 26.9.2001 (C) Copyright 2001-2007, 2011, 2015 Arnold Willemer