Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
datenbanken:mysql [2021/05/06 19:04] – [Konfiguration] st | datenbanken:mysql [2023/01/16 23:20] (aktuell) – [offene Dateien für mysql erhöhen] st | ||
---|---|---|---|
Zeile 127: | Zeile 127: | ||
+ | ==== Verschlüsselung ==== | ||
+ | MySql kann ssl-Verschlüsselung, | ||
+ | <code bash> | ||
+ | |||
+ | my.cnf: | ||
+ | < | ||
+ | ssl-cert = / | ||
+ | ssl-key = / | ||
+ | </ | ||
+ | |||
+ | Andere Fehlerquellen: | ||
==== Konfigurationsparamter tunen ==== | ==== Konfigurationsparamter tunen ==== | ||
+ | [[https:// | ||
+ | |||
+ | Wichtige Parameter ausgeben und Speicherverbrauch berechnen: | ||
+ | <code bash> | ||
+ | mysql -e "show variables; show status" | ||
+ | { | ||
+ | VAR[$1]=$2 | ||
+ | } | ||
+ | END { | ||
+ | MAX_CONN = VAR[" | ||
+ | MAX_USED_CONN = VAR[" | ||
+ | BASE_MEM=VAR[" | ||
+ | MEM_PER_CONN=VAR[" | ||
+ | MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN | ||
+ | MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN | ||
+ | printf " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf " | ||
+ | printf "| %40s | %15.3f MB |\n", "BASE MEMORY", | ||
+ | printf " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf " | ||
+ | printf "| %40s | %15.3f MB |\n", " | ||
+ | printf " | ||
+ | printf "| %40s | %18d |\n", " | ||
+ | printf "| %40s | %18d |\n", " | ||
+ | printf " | ||
+ | printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", | ||
+ | printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", | ||
+ | printf " | ||
+ | }' | ||
+ | </ | ||
+ | [[https:// | ||
^ Parameter ^ Bedeutung ^ Default ^ | ^ Parameter ^ Bedeutung ^ Default ^ | ||
Zeile 152: | Zeile 206: | ||
**Rechte** (globale [admins], lokale [user]: auf eine Datenbank beschränkte Rechte) | **Rechte** (globale [admins], lokale [user]: auf eine Datenbank beschränkte Rechte) | ||
+ | |||
+ | ==== remove binary logs ==== | ||
+ | |||
+ | PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;# | ||
+ | |||
+ | / | ||
+ | < | ||
+ | [mysqld] | ||
+ | expire_logs_days=3 | ||
+ | </ | ||
+ | |||
+ | https:// | ||
==== Backup ==== | ==== Backup ==== | ||
Zeile 244: | Zeile 310: | ||
=== MySQL-Benutzer anlegen === | === MySQL-Benutzer anlegen === | ||
- | Der SQL-Code: | + | Der SQL-Code |
<code sql> | <code sql> | ||
- | CREATE USER ' | + | CREATE USER ' |
- | GRANT USAGE ON *.* TO ' | + | GRANT USAGE ON *.* TO ' |
CREATE DATABASE IF NOT EXISTS `DBUSER1`; | CREATE DATABASE IF NOT EXISTS `DBUSER1`; | ||
GRANT ALL PRIVILEGES ON `DBUSER1`.* TO ' | GRANT ALL PRIVILEGES ON `DBUSER1`.* TO ' | ||
Zeile 278: | Zeile 344: | ||
SQL-Befehle zum anlegen eines neuen Benutzers user1 mit dem passwort " | SQL-Befehle zum anlegen eines neuen Benutzers user1 mit dem passwort " | ||
- | <file> | + | <code sql> |
CREATE USER ' | CREATE USER ' | ||
GRANT USAGE ON *.* TO ' | GRANT USAGE ON *.* TO ' | ||
Zeile 285: | Zeile 351: | ||
GRANT ALL PRIVILEGES ON `db1`.* TO ' | GRANT ALL PRIVILEGES ON `db1`.* TO ' | ||
- | </file> | + | </code> |
=== Benutzer löschen === | === Benutzer löschen === | ||
Zeile 302: | Zeile 368: | ||
=== Passwort Ändern === | === Passwort Ändern === | ||
- | <code bash> | + | * Alte Datenbanken: |
use mysql; | use mysql; | ||
update user set password=PASSWORD(" | update user set password=PASSWORD(" | ||
+ | flush privileges; | ||
+ | quit | ||
</ | </ | ||
- | + | * ab Version 5.7.x: <code bash> | |
- | ab Version 5.7.x: | + | |
- | <code bash> | + | |
use mysql; | use mysql; | ||
update user set authentication_string=PASSWORD(" | update user set authentication_string=PASSWORD(" | ||
+ | flush privileges; | ||
+ | quit | ||
</ | </ | ||
- | + | * ab MariaDB-10.4+ (mysql.user ist eine View und keine Table mehr [[https:// | |
- | <code bash> | + | SET PASSWORD FOR ' |
flush privileges; | flush privileges; | ||
quit | quit | ||
</ | </ | ||
- | |||
- | |||
=== Rechte eines Benutzers === | === Rechte eines Benutzers === | ||
+ | <code mysql> | ||
SHOW GRANTS; | SHOW GRANTS; | ||
SHOW GRANTS FOR CURRENT_USER(); | SHOW GRANTS FOR CURRENT_USER(); | ||
+ | </ | ||
=== Plugin " | === Plugin " | ||
Zeile 414: | Zeile 481: | ||
Quelle: [[https:// | Quelle: [[https:// | ||
+ | |||
+ | ==== mysql 10.3 (buster) -> 10.5 (bullseye) ==== | ||
+ | |||
+ | Folgende Einstellungen sind weggefallen: | ||
+ | < | ||
+ | query_cache_type' | ||
+ | 2query_cache_size' | ||
+ | query_cache_limit | ||
+ | innodb_large_prefix | ||
+ | innodb_file_format' | ||
+ | show_compatibility_56 | ||
+ | </ | ||
===== MySQL interna ===== | ===== MySQL interna ===== | ||