======= MySQL =======
[[wpde>MySQL]] [ˌmaɪɛskjuːˈɛl] ist ein Relationales Datenbankverwaltungssystem der schwedischen Firma [[wpde>MySQL AB]] die mittlerweile ((seit Februar 2008)) durch Sun Microsystems übernommen wurde. MySQL ist als Open-Source-Software für verschiedene Betriebssysteme verfügbar.


{{:datenbanken:mysql.odp|MySQL.odp}}
{{:datenbanken:mysql.pdf|MySQL.pdf}}

===== MySQL-Befehle =====
^ Befehl / Programm ^ Funktion ^
| mysqld | MySQL server daemon (=Dienst) |
| safe_mysqld | Server prozess Monitor |
| mysqlaccess | Programm zum Erzeugen von MySQL-Benutzern |
| mysqladmin | Administrationstool |
| mysqldump | Programm für das Erstellen von Datenbankbackups ("dump") |
| mysql | Kommandozeilenzugriff auf MySQL |
| mysqlshow | listest alle MySQL-Datenbanken auf |




===== Links =====

  * siehe **[[linux:benchmarks#io|Benchmarks]]**
  * [[http://dev.mysql.com/doc/|MySQL Documentation]]
  * [[http://www.heise.de/open/artikel/72193|MySQL: Die freie Wahl]]
  * [[http://www.little-idiot.de/mysql/|MySQL Datenbankhandbuch]]
  * [[http://forge.mysql.com/|MySQL Forge]] Codebeispiele

  * [[http://www.cosmocode.de/de/material/seminare/20060214_mysql5/index.html|MySQL 5.0 Präsentation]]
  * **[[http://www.bookzilla.de/shop/action/productDetails?artiId=2904854|High Performance MySQL (Jeremy D. Zawodny, Derek J. Balling)]]**

  * [[http://www.heise.de/open/artikel/71342|Zertifizierung als Geschäftsmodell]]
  * [[http://www.heise.de/newsticker/meldung/74723|Plugin bringt Volltextsuche für alle MySQL-Engines]]
  * [[http://bobcares.com/article44.html|Running Multiple MySQL versions]]
  * [[http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1228098,00.html|MySQL: Choosing the most important features]]
  * [[http://www.heise.de/newsticker/Plattformneutraler-MySQL-Profiler--/meldung/132303|Plattformneutraler MySQL-Profiler]]
  * [[http://www.heise.de/newsticker/meldung/Microsoft-hilft-beim-Umstieg-von-MySQL-auf-SQL-Server-1058328.html|Microsoft hilft beim Umstieg von MySQL auf SQL Server]]

==== Tutorials ====
  * [[http://www.schattenbaum.net/php/mstart.php|MySQL und PHP]]

==== SQL ====

  * [[http://linuxhelp.blogspot.com/2005/06/mysql-cheat-sheet.html|MySQL - Cheat Sheet]]
  * [[http://www.nparikh.org/unix/mysql.php|Gute Übersicht über oft gebrauchte MySQL-Befehle/Abfragen]]
  * [[http://www.it-infothek.de/sql/sqlmain.html|Structured Query Language (SQL)-Übersicht]]
  * [[https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins|Visual Representation of SQL Joins]]

==== Cluster ====

  * [[Galera]]
  * [[http://guxx.de/2007/07/09/mysql-ndb-cluster-installation-und-konfigurierung-howto/|MySQL-NDB-Cluster Installation und Konfiguration (HOWTO)]]
  * [[https://blogs.oracle.com/LinuxJedi/entry/mysql_cluster_uses|MySQL Cluster Uses]] 



===== Installation =====
**kompilieren** aus den Quellen:
  (entpacken: tar xvzf ARCHIVDATEI)
  ./configure
  make
  make install

mindestens für den letzten Schritt braucht man **root-Rechte** (also mit sudo oder als root starten) und für alle folgenden Möglichkeiten auch.

als vorkompiliertes **Paket** installieren:
  * rpm-Paket (Suse, Redhat, Fedora, ...): ''rpm -i PAKETDATEI.rpm'' oder
  * deb-Paket (Debian, (K)ubuntu): ''dpkg -i PAKETDATEI.deb''

Oder am einfachsten aus einem **Repository** (Sammlung von Paketen, meist direkt aus dem Internet):
  * yum (Suse, Redhat, Fedora, ...): ''yum install mysql''
  * apt (Debian, K)ubuntu): ''ap-get install mysql''

==== MySQL entfernen vor mariadb Installation ====

<code bash>
apt purge  -y mysql-server mysql-server-5.7 mysql-server-core-5.7
apt purge  -y mysql-client-5.7 mysql-client-core-5.7 mysql-common
rm -Rf /etc/mysql
rm -Rf /var/lib/mysql
# prüfen ob /etc/apparmor.d/usr.sbin.mysqld sauber ist oder "aa-remove-unknown" ausführen
service apparmor teardown
service apparmor start
</code>
===== Konfiguration =====
Aktuelle Konfiguration des laufenden Servers: <code bash>mysqladmin variables -p</code>

Konfiguration testen: ''mysqld --validate-config''

Einige Einstellungen von MySQL: Datei ''/etc/mysql/my.conf''
  bindaddress = 127.0.0.1
WICHTIG: soll nur auf lokale Anfragen antworten, sonst lauscht mysql auf Logins von anderen Hosts (das sollte man nur erlauben wenn es benötigt wird, z.B. bei spezialisierten Datenbankservern)
  user = mysql
Der Benutzer unter dem mysql läuft, am besten auch überprüfen das sich der Nutzer mysql nicht über ssh einloggen kann.
  port = 3306
Der Port auf dem Mysql auf Anfragen lauscht.
  # log = /var/log/mysql.log
  # log = /var/log/mysql/mysql.log
Extra-logdateien für Mysql, standardmäßig auskommentiert weil die Dateien (abhängig von der Menge der Anfragen) schnell groß werden und Leistung kosten.

==== deprecated Konfigurationsvariablen ====

Beim dist-upgrade von ubuntu 18.04 zu 20.04 (Version 5.7 auf 8.0) werden folgenden Einstellungen werden nicht mehr unterstützt:

<file>
query_cache_type
query_cache_size
query_cache_limit
innodb_large_prefix
innodb_file_format
show_compatibility_56
</file>
==== offene Dateien für mysql erhöhen ====

  * Symptom: mysqld & systemd - errno: 24 - Too many open files; soft limit ist standardmäßig 1024, hard limit 4096.
  * check mit: <code bash>cat /proc/$(cat /var/run/mysqld/mysqld.pid)/limits | grep files</code>

Ein Eintrag in der ''/etc/security/limits.conf'' hilft nichts<file>mysql soft nofile 65535
mysql hard nofile 65535</file>

    - Methode 1 (override):<code bash>systemctl edit mysql</code> (legt an: /etc/systemd/system/mysql.service.d/override.conf) <file>[Service]
LimitNOFILE=infinity
LimitMEMLOCK=infinity
</file>
    - Methode 2: unit-file direkt editieren: ''/etc/systemd/system/multi-user.target.wants/mysql.service'' <file>LimitNOFILE=infinity
LimitMEMLOCK=infinity</file> <code bash>systemctl daemon-reload</code>


==== 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 ^ 
| query_cache_limit | Gibt den Maximalwert eines gecachten Objekts an. Der Standardwert ist in der Regel 2 MB. Bei Abfragen mit großen Ergebnissen kann es Sinn machen, diesen Wert leicht zu erhöhen. | |
| query_cache_size | Gibt die Maximalgröße des Query Caches an. Sollte bei stark belastetetn Systemen ruhig auf 32 oder 64 MB gesetzt werden. | |
| table_cache | Anzahl der gecachten Tables. Diese Einstellungen muss eigentlich fast immer erhöht werden. | |
| tmp_table_size | Größe der maximal implizit im RAM erzeugten temporären Tabellen. Diese Einstellung muss ebenfalls fast immer vergrößert werden. Beispielsweise auf 64 MB | Nein, [[https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_tmp_table_size|MySQL: 16M]]  |

^ Parameter ^ Bedeutung ^ Default ^ 
| innodb_flush_method | auf O_DIRECT setzen! | |
| innodb_buffer_pool_size | Gibt die Größe des Speichers an, in dem die InnoDBs verwaltet werden. Hier gilt: Viel hilft viel. In einer idealen Welt sollte der Wert so groß sein, dass die gesamte DB hier Platz findet. | |
| innodb_file_per_table | ''Unbedingt setzen!'' Dadurch wird pro Table eine Datei angelegt. Das lässt sich wesentlich besser verwalten als eine riesige Datei. | Ja: [[https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_file_per_table|MySQL]] [[https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_file_per_table|mariaDB]]|
| innodb_data_file_path | Gibt an wie die Datenbankdateien heißen und wie sie vergrößert werden. Bitte auf folgendes setzen:  ''innodb_data_file_path=ibdata1:10M:autoextend'' | [[https://dev.mysql.com/doc/refman/5.6/en/innodb-init-startup-configuration.html|5.6]](12M) [[https://dev.mysql.com/doc/refman/5.7/en/innodb-system-tablespace.html|5.7]] [[https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_data_file_path|mariaDB]] |

^ MyIsam ^^
| key_buffer | Gibt den Buffer für MyIsam Tabellen an. Es ist quasi die innodb_buffer_pool_size für MyIsam*.* Auch hier gilt: Viel hilft viel. Es reicht allerdings, wenn sämtliche Indexe in den Buffer passen. |

===== Administration =====
mysqladmin (Befehle z.B. status, create, drop, stop)

**Benutzer** (db mysql,tabelle user)\\ 
**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 ====

<box round blue 100% | **Offline-Backup**>
Wenn man den Server nicht rund um die Uhr verfügbar haben muss, kann man ein Offline-Backup machen. Dazu
  - stoppt man den Server <code>/etc/init.d/mysqld stop</code>
  - kopiert alle Datendateien (mit cp, rsync oder einer anderen Möglichkeit) unter ''/var/lib/mysql'' z.B. nach ''/tmp/mysql-backup'': <code>cp -r /var/lib/mysql /tmp/mysql-backup</code>
  - und startet den Server wieder<code>/etc/init.d/mysqld start</code>
</box>

**Online-Backups** (während des Betriebs) haben jedoch den Vorteil nicht nur die Binärdateien zu sichern (die Versions-/Architekturabhängig sind!) sondern korrekte SQL-Statements zu produzieren die wieder eingespielt werden können.

  * [[https://www.lenzg.net/mylvmbackup/#Downloads|mylvmbackup]] ([[https://packages.debian.org/buster/mylvmbackup|debian Paket]]) - mysql-Backup über [[linux:lvm]]-snapshots - verbindet sich zu mysql, setzt lese-locks für alle Tabllen, flusht die caches und damit das Backup immer konsistent ist wird das as redo-Log von mysql genutzt
  * [[https://sourceforge.net/projects/automysqlbackup/|AutoMySQLBackup]]
  * [[server:phpmyadmin]]
  * [[shellscripts:mysql_dump|mysqldump]]: 
<code>mysqldump -B db1 db2 db3 -u root -p Passwort > Backupdatei.sql</code>
^ Option ^ Effekt ^
| <nowiki>--</nowiki>all-databases  | sichert alle Datenbanken | 
| -B  | Datenbanken (hier db1 db2 db3), die man sichern will |
| -u | user für mysql | 
| -p | Passwort, wenn keins angegeben ist fragt er nach. Alternativ kann das Passwort auch in der Datei ''.my.conf'' im home-Verzeichnis gesichert werden: :!: siehe **[[http://dev.mysql.com/doc/refman/5.0/en/password-security.html|MySQL-Doku - Password-Security]]**. \\  Der Speicherort des "Option-File" kann auch per Option angegeben werden: ''<nowiki>--</nowiki>defaults-file=/etc/my.cnf''. Bei Debian sind die Zugangsdaten fürfür die Paketverwaltung in /etc/mysql/debian.cnf abgelegt. |

Das ganze wird dann in Backupdatei.sql geschrieben und kann später wieder zurückgespielt werden.
  * [[http://www.mysqldumper.de/|mysqldumper]]
  * [[http://www.ozerov.de/bigdump.php|Bigdump]]
  * [[http://sourceforge.net/project/showfiles.php?group_id=101066|AutoMySQLBackup (Script)]], [[http://www.ducea.com/2006/05/27/backup-your-mysql-databases-automatically-with-automysqlbackup/#ViewPollResults|Artikel dazu]]

<box round blue 100% | **Offline-Backup**>
Für regelmäßige Backup müsste man die Zugangsdaten auf dem System hinterlegen. Das wäre mit einem Administrativen Zugang eher problematisch. Es geht aber auch anders: Es reicht einen **beschränkten Benutzer** anzulegen der lediglich die **notwendigsten Rechte** erhält:
  - "SELECT"
  - "FILE"
  - PROCESS (wenn Tablespaces dedumpt werden sollen ((mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces)) )
  - und "LOCK TABLES" (in den Kästen "Daten" und "Administration"  wenn man das per phpmyadmin durchführt)<code sql>CREATE USER 'dump'@'localhost' IDENTIFIED BY 'GEHEIM';
GRANT SELECT, FILE, PROCESS, LOCK TABLES ON *.* TO 'dump'@'localhost';</code>

Weniger sicher ist ein direkter insert in die Tabelle user:
<code bash>
mysql> USE mysql;
mysql> INSERT INTO user SET Host ='localhost', User = 'dump', Password = Password('GEHEIM'), Select_priv ='Y', File_priv ='Y', Lock_tables_priv ='Y';
mysql> FLUSH PRIVILEGES;
</code>
Ab Version 5.7 heißt das Feld statt "Password" nun "authentication_string" und das Feld ssl_cipher braucht einen Wert:<file>ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value</file>


In der Konsole sollte folgender Befehl erfolgreich sein: <code bash>mysqldump -u dump -pGEHEIM --all-databases > Backup.sql</code>
Oder das Passwort liegt in einer Datei /root/.my.cnf:
<code bash>mysqldump --defaults-file=/root/.my.cnf -u dump --all-databases > backup.sql</code>
wobei /root/.my.cnf den folgenden Inhalt hat:<file>[client]
password=GEHEIM</file>
</box>

=== Backup-Links ===
  * [[http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html|mysqldump]]
  * [[http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html|mysqlhotcopy]]
  * [[http://phpmyadmin.sourceforge.net/|phpmyadmin]]

  * [[http://www.cyberciti.biz/tips/how-to-backup-mysql-databases-web-server-files-to-a-ftp-server-automatically.html|How to backup MySQL databases, web server files to a FTP server automatically]]


=== Zmanda ===

Kommerzielle Version von Amanda.

[[http://www.zmanda.com/backup-mysql.html|Zmanda Recovery Manager for MySQL]] mit und ohne Support unter der GPL
[[http://www.linuxtoday.com/storage/2006111300626OSHLSV|Zmanda: MySQL Online Backup]]
[[http://www.zmanda.com/quick-mysql-backup.html|How to setup and verify a backup solution for MySQL in 15 minutes - all using open source software]]

==== Restore ====
  * mit [[server:phpmyadmin]]
  * mit [[http://www.ozerov.de/bigdump.php|Bigdump]]
  * [[http://www.mysqldumper.de/|mysqldumper]] - definitiv einen Blick wert

  - Db erzeugen:<code>mysqladmin -u root -p create dbname</code>
  - SQL-Datei einspielen <code>mysql [opt] dbname < datei.sql -u root</code>

Falls der SQL-Dump schon eine Datenbank anlegt:
  mysql --user=user_name --password=geheim < datei.sql


==== Monitoring ====

Ein kleiner Funktionstest im laufenden Betrieb:
<code bash>/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping</code>
<file>mysqld is alive</file>

[[https://github.com/yahoo/mysql_perf_analyzer|MySQL Performance Analyzer von Yahoo]]
==== Benutzermanagement ====


=== MySQL-Benutzer anlegen ===

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 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;
CREATE DATABASE IF NOT EXISTS `DBUSER1`;
GRANT ALL PRIVILEGES ON `DBUSER1`.* TO 'DBUSER1'@'%';
</code>

<code bash>
#!/bin/sh

# DB erzeugen
user=DBUSER1 # ÄNDERN!
db="$user"  # kann, muss aber nicht gleich sein.
hostname="localhost"
db_pass_file=$(mktemp)
sql_file=$(mktemp)
pwgen 18 > "$db_pass_file" # pwgen muss installiert sein
# könnte auch mit urandom ersetzt werden:
# db_pass_file=$(tr -cd -- "-._\!+a-zA-Z0-9" < /dev/urandom | head -c 18 && echo)

echo "CREATE USER '$user'@'$hostname' IDENTIFIED BY '$(cat "$db_pass_file")';" >> "$sql_file"
echo "GRANT USAGE ON *.* TO '$user'@'$hostname' IDENTIFIED BY '$(cat "$db_pass_file")' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;" >> "$sql_file"
echo "CREATE DATABASE IF NOT EXISTS \`$db\`;" >> "$sql_file"
echo "GRANT ALL PRIVILEGES ON \`$db\`.* TO '$user'@'$hostname';" >> "$sql_file"

echo "Enter mysql-password: "
mysql -u root -p < "$sql_file"
</code>

**Alte Methode**:

SQL-Befehle zum anlegen eines neuen Benutzers user1 mit dem passwort "passwort1", der alle Benutzerrechte für die neue Datenbank db1 bekommt.

<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;

CREATE DATABASE IF NOT EXISTS `db1` ;

GRANT ALL PRIVILEGES ON `db1`.* TO 'user1'@'localhost';
</code>

=== Benutzer löschen ===

<code sql>
DROP USER 'BENUTZERNAME'@'%';
FLUSH PRIVILEGES;
</code>

=== Benutzer anzeigen ===

<code sql>SELECT user,password,plugin,host FROM mysql.user;</code>
ab 5.7.x:
<code sql>SELECT user,authentication_string,plugin,host FROM mysql.user;</code>

=== Passwort Ändern ===

  * 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>
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>
SET PASSWORD FOR 'BENUTZERNAME'@localhost = PASSWORD("GEHEIM");
flush privileges;
quit
</code>
=== Rechte eines Benutzers ===

<code mysql>
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();
</code>

=== Plugin "localhost" ===

Durch das Plugin "localhost" kann sich root von localhost aus ohne Authentifizierung anmelden. Dieses Verhalten ist möglicherweise unerwünscht.

Abschalten
<code sql>UPDATE mysql.user SET plugin = '' WHERE user = 'root' AND host = 'localhost';</code>

https://stackoverflow.com/questions/44298160/mysql-mariadb-10-0-29-set-root-password-but-still-can-login-without-asking-p
==== Replication ====
Replication ist das duplizieren der Daten auf einen anderen Server. Damit wird sichergestellt, dass die Daten konsistent bleiben, da beim einfachen Dump im laufenden Betrieb Inkonsistenzen durch nicht vollständige Transaktionen auftreten können.

  * [[http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html|Live Backups of MySQL Using Replication]]
  * [[http://dev.mysql.com/doc/refman/4.1/en/replication.html|Replication]]
  * [[http://www.howtoforge.com/mysql_database_replication|How To Set Up Database Replication In MySQL]]
  * [[http://www.linuxtoday.com/infrastructure/2006101000426OSHLSV|HowtoForge: MySQL Master Master Replication]]



==== Security =====
Im Grunde genommen sehr grundsätzliche Dinge
  * MySQL nicht als root betreiben (diese Sünde begeht hoffentlich keiner)
  * Systemsicherung
  * Benutzerrechte sinnvoll vergeben (USAGE-Rechte für jede Applikation)
  * Aufteilung in Datenbanken mit eigenen Rechte wo möglich
  * entfernten (Netzwerk-) Zugriff beschränken/abschalten

[[http://www.little-idiot.de/mysql/mysql-137.html|Wie sichere ich MySQL gegen Hacker ab ?]]
[[http://dev.mysql.com/doc/refman/5.0/en/password-security.html|Keeping Your Password Secure]]
[[http://www.securityfocus.com/infocus/1726|Securityfocus: Securing MySQL]]
[[http://www.linux.com/article.pl?sid=04/08/19/1422204|Securing MySQ]]
[[http://dev.mysql.com/tech-resources/articles/securing_mysql_windows.html|Securing a MySQL Server on Windows]]



=== MySQL Lockdown Checklist ===

[[http://www.ngssoftware.com/papers/HackproofingMySQL.pdf|Source]]

FIXME Eng -> D

The following is a quick-reference list of things to do when you're tightening the security
of your MySQL server. Other excellent lists can be found at

http://www.databasesecurity.com/mysql.htm
http://www.securityfocus.com/infocus/1667
http://www.kitebird.com/articles/ins-sec.html

  * Read the MySQL security guidelines at http://dev.mysql.com/doc/mysql/en/Security.html
  * Deploy either IPTables (Linux) or an IPSec filtering ruleset (Windows) on your MySQL servers.
  * Visit http://www.mysql.com/products/mysql/ often, and check for updates.
  * Remove all non-root MySQL users
  * Rename the 'root' MySQL user to something obscure
  * Create a MySQL user for each web application – or possibly for each 'role' within each web application. For instance, you might have one MySQL user that you use to update tables, and another, lower-privileged user that you use to 'select' from tables. Ensure that MySQL users are restricted by IP address as well as passwords. See section 5.4 of the MySQL manual, “The MySQL Access Privilege System” for more information.
  * Use a low – privileged 'mysql' account to run the MySQL daemon. This is the default on some platforms, but not others. For example, MySQL runs as the local 'system' account under Windows.
  * Run mysqld with the '<nowiki>--</nowiki>chroot' option. Ensure that the MySQL user cannot access files
  * outside of a limited set of directories. Specifically, the MySQL user should be prohibited from reading operating system configuration files. In some cases you might want to prevent the MySQL user from being able to modify the MySQL configuration files.
  * Don't give accounts privileges that they don't absolutely need, especially 'File_priv' and 'Grant_priv'. If you have to interact with the file system from within MySQL, consider creating a seperate MySQL account that your application can use for this purpose.
  * Disable the LOAD DATA LOCAL INFILE command by adding “set-variable=local-infile=0” to the my.cnf file.
  * Know your bugs! Check vulnerability databases such as SecurityFocus and ICAT regularly for MySQL bugs.



===== Troubleshooting =====

Wenn der Start von MySQL nicht klappt, sollte man sich erstmal in den Log-Dateien schlau machen. Er guter Anlaufpunkt ist dabei das Systemlog (''/var/log/syslog'') in der sich dann evtl. folgende (von mir gekürzte!) Zeile finden lässt:
<file>
mysqld_safe[14698]: started
mysqld[14701]: InnoDB: Started; log sequence number 0 43655
mysqld[14701]: [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
mysqld[14701]: [ERROR] Do you already have another mysqld server running on port: 3306 ?
mysqld[14701]: [ERROR] Aborting
</file>
MySQL kann sicht also nicht an den Port 3306 binden. Jetzt gibt es zwei einfache Problemursachen:
  - ein anderes Programm (oder eine andere MySQL-Instanz?) hat sich bereits den Port 3306 geschnappt. Das sollte man mit (''sudo netstat -tulpen'') nachprüfen. In diesem Fall existiert eine ähnliche Zeile zu <file>
Aktive Internetverbindungen (Nur Server)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       Benutzer   Inode      PID/Program name   
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN     109        26526      22498/mysqld 
</file>
  - man hat MySQL eine fehlerhafte IP angegeben, die dann die Bindung an Port 3306 scheitern lässt. Dazu sollte man einmal in ''/etc/mysql/my.cnf'' nach der Zeile <file>bind-address = 127.0.0.1</file> schauen. Mit einem Befehl: <code>grep bind-address /etc/mysql/my.cnf</code>
Als IP ist natürlich auch eine andere möglich, etwa wenn die Datenbank über Netzwerk erreichbar sein soll. Wenn diese IP nicht dem aktuellen Stand entspricht (etwa weil diese IP über DHCP vergeben worden ist und man nun eine neue hat) scheitert die Bindung zwangsläufig. Im Zweifel kann man mit sich mit ''ifconfig'' die aktuellen IPs der Netzwerkschnittstellen anzeigen lassen und einmal mit ping IP (wobei IP durch die eingetragene IP ersetzt werden muss) einen Kontaktversuch unternehmen kann. So ist es mir auch schon einmal untergekommen, das die Netzwerkschnittstelle loopback (lo) durch eine Fehleinrichtung nicht die IP 127.0.0.1 hatte.

:!: [[http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html|Can't connect to [local] MySQL server]]


==== mysql table is marked as crashed and last (automatic?) repair failed ====

Reparatur anstoßen: <code>repair table TABELLENNAME;</code>

Quelle: [[https://stackoverflow.com/questions/8843776/mysql-table-is-marked-as-crashed-and-last-automatic-repair-failed|mysql table is marked as crashed and last (automatic?) repair failed]]

==== mysql 10.3 (buster) -> 10.5 (bullseye) ====

Folgende Einstellungen sind weggefallen:
<file>
query_cache_type'
2query_cache_size'
query_cache_limit
innodb_large_prefix
innodb_file_format'
show_compatibility_56
</file>

==== MySQL 8.4 mysql_native_password und ansible ====

MySQL 8.4 lädt standardmäßig kein mysql_native_password mehr (Fehlermeldung: ''Plugin 'mysql_native_password' is not loaded.''), in MySQL 9.0 existiert es nicht mehr.
Nachfolger: caching_sha2_password (PHP unterstützt das seit 7.4).

siehe auch: https://php.watch/articles/fix-php-mysql-84-mysql_native_password-not-loaded

statt 
<code yaml>
community.mysql.mysql_user:
  password: 'secret'
</code>
also
<code yaml>
community.mysql.mysql_user:
  password: '*EE0D72C1085C46C5278932678FBE2C6A782821B4'
  encrypted: true
</code>
===== MySQL interna =====

==== Binary Logs ====

Die [[https://dev.mysql.com/doc/refman/8.0/en/binary-log.html|Binary Logs]] sind zur Absicherung von Schreibzugriffen in Replikationens-Umgebungen und Restore gedacht.

Logs sofort "purgen":<code bash>mysql> PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 2 day);</code>

Für die Sitzung: <code bash>mysql> set global expire_logs_days = 2;</code>

Dauerhaft:  ''/etc/mysql/my.cnf''
<file>
[mysqld]
expire_logs_days=2
</file>
==== Datentypen ====
[[http://www.schmager.de/mysql.php|MySQL-Datentypen]]
[[http://www.grammiweb.de/uebersichten/mysql-datentypen.shtml|MySQL-Datentypen II]]
[[http://dev.mysql.com/doc/refman/5.1/de/connector-j-reference-type-conversions.html|Datentypen von Java, JDBC und MySQL]]
[[http://www.webmaster-tipps.net/content/view/48/70/|MySQL - Datentypen III]]

==== Speicherengines ====
MySQL bietet verschiedene Speicher-Engines für den Zugriff auf die Daten an. Jedes ist auf ihr Einsatz-Szenario optimiert.

**[[wpde>MyISAM]]** ist die Standard-Speicher-Engine von MySQL unter UNIX-basierten Systemen und ist für schnellen Zugriff auf Tabellen und Indizes ohne Transaktionssicherung gedacht.

Transaktionen bietet [[wpde>InnoDB]] an und ist unter [[windows:Windows]] als die default-Engine eingestellt, allerdings unterstützt [[wpde>InnoDB]] keine Volltextsuche.

[[wpde>|Wikipedia]] bietet einen guten Überblick über alle [[http://de.wikipedia.org/wiki/MySQL#Speicher-Engines|Speicher-Engines von MySQL]].
==== Index ====
Ein [[wpde>Datenbankindex]], oder kurz Index, ist eine von den Datenstruktur getrennte Indexstruktur in einer Datenbank, die die Suche und das Sortieren nach bestimmten Feldern beschleunigt.

==== Trigger ====
Ein Datenbanktrigger, meist nur Trigger genannt, ist eine Funktionalität von diversen Datenbankmanagementsystemen, insbesondere von großen relationalen Datenbankmanagementsystemen.

==== Stored Procedure ====
Der Begriff [[wpde>Stored Procedure|Gespeicherte Prozedur]] (GP) oder englisch Stored Procedure (SP) bezeichnet eine Funktion bestimmter Datenbankmanagementsysteme. In einer Stored Procedure können ganze Abläufe von Anweisungen unter einem Namen gespeichert werden, die dann auf dem Datenbankserver zur Verfügung stehen und ausgeführt werden können. Mit anderen Worten: Eine SP ist ein eigenständiger Befehl, der eine Abfolge von gespeicherten Befehlen ausführt.

==== User Defined Function ====
[[wpde>User Defined Function|Benutzerdefinierte Funktionen]] (engl. User Defined Function, Abk. UDF)) werden in Datenbanksystemen verwendet.

==== View (Sicht) ====
[[wpde>Sicht (Datenbank)]]