Prof. Dr.-Ing. Oliver Radfelder
Informatik / Wirtschaftsinformatik
Hochschule Bremerhaven
MySQL/MariaDB - ganz schnell

In unserer Infrastruktur gibt es einen MariaDB-Server, auf dem jede/r eine eigene Datenbank besitzt. Wenn Sie auf hopper angemeldet sind, rufen Sie das Programm mariadb auf, damit Sie mit Ihrer Datenbank verbunden werden. Dort können Sie sich mit:

SHOW TABLES;

alle Tabellen in Ihrer Datenbank anzeigen lassen.

Um eine einfache Tabelle anzulegen, erzeugen Sie sie mit 

CREATE TABLE demo (
  name VARCHAR(256),
  age INTEGER
  );

Damit wurde eine Tabelle mit dem Namen demo angelegt und mit einer Spalte mit Namen name, in der jeweils eine maximal 256 Zeichen lange Zeichenkette stehen kann und eine Spalte vom Typ Integer mit dem Name age.

Das Programm mariadb verlassen Sie mit quit oder exit.

Mit einer INSERT-Anweisung werden einzeln Zeilen in die Tabelle eingefügt:

INSERT INTO demo values ('ich', 30);
INSERT INTO demo values ('du', 40);

Mit einer SELECT-Anweisung werden alle Zeilen aus einer Anfrage (Tabelle) herausgefiltert, die einem bestimmten Kriterium entsprechen:

SELECT *
FROM demo
WHERE name = 'du' OR age = 40;

Der WHERE-Teil kann weggelassen werden, wenn alle Zeilen ausgegeben werden sollen. Statt des Sternchens kann auch explizit angegeben werden, welche Spalten in welcher Reihenfolge ausgegeben werden sollen:

SELECT age, name
FROM demo;

Mit der UPDATE-Anweisung werden jeweils alle Zeilen verändert, die das mit der WHERE-Klausel bestimmte Kriterium erfüllen:

UPDATE demo
SET name = 'DU'
WHERE name = 'du';

Ähnlich verhält es sich mit der DELETE-Anweisung: Es werden alle Datensätze gelöscht, die das in der WHERE-Klausel bestimmte Kriterium erfüllen:

DELETE FROM demo 
WHERE name = 'DU';

Als Gegenstück zum Erzeugen von Tabellen gibt es natürlich auch die Möglichkeit, sie wieder zu entfernen:

DROP TABLE demo;

Damit haben Sie schon die Möglichkeit, einfache Tabellen anzulegen, abzufragen und sie zu verändern. Was ein gutes Datenbankdesign ist und warum SQL als so eine mächtige Idee betrachtet wird, dazu gibt es Vorlesungen - und natürlich können Sie sich selbst mit den entsprechenden Tutorials schlau machen (z.B. https://www.w3schools.com/sql/, https://www.w3schools.com/mysql/, https://mariadb.org/documentation/ ).

Insbesondere der Datentyp Timestamp mit der Belegung DEFAULT ist für viele Anwendungen interessant. Zusätzlich sollten Sie schauen, dass Ihnen das Konzept des Index vertraut ist, um die Bearbeitung von Anfragen mit Bedingungen und sortierten Ausgaben zu beschleunigen.

MySQL/MariaDB betten sich recht angenehm in die Linux-Infrastruktur ein: Zum Einen können Sie dem Programm mariadb auf der Standardeingabe SQL-Befehle übergeben - was natürlich zum Skripten und Automatisieren recht interessant ist:

mariadb < textdatei_mit_sql_anweisungen.sql

Zum Anderen lassen sich die Ergebnisse natürlich auch - wieder automatisiert - recht gut weiterverarbeiten - mit bash, perl, python etc:

echo 'select * from demo' | mariadb -s | tr '\t' '|'

Statt des tr-Aufrufes können Sie natürlich auch mit while-read arbeiten - dann muss man allerdings darauf achten, dass von mariadb das Tab-Zeichen als Trenner ausgegeben wird - für den Fall, dass Felder auch Leerzeichen enthalten können, muss der IFS (Internal Field Separator) auf das Tab-Zeichen reduziert werden:

echo 'select * from demo order by id limit 0,10' | mariadb -s | \
while IFS=$'\t' read -r a b c; do echo "[$a][$b]"; done

In mariadb gibt es mittlerweise das Konstrukt insert into ... returning, was insbesondere in Web-Anwendung hilfreich ist, weil man oft direkt nach dem insert die erzeugte ID benötigt:

mariadb -sN -e 'insert into demo (name) values ("moin") returning id'

Wie Sie mit php auf MySQL/MariaDB zugreifen, finden Sie in dem kleinen PHP-Tutorial unter: php.html!

In Java benötigen Sie den passenden mariadb-Treiber, den Sie in Ihrem Docker in der tomcat-Installation finden (/opt/tomcat/lib/) und dort *nicht* selbst einbinden müssen.

public class Demo {
  public static void main(String[] args)throws Exception{
    String userName,password,url;
    Connection con;
    Statement st;

    userName="YOUONHOPPER";
    password="YOURDBPASSWORD";
    url="jdbc:mariadb://mysql-server:3306/YOURDBNAME";
    con=DriverManager.getConnection(url, userName, password);
    System.out.println("Connection is successful");
    Statement statement = con.createStatement();
    ResultSet resultSet = statement.executeQuery("select * from demo");
    while(resultSet.next()){
      System.out.println(resultSet.getString("name"));
    }
    con.close();
  }
}

Die passenden Daten finden Sie wieder in ~/.my.cnf. In einem normalen Java-Programm müssen Sie beim Start aber dafür sorgen, dass der Datenbank-Treiber im CLASSPATH liegt.

export CLASSPATH=/opt/tomcat/lib/mariadb-java-client-*.jar
CLASSPATH=.:$CLASSPATH
javac Demo.java && java Demo

Denken Sie daran, dass Passwörter und zumeist auch Server und Datenbank nicht in Quellcode gehören. Eine Variante ist, die Daten aus der .my.cnf herauszulesen:

FileReader in = new FileReader(
   String.format("%s/.my.cnf", System.getProperty("user.home")));
Properties cred = new Properties();
cred.load(in);
String url = String.format("jdbc:mariadb://mysql-server:3306/%s", cred.getProperty("database"));
Connection con = DriverManager.getConnection(url, cred.getProperty("user"), cred.getProperty("password"));

Generationen von angehenden Datenbank-Expert:innen haben anhand der bekannten DVD-Rental-Datenbank (sakila, github-sakila) den Umgang mit SQL gelernt. Wenn Sie das auch tun möchten, kopieren Sie sich die Mariadb-Variante in Ihren Docker-Container, packen das Archiv aus, ersetzen Sie alle Vorkommen von sakila durch Ihren Datenbank-Namen und importieren Sie die Tabellen. Achtung, dabei entfernen Sie alle bisher dort exisitierenden Tabellen!

scp /home/common/data/sakila-db.tar.gz mydocker:
ssh mydocker
tar -zxvf sakila-db.tar.gz
cd sakila-db
sed -i 's/sakila/demo_db/g' sakila-schema.sql sakila-data.sql
cat sakila-schema.sql sakila-data.sql | mariadb
mariadb -e "insert into city (city,country_id) values ('Bremerhaven',38)"
mariadb -e "
select * from city left join country 
              on city.country_id = country.country_id 
              where country.country='Germany'"

Dump and Restore:

Um das Programm mariadb-dump in aktuellen Versionen zu nutzen, ist es besser, die .my.cnf-Datei etwas anzupassen:

[client]
user=demo
password=ihrganzgeheimespasswort
host=mysql-server

[mariadb-client]
database=demo_db

In den Docker-Containern wird die Datei entsprechend erzeugt. In Ihrem hopper-Account kann es sein, dass Sie das noch anpassen müssen.

Damit können Sie aber nun den aktuellen Zustand dumpen und das Ergebnis wieder einspielen. Für Testkonstellationen und Backups ist das unentbehrlich.

mariadb-dump --databases demo_db > dump.sql
mariadb < dump.sql

Durch das Wiedereinspielen werden allerdings Tabellen, Trigger und Routinen, die nach dem Dump erzeugt wurden, nicht gelöscht - was aber durchaus bisweilen, um einen je umfassend definierten Zustand zu erzeugen, sinnvoll sein kann. Sie können allerdings innerhalb einer Transaktion Ihre Datenbank komplett entfernen und neu erzeugen:

echo 'DROP SCHEMA IF EXISTS demo_db; CREATE SCHEMA demo_db; USE demo_db; ' > reset.sql
mariadb < reset.sql
# bzw.:
cat reset.sql dump.sql | mariadb

Vergessen Sie also bitte wirklich nicht, dass das in einer Transaktion oder zumindest Sitzung geschehen muss! Wenn Sie nicht wollen, dass Ihre bisherigen Daten gelöscht werden, entfernen Sie die entspechenden Zeilen DROP SCHEMA IF EXISTS... und CREATE SCHEMA ....

Wie Sie eine eigene Mariadb-Instanz in Ihrem docker-Container oder in der VM verwalten, finden Sie unter docker.html.