Basisinstallation

Hier die Sammlung meiner meist genutzten MySQL-Befehle.

MySQL-Server installieren:

sudo apt install mysql-server
sudo mysql_secure_installation

Danach folgt die Installation und Konfiguration des MySQL-Servers mit zahlreichen Abfragen, die aber aus meiner Sicht selbsterklärend sind.

root mit Passwort-Authentifizierung einrichten

Ab MySQL 5.7 kommuniziert der root-Nutzer nur noch mit dem auth-sock und ist nicht mehr für direkte Passwort-Authentifizierung eingerichtet. Um sich als MySQL-root mit Passwort authentifizieren zu können, muss dies in der Konfiguration entsprechend eingerichtet werden:

sudo mysql
SELECT user,authentication_string,plugin,host FROM mysql.user;

Nun werden alle mysql-Nutzer und ihre Authentifizierungsmethoden angezeigt. Um dem Nutzer root nun auch die Passwort-Authentifizierung zu gewähren, wird folgendes Kommando ausgeführt:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Wenn man nun erneut den Befehl

SELECT user,authentication_string,plugin,host FROM mysql.user;

ausführt, sollte die Änderung sichtbar sein.

Danach kann man sich aus der shell mit

mysql -u root -p

Als mysql-Nutzer root anmelden.

MySQL-Nutzer und Datenbanken anlegen

Zuerst als mysql-root anmelden.
Datenbanken anzeigen:

SHOW DATABASES;

Datenbank auswählen:

USE datenbankname;

Alle Tabellen anzeigen:

SHOW TABLES;

Datenbank anlegen:

CREATE DATABASE datenbankname;

Nutzer anlegen:

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

Rechte vergeben:

GRANT ALL PRIVILEGES ON datenbankname . * TO 'username'@'localhost';
FLUSH PRIVILEGES;

Import / Export von Datenbanken

SQL Export (auf der shell):

mysqldump -u username -p datenbankname > /pfad/zum/datenbankexport.sql

Import:

mysql -u username -p datenbankname < /pfad/zum/datenbankimport.sql

MySQL Export als csv

Manchmal kann der Export von mysql-Tabellen als csv (comma separated value) hilfreich sein, um z.B. die exportierten Daten in Excell / Libreoffice weiter zu bearbeiten. Dazu gibt es den Befehl „INTO OUTFILE“, mit dem sich biebige Inhalte aus Datenbanktabellen in eine externe Datei schreiben lassen.

SELECT  spaltenname1,spaltenname2,spaltenname3
INTO OUTFILE '/var/lib/mysql-files/export.csv'
FIELDS TERMINATED BY ';' 
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\' 
LINES TERMINATED BY '\n'
FROM tablename WHERE column="selector";

Aus Sicherheitsgründen kann man je nach Standardeinstellung  nur nach /var/lib/mysql-files/ exportieren. Man kann sich das Verzeichnis auf dem eigenen Server mit

SHOW VARIABLES LIKE "secure_file_priv";

anzeigen lassen.

Die exportierte Datei namens export.csv enhält 3 Spalten, Werte sind mit “ eingefasst und ein ; als Feldtrenner vorhanden.

Suchen und ersetzen

Hier gibt es 2 grundsätzlich verschiedene Optionen. Wenn es nur um suchen&ersetzen in einer einzelnen Tabelle geht:

UPDATE tabellenname SET feldname = replace(feldname,'suchstring','ersetzstring');

Wenn es darum geht, einen Suchstring in der kompletten Datenbank über viele Tabellen hinweg global zu ersetzen empfiehlt es sich zuerst die Datenbank zu exportieren (s.o.) und danach mit sed den Export zu bearbeiten:

sed -i 's/suchstring/ersetzstring/g' /pfad/zum/datenbankexport.sql

Danach wir der Export wieder importiert (s.o.).

Tabellen reparieren

Wenn der mysql-Server abstürzt, können Tabellen fallweise korrumpieren. Mit

mysqlcheck -u root -p --repair --all-databases

lassen sich von der shell aus alle Tabellen reparieren.