datenbanken:mysql

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
Nächste ÜberarbeitungBeide Seiten der Revision
datenbanken:mysql [2021/11/01 11:03] – [mysql table is marked as crashed and last (automatic?) repair failed] stdatenbanken:mysql [2022/12/02 12:26] – [Passwort Ändern] st
Zeile 130: Zeile 130:
 ==== Konfigurationsparamter tunen ==== ==== 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 ^  ^ Parameter ^ Bedeutung ^ Default ^ 
Zeile 152: Zeile 195:
 **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;#
 +
 +/etc/mysql/my.cnf
 +<file>
 +[mysqld]
 +expire_logs_days=3
 +</file>
 +
 +https://gist.github.com/tiagocardosos/0d8e7e95316696be7a400e2c60909c43
  
 ==== Backup ==== ==== Backup ====
Zeile 244: Zeile 299:
 === MySQL-Benutzer anlegen === === 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> <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'@'%' = '***';+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;
 CREATE DATABASE IF NOT EXISTS `DBUSER1`; CREATE DATABASE IF NOT EXISTS `DBUSER1`;
 GRANT ALL PRIVILEGES ON `DBUSER1`.* TO 'DBUSER1'@'%'; GRANT ALL PRIVILEGES ON `DBUSER1`.* TO 'DBUSER1'@'%';
Zeile 302: Zeile 357:
 === Passwort Ändern === === Passwort Ändern ===
  
-<code bash>+  * Alte Datenbanken: <code bash>
 use mysql; use mysql;
 update user set password=PASSWORD("GEHEIM") where User='BENUTZERNAME'; update user set password=PASSWORD("GEHEIM") where User='BENUTZERNAME';
 +flush privileges;
 +quit
 </code> </code>
- +  * ab Version 5.7.x: <code bash>
-ab Version 5.7.x: +
-<code bash>+
 use mysql; use mysql;
 update user set authentication_string=PASSWORD("GEHEIM") where User='BENUTZERNAME'; update user set authentication_string=PASSWORD("GEHEIM") where User='BENUTZERNAME';
 +flush privileges;
 +quit
 </code> </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; flush privileges;
 quit quit
 </code> </code>
- 
- 
 === Rechte eines Benutzers === === Rechte eines Benutzers ===
  
 +<code mysql>
 SHOW GRANTS; SHOW GRANTS;
 SHOW GRANTS FOR CURRENT_USER(); SHOW GRANTS FOR CURRENT_USER();
 +</code>
  
 === Plugin "localhost" === === Plugin "localhost" ===