Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- datenbanken:mysql [2022/01/28 19:35] – [MySQL-Benutzer anlegen] 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, der key muss allerdings im PKCS#1-Format vorliegen (ansonsten kommt "Unable to get private key"):
+<code bash>openssl rsa -in /etc/mysql/certs/server.crt.pkcs8 -out /etc/mysql/certs/server.crt</code>
+my.cnf:
+<file>
+ssl-cert = /etc/mysql/certs/server.crt
+ssl-key = /etc/mysql/certs/server.key
+</file>
+Andere Fehlerquellen: https://dba.stackexchange.com/questions/201770/mysql-ssl-error-unable-to-get-private-key|
 ==== Konfigurationsparamter tunen ====
+[[https://github.com/major/MySQLTuner-perl|MySQLTuner]]
+Wichtige Parameter ausgeben und Speicherverbrauch berechnen:
+<code bash>
+mysql -e "show variables; show status" | awk '
+{
+VAR[$1]=$2
+}
+END {
+MAX_CONN = VAR["max_connections"]
+MAX_USED_CONN = VAR["Max_used_connections"]
+BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
+MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
+MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
+MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN
+printf "+------------------------------------------+--------------------+\n"
+printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
+printf "+------------------------------------------+--------------------+\n"
+printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
+printf "+------------------------------------------+--------------------+\n"
+printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
+printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
+printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
+printf "+------------------------------------------+--------------------+\n"
+printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
+printf "+------------------------------------------+--------------------+\n"
+printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
+printf "| %40s | %18d |\n", "max_connections", MAX_CONN
+printf "+------------------------------------------+--------------------+\n"
+printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
+printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
+printf "+------------------------------------------+--------------------+\n"
+}'
+</code>
+[[https://stackoverflow.com/questions/23003626/mysql-optimization-mysqls-maximum-memory-usage-is-dangerously-high-but-still|Quelle]]
 ^ Parameter ^ Bedeutung ^ Default ^
@@ Zeile 152: / Zeile 206: @@
 **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;#
+/etc/mysql/my.cnf
+<file>
+[mysqld]
+expire_logs_days=3
+</file>
+https://gist.github.com/tiagocardosos/0d8e7e95316696be7a400e2c60909c43
 ==== Backup ====
@@ Zeile 244: / Zeile 310: @@
 === MySQL-Benutzer anlegen ===
-Der SQL-Code:
+Der SQL-Code ((bei altem mysql <code sql>CREATE USER 'DBUSER1'@'%' IDENTIFIED WITH mysql_native_password; SET PASSWORD FOR 'DBUSER1'@'%' = '***';</code>)):
 <code sql>
-CREATE USER 'DBUSER1'@'%' IDENTIFIED WITH mysql_native_password;
+CREATE USER 'DBUSER1'@'%' IDENTIFIED BY 'GEHEIM';
 GRANT USAGE ON *.* TO 'DBUSER1'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
-SET PASSWORD FOR 'DBUSER1'@'%' = '***';
 CREATE DATABASE IF NOT EXISTS `DBUSER1`;
 GRANT ALL PRIVILEGES ON `DBUSER1`.* TO 'DBUSER1'@'%';
@@ Zeile 279: / Zeile 344: @@
 SQL-Befehle zum anlegen eines neuen Benutzers user1 mit dem passwort "passwort1", der alle Benutzerrechte für die neue Datenbank db1 bekommt.
-<file>
+<code sql>
 CREATE USER 'user1'@'localhost' IDENTIFIED BY 'passwort1';
 GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY 'passwort1' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
@@ Zeile 286: / Zeile 351: @@
 GRANT ALL PRIVILEGES ON `db1`.* TO 'user1'@'localhost';
-</file>
+</code>
 === Benutzer löschen ===
@@ Zeile 303: / Zeile 368: @@
 === Passwort Ändern ===
-<code bash>
+  * Alte Datenbanken: <code bash>
 use mysql;
 update user set password=PASSWORD("GEHEIM") where User='BENUTZERNAME';
+flush privileges;
+quit
 </code>
+  * ab Version 5.7.x: <code bash>
-ab Version 5.7.x:
-<code bash>
 use mysql;
 update user set authentication_string=PASSWORD("GEHEIM") where User='BENUTZERNAME';
+flush privileges;
+quit
 </code>
+  * ab MariaDB-10.4+ (mysql.user ist eine View und keine Table mehr [[https://mariadb.com/kb/en/set-password/|SET PASSWORD]] oder [[https://mariadb.com/kb/en/alter-user/|ALTER USER]]):<code bash>
-<code bash>
+SET PASSWORD FOR 'BENUTZERNAME'@localhost = PASSWORD("GEHEIM");
 flush privileges;
 quit
 </code>
 === Rechte eines Benutzers ===