MySQL

Aus brainelectronics Wiki
Wechseln zu: Navigation, Suche

Installation

Zur Installation wird in den root User gewechselt und die nötigen Packete installiert.

   $ sudo su
   $ apt-get install apache2 php5 mysql-server mysql-client php5-mysql phpmyadmin python-mysqldb
   $ ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
   $ a2enconf phpmyadmin
   $ service apache2 restart 

Mac MySQL Package

   $ sudo pip install MySQL-python

Bei Fehlern: brew install mysql export PATH=$PATH:/usr/local/mysql/bin sudo pip install MySQL-Python

Test

Die Default Start Seite http://localhost sollte nun eine Apache Default Seite anzeigen.

Location

Die Dateien und Datenbanken befinden sich nach der Installation unter

   $ /var/lib/mysql

Alternativer Ort

Soll die MySQL Datenbank z.B. auf einem externen Medium gespeichert werden:

   $ sudo service mysqld stop
   $ cp -rap /var/lib/mysql /mnt/usbstorage/mysql
   $ sudo chown mysql:mysql /mnt/usbstorage/mysql

Anschließend muss noch das neue Datenverzeichnis in der config Datei angeben werden:

   $ sudo nano /etc/mysql/my.cnf
[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
#datadir                = /var/lib/mysql
#### neues verzeichnis
datadir         = /raid1/mysql
####
tmpdir          = /tmp
 

Danach den MySQL Service wieder starten.

   $ sudo service mysqld start

PhpMyAdmin

Anmeldung

Die PhpMyAdmin Seite sollte unter http://localhost/phpmyadmin erreichbar sein.

Einstellungen

Externer Zugriff

Soll die MySQL Datenbank auch von außerhalb, z.B. einem anderen Computer im Netzwerk oder über DynDNS, erreicht werden, so muss die bind-adress = 127.0.0.0 in den MySQL Settings auskommentiert werden.

   $ sudo nano /etc/mysql/my.cnf

und

   bind-adress = 127.0.0.0

mit einem vorangehenden # auskommentieren

   # bind-adress = 127.0.0.0

Nach jeden Änderungen muss der Serive neu gestartet werden.

   $ service mysql restart

Benutzer erstellen

Nun muss den entsprechenden Benutzern noch ein Account angelegt werden.

   $ sudo su
   $ mysql -u root -p
   $ CREATE USER 'neuerExternerNutzer'@'IP-des-externen-Nutzer' IDENTIFIED BY 'sein-passwort';

Soll ein Nutzer von überall Zugriff erhalten können, so muss die letzte Zeile wie folgt lauten:

   $ CREATE USER 'neuerUeberallExternerNutzer'@'%' IDENTIFIED BY 'sein-passwort';

Rechte erteilen

Zugriff auf alle Datenbanken

Soll der Nutzer neuerExternerNutzer jeglichen Zugriff auf alle vorhandenen Datenbanken erhalten, so nutzt man

   $ GRANT ALL ON *.* TO 'neuerExternerNutzer'@'IP-des-externen-Nutzer';
Zugriff auf spezielle Datenbank

Soll dem Nutzer neuerExternerNutzer jeglicher Zugriff einzig auf die Datenbank eine-spezielle-Datenbank gegeben werden, ist folgender Befehl zu verwenden.

   $ GRANT ALL ON eine-spezielle-Datenbank.* TO 'neuerExternerNutzer'@'IP-des-externen-Nutzer';
Zugriff auf spezielle Tabelle

Soll dem Nutzer neuerExternerNutzer jeglicher Zugriff einzig auf die Tabelle eine-Tabelle der Datenbank eine-spezielle-Datenbank gegeben werden, ist folgender Befehl zu verwenden.

   $ GRANT ALL ON eine-spezielle-Datenbank.eine-Tabelle TO 'neuerExternerNutzer'@'IP-des-externen-Nutzer';
Nur Lesezugriff auf eine Datenbank
   $ GRANT SELECT ON eine-spezielle-Datenbank.* TO 'neuerExternerNutzer'@'IP-des-externen-Nutzer' IDENTIFIED BY 'seinPassword';

Ist die IP Adresse des Nutzers nicht bekannt oder soll diese nicht entscheidend für den Zugriff sein, so wird wie bereits beim Erstellen eines Nutzers ...@'%' verwendet. Abschließen

   $ exit;
Alle Rechte
GRANT EXECUTE, PROCESS, SELECT, SHOW DATABASES, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, CREATE USER, FILE, LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN, SUPER ON *.* TO 'neuerExternerNutzer'@'IP-des-externen-Nutzer' WITH GRANT OPTION;
 

Rechte testen

Vom Computer/Gerät des oben angelegten neuerExternerNutzer kann nun zur erlaubten Datenbank über ein Terminal mit sein-passwort verbunden werden.

   $ mysql -u neuerExternerNutzer -h IP-des-externen-Nutzer -p

Passwort ändern

   $ SET PASSWORD FOR 'bestehenderNutzerName'@'IP-des-Zugriffsbereichs' = PASSWORD('neuesPasswort');

Import Größe ändern

Für den Import eines Backups, muss ggf. die maximal zulässige Import File Größe geändert werden. Diese kann im File /etc/php5/apache2/php.ini in Zeile 820 eingestellt werden.

Import von Backup

Wird MySQL und phpmyadmin neu installiert und eine Backup Datei importiert, so muss das Passwort 'dbpass' in der Datei /etc/phpmyadmin/config-db.php mit dem Passwort des ursprünglichen Systems übereinstimmen. Ansonsten können nicht root Nutzer sich nicht mehr einloggen oder ihre Tabellen einstehen.

   $ mysql -p -u root < MySQL-Backup-2017.sql

Backup erstellen

Über eine ausführbare Datei im Verzeichnis /etc/cron.daily/ kann ein tägliches Backup der gesamten Datenbank durchgeführt werden. Im nachfolgenden Beispiel Code wird die Datei MySQL_Backup_2016-01-09-06-25-1483939506.sql heißen.

   $ sudo nano /etc/cron.daily/mysqlBackup
#!/bin/sh
#SQL Backup
FILENAME="/directory/to/storage/location/MySQL_Backup_"`date +%Y-%m-%d-%H-%M-%s`".sql"
mysqldump --user="root" --password="dasPasswort" --all-databases  > $FILENAME #2> /dev/null
echo $FILENAME
 

Wiederherstellen

Wird phpmyadmin neu installiert so muss für den Import des Backups die Passwörter bei der Installation übereinstimmten.
Diese können nachträglich geändert werden, falls folgender Fehler auftritt:

1142 - SELECT command denied to user for table 'pma__table_uiprefs'
 
   $ nano /etc/phpmyadmin/config.inc.php
/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'your_root';
$cfg['Servers'][$i]['controlpass'] = 'your_password';
 

Reindex Autoincrement

Werden in einer mit Autoincrement bestückten Tabelle Zeilen gelöscht, so stimmt deren Reihenfolge nicht mehr. Dieses Problem löst der folgende SQL Befehl:

SET @count = 0;
UPDATE tabellenName SET tabellenName.autoIncrementSpalte = @count:= @count + 1;
 

Duplikate

Ist eine folgende Tabelle vorhanden

ID Stadt PLZ Strasse Name
1 München 80331 Marienplatz 1 Zentrum
2 München 80331 Marienplatz 1 Zentrum München
3 Hamburg 20457 Kehrwieder 2-4 MiWuLa
4 München 80331 Marienplatz 1 Zentrum München

Anzeigen

SELECT * FROM tabellenName group by beispielSpalteStrasse having count(*) >= 2
 

Zeigt folgenden Output, da die erste doppelte Zeile ausgegeben wird

ID Stadt PLZ Strasse Name
1 München 80331 Marienplatz 1 Zentrum

Löschen

Mit folgendem Befehl werden die neueren Datensätze (ID 4) gelöscht, da hier Strasse und Name übereinstimmen

delete
from tabellenName using tabellenName,
    tabellenName e1
where tabellenName.id > e1.id
    and tabellenName.beispielSpalteStrasse = e1.beispielSpalteStrasse
    and tabellenName.beispielName = e1.beispielName
 

SSL

MySQL Konfiguration

Zunächst muss SSL für MySQL aktiviert werden, dies erfolgt über das Config File welches zum schreiben geöffnet wird

   $ sudo nano /etc/mysql/my.cnf

Dort wird ziemlich am Ende - jedoch VOR '[mysqldump]' - der Datei unter SSL folgendes aktiviert/eingefügt:

ssl=1
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem
 

Zertifikate erstellen

Anschließend wird in eben das Verzeichnis '/etc/mysql' gewechselt und dort die Zertifikate erstellt und schlussendlich die nötigen Rechte vergeben.

   $ cd /etc/mysql/
   $ sudo su
   $ openssl genrsa 2048 > ca-key.pem
   $ openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
   $ openssl req -newkey rsa:2048 -days 3560 -nodes -keyout server-key.pem > server-req.pem
   $ openssl rsa -in server-key.pem -out server-key.pem
   $ openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
   $ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
   $ openssl rsa -in client-key.pem -out client-key.pem
   $ openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
   $ chmod 400 /etc/mysql/*.pem
   $ chown mysql /etc/mysql/*.pem

Danach ist ein Neustart von MySQL nötig

   $ sudo /etc/init.d/mysql restart

Zertifikate testen

Zum Test können diese Zertifikate nun noch überprüft werden.

   $ openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: C = AU, ST = Some-State, O = Internet Widgits Pty Ltd
error 18 at 0 depth lookup:self signed certificate
OK
client-cert.pem: C = AU, ST = Some-State, O = Internet Widgits Pty Ltd
error 18 at 0 depth lookup:self signed certificate
OK
 

Konfiguration prüfen

Zur Überprüfung, ob alle Einstellungen und Zertifikate korrekt übernommen wurden, loggt man sich ein und frägt den SSL Status ab.

   $ sudo su
   $ mysql -u root -p
   mysql> show variables like '%ssl%';

Dieser sollte folgendermaßen aussehen:

+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| have_openssl  | YES                              |
| have_ssl      | YES                              |
| ssl_ca        | /etc/mysql/ca-cert.pem           |
| ssl_capath    |                                  |
| ssl_cert      | /etc/mysql/server-cert.pem       |
| ssl_cipher    |                                  |
| ssl_key       | /etc/mysql/server-key.pem        |
+---------------+----------------------------------+
 

SSL für User aktivieren

Nun kann einem bereits existierenden Nutzer z.B. alle Rechte für alle Datenbanken (*.*) von der IP 'localhost' mit einem gültigen SSL Client Zertifikat erteilt werden:

   mysql> GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'my-password-for-ssluser' REQUIRE X509;

SSL Verbindung testen

Auf dem Gerät selber (localhost) kann nun die verschlüsselte Verbindung getestet werden. Zum einfacheren Übergeben der Zertifikate wird hierfür in den Ordner gewechselt in dem die Zertifikate liegen.

   $ cd /etc/mysql/
   $ mysql -u ssluser -p --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

Der aktuelle Verschlüsselungsstatus des verbundenen Clients kann dabei folgendermaßen überprüft werden:

   mysql> SHOW STATUS LIKE 'Ssl_cipher';

+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA | 
+---------------+--------------------+
1 row in set (0.00 sec)
 

Verwendung auf externen Geräten

Hierfür müssen sich natürlich die Zertifikate 'ca-cert.pem', 'client-cert.pem' und 'client-key.pem' dort befinden und bei der Verbindung angegeben werden.

Python Script

Dieses Script MUSS mit

   $ sudo python scriptName.py 

ausgeführt werden, da sonst folgender Fehler ausgegeben wird:

_mysql_exceptions.OperationalError: (2026, 'SSL connection error: Unable to get certificate')
  
#!/usr/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

sslSettings = {
        'cert': '/etc/mysql/client-cert.pem',
        'key': '/etc/mysql/client-key.pem',
        'ca':'/etc/mysql/ca-cert.pem'
}

con = mdb.connect(
        host='127.0.0.1',
        user='ssluser',
        passwd='my-password-for-ssluser',
        ssl=sslSettings)

with con:
        cursor = con.cursor()
        #sql = "SELECT VERSION()"
        sql = "SHOW STATUS like 'Ssl_cipher'"
        try:
                # cursor.execute(‘SHOW STATUS like “Ssl_cipher”‘)
                cursor.execute(sql)
                result = cursor.fetchone()
                print result
        except:
                print "Error: unable to fetch data"
 

PHP Script

Dieses Script MUSS mit

   $ sudo php scriptName.php

ausgeführt werden, da sonst folgender Fehler ausgegeben wird:

SSL error: Unable to get certificate from '/etc/mysql/client-cert.pem'
PHP Warning:  mysqli_real_connect(): (HY000/2026): SSL connection error: Unable to get certificate in /home/pi/Documents/scriptName.php on line 4
  
<?php
  $conn=mysqli_init();
  mysqli_ssl_set($conn, '/etc/mysql/client-key.pem', '/etc/mysql/client-cert.pem', NULL, NULL, NULL);
  if (!mysqli_real_connect($conn, '127.0.0.1', 'ssluser', 'my-password-for-ssluser'))
  {
    die();
  }
  $res = mysqli_query($conn, 'SHOW STATUS like "Ssl_cipher"');
  print_r(mysqli_fetch_row($res));
  mysqli_close($conn);
?>
 

Backup

Zum täglichen Erstellen von Backups kann ein Cronjob verwendet werden. Soll dieser nicht täglich sondern stündlich oder wöchentlich erfolgen, so wird der Eintrag einfach dort vorgenommen.

   $ sudo nano /etc/cron.daily/mysqlBackup
#!/bin/sh

#SQL Backup
FILENAME="/path/to/backup/location/MySQL_Backup_"`date +%Y-%m-%d`".sql"
mysqldump --user="root" --password="rootPassword" --all-databases  > $FILENAME #2> /dev/null
 

Das ganze wird mit

   $ ./mysqlBackup

einmal ausgeführt und erstellt damit das erste Backup im angegebenen Verzeichnis mit dem Namen "MySQL_Backup_2016-06-28.sql"

Credits

MySQL für Ubuntu
MySQL mit Python
MySQL mit SSL
Zertifikate mit OpenSSL generieren