# MySQL

Nützliche Codeschnipsel für die Verwaltung von MySQL Servern

# Benutzer mit Adminrechten anlegen

```SQL
## only local
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
## remote connection - not secure
CREATE USER 'admin'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```

# Datenbanken ex- und importieren

#### Datenbanken exportieren (dumpen)

##### alle Datenbanken dumpen

<div id="bkmrk-mysqldump--uuser--p-">```
mysqldump <span class="re5">-uUSER</span> <span class="re5">-p</span> <span class="re5">--all-databases</span> <span class="sy0">></span> my-mysql-dump.sql
```

</div>##### eine bestimmte Datenbank dumpen

<div id="bkmrk-mysqldump--uuser--p--0">```
mysqldump <span class="re5">-uUSER</span> <span class="re5">-p</span> mydatabase1 <span class="sy0">></span> my-mysql-dump.sql
```

</div>##### mehrere Datenbanken dumpen

<div id="bkmrk-mysqldump--uuser--p--1">```
mysqldump <span class="re5">-uUSER</span> <span class="re5">-p</span> <span class="re5">--databases</span> db_name1 db_name2 db_name_n <span class="sy0">></span> my-mysql-dump.sql
```

</div>##### nur eine bestimmte Tabelle aus einer Datenbank dumpen

<div id="bkmrk-mysqldump--uuser--p--2">```
mysqldump -uUser -p mydatabase1 table_name > my-mysql-dump.sql
```

</div>##### Größe eines Datenbankdumps reduzieren (bspw. für schnelleren Transfer zwischen zwei Servern)

<div id="bkmrk-mysqldump--uuser--p--3">```
mysqldump -uUser -p mydatabase1 table_name | gzip -c > my-mysql-dump.sql.gz
```

</div>##### Dies kann man sogar automatisieren als Cronjob um eine Datenbank regelmäßig zu sichern

<div id="bkmrk-crontab--e"><div>```
crontab <span class="re5">-e</span>
```

</div></div>*Dann ans Ende der Datei folgenden Code einfügen*

<div id="bkmrk-0-%2A%2F6-%2A-%2A-%2A-mysqldum"><div>```
<span class="nu0">0</span> <span class="sy0">*/</span><span class="nu0">6</span> <span class="sy0">*</span> <span class="sy0">*</span> <span class="sy0">*</span> mysqldump <span class="re5">-u</span> <span class="st_h">'User'</span> <span class="re5">-p</span> <span class="st_h">'Password'</span> mydatabase1 table_name <span class="sy0">|</span> <span class="kw2">gzip</span> <span class="re5">-c</span> <span class="sy0">></span> my-mysql-dump.sql.gz <span class="sy0">/</span>dev<span class="sy0">/</span>null <span class="nu0">2</span><span class="sy0">>&</span><span class="nu0">1</span>
```

</div></div>*Dies führt eine Datenbanksicherung alle 6 Stunden aus*

#### Datenbankdump zurückspielen (importieren)

<div id="bkmrk-mysql--uuser--p-myda"><div>```
mysql <span class="re5">-uUSER</span> <span class="re5">-p</span> mydatabase1 <span class="sy0"><</span> my-mysql-dump.sql
```

</div></div>*Hierbei muss die Datenbank unter dem angegebenem Datenbanknamen bereits existieren (in diesem Beispiel mydatabase1)*

# MySQL Tipps & Tricks

### MySQL Backup per Konsole einspielen

```shell
pv export_db_name.sql | mysql -uroot -p db_name
```

<p class="callout info">**Hinweis:** Benötigt pv `(apt install pv)`</p>

### Neuen Benutzer erstellen und Rechte gewähren

```SQL
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';
GRANT ALL ON my_db.* TO 'new_user'@'localhost';
```

### Prozessliste laufend aktualisiert anzeigen

```shell
mysqladmin -u root -p -i 1 processlist
```

### Passwort neu setzen (für Useraccounts)

<div id="bkmrk-"></div>##### für MySQL

```SQL
ALTER USER user IDENTIFIED BY 'auth_string';
```

##### für MariaDB

```
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpass');
```

# Externen Zugriff zulassen

<p class="callout info">In dieser Anleitung spreche ich immer von einem MySQL Server, gemeint ist damit natürlich auch der MariaDB Server. Sollten einzelne Schritte zwischen dem MySQL Server und dem MariaDB Server abweichend sein behandle ich diese seprarat  
</p>

Um den externen Zugriff auf eine Datenbank bzw. einen MySQL Server freizuschalten bedarf es einer Änderung der Konfigurationsdatei des MySQL Servers.

```bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```

Bei Installationen des MariaDB Servers findet man die Konfigurationsdatei abweichend hier

```bash
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
```

Dort findet man in beiden Fällen eine umfangreiche Konfigurationsdatei vor, uns interessiert allerdings nur eine bestimmte Zeile

```bash
. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
. . .
```

Hier ändern wir den Inhalt folgendermaßen um

```bash
. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
. . .
```

Datei speichern &amp; schließen und anschließend den MySQL Server neu starten

```bash
sudo systemctl restart mysql
```

```bash
FLUSH PRIVILEGES;
```

Nun könnten theoretisch bereits alle Datenbanken extern erreicht werden, allerdings muss dieses Recht pro User und pro Datenbank explizit noch gesetzt werden. Hierzu loggen wir uns lokal auf dem MySQL Server ein und bearbeiten bzw. erstellen uns einen User mit den passenden Zugriffsrechten

```bash
mysql -u root -p

### Existierenden User für externen Zugriff von einer einzigen IP freischalten (für bspw. den Zugriff von einem Server zum nächsten bei gleichbleibender IP)
RENAME USER 'username'@'localhost' TO 'username'@'remote_ip';

### Wenn man jedoch von zuhause auf den Server zugreifen möchte eignet sich diese Methode nicht da i.d.R. die IP des heimischen Anschlusses regelmäßig geändert wird.
### Hierfür erlauben wir also den Zugriff von JEDER IP - Hinweis: Zu dem allgemein erhöhten Angriffsrisiko durch den externen Zugriff steigern wir die Gefahr erneut durch den Zugriff von JEDER IP aus. 
### Grundsätzlich gilt daher: Immer ausreichend lange und komplizierte Passwörter verwenden um es pozentiellen Angreifern nicht allzu leicht zu machen
RENAME USER 'username'@'localhost' TO 'username'@'%';

### Neuen User erstellen für den externen Zugriff
CREATE USER 'username'@'remote_ip' IDENTIFIED BY 'password';
### ODER
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
### Der neue Benutzer hat dann allerdings noch keine Zugriffsrechte um Aktionen an Datenbanken oder Tabellen auszuführen, dies muss separat erfolgen
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'username'@'remote_ip' WITH GRANT OPTION;
### Hinweis: Nur die Rechte vergeben die auch wirklich benötigt werden
```

Zum Abschluss laden wir noch die Berechtigungen neu und verlassen den MySQL Server

```bash
FLUSH PRIVILEGES;
exit
```

Der Zugriff von Außerhalb sollte nun funktionieren

Abschließender Hinweis:

Sollte auf dem Server eine Firewall konfiguriert sein muss auch hier der Zugriff freigegeben werden, hier am Beispiel von `ufw`

```bash
sudo ufw allow from remote_ip to any port 3306
## Alternativ kann auch hier wieder der Zugriff von jeder IP erlaubt werden, jedoch wird auch hiervon abgeraten
sudo ufw allow 3306
```