Inhaltsverzeichnis

MySQL

MySQL

Aussprache des Namens: Die offizielle Aussprache ist Mei-es-kju-ell.

Seit MySQL 3.23 (Januar 2001) gibt es eine eingebaute Replikation.
Seit MySQL 5.5 (03. Dezember 2010) unterstützt die Standard-Speicherengine (InnoDB) erst Transaktionen.

Welche MySQL-Variante ist installiert?
> dpkg -l | grep -Ei 'mysql|mariadb|percona|xtradb'

Cluster-Eigenschaften der verschiedenen MySQL-Derivate
Oracle
- apt install mysql-server
- Die MySQL-Versionen haben einen Lebenszyklus von 8 Jahren
  - MySQL Product Support EOL Announcements
  - MySQL Lifecycle (EOL)
    - MySQL 8.0: April 2026
    - MySQL 5.7: 21. Oktober 2023
    - MySQL 5.6: 05. Februar 2021
    - MySQL 5.5: 03. December 2018
    - MySQL 5.1: 31. December 2013
    - MySQL 5.0: 09. January 2012
Percona
- Percona-Repository
- Percona xtrabackup
MariaDB
- MariaDB
MySQL-Repository für Ubuntu einbinden (alt)

Ab Version 8.0 ist MySQL nicht mehr abwärtskompatibel – weder zu älteren MySQL-Versionen noch zu MariaDB. MariaDB möchte sich von MySQL zukünftig vor allem durch Flexibilität abgrenzen. Anwendern steht neben den von MySQL unterstützten Standard-Engines eine stetig wachsende Anzahl alternativer Datenbank-Engines für spezielle Anwendungsfälle zur Verfügung.

> echo "SHOW VARIABLES WHERE Variable_name LIKE ('version%');" | mysql -t
+-------------------------+-------------------------------------------------------------------------------------+
| Variable_name           | Value                                                                               |
+-------------------------+-------------------------------------------------------------------------------------+
| version                 | 8.0.26-16.1                                                                         |
| version_comment         | Percona XtraDB Cluster (GPL), Release rel16, Revision b141904, WSREP version 26.4.3 |
| version_compile_machine | x86_64                                                                              |
| version_compile_os      | Linux                                                                               |
| version_compile_zlib    | 1.2.11                                                                              |
| version_suffix          | .1                                                                                  |
+-------------------------+-------------------------------------------------------------------------------------+

ignore-db-dir        = lost+found

Cluster-Eigenschaften der verschiedenen MySQL-Derivate
mysql-community-server von Oracle
- mysql-community-server 5.7
- mysql-community-server 8.0
  1. gpg --import mysql_pubkey.asc
  2. apt-key add mysql_pubkey.asc
  3. apt update
  4. apt install mysql-apt-config
  5. apt install mysql-community-server
Installation von MySQL 5.6.13 auf Ubuntu 13.10
Installation von MySQL 5.7 auf Ubuntu 16.04.5 LTS
MySQL-Repository für Ubuntu einbinden
Cluster-Verwaltung
- MySQL-MMM - wird nicht mehr gepflegt
- KeepaliveD
Percona XtraDB Cluster (PXC) - eine von Percona verbesserte Version von MySQL
MySQL mit SSL verschlüsselten Verbindungen

Maximale Anzahl der bisher gleichzeitigen Verbindungen (Galera/WSREP)

~/bin/Max_used_connections.sh

#!/bin/bash
 
for S in $(ls /var/run/mysqld/mysqld*.sock)
do
	echo "SHOW STATUS;" | mysql --socket=${S} | grep -Ei 'wsrep_incoming_addresses|Max_used_connections[^_]' | awk '{print $2}' | tr -s '\n' '\t'
	echo
done

</hidden>

# /root/bin/Max_used_connections.sh
4	db01:3316,db02:3316,db03:3316
1084	db01:3326,db02:3326,db03:3326

RAM-Verbrauch abschätzen

> touch ~/bin/MySQL-RAM.sh
> chmod 0755 ~/bin/MySQL-RAM.sh
> vim ~/bin/MySQL-RAM.sh

~/bin/MySQL-RAM.sh

#!/bin/bash
 
#VERSION="v2024061200"		# erstellt
VERSION="v2024061800"		# verbessert
 
if [ x = "x${1}" ] ; then
	STECKER="-S /var/run/mysqld/mysqld.sock"
else
	STECKER="-S /var/run/mysqld/mysqld_${1}.sock"
fi
 
# mysqlshow --defaults-file=~/.my.cnf --socket=/var/run/mysqld/mysqld.sock
MYSQL_AUSGABE="$(echo "SHOW VARIABLES;" | mysql -N ${STECKER} | grep -Ei '^key_buffer_size|^read_buffer_size|^sort_buffer_size|^max_connections')"
echo "${MYSQL_AUSGABE}" | sed 's/.*/    &/'
echo "# key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory"
KEY_BUFFER_SIZE="$(echo "${MYSQL_AUSGABE}" | awk '/^key_buffer_size/{print $2}')"
READ_BUFFER_SIZE="$(echo "${MYSQL_AUSGABE}" | awk '/^read_buffer_size/{print $2}')"
SORT_BUFFER_SIZE="$(echo "${MYSQL_AUSGABE}" | awk '/^sort_buffer_size/{print $2}')"
MAX_CONNECTIONS="$(echo "${MYSQL_AUSGABE}" | awk '/^max_connections/{print $2}')"
echo "
SHOW STATUS;
SHOW VARIABLES;
" | mysql -N ${STECKER} | grep -Ei 'Max_used_connections|^max_allowed_packet'
echo
echo "SELECT * FROM sys.memory_global_total \G;" | mysql -t ${STECKER} | grep -F total_allocated
echo "${KEY_BUFFER_SIZE} ${READ_BUFFER_SIZE} ${SORT_BUFFER_SIZE} ${MAX_CONNECTIONS}" | awk '{printf "MySQL-RAM-Bedarf: ca. %.0f MiB\n", ($1 + ($2 + $3) * $4) / 1024}'
echo
free -m

</hidden>

> ~/bin/MySQL-RAM.sh
    key_buffer_size	4194304
    max_connections	50
    read_buffer_size	65536
    sort_buffer_size	131072
# key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory
Max_used_connections	31
Max_used_connections_time	2024-06-12 00:25:12
max_allowed_packet	33554432
 
MySQL-RAM-Bedarf: ca. 13696 MiB
 
              total        used        free      shared  buff/cache   available
Mem:          15997        9797         157           0        6042        5869
Swap:             0           0           0

verschiedene MySQL-Cluster-Varianten

Die MySQL-Shell enthält die MySQL AdminAPI, auf die über die globale Variable dba und die zugehörigen Methoden zugegriffen wird. MySQL-Shell bietet neben einem nativen SQL-Modus zwei Skriptsprachenmodi, JavaScript und Python. Wenn MySQL-Shell gestartet wird, befindet es sich standardmäßig im JavaScript-Modus. Wechseln Sie den Modus, indem Sie \js für den JavaScript-Modus und \py für den Python-Modus ausgeben.
- InnoDB ReplicaSet - Asynchrone Replikation, die auf GTID basiert
- Group Replication - Semisynchrone Replikation
  - Im Multi-Primär-Modus können langsamere Mitglieder auch einen übermäßigen Rückstand an zu zertifizierenden und anzuwendenden Transaktionen aufbauen, … → Das bedeutet, es ist keine synchrone Replikationstechnik!
- InnoDB Cluster - Back-End der Group Replication
- MySQL Router - übernimmt die Aufgabe, die auch ProxySQL erledigt, nur speziell für "Group Replication"
Percona XtraDB Cluster - Synchrone Replikation

Asynchrone MySQL-Replikation

GTID-Cluster mit MySQL 8.0

Synchrone MySQL-Replikation

verschiedenes

Kurtzübersicht SQL:

(Lese-)Rechte auf alle Tabellen einer DB erteilen: GRANT SELECT ON db.* TO fritz; FLUSH PRIVILEGES;
Tabelle umbenennen bzw. verschieben: RENAME TABLE db_alt.tabelle_alt TO db_neu.tabelle_neu;
alle Tabellenpartitionen anzeigen: SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE PARTITION_DESCRIPTION IS NOT NULL;
Inhalt einer Partition zählen: SELECT COUNT(*) FROM db.tabelle PARTITION (name_der_partition);
Datensatz eintragen: INSERT INTO db.tabelle SELECT * FROM db.tabelle WHERE feld01='wert';
Datensatz ändern: UPDATE db.tabelle SET feld02='wert01' WHERE feld01='wert';
Datensatz löschen (einzelne Datensätze, korrekt): DELETE FROM db.tabelle WHERE feld01='wert';
Datensatz löschen (alles, schnell): TRUNCATE TABLE db.tabelle;
Root-Passwort ändern (vor MySQL 8.0): UPDATE mysql.user SET authentication_string=PASSWORD('geheim') WHERE User='root';
Root-Passwort ändern (ab MySQL 8.0): ALTER USER root IDENTIFIED BY 'geheim'; FLUSH PRIVILEGES;

TRUNCATE TABLE leert eine Tabelle vollständig. Es erfordert das DROP-Privileg. Logisch gesehen ähnelt TRUNCATE TABLE einer DELETE-Anweisung, die alle Zeilen löscht, oder einer Folge von DROP TABLE- und CREATE TABLE-Anweisungen.

einen Benutzer mit Passwort in einer anderen DB eintragen, ohne das Passwort kennen zu müssen:

# echo "SELECT user,authentication_string FROM mysql.user;" | mysql -hdbserver -P3306 -N | awk '{print "CREATE USER",$1,"; UPDATE mysql.user SET authentication_string=\""$2"\" WHERE user=\""$1"\";"}'
...
CREATE USER fritz;
CREATE USER fritz IDENTIFIED BY 'Passwort'; FLUSH PRIVILEGES;
CREATE USER fritz IDENTIFIED WITH mysql_native_password AS '*EC567F88BA2EC345EAE597C24C33B0364D5DBA60';

ALTER USER fritz IDENTIFIED BY 'Passwort'; FLUSH PRIVILEGES;
UPDATE mysql.user SET authentication_string="*EC567F88BA2EC345EAE597C24C33B0364D5DBA60" WHERE user="fritz";
FLUSH PRIVILEGES;
...

Aus diesen CREATE USER-Kommandos sucht man sich dann die relevanten aus und gibt sie in der neuen DB ein.

> echo "SHOW DATABASES LIKE 'mysql';" | mysql -t
+------------------+
| Database (mysql) |
+------------------+
| mysql            |
+------------------+

> echo "USE mysql; SHOW TABLES LIKE 'user';" | mysql -t
+------------------------+
| Tables_in_mysql (user) |
+------------------------+
| user                   |
+------------------------+

MySQL mit Replikation

MySQL mit Replikation

Variablen ausgeben

@@ zeigt auf global definierte Variablen

@ zeigt auf für die Session definierte Variablen

SELECT @@server_id
SELECT CONCAT(@@server_id, UUID());
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES WHERE Variable_name IN ('hostname');
SHOW STATUS WHERE Variable_name IN ('Connections','Created_tmp_files','Innodb_buffer_pool_write_requests','Innodb_data_fsyncs','Innodb_data_writes','Innodb_data_written','Innodb_log_write_requests','Innodb_log_writes','Innodb_pages_written','Queries','Ssl_cipher','Uptime','Uptime_since_flush_status','Innodb_buffer_pool_load_status');

nach Datum selektiert ausgeben

aktueller Zeitstempel: NOW() → 2020-03-25 17:31:46
aktuelles/heutiges Datum: DATE(NOW()) → 2020-03-25
heutiges Datum: DATE(NOW() - INTERVAL 0 DAY) → 2020-03-25
gestriges Datum: DATE(NOW() - INTERVAL 1 DAY) → 2020-03-24
Datum von Vorgestern: DATE(NOW() - INTERVAL 2 DAY) → 2020-03-23

die 10 ältesten Einträge von Gestern, absteigend sortiert:

SELECT zeitstempel
FROM datenbank.tabelle
WHERE zeitstempel < DATE(NOW() - INTERVAL 0 DAY)
AND zeitstempel > DATE(NOW() - INTERVAL 1 DAY)
ORDER BY zeitstempel
ASC
LIMIT 10;

die 10 jüngsten Einträge von Gestern, aufsteigend sortiert:

SELECT zeitstempel
FROM datenbank.tabelle
WHERE zeitstempel < DATE(NOW() - INTERVAL 0 DAY)
AND zeitstempel > DATE(NOW() - INTERVAL 1 DAY)
ORDER BY zeitstempel
DESC
LIMIT 10;

die 10 ältesten Einträge von Vorgestern, absteigend sortiert:

SELECT zeitstempel
FROM datenbank.tabelle
WHERE zeitstempel < DATE(NOW() - INTERVAL 1 DAY)
AND zeitstempel > DATE(NOW() - INTERVAL 2 DAY)
ORDER BY zeitstempel
ASC
LIMIT 10;

VIEWs zeigen

> echo "SHOW STATUS WHERE variable_name LIKE 'Max_used_connections%' OR variable_name LIKE 'Uptime';" | mysql -t
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| Max_used_connections      | 386                 |
| Max_used_connections_time | 2025-06-24 10:19:25 |
| Uptime                    | 5931173             |
+---------------------------+---------------------+

VIEWs zeigen

alle VIEWs in der DB zeigen:

> echo "SELECT * FROM VIEWS \G;" | mysql -t information_schema

alle VIEWs in einer bestimmten DB zeigen:

> echo "SHOW FULL TABLES IN DatenBankName WHERE table_type = 'VIEW';" | mysql -t

nur eine mit Details anzeigen (die Platzhalter in den eckigen Klammern, werden bei der Ausgabe von "SELECT * FROM VIEWS" mit Namen angezeigt):

> echo "SHOW CREATE VIEW [TABLE_NAME] \G;" | mysql -t [TABLE_SCHEMA]
> echo "SHOW CREATE VIEW viewname \G;" | mysql -t datenbankname

Dirty Pages

> echo "SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct'; SHOW GLOBAL STATUS LIKE '%dirty%';" | mysql -t
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 75.000000 |
+----------------------------+-----------+
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| Innodb_buffer_pool_pages_dirty | 115     |
| Innodb_buffer_pool_bytes_dirty | 1884160 |
+--------------------------------+---------+

Stellt man "innodb_max_dirty_pages_pct" auf "0", dann werden beim DB anhalten immer alle Daten komplett in die Tabellen geschrieben. Das hat zur Folge, dass die DB mehr Zeit zum anhalten benötigt.

Eine Tabelle stückchenweise durchlaufen

/root/bin/Tabelle_stückchenweise durchlaufen.sh

#!/bin/bash
 
DB="test_db"
TAB="test_tab"
 
VON="0"
ANZAHL="1000"
#NR="16525955"
NR="$(echo "SELECT COUNT(*) FROM ${DB}.${TAB};" | mysql -N)"
while (( "${VON}" <= "${NR}" ))
do
        # hier wird die Größe der Datensätze aus einer Spalte gemessen und in eine andere Tabelle geschrieben
        echo "INSERT INTO ${DB}.data_length_${TAB}_test SELECT LENGTH(ENTRYDATA), ID FROM ${DB}.${TAB} LIMIT ${VON},${ANZAHL};" | mysql
        VON="$(echo "${VON} ${ANZAHL}" | awk '{print $1 + $2 + 1}')"
done

Überwachung

/root/bin/check_debian_db.sh

#!/bin/bash
 
#==============================================================================#
#
# DB-Check
#
#------------------------------------------------------------------------------#
#
# /etc/mysql/debian.cnf
#
# mysqladmin --defaults-file=/etc/mysql/debian.cnf ping
# mysqld is alive
#
#==============================================================================#
 
DB_STATUS=$(mysqladmin --defaults-file=/etc/mysql/debian.cnf ping);
 
#echo "
#DB_STATUS='${DB_STATUS}'
#"
 
if [ "${DB_STATUS}" = "mysqld is alive" ]
then
        # An
        exit 0;
else
        # Aus
        exit 1;
fi

Benchmark

MySQL - Benchmark
SHOW ENGINE INNODB STATUS\G / SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G; → SHOW ENGINE
set GLOBAL innodb_status_output=ON; → Enabling InnoDB Monitors

Tabelle per SQL in eine andere Datenbank kopieren

http://blog.mixable.de/mysql-tabellen-in-andere-datenbank-kopieren/

> CREATE TABLE db2.newTable LIKE db1.oldTable
> ALTER  TABLE db2.newTable DISABLE KEYS

alle Tabellen:

> INSERT INTO  db2.newTable SELECT * FROM db1.oldTable

ausgewählte Spalten:

> INSERT INTO  db2.newTable (sp3,sp4,sp7) SELECT sp3,sp4,sp7 FROM db1.oldTable

> ALTER  TABLE db2.newTable ENABLE KEYS

aus einer Tabelle (oldTable) bestimmte Datensätze in eine andere Tabelle (newTable) überschreiben (löschen+kopieren):

> for id in 1 2 3 4 5 6 7 8 9 ; do echo "DELETE FROM newTable WHERE id='${id}'; INSERT INTO newTable SELECT * FROM oldTable WHERE id='${id}';" | mysql -N db ; done

InnoDB Status

> SHOW ENGINE INNODB STATUS;

Aus dieser Ausgabe kann man folgende Informationen gewinnen:

BACKGROUND THREAD
SEMAPHORES
LATEST DETECTED DEADLOCK
TRANSACTIONS
FILE I/O
INSERT BUFFER AND ADAPTIVE HASH INDEX
LOG
BUFFER POOL AND MEMORY
INDIVIDUAL BUFFER POOL INFO
ROW OPERATIONS

MySQL-Sicherungsmethoden

Für dieses Beispiel wurde eine VM mit zwei CPU-Kernen und einem MySQL-Volumen (LVM) von ca. 280GiB größe verwendet. Die MySQL-Daten in dem Verzeichnis bzw. auf dem Volumen belegen, inklusive BIN-LOGs, ca. 99 GiB. Alle folgenden Angaben beziehen sich auf diese Werte.

Im ersten anlauf wurde ein Verzeichnis per SSHFS eingebunden und dort ein (mit gzip gepackter) Dump abgelegt.

Es wurde dieses Kommando dafür eingesetzt (mit Bandbreitenbegrenzung auf 3 MiB/s: mit "pv"):

mysqldump -AREc --opt --triggers | gzip -9 | pv -q -L 3m > mysqldump.tar.gz → ca. 1,9 Stunden
- unkomprimierte Dump-Größe: ca. 33 GiB
- komprimierte Dump-Größe: ca. 20 GiB

Weiterhin gibt es die Möglichkeit ein LVM-SnapShot als Sicherung zu verwendet, dabei wären dann auch die BIN-LOG's gesichert.

Ein experimenteller Sicherungstest hat folgende Messwerte ergeben:

tar cvf - /etc/mysql /var/lib/mysql | bzip2 -9 > mysqlbackup.tar.bz2 → ca. 10,5 Stunden
- unkomprimierte Daten-Größe: ca. 99 GiB
- Archiv-Größe: 53,5 GiB
7za a -sfx mysqlbackup_-_ELF_LSB_7z-sfx.exe /etc/mysql /var/lib/mysql → ca. 7 Stunden
- unkomprimierte Daten-Größe: ca. 99 GiB
- Archiv-Größe: 13,5 GiB

Daraus geht eindeutig hervor, dass in diesem Beispiel das packen mit 7-Zip deutlich effizienter ist als das packen Einsatz von BZip2.

passwortlosen Login

~/.my.cnf

[client]
host     = localhost
user     = root
password = geheim
socket   = /var/run/mysqld/mysqld.sock

speziell bei Debian kann man das (als "root") auch so machen:

# ln -s /etc/mysql/debian.cnf ~/.my.cnf

meine_zugangsdaten.cnf

[client]
user     = root
password = ganzgeheim
socket   = /var/run/mysqld/mysqld.sock

Das kann man so machen, zu beachten ist, dass die Option --defaults-file= die erste sein muss:

> mysqlshow --defaults-file=/etc/mysql/meine_zugangsdaten.cnf -t -hdbrechner
> mysqlshow --defaults-file=/home/ich/.my_dbprivat.cnf -t -hdbrechner
> mysqlshow --defaults-file=/etc/mysql/debian.cnf -t -hdbrechner

Allgemeines

sonst liest MySQL die vorgesehene Konfiguration mein Start nicht ein:

# chmod 0644 /etc/mysql/my.cnf

Ein klassischer MySQL_5-Cluster orientiert sich an der Variablen "server_id", die auf allen Knoten unterschiedlich sein muss!

> echo "SELECT @@server_id;" | mysql -t
+-------------+
| @@server_id |
+-------------+
|        1002 |
+-------------+

oder so

> echo "SHOW VARIABLES LIKE 'server_id';" | mysql -t
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1002  |
+---------------+-------+

Ein Galera-Cluster orientiert sich an der Variablen "wsrep_cluster_name", die auf allen Knoten gleich sein muss!

> echo "SHOW VARIABLES WHERE Variable_name IN ('wsrep_cluster_name');" | mysql -t
+--------------------+-------------+
| Variable_name      | Value       |
+--------------------+-------------+
| wsrep_cluster_name | meincluster |
+--------------------+-------------+

Hinweis für die Nutzung von Galera ⇒ Es dürfen in den Datenbanken nur InnoDB-Tabellen und keine MyISAM-Tabelle verwendet werden! Überprüfen kann man das mit diesem Kommando:

mysqlshow | grep -E '^[|]' | grep -Ev 'Databases|information_schema|performance_schema|mysql' | awk '{print $2}' | while read DB;do mysqlshow -i ${DB} | grep -E '^[|]' | awk '{print $2,$4}' | grep -Eiv 'InnoDB|_view|Name Engine' | sed "s/.*/${DB}.&/";done

eine Tabelle in eine Datei speichern und aus einer Datei wiederherstellen

aus Sicherheitsgründen darf MySQL nur in ein bestimmtes Verzeichnis schreiben und aus ihm lesen:

> echo "SHOW VARIABLES LIKE 'secure_file_priv';" | mysql -B -N | awk '{print $2}'

so wird der Inhalt einer Tabelle in eine Datei geschrieben:

> echo "SELECT * INTO OUTFILE '/var/lib/mysql-files/tabelle.sql' FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM datenbank.tabelle;" | mysql

so wird der Inhalt aus einer Datei in eine Tabelle geschrieben:

> echo "LOAD DATA INFILE '/var/lib/mysql-files/tabelle.sql' REPLACE INTO TABLE datenbank.tabelle FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';" | mysql -f

Hierbei muss man auf die Feldtrennzeichen (in diesem Beispiel '|') achten, diese dürfen nicht in der Tabelle vorkommen.

LOAD DATA INFILE

echo "CREATE USER superuser IDENTIFIED BY '...'; FLUSH PRIVILEGES;" | mysql
echo "GRANT SELECT, INSERT, UPDATE, DELETE ON * TO 'superuser'@'%'; FLUSH PRIVILEGES;" | mysql dbname
echo "GRANT FILE, SUPER ON *.* TO 'superuser'@'%'; FLUSH PRIVILEGES;" | mysql
echo "SHOW GRANTS FOR superuser;" | mysql -t

Tabellen_anlegen.sql

DROP TABLE IF EXISTS `dbname`.`tabname`;
 
CREATE TABLE `dbname`.`tabname` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`pwhash` VARCHAR(768) NOT NULL,
	`count` VARCHAR(2) NOT NULL,
	PRIMARY KEY (`id`),
	KEY `pwhash_INDEX` (`pwhash`)
) ENGINE=InnoDB
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 128 */
;
 
SHOW CREATE TABLE `dbname`.`tabname`;

Beispiel aus Doku

SET GLOBAL local_infile=1;
 
LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE dbname.tabname
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

test.sql

SET GLOBAL local_infile=1;
 
START TRANSACTION;
LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE dbname.tabname
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\n'
(pwhash, count);
COMMIT;

cat test.sql | mysql --local-infile=1 -usuperuser -p...
echo "SELECT * FROM dbname.tabname LIMIT 12;" | mysql -t
+----+-------------------------------------+-------+
| id | pwhash                              | count |
+----+-------------------------------------+-------+
|  1 | 001C6CE64AFA01E95015ABAE848E45F6E01 | 4     |
|  2 | 001C6CE64AFA01E95015ABAE848E45F6E02 | 4     |
|  3 | 001C6CE64AFA01E95015ABAE848E45F6E03 | 4     |
|  4 | 001C6CE64AFA01E95015ABAE848E45F6E04 | 4     |
|  5 | 001C6CE64AFA01E95015ABAE848E45F6E05 | 4     |
|  6 | 001C6CE64AFA01E95015ABAE848E45F6E06 | 4     |
|  7 | 001C6CE64AFA01E95015ABAE848E45F6E07 | 4     |
|  8 | 001C6CE64AFA01E95015ABAE848E45F6E08 | 4     |
|  9 | 001C6CE64AFA01E95015ABAE848E45F6E09 | 4     |
| 10 | 001C6CE64AFA01E95015ABAE848E45F6E10 | 4     |
+----+-------------------------------------+-------+

bekannte Fehler und ihre Lösungen

Too many connections

zuviele relay log's

damit die Relay-Logs abgearbeitet werden, muss der SLAVE-Prozess (mindestens der SQL_THREAD) laufen und dann ein FLUSH für die Relay-Logs abgesendet werden:

> echo "STOP SLAVE;" | mysql
> echo "START SLAVE SQL_THREAD;" | mysql
> echo "FLUSH RELAY LOGS;" | mysql

danach:

> echo "START SLAVE;" | mysql

Repairing The Replication: skip the invalid SQL query

> echo "STOP SLAVE;" | mysql
> echo "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;" | mysql
> echo "START SLAVE;" | mysql

SQL Error (2027): Malformed packet

This error only occurs if the result set is more than a certain number of rows (using LIMIT 14561 rows works, 14562 gives this error).

Oft tritt dieser Fehler auf, wenn CSV-Dateien importiert werden, die unsauber formatiert sind.

arbeiten mit TEMPORARY TABLE

In diesem Beispiel haben wir in einer Datenbank (testdatenbank) zwei Tabellen, aus denen ganz bestimmte Datensätze mit den gleichen IDs gelöscht werden sollen. Die Bedingung lautet, dass die Datensätze von vor dem 2021-01-29 sein sollen, keinen Datumseintrag haben sollen, der Status 'successful' oder 'error' lauten soll und zu einer aus einer Reihe von Kategorien gehören soll.

Zuerst wird mit USE die DB geöffnet, dann wird im ersten Block eine "TEMPORARY TABLE" angelegt (diese verschwindet automatisch, wenn die DB-Verbindung wieder getrennt wird. Im mittleren Block werden die benötigten IDs ausgelesen und in die "TEMPORARY TABLE" geschrieben. Und im letzten Block werden innerhalb einer Transaktion alle Datensätze aus beiden Tabellen gelöscht, die einer der IDs aus der "TEMPORARY TABLE" entsprechen.

beispiel_mit_temporary_table.sql

USE testdatenbank;
 
CREATE TEMPORARY TABLE temp_tabelle(
    id INT PRIMARY KEY
);
 
INSERT INTO temp_tabelle
SELECT id
        FROM test_tabelle_1
        WHERE (startDate < '2021-01-29 00:00:00' OR startDate IS NULL)
        AND state IN ('successful','error')
        AND kategorie IN (73,4,5,6,10,125,126,75,41,59,60,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,123,124,128,134,135);
;
 
START TRANSACTION;
        DELETE FROM test_tabelle_1 WHERE id IN (SELECT id FROM temp_tabelle);
        DELETE FROM test_tabelle_2 WHERE id IN (SELECT id FROM temp_tabelle);
COMMIT;

Start mit MySQL

sollte es Fehlermeldungen beim Start geben, dann kann oft eines dieser Kommandos helfen:

> mysqlcheck -Ag --auto-repair
> mysql_upgrade --force

Ab MySQL 8.0:

> mysql_upgrade 
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically.
Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory.
To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary.
The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons.
In that case, the upgrade sequence will run again during the next MySQL server start.
If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option
to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.

die Strucktur (das Schema) aller Datenbanken schreibt man so raus:

# mysqldump -Ad
# mysqldump --opt -AEcd

den Typ und die DB-Engine aller Tabellen in dem DBMS ausgeben:

# echo "SELECT table_schema, table_name, table_type, engine FROM information_schema.tables;" | mysql -t
+--------------------------+---------------------------------------+-------------+--------+
| table_schema             | table_name                            | table_type  | engine |
+--------------------------+---------------------------------------+-------------+--------+
...
| information_schema       | VIEWS                                 | SYSTEM VIEW | MyISAM |
| kontakt                  | adresse                               | BASE TABLE  | InnoDB |
...

MySQL commands:

Note that all text commands must be first on line and end with ';'
help    (\h)   Display this help.
?       (\?)   Synonym for `help'.
clear   (\c)   Clear command.
connect (\r)   Reconnect to the server. Optional arguments are db and host.
edit    (\e)   Edit command with $EDITOR.
ego     (\G)   Send command to mysql server, display result vertically.
exit    (\q)   Exit mysql. Same as quit.
go      (\g)   Send command to mysql server.
nopager (\n)   Disable pager, print to stdout.
notee   (\t)   Don't write into outfile.
pager   (\P)   Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)   Print current command.
quit    (\q)   Quit mysql.
rehash  (\#)   Rebuild completion hash.
source  (\.)   Execute a SQL script file. Takes a file name as an argument.
status  (\s)   Get status information from the server.
tee     (\T)   Set outfile [to_outfile]. Append everything into given outfile.
use     (\u)   Use another database. Takes database name as argument.

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

will man eine Replikation aufbauen, dann kommt es gelegentlich zu dieser Fehlermeldung:

*************************** 2. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.5.103
                  Master_User: otto
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 918974056
               Relay_Log_File: mysql-relay-bin-live.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1872
                   Last_Error: Slave failed to initialize relay log info structure from the repository
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 918974056
              Relay_Log_Space: 0
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1872
               Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 160815 13:07:03
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 7311df03-83de-11e5-a96b-00facb90c1a2:25839145-25853443,
dcd4eaf5-2411-11e6-b91b-842b2b6265d0:1-2,
e9c12dc7-3d36-11e6-ae3f-842b2b626ca2:1-3200
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: live
           Master_TLS_Version:

Dann kann es oft schon helfen, wenn man einfach nur das macht:

> echo "STOP SLAVE ; START SLAVE ;" | mysql -S /var/run/mysqld/mysqld_3355.sock

…sollte es dann weiterhin zu dieser Fehlermeldung kommen, zusätzlich einen Restart des DBMS:

> /etc/init.d/mysql_3355 restart
> echo "STOP SLAVE ; START SLAVE ;" | mysql -S /var/run/mysqld/mysqld_3355.sock

…sollte jetzt immer noch dieser Fehlermeldung ausgegeben werden, dann müssen wir einen RESET SLAVE machen!

Dabei ist aber Vorsicht geboten! Besonders, wenn man mit Channel_Name arbeitet!

In diesem Beispiel hat der Channel live das Problem, die andere(n) Replikation(en) laufen einwandfrei. Also dürfen wir auch nur den betroffenen Channel reseten:

> echo "RESET SLAVE FOR CHANNEL 'live';" | mysql -t -S /var/run/mysqld/mysqld_3355.sock

dann sieht die Statusausgabe so aus:

*************************** 2. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.5.103
                  Master_User: otto
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin-live.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 0
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 7311df03-83de-11e5-a96b-00facb90c1a2:25839145-25853443,
dcd4eaf5-2411-11e6-b91b-842b2b6265d0:1-2,
e9c12dc7-3d36-11e6-ae3f-842b2b626ca2:1-3200
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: live
           Master_TLS_Version:

Jetzt müssen die Replikations-Daten erneut eingegeben werden:

> echo "CHANGE MASTER TO MASTER_HOST='192.168.5.103', MASTER_PORT=3306, MASTER_USER='otto', MASTER_PASSWORD='geheim', MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=918974056 FOR CHANNEL 'live';" | mysql -S /var/run/mysqld/mysqld_3355.sock
> echo "START SLAVE;" | mysql -S /var/run/mysqld/mysqld_3355.sock

Nun sollte alles prima laufen:

*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.103
                  Master_User: otto
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 918974056
               Relay_Log_File: mysql-relay-bin-live.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 918974056
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 301
                  Master_UUID: ea9680e3-0618-11e5-b548-0075169c5ba7
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 7311df03-83de-11e5-a96b-00facb90c1a2:25839145-25853443,
dcd4eaf5-2411-11e6-b91b-842b2b6265d0:1-2,
e9c12dc7-3d36-11e6-ae3f-842b2b626ca2:1-3200
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: live
           Master_TLS_Version:

DB fest auf UTF-8 einstellen

http://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf8-in-my-cnf

> vi my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'

Error ‚You cannot ‚ALTER‘ a log table if logging is enabled‘ on query.

http://blog.grufo.com/2016/04/25/error-you-cannot-alter-a-log-table-if-logging-is-enabled-on-query-default-database-mysql-query-alter-table-slow_log/

STOP SLAVE;
SET GLOBAL slow_query_log = ‚OFF‘;
START SLAVE;
SHOW SLAVE STATUS\G;
SET GLOBAL slow_query_log = ‚ON‘;

DB-Benutzer löschen

# echo "DROP USER fritz" | mysql -uroot -p mysql

DB-Benutzer anlegen

/home/bin/add_db_user.sh

#!/bin/bash

# Mit diesem Script wird ein unpreveligierter User angelegt,
# der nur die Rechte SELECT, INSERT und UPDATE für genau eine
# DB bekommt.

while [ "${#}" -ne "0" ]; do
        case "$1" in
                -dbh)
                        DBHOST="${2}"
                        shift
                        ;;
                -dbn)
                        DBNAME="${2}"
                        shift
                        ;;
                -dbu)
                        DBUSER="${2}"
                        shift
                        ;;
                -dbp)
                        DBPASS="${2}"
                        shift
                        ;;
                -rp)
                        RTPASS="${2}"
                        shift
                        ;;
                -h)
                        echo "Usage: $0 -h" >&2
                        echo "Usage: $0 -dbh [DB-Host] -dbn [DB-Name] -dbu [Benutzer] -dbp [Passwort] -rp [root-Passwort]" >&2
                        echo "Usage: $0 -dbh meine.datenbank.de -dbn db_name -dbu fritz -dbp geheim -rp ganzgeheim" >&2
                        exit 1
                        ;;
                *)
                        if [ "$(echo "${1}"|egrep '^-')" ] ; then
                                echo "Der Parameter '${1}' wird nicht unterstützt!"
                        fi
                        shift
                        ;;
        esac
done

if [ -z "${DBHOST}" -o -z "${DBNAME}" -o -z "${DBUSER}" -o -z "${DBPASS}" -o -z "${RTPASS}" ] ; then
echo "
DBHOST='${DBHOST}'
DBNAME='${DBNAME}'
DBUSER='${DBUSER}'
DBPASS='${DBPASS}'
RTPASS='${RTPASS}'
"
        ${0} -h
        exit 2
fi

ping -c3 ${DBHOST}
if [ "$?" != "0" ] ; then
        echo "
        ${DBHOST} ist nicht erreichbar...
        "
        exit 3
fi


# Benutzer anlegen
echo "CREATE USER ${DBUSER} IDENTIFIED BY '${DBPASS}';" | mysql -t -h ${DBHOST} -uroot -p${RTPASS} mysql

# Blle Rechte weg
echo "REVOKE ALL PRIVILEGES,GRANT OPTION FROM ${DBUSER};" | mysql -t -h ${DBHOST} -uroot -p${RTPASS}

# bekommt nur die Rechte: SELECT, INSERT und UPDATE
echo "GRANT SELECT ON * TO ${DBUSER}; FLUSH PRIVILEGES;" | mysql -t -h ${DBHOST} -uroot -p${RTPASS} ${DBNAME}
echo "GRANT INSERT ON * TO ${DBUSER}; FLUSH PRIVILEGES;" | mysql -t -h ${DBHOST} -uroot -p${RTPASS} ${DBNAME}
echo "GRANT UPDATE ON * TO ${DBUSER}; FLUSH PRIVILEGES;" | mysql -t -h ${DBHOST} -uroot -p${RTPASS} ${DBNAME}

# Test
mysqlshow -t -h${DBHOST} -u${DBUSER} -p${DBPASS}
mysqlshow -t -h${DBHOST} -u${DBUSER} -p${DBPASS} ${DBNAME}

Backup / Dump

MySQL-Dump

BINLOG/BIN-LOG von einer MySQL-DB löschen

bis zu einem bestimmten BINLOG alles löschen was älter ist:

> echo "PURGE MASTER LOGS TO 'mysql-bin.013631';" | mysql

nur die letzten 10 BINLOG liegen lassen, alle älteren löschen:

> echo "PURGE MASTER LOGS TO '$(cd /var/lib/mysql/data/ && ls -t mysql-bin.* | egrep '[0-9]' | head -n10 | tail -n1)';" | mysql

BINLOG bis zu einem bestimmten Zeitpunkt löschen:

> echo "PURGE MASTER LOGS BEFORE '2018-03-09 09:58:20';" | mysql

nur die BINLOG-Daten aus den letzten 24 Stunen liegen lassen, alle älteren löschen:

> echo "PURGE MASTER LOGS BEFORE '$(date -d"1 day ago" +"%F %T")';" | mysql

nur die BINLOG-Daten aus den letzten 3 Stunen liegen lassen, alle älteren löschen:

> echo "PURGE MASTER LOGS BEFORE '$(date -d"3 hour ago" +"%F %T")';" | mysql

nur die BINLOG-Daten aus den letzten 30 Minuten liegen lassen, alle älteren löschen:

> echo "PURGE MASTER LOGS BEFORE '$(date -d"30 minute ago" +"%F %T")';" | mysql

/root/bin/PURGE_BIN-LOGS.sh

#!/bin/bash
 
# wenn mal die Platte vollgelaufen ist,
# dann hilft nur noch dieses Skript
 
VERSION="v2019120400"
 
### die aelteste BIN-LOG-Datei loeschen
### aber die juengste nie
### egal wieviele oder wenige dort sind
rm $(ls /var/lib/mysql/data/mysql-bin.0* | head -n1)
 
### per SQL alle BIN-LOGs loeschen, die nicht von heute sind
echo "PURGE MASTER LOGS BEFORE '$(date +'%F')';" | mysql
 
echo "SHOW VARIABLES LIKE 'expire_logs_days';" | mysql -t

Speicherbelegung von MySQL-DB

> watch -n1 'df -h;echo;free;echo;pmap -d $(ps ax | fgrep -v grep | fgrep "/usr/sbin/mysqld" | sed "s/^ *//;s/ .*//") | fgrep "writeable/private:"'
mapped: 13289100K    writeable/private: 11467036K    shared: 0K

https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

MAX_MEMORY_GB.sh

#!/bin/bash
 
#+ @@innodb_additional_mem_pool_size
 
echo "SELECT @@max_connections AS max_connections;" | mysql -t
for v in key_buffer_size query_cache_size innodb_buffer_pool_size innodb_log_buffer_size read_buffer_size read_rnd_buffer_size sort_buffer_size join_buffer_size binlog_cache_size thread_stack tmp_table_size
do
        #echo "echo \"SELECT ( @@${v} ) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;\" | mysql -t"
        echo "SELECT @@${v} / 1024 AS '${v} in kB';" | mysql -t
done
 
echo '################################################################################'
 
echo 'SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;' | mysql -t

> bash MAX_MEMORY_GB.sh
+-----------------+
| max_connections |
+-----------------+
|             600 |
+-----------------+
+-----------------------+
| key_buffer_size in kB |
+-----------------------+
|             8192.0000 |
+-----------------------+
+------------------------+
| query_cache_size in kB |
+------------------------+
|                 0.0000 |
+------------------------+
+-------------------------------+
| innodb_buffer_pool_size in kB |
+-------------------------------+
|                  8388608.0000 |
+-------------------------------+
+------------------------------+
| innodb_log_buffer_size in kB |
+------------------------------+
|                   32768.0000 |
+------------------------------+
+------------------------+
| read_buffer_size in kB |
+------------------------+
|               128.0000 |
+------------------------+
+----------------------------+
| read_rnd_buffer_size in kB |
+----------------------------+
|                   256.0000 |
+----------------------------+
+------------------------+
| sort_buffer_size in kB |
+------------------------+
|               256.0000 |
+------------------------+
+------------------------+
| join_buffer_size in kB |
+------------------------+
|               256.0000 |
+------------------------+
+-------------------------+
| binlog_cache_size in kB |
+-------------------------+
|                 32.0000 |
+-------------------------+
+--------------------+
| thread_stack in kB |
+--------------------+
|           192.0000 |
+--------------------+
+----------------------+
| tmp_table_size in kB |
+----------------------+
|               2.0000 |
+----------------------+
################################################################################
+---------------+
| MAX_MEMORY_GB |
+---------------+
|        8.6811 |
+---------------+

/root/bin/MAX_MEMORY.sh

#!/bin/sh
 
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"
}'

> /root/bin/MAX_MEMORY.sh
+------------------------------------------+--------------------+
|                          key_buffer_size |           8.000 MB |
|                         query_cache_size |           0.000 MB |
|                  innodb_buffer_pool_size |        8192.000 MB |
|          innodb_additional_mem_pool_size |           0.000 MB |
|                   innodb_log_buffer_size |          32.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |        8232.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           0.250 MB |
|                         read_buffer_size |           0.125 MB |
|                     read_rnd_buffer_size |           0.250 MB |
|                         join_buffer_size |           0.250 MB |
|                             thread_stack |           0.188 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |           0.002 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |           1.096 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                118 |
|                          max_connections |                600 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |        8361.293 MB |
|                              TOTAL (MAX) |        8889.422 MB |
+------------------------------------------+--------------------+

im RAM

> echo "SELECT @@innodb_buffer_pool_size/1024/1024 AS 'innodb_buffer_pool_size in MB';" | mysql -t -S /var/run/mysqld/mysqld_3319.sock
+-------------------------------+
| innodb_buffer_pool_size in MB |
+-------------------------------+
|                 2048.00000000 |
+-------------------------------+

Weil InnoDB auch noch andere Puffer benutzt, muss der hier angegebene Wert mind. verdoppelt werden, um den (unter Last) tatsächlich verwendeten RAM abzuschätzen.

der aktuell tatsächlich verwendete RAM, kann so ermittelt werden:

> pmap -d $(ps ax | fgrep -v grep | fgrep "/usr/sbin/mysqld" | sed "s/^ *//;s/ .*//") | fgrep "writeable/private:"

/root/bin/MySQL-Speicherplatzverbrauch_im_RAM.sh

#!/bin/bash
 
#
# Dieses Skript zeigt, wieviel Arbeitsspeicher
# von welchem (laufenden) DBMS verwendet wird.
#
 
### damit "sort -n" das Dezimaltrennzeichen richtig deutet
LANG=C
 
### PIDs der MySQL-Instanzen finden
PIDS="$(pidof mysqld)"
 
### Schleife über jede PID einzeln
for EINE_PID in ${PIDS}
do
        ### zur PID zugehörige MySQL-Konfigurationsdatei finden
        for CNF in $(ps ax | fgrep -v 'grep ' | fgrep -v 'awk ' | egrep "^[ \t]*${EINE_PID} " | tr -s ' ' '\n' | egrep '[-][-]defaults-file=')
        do
                ### aus der Konfiguration die IP+Port holen
                ADRESSE_PORT="$(my_print_defaults ${CNF} mysqld | egrep '[-][-]port=|[-][-]bind-address=' | sort | awk -F'=' '{print $2}' | tr -s '\n' ' ' | awk '{print $1":"$2}')"
                #echo "${ADRESSE_PORT}"
 
                ### Speicherbelegung der PID errechnen
                SPEICHER="$(pmap ${EINE_PID} | fgrep ' [ ' | awk '{print $2}' | sed 's/K/*1024/;s/M/*1024*1024/;s/G/*1024*1024*1024/' | bc -l | awk '{z=$1;s+=z}END{print s/1024/1024/1024,"GB"}')"
                echo "${SPEICHER} - ${ADRESSE_PORT}"
        done
done | sort -n

auf Platte

/root/bin/mysql-speicherverbrauch.sh

#!/bin/bash
 
#
# tatsaechlicher Speicherplatzbedarf von diesem DBMS
#
 
VERSION="v2017033100"
#MYSQL_PROG="mysql --defaults-file=/etc/mysql/debian.cnf -t"
MYSQL_PROG="mysql --defaults-file=/root/.my.cnf -t"
 
 
### Übersicht
echo "
SELECT
        table_schema,
        table_name,
        data_length,
        index_length,
        data_length,
        index_length,
        data_free
FROM
        information_schema.tables
WHERE
        data_length IS NOT NULL
        AND data_length<>'information_schema'
        AND data_length<>'performance_schema'
        AND data_length<>'sys'
ORDER BY data_length DESC
;" | ${MYSQL_PROG}
 
 
### Summen
echo "
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT 
        table_schema AS 'Datenbankname',
        table_name,
        ROUND( SUM( data_length ) / 1024 / 1024, 3 ) AS 'Daten (MB)',
        ROUND( SUM( index_length ) / 1024 / 1024, 3 ) AS 'Index (MB)',
        ROUND( SUM( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Gesamt (MB)',
        ROUND( SUM( data_free ) / 1024 / 1024, 3 ) AS 'Freier Speicher (MB)'
FROM
        information_schema.tables
GROUP BY table_schema
ORDER BY 5 DESC
;" | ${MYSQL_PROG}

Mit OPTIMIZE TABLE kann der Speicherbereich, der von der Datei noch belegt wird aber nicht mehr benötigt wird, wieder frei gegeben.

Tabellen defragmentieren - leeren InnoDB-Speicherplatz wieder frei geben → OPTIMIZE TABLE

MySQL-Tabellen reparieren

REPAIR TABLE `datenbankname`.`tabellenname` USE_FRM;

alle MySQL-DB-Back-Ends reparieren

# mysqlcheck --repair -uBENUTZER -pPASSWORT meine_datenbank meine_tabelle
# mysqlcheck --analyze -uBENUTZER -pPASSWORT meine_datenbank meine_tabelle
# mysqlcheck --optimize -uBENUTZER -pPASSWORT meine_datenbank meine_tabelle

nur MyISAM reparieren

Fehlerüberprüfung

Überprüfung:

# myisamchk -c /var/lib/mysql/meine_datenbank/meine_tabelle.MYI

Reparatur möglichkeiten

Wenn bei der obigen Überprüfung Fehler aufgetreten sind, dann wie folgt weiter.

alte Reparaturreste entfernen:

# rm -f /var/lib/mysql/meine_datenbank/meine_tabelle.TMD

schnelle Reparatur

# myisamchk -r /var/lib/mysql/meine_datenbank/meine_tabelle.MYI

genaue Reparatur

Wenn bei der Schnellreparatur mir "-r" diese Fehlermeldung kommt:

# myisamchk -r /var/lib/mysql/meine_datenbank/meine_tabelle.MYI
myisamchk: error: myisam_sort_buffer_size is too small

dann hilft nur noch die Option "-o":

# myisamchk -o /var/lib/mysql/meine_datenbank/meine_tabelle.MYI

SQL

Den Inhalt einer Tabelle in eine andere Tabelle kopieren

Hier wird eine neue Tabelle (test_datenbank.test_tabelle2) als exakte Kopie einer alten Tabelle (test_datenbank.test_tabelle1) angelegt:

echo "CREATE TABLE test_datenbank.test_tabelle2 LIKE test_datenbank.test_tabelle1;" | mysql

Hier werden die Daten aus der alten Tabelle (test_datenbank.test_tabelle1) in die neue Tabelle (test_datenbank.test_tabelle2) kopiert:

echo "INSERT INTO test_datenbank.test_tabelle2 SELECT * FROM test_datenbank.test_tabelle1;" | mysql

Nur bestimmte Spalten einer Tabelle in eine andere Tabelle kopieren

Es sollen die inhalte bestimmter Spalten (datum,betrag,gegenstand,name,beschreibung) einer Tabelle (test_tabelle1) in eine andere Tabelle (test_tabelle2) kopiert werden:

echo "INSERT INTO test_datenbank.test_tabelle2 (datum,betrag,gegenstand,name,beschreibung) SELECT datum,betrag,gegenstand,name,beschreibung FROM test_datenbank.test_tabelle1;" | mysql

MySQL mit partitionierten Tabellen

partitionierte Tabellen mit MySQL

SQL-Aufrufe (Query)

Datenbank anlegen:

> echo "CREATE DATABASE IF NOT EXISTS testdb;" | mysql
> mysqlshow testdb

Tabelle in der Datenbank anlegen:

> echo "CREATE TABLE benutzer (id INT, name VARCHAR(64));" | mysql testdb
> mysqlshow testdb benutzer
> echo "SELECT * FROM benutzer WHERE name LIKE 'm%' ORDER BY id ASC;" | mysql -t testdb

einen Datensatz in die Tabelle einspielen:

> echo "INSERT INTO benutzer (id, name) VALUES ('1','Fritz');" | mysql testdb
> echo "INSERT INTO benutzer (id, name) VALUES ('2','Mustermann');" | mysql testdb
> echo "SELECT * FROM benutzer ORDER BY id ASC;" | mysql -t testdb
> echo "SELECT * FROM benutzer WHERE name LIKE 'm%' ORDER BY id ASC;" | mysql -t testdb

einen Datensatz in der Tabelle ändern:

> echo "UPDATE benutzer SET name='Musterfrau' WHERE name='Mustermann';" | mysql testdb
> echo "SELECT * FROM benutzer ORDER BY id ASC;" | mysql -t testdb

einen Datensatz aus der Tabelle löschen:

> echo "DELETE FROM benutzer WHERE id=1;" | mysql testdb
> echo "SELECT * FROM benutzer ORDER BY id ASC;" | mysql -t testdb

die vorhandene Tabelle um drei Spalten erweitern:

> echo "ALTER TABLE benutzer ADD vorname VARCHAR(64) AFTER name" | mysql testdb
> echo "ALTER TABLE benutzer ADD eingetragen TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER vorname" | mysql testdb
> echo "ALTER TABLE benutzer ADD aktualisiert TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP AFTER eingetragen;" | mysql testdb
> echo "SELECT * FROM benutzer ORDER BY id ASC;" | mysql -t testdb

die ganze Datenbank, mit allen Tabellen darin, löschen:

> echo "DROP DATABASE IF EXISTS testdb;" | mysql

siehe auch: MySQL Subquery / MySQL SubSELECT

Benutzer anlegen und löschen

> echo "INSERT INTO user (host,user) VALUES ('%','fritz');" | mysql -uroot -p[passwort] mysql
> echo "DROP USER fritz;" | mysql -uroot -p[passwort] mysql

Passwort ändern

seit die Passwortspalte nicht mehr "password" heißt, sondern "authentication_string", muss das Kommando so aussehen:

> echo "UPDATE user SET authentication_string=password('geheimes Passwort') where user='Benutzername';" | mysql mysql
> mysqladmin reload

Root-Passwort ändern (vor MySQL 8.0):

> echo "UPDATE mysql.user SET authentication_string=PASSWORD('geheim') WHERE User='root';" | mysql

> echo "CREATE USER rouser IDENTIFIED WITH mysql_native_password BY 'geheim';" | mysql
> echo "UPDATE mysql.user SET host='%',authentication_string=PASSWORD('geheim'),plugin='mysql_native_password' WHERE user='root';" | mysql

root-Passwort ist bekannt

Passwort ändern:

# echo "UPDATE user SET password=password('neugeheim') where user='root';" | mysql -uroot [-paltgeheim]
# mysqladmin reload

Jetzt sollte ein:

# mysql -uroot -pgeheim

funktionieren.

ODER

# mysql -uroot -e "UPDATE user SET password=password('geheim') WHERE user='root';" mysql
# mysqladmin reload

ODER

# mysqladmin -uroot -p'altes_Passwort' password 'neues_Passwort'
# mysqladmin reload

root-Passwort ist unbekannt

http://www.howtoforge.com/reset-forgotten-mysql-root-password

> service mysql stop
> screen -d -m -S mysql mysqld_safe --skip-grant-tables
> echo "UPDATE user SET Password=PASSWORD('geheim') where user='root'; FLUSH PRIVILEGES;" | mysql mysql
> ps wwaux|fgrep -v grep|fgrep -v awk|awk '/mysql/{print $2}'
10259
> kill -9 10259

dem Benutzer-Rechte erteilen und entziehen

alle Rechte entziehen:

# echo "REVOKE ALL PRIVILEGES, GRANT OPTION FROM fritz;" | mysql -u root -p

alle Rechte erteilen:

# echo "GRANT ALL PRIVILEGES ON *.* TO 'fritz'@'%' IDENTIFIED BY 'geheim';FLUSH PRIVILEGES;" | mysql -u root -p

ALTER-Rechte erteilen:

# echo "GRANT ALTER ON *.* TO fritz;FLUSH PRIVILEGES;" | mysql -u root -p

ALTER-Rechte entziehen:

# echo "REVOKE ALTER ON *.* FROM fritz;FLUSH PRIVILEGES;" | mysql -u root -p

Grant-Beispiel

Als erstes legen wir uns die MySQL-Passwort-Datei an, damit wir nicht bei jedem Kommando das Passwort mit eingeben müssen:

> vi ~/.my.cnf
[client]
host     = localhost
user     = root
password = "geheim"
socket   = /var/run/mysqld/mysqld.sock

http://dev.mysql.com/doc/refman/5.1/de/grant.html

> ssh root@DatenbankHostname
> echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';GRANT GRANT OPTION ON *.* TO 'root'@'%';FLUSH PRIVILEGES;" | mysql
> exit

> echo "DROP USER fritz;" | mysql -t -h DatenbankHostname mysql
> echo "CREATE USER fritz IDENTIFIED BY 'geheim';" | mysql -t -h DatenbankHostname mysql
> echo "REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'fritz'@'%';" | mysql -t -h DatenbankHostname mysql
> echo "GRANT SHOW DATABASES ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname

> echo "GRANT SELECT ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT INSERT ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT UPDATE ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT DELETE ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT CREATE ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT DROP ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT INDEX ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT ALTER ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT CREATE TEMPORARY TABLES ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT CREATE VIEW ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT SHOW VIEW ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname
> echo "GRANT TRIGGER ON * TO 'fritz'@'%';FLUSH PRIVILEGES;" | mysql -t -h DatenbankHostname Datenbankname

dem Benutzer nur bestimmte Rechte geben

als erstes nehmen wir dem neuen User ALLE Rechte:

# echo "REVOKE ALL PRIVILEGES, GRANT OPTION FROM fritz;" | mysql -hdbrechner -uroot -pgeheim datenbank

http://dev.mysql.com/doc/refman/5.1/en/grant.html

jetzt bekommt er nur Leserechte auf seine Datenbanken:

# echo "GRANT SELECT ON * TO fritz;FLUSH PRIVILEGES;" | mysql -hdbrechner -uroot -pgeheim datenbank

zum Schluss bekommt er noch etwas Schreibrechte auf einzelne Tabellen:

# echo "GRANT INSERT ON tabelle01 TO fritz;FLUSH PRIVILEGES;" | mysql -hdbrechner -uroot -pgeheim datenbank

Es kann immer nur eine einzige Tabelle angegeben werden!

jetzt lassen wir uns seine aktuellen Rechte noch einmal anzeigen, damit wir sicher sind, dass alles nach unserem Wunsch ausgeführt wurde:

# echo "SHOW GRANTS FOR 'fritz' \G;" | mysql -hdbrechner -uroot -pgeheim

oder

# echo "SHOW GRANTS FOR 'fritz';" | mysql -hdbrechner -uroot -pgeheim

DB-User mit eingeschränkten Rechten anlegen

# echo "GRANT SELECT,UPDATE,INSERT,DELETE ON Tabelle TO 'Benutzer'@'ClientRechner' IDENTIFIED BY 'geheim'; FLUSH PRIVILEGES;" | mysql --defaults-file=meine_zugangsdaten.cnf -t -hServerRechner Datenbank

Hier ist ein Beispiel, in dem der User fritz mit dem Passwort geheim und den Rechten SELECT,UPDATE,INSERT,DELETE für die DB datenbank auf dem DBMS-Host dbrechner angelegt wird:

# echo "DROP USER fritz;CREATE USER fritz IDENTIFIED BY 'geheim';REVOKE ALL PRIVILEGES, GRANT OPTION FROM fritz; GRANT SELECT,UPDATE,INSERT,DELETE ON * TO 'fritz'@'%' IDENTIFIED BY 'geheim';FLUSH PRIVILEGES;" | mysql -uroot -p -hdbrechner datenbank

DB-Backup

MySQLDump benötigt mindestens diese Berechtigungen:

> echo "GRANT SELECT, RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'geheim'; FLUSH PRIVILEGES;" | mysql
> echo "GRANT CREATE, INSERT, DROP ON mysql.ibbackup_binlog_marker TO 'backup'@'localhost';" | mysql
> echo "GRANT CREATE, INSERT, DROP ON mysql.backup_progress TO 'backup'@'localhost';" | mysql
> echo "GRANT CREATE, INSERT, SELECT, DROP ON mysql.backup_history TO 'backup'@'localhost';" | mysql
> echo "GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'backup'@'localhost';" | mysql
> echo "FLUSH PRIVILEGES;" | mysql

Datenbank anlegen und löschen

> echo "CREATE DATABASE IF NOT EXISTS meinedb;" | mysql -hdbrechner -uroot -pgeheim datenbank
> echo "DROP DATABASE IF EXISTS meinedb;" | mysql -hdbrechner -uroot -pgeheim datenbank

Tabelle anlegen und löschen

eine neue Tabelle anlegen:

# echo "CREATE TABLE IF NOT EXISTS tabelle01;" | mysql -hdbrechner -uroot -pgeheim datenbank

die ganze Tabelle wird komplett gelöscht:

# echo "DROP TABLE IF EXISTS tabelle01;" | mysql -hdbrechner -uroot -pgeheim datenbank

Spalte in einer Tabelle ändern

https://stackoverflow.com/questions/22668024/how-to-change-column-size-of-varchar-type-in-mysql?rq=1

Tabelle anlegen:

> echo "CREATE TABLE test (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, info VARCHAR(32)) ENGINE=InnoDB DEFAULT CHARSET=utf8;" | mysql datenbank
> echo "SHOW CREATE TABLE test;" | mysql -t datenbank

Spaltentyp von VARCHAR(32) in VARCHAR(64) ändern

> echo "ALTER TABLE test MODIFY COLUMN info VARCHAR(64);" | mysql datenbank
> echo "SHOW CREATE TABLE test;" | mysql -t datenbank

AUTO_INCREMENT übernehmen

so wird der AUTO_INCREMENT mit angezeigt:

echo 'SHOW CREATE TABLE `testdb`;' | mysql

so wird der AUTO_INCREMENT von vorlagedb nicht mit in die testdb übernommen:

echo 'CREATE TABLE `testdb` LIKE `vorlagedb`;' | mysql ${DB}

Datensätze anlegen, ändern und löschen

einen Datensatz anlegen bzw. in die Tabelle schreiben:

# echo "INSERT INTO tabelle01 (spalte01,spalte02) VALUES ('wert01','wert02');" | mysql -uroot -pgeheim datenbank

einen Datensatz ändern:

# echo "UPDATE tabelle01 SET spalte01 = 'neuer Wert' WHERE spalte02 = 'Bedingungswert';" | mysql -h myserver -u fritz -pgeheim datenbank

alle Datensätze mit "alte Daten" in der Spalte "spalte", werden gelöscht:

# echo "DELETE FROM tabelle01 WHERE spalte='alte Daten';" | mysql -hdbrechner -uroot -pgeheim datenbank

alle Datensätze, die die Zeichenkette "neu" in der Spalte "spalte" haben werden nicht gelöscht, der Rest wird gelöscht:

# echo "DELETE FROM tabelle01 WHERE spalte NOT LIKE '%neu%';" | mysql -hdbrechner -uroot -pgeheim datenbank

Die "DELETE"-Anweisung benötigt IMMER eine "WHERE"-Bedingung!

sonstiges

Einen passwortlosen User mit sehr eingeschränkten Rechten anlegen:

# echo "DROP USER fritz;DROP DATABASE IF EXISTS meinedb;" | mysql -uroot -p[passwort] mysql
# echo "CREATE DATABASE meinedb;" | mysql -uroot -p[passwort] mysql
# echo "INSERT INTO user (host,user) VALUES ('%','fritz');" | mysql -uroot -p[passwort] mysql
# echo "FLUSH PRIVILEGES;" | mysql -uroot -p[passwort] mysql
# echo "REVOKE ALL ON *.* TO fritz;" | mysql -uroot -p[passwort] meinedb
# echo "GRANT SELECT ON * TO fritz;" | mysql -uroot -p[passwort] meinedb
# echo "FLUSH PRIVILEGES;" | mysql -uroot -p[passwort] meinedb

"fritz" kann jetzt nur die Tabellen aus "meinedb" lesen, sonst nix.

einen Benutzer anlegen:

# CREATE USER fritz IDENTIFIED BY 'Geheim';

ihm alle Rechte nehmen:

# REVOKE ALL PRIVILEGES ON *.* FROM webteam;

ihm Leserechte für die DB "meinedb" geben:

# GRANT SELECT ON meinedb.* TO 'fritz'@'%' IDENTIFIED BY 'Geheim';

Datenbanken und Tabellen anzeigen:

zeigt alle Datenbanken:

# mysqlshow -uroot -pgeheim

zeigt alle Tabellen von [DATENBANK]:

# mysqlshow -uroot -pgeheim [DATENBANK]

ODER

in die "Master-Datenbank" gehen:

> use mysql;

zeigt alle Datenbanken:

> show databases;

zeigt alle Tabellen von mysql:

> show tables;

den Inhalt der Tabelle "[tabelle]" anzeigen:

> SELECT * FROM [tabelle];

User anlegen, DB anlegen und dem User die Zugriffsrechte auf die DB geben:

# echo "CREATE USER dbuser IDENTIFIED BY 'geheim';
CREATE DATABASE IF NOT EXISTS [Datenbankname];
GRANT ALL ON [Datenbankname].* TO dbuser@localhost;" | mysql -u root -p

MySQL starten, ohne das Passwörter abgefragt werden:

# /usr/bin/safe_mysqld --skip-grant-tables

oder

# /usr/bin/mysqld_safe --skip-grant-tables

Passwort ändern:

# echo "SET PASSWORD FOR dbuser = PASSWORD('geheim');" | mysql -u root -p

User löschen:

# echo "DROP USER [böserjunge];" | mysql -t -h localhost -u root -pgeheim

Tabellenstruktur anzeigen:

# echo "SHOW CREATE TABLE [Tabellenname]" | mysql -t -h localhost -uroot -p [Datenbankname]

Datenbank anlegen:

# echo "CREATE DATABASE [Datenbankname];" | mysql -t -h localhost -uroot -p
# echo "CREATE DATABASE IF NOT EXISTS [Datenbankname];" | mysql -t -h localhost -uroot -p

Datenbank löschen:

# echo "DROP DATABASE [Datenbankname];" | mysql -t -h localhost -uroot -p [Datenbankname]
# echo "DROP DATABASE IF EXISTS [Datenbankname];" | mysql -t -h localhost -uroot -p [Datenbankname]

Tabelle anlegen:

# echo "CREATE TABLE [Tabellenname] (timestamp DATETIME)" | mysql -t -h localhost -uroot -p [Datenbankname]

In der Praxis sieht eine Tabelle eher etwas komplexer aus:

# echo "CREATE DATABASE IF NOT EXISTS [Datenbankname];
USE [Datenbankname];

CREATE TABLE IF NOT EXISTS [Tabellenname] (
timestamp DATETIME,
resource_id SMALLINT,
hostname VARCHAR(254) default NULL,
ip4 VARCHAR(4096) default NULL,
mac VARCHAR(2048) default NULL,
bios_release_date VARCHAR(256) default NULL,
bios_manufacturer VARCHAR(256) default NULL,
bios_product_name VARCHAR(256) default NULL,
bios_serial_number VARCHAR(256) default NULL,
cpu_anzahl SMALLINT,
bus VARCHAR(8) default NULL,
vm VARCHAR(8) default NULL,
htt SMALLINT,
cpu_family VARCHAR(256) default NULL,
cpu_manufacturer VARCHAR(256) default NULL,
cpu_id VARCHAR(256) default NULL,
cpu_signature VARCHAR(256) default NULL,
cpu_core_count SMALLINT,
cpu_core_enabled SMALLINT,
memtotal VARCHAR(256) default NULL,
hdd VARCHAR(256) default NULL,
PRIMARY KEY (resource_id),
KEY (bios_serial_number),
KEY mac (mac)
) TYPE=MyISAM;" | mysql -t -h localhost -uroot -p

Tabelle löschen:

# echo "DROP TABLE [Tabellenname];" | mysql -t -h localhost -uroot -p [Datenbankname]

Alle Datensätze löschen:

# echo "DELETE FROM [Tabellenname];" | mysql -t -h localhost -uroot -p [Datenbankname]

Einen Datensatz löschen:

# echo "DELETE FROM [Tabellenname] WHERE [Spalte]='Wert';" | mysql -t -h localhost -uroot -p [Datenbankname]

Weitere Beispiele:

DB-Client-Programm: mysql
DB-Server         : myserver
DB-Namen          : [DatenBank]
TAB-Namen         : [DBTabelle]

Tabelle als Datei exportieren, hierbei gelten die MySQL-Standardwerte für das Dateiformat:

# echo "SELECT *
INTO OUTFILE '/tmp/[DBTabelle].txt'
FROM [DBTabelle] LIMIT 10;" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]

Tabelle als CSV-Datei exportieren:

# echo "SELECT *
INTO OUTFILE '/tmp/[DBTabelle].csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM [DBTabelle] LIMIT 10;" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]

Tabelle als CSV-Datei (Semikolon getrennt) exportieren:

# echo "SELECT *
INTO OUTFILE '/tmp/[DBTabelle].csv'
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
FROM [DBTabelle] LIMIT 10;" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]

Die Tabelle als CSV-Datei (Semikolon getrennt) wieder importieren (das ist sauschnell, weil hierbei keine Indizes aktuallisiert werden):

# echo "LOAD DATA LOCAL INFILE '/tmp/[DBTabelle].csv' INTO TABLE [DBTabelle] FIELDS TERMINATED BY ';';" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]

MySQL - LOAD DATA INFILE

#!/bin/bash

#
# Dieses Skript schreibt von der angegebenen Datenbank
# jede Tabelle in eine eigene CSV-Datei.
#

DATENBANK="${1}"

if [ -z "${DATENBANK}" ] ; then
        echo "${0} Datenbank"
        exit 1
else
        mkdir "${DATENBANK}"
        chmod 0777 "${DATENBANK}"
        cd "${DATENBANK}"
        VERZEICHNIS="$(pwd)"
fi

mysqlshow "${DATENBANK}" | egrep -v "${DATENBANK}|Tables" | awk '{print $2}' | egrep -v '^$' | while read TABELLE
do
        echo "${VERZEICHNIS}/${TABELLE}"
        #echo "echo \"SELECT * INTO OUTFILE '${VERZEICHNIS}/${TABELLE}.csv' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' FROM ${TABELLE};\" | mysql -t ${DATENBANK}"
        echo "SELECT * INTO OUTFILE '${VERZEICHNIS}/${TABELLE}.csv' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' FROM ${TABELLE};" | mysql -t ${DATENBANK}
done

#!/bin/bash

#
# Dieses Skript liest die CSV-Dateien aus dem aktuellen Verzeichnis in die angegebene Datenbank.
#

DATENBANK="${1}"

if [ -z "${DATENBANK}" ] ; then
        echo "${0} Datenbank"
        exit 1
fi

ls *.csv | sed 's/[.]csv$//' | while read TABELLE
do
        echo "${TABELLE}"
        echo "LOAD DATA LOCAL INFILE '${TABELLE}.csv' INTO TABLE ${TABELLE} FIELDS TERMINATED BY ';';" | mysql ${DATENBANK}
done

Datenbanken zeigen:

# echo "SHOW DATABASES" | mysql -h myserver -u fritz
# mysqlshow -h myserver -u fritz -p
# mysqlshow -t -h myserver -u fritz -p

Tabellen zeigen:

# echo "SHOW TABLES" | mysql -h myserver -u fritz [DatenBank]
# mysqlshow -h myserver -u fritz [DatenBank] -p
# mysqlshow -t -h myserver -u fritz [DatenBank] -p

Tabelleninhalt zeigen:

# echo "SELECT * FROM [DBTabelle]" | mysql -h myserver -u fritz -p [DatenBank]
# echo "SELECT * FROM [DBTabelle]" | mysql -t -h myserver -u fritz [DatenBank] -p

SUBSTRING:

# echo "SELECT hostname FROM belastungswaechter LIMIT 1;" | mysql -N -ufritz -pgeheim -hserver datenbank
rechnername.domain.de

# echo "SELECT SUBSTRING_INDEX(hostname,'.',1) FROM belastungswaechter LIMIT 1;" | mysql -N -ufritz -pgeheim -hserver datenbank
rechnername

# echo "SELECT SUBSTRING_INDEX(hostname,'.',2) FROM belastungswaechter LIMIT 1;" | mysql -N -ufritz -pgeheim -hserver datenbank
rechnername.domain

# echo "SELECT SUBSTRING_INDEX(hostname,'.',-2) FROM belastungswaechter LIMIT 1;" | mysql -N -ufritz -pgeheim -hserver datenbank
domain.de

IP-Adressen in numerischer Reihenfolge anzeigen:

# echo "SELECT * FROM [DBTabelle] ORDER BY INET_ATON(ip_address) LIMIT 10;" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]

Tabelleninhalt nach der 3. Spalte sortieren (nur die ersten 10 Zeilen:

# echo "SELECT spalte1,spalte2,spalte3 FROM [DBTabelle] ORDER BY 3 ASC LIMIT 10" | mysql -h myserver -u fritz -p [DatenBank]

Tabelleninhalt in umgekehrtsortierter Reihenfolge zeigen (nur die letzten 10 Zeilen):

# echo "SELECT spalte1,spalte2,spalte3 FROM [DBTabelle] ORDER BY 3 DESC LIMIT 10" | mysql -h myserver -u fritz -p [DatenBank]

eine Liste aller unterschiedlichen Einträge aus der Spalte 3 zeigen (nur die letzten 10 Zeilen):

# echo "SELECT spalte1,spalte2,spalte3 FROM [DBTabelle] GROUP BY 3 ORDER BY 3 DESC LIMIT 10" | mysql -h myserver -u fritz -p [DatenBank]

Wichtig ist hierbei, dass "GROUP BY" immer vor "ORDER BY" stehen muss!

MySQL kann auch mit RegEx umgehgen, hier mal zwei Beispiele:

die letzten 50 Einträge anzeigen, die älter als 7 Tage sind (Spalte "DeviceReportedTime") und eine IP in der Spalte "Message" enthalten:

# echo "SELECT ID,DeviceReportedTime,Facility,FromHost,Message,SysLogTag FROM SystemEvents WHERE DeviceReportedTime < '$(date -d-7days +'%F %T')' AND Message REGEXP '[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}' ORDER BY ID DESC LIMIT 50;" | mysql -t -ursyslog -p Syslog

Alle Datensätze löschen, die älter als 180 Tage sind (Spalte "DeviceReportedTime") und eine IP in der Spalte "Message" enthalten:

# echo "DELETE FROM SystemEvents WHERE DeviceReportedTime < '$(date -d-180days +'%F %T')' AND Message REGEXP '[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}';"

Weitere Beispiele gibt es hier: http://dev.mysql.com/doc/refman/5.1/de/regexp.html und hier http://gnosis.cx/publish/programming/regular_expressions.html

Anzeige der Anzahl der Einträge pro Spalte3:

# echo "SELECT spalte3,COUNT(*) FROM [DBTabelle] GROUP BY 1 ORDER BY 1 DESC LIMIT 10" | mysql -h myserver -u fritz -p [DatenBank]

Will man den Wert in der "Spalte1" auf "neuer Wert" setzen, wenn die Bedingung erfüllt ist, dass der Wert "Bedingungswert" in der "Spalte2" vorhanden ist, dann geht das so:

# echo "UPDATE [DBTabelle] SET [Spalte1] = 'neuer Wert' WHERE [Spalte2] = 'Bedingungswert';" | mysql -h myserver -u fritz -p [DatenBank]

Infos zu den Tabellen abrufen:

# echo "SELECT table_name, table_type, engine FROM information_schema.tables;"|mysql -t -h localhost -uroot -p openqrm
+---------------------------------------+-------------+--------+
| table_name                            | table_type  | engine |
+---------------------------------------+-------------+--------+
| CHARACTER_SETS                        | SYSTEM VIEW | MEMORY | 
| COLLATIONS                            | SYSTEM VIEW | MEMORY | 
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY | 
| COLUMNS                               | SYSTEM VIEW | MyISAM | 
| COLUMN_PRIVILEGES                     | SYSTEM VIEW | MEMORY | 
| KEY_COLUMN_USAGE                      | SYSTEM VIEW | MEMORY | 
| PROFILING                             | SYSTEM VIEW | MEMORY | 
| ROUTINES                              | SYSTEM VIEW | MyISAM | 
| SCHEMATA                              | SYSTEM VIEW | MEMORY | 
| SCHEMA_PRIVILEGES                     | SYSTEM VIEW | MEMORY | 
| STATISTICS                            | SYSTEM VIEW | MEMORY | 
| TABLES                                | SYSTEM VIEW | MEMORY | 
...
| appliance_info                        | BASE TABLE  | MyISAM | 
| cloud_appliance                       | BASE TABLE  | MyISAM | 
| cloud_config                          | BASE TABLE  | MyISAM |
| cloud_image                           | BASE TABLE  | MyISAM | 
| cloud_ipgroups                        | BASE TABLE  | MyISAM | 
| cloud_iplc                            | BASE TABLE  | MyISAM | 
| cloud_iptables                        | BASE TABLE  | MyISAM |
| cloud_irlc                            | BASE TABLE  | MyISAM |
| cloud_nat                             | BASE TABLE  | MyISAM | 
| cloud_private_image                   | BASE TABLE  | MyISAM | 
| cloud_requests                        | BASE TABLE  | MyISAM | 
| cloud_selector                        | BASE TABLE  | MyISAM | 
| cloud_transaction                     | BASE TABLE  | MyISAM | 
| cloud_users                           | BASE TABLE  | MyISAM | 
| cloud_users_limits                    | BASE TABLE  | MyISAM | 
| deployment_info                       | BASE TABLE  | MyISAM | 
...

Schema einer Tabelle anzeigen:

# echo "desc cloud_ipgroups;"|mysql -t -h localhost -uroot -p openqrm
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ig_id        | int(5)      | YES  |     | NULL    |       | 
| ig_name      | varchar(50) | YES  |     | NULL    |       | 
| ig_network   | varchar(50) | YES  |     | NULL    |       | 
| ig_subnet    | varchar(50) | YES  |     | NULL    |       | 
| ig_gateway   | varchar(50) | YES  |     | NULL    |       | 
| ig_dns1      | varchar(50) | YES  |     | NULL    |       | 
| ig_dns2      | varchar(50) | YES  |     | NULL    |       | 
| ig_domain    | varchar(50) | YES  |     | NULL    |       | 
| ig_activeips | int(5)      | YES  |     | NULL    |       | 
+--------------+-------------+------+-----+---------+-------+

# echo "desc cloud_iptables;"|mysql -t -h localhost -uroot -p openqrm
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| ip_id           | int(5)      | YES  |     | NULL    |       | 
| ip_ig_id        | int(5)      | YES  |     | NULL    |       | 
| ip_appliance_id | int(5)      | YES  |     | NULL    |       | 
| ip_cr_id        | int(5)      | YES  |     | NULL    |       | 
| ip_active       | int(5)      | YES  |     | NULL    |       | 
| ip_address      | varchar(50) | YES  |     | NULL    |       | 
| ip_subnet       | varchar(50) | YES  |     | NULL    |       | 
| ip_gateway      | varchar(50) | YES  |     | NULL    |       | 
| ip_dns1         | varchar(50) | YES  |     | NULL    |       | 
| ip_dns2         | varchar(50) | YES  |     | NULL    |       | 
| ip_domain       | varchar(50) | YES  |     | NULL    |       | 
+-----------------+-------------+------+-----+---------+-------+

Zeitangaben in SQL

# echo "SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR);" | mysql -t -h localhost -uroot -p
+---------------------------+
| (NOW() + INTERVAL 2 HOUR) |
+---------------------------+
| 2012-02-15 23:36:38       |
+---------------------------+

# echo "SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR);" | mysql -t -h localhost -uroot -p
+----------------------------------+
| DATE_SUB(NOW(), INTERVAL 2 HOUR) |
+----------------------------------+
| 2012-02-15 19:36:41              |
+----------------------------------+

Heute:

# echo "SELECT DATE(NOW());" | mysql -t -h localhost -uroot -p
+-------------+
| DATE(NOW()) |
+-------------+
| 2012-03-12  |
+-------------+

# echo "SELECT CURDATE();" | mysql -t -h localhost -uroot -p
+------------+
| CURDATE()  |
+------------+
| 2012-03-12 |
+------------+

Gestern:

# echo "SELECT DATE(DATE_SUB(NOW(), INTERVAL 24 HOUR));" | mysql -t -h localhost -uroot -p
+-----------------------------------------+
| DATE(DATE_SUB(NOW(), INTERVAL 24 HOUR)) |
+-----------------------------------------+
| 2012-03-11                              |
+-----------------------------------------+

# echo "SELECT CURDATE() - interval 1 day;" | mysql -t -h localhost -uroot -p
+----------------------------+
| CURDATE() - interval 1 day |
+----------------------------+
| 2012-03-11                 |
+----------------------------+

Datenmenge bzw. Größe einer DB ausgeben

> echo "SELECT table_schema 'Datenbankname', sum( data_length + index_length ) / 1024 / 1024 'Datenbank in MB' FROM information_schema.tables GROUP BY table_schema;" | mysql -t
+--------------------+-----------------+
| Datenbankname      | Datenbank in MB |
+--------------------+-----------------+
| information_schema |      0.15625000 |
| mysql              |      2.41331005 |
| performance_schema |      0.00000000 |
| sys                |      0.01562500 |
| Syslog             |    201.51562500 |
+--------------------+-----------------+

> echo "SELECT table_schema 'Datenbankname', sum( data_length + index_length ) / 1024 / 1024 'Datenbank in MB',data_length / 1024 / 1024 'Daten in MB',index_length / 1024 / 1024 'Index in MB' FROM information_schema.tables GROUP BY table_schema;" | mysql -t
+--------------------+-----------------+--------------+-------------+
| Datenbankname      | Datenbank in MB | Daten in MB  | Index in MB |
+--------------------+-----------------+--------------+-------------+
| information_schema |      0.15625000 |   0.00000000 |  0.00000000 |
| mysql              |      2.41331005 |   0.00000000 |  0.00390625 |
| performance_schema |      0.00000000 |   0.00000000 |  0.00000000 |
| sys                |      0.01562500 |         NULL |        NULL |
| Syslog             |    201.51562500 | 201.50000000 |  0.00000000 |
+--------------------+-----------------+--------------+-------------+

gibt nur die komplette Summe (in MB) von allen Datenbanken aus:

> echo "SELECT table_schema, sum( data_length + index_length ) / 1024 / 1024 FROM information_schema.tables GROUP BY table_schema;" | mysql -N | awk '{z=$2;s+=z}END{print s,"MB"}'
204.101 MB

Datenmenge bzw. Größe einer Tabelle ausgeben

> echo "SELECT table_schema 'Datenbank', table_name 'Tabelle', sum( data_length + index_length ) / 1024 / 1024 'Gesamtgroesse in MB',data_length / 1024 / 1024 'Daten in MB',index_length / 1024 / 1024 'Index in MB' FROM information_schema.tables WHERE table_schema='Syslog' AND table_name='SystemEvents';" | mysql -t
+-----------+--------------+---------------------+--------------+-------------+
| Datenbank | Tabelle      | Gesamtgroesse in MB | Daten in MB  | Index in MB |
+-----------+--------------+---------------------+--------------+-------------+
| Syslog    | SystemEvents |        201.50000000 | 201.50000000 |  0.00000000 |
+-----------+--------------+---------------------+--------------+-------------+

#!/bin/bash
 
#------------------------------------------------------------------------------#
#
# Zeigt die Größe jeder einzelnen Tabelle einer Datenbank an.
# getestet mit MySQL Version 8.0.31
#
#------------------------------------------------------------------------------#
 
if [ "x${1}" = x ] ; then
        echo "${0} [Datenbankname]"
        echo "${0} simsysnetwork"
        echo "${0} simsysnetwork -N"
        exit 1
fi
 
if [ "x${2}" = "x-N" ] ; then
        OPT="-N"
        SQL="SELECT
        sum( data_length + index_length ) / 1024 / 1024 'Gesamtgroesse in MB',
        data_length / 1024 / 1024 'Daten in MB',
        index_length / 1024 / 1024 'Index in MB',
        table_schema 'Datenbank',
        table_name 'Tabelle'
        FROM information_schema.tables WHERE table_schema='${1}' AND"
else
        OPT="-t"
        SQL="SELECT
        table_schema 'Datenbank',
        table_name 'Tabelle', sum( data_length + index_length ) / 1024 / 1024 'Gesamtgroesse in MB',
        data_length / 1024 / 1024 'Daten in MB',
        index_length / 1024 / 1024 'Index in MB'
        FROM information_schema.tables WHERE table_schema='${1}' AND"
fi
 
for TAB in $(echo "SHOW TABLES;" | mysql -N ${1})
do
        echo "${SQL} table_name='${TAB}';" | mysql ${OPT}
done

/root/bin/Datenbankgröße.sh

#!/bin/bash
 
#------------------------------------------------------------------------------#
 
echo "
SELECT
        table_schema 'Datenbankname',
        sum( data_length + index_length ) / 1024 / 1024 'Datenbank in MB',
        data_length / 1024 / 1024 'Daten in MB',
        index_length / 1024 / 1024 'Index in MB'
FROM
        information_schema.tables
GROUP BY table_schema
;" | mysql -t
 
#------------------------------------------------------------------------------#

/root/bin/Tabellengröße.sh

#!/bin/bash
 
#------------------------------------------------------------------------------#
echo "
SELECT 
        table_schema AS 'Datenbankname',
        table_name,
        ROUND( DATA_LENGTH / 1024 / 1024, 3 ) AS 'Daten (MB)',
        ROUND( INDEX_LENGTH / 1024 / 1024, 3 ) AS 'Index (MB)',
        ROUND( DATA_FREE / 1024 / 1024, 3 ) AS 'Freier Speicher (MB)'
FROM
        information_schema.tables
WHERE
        TABLE_TYPE='BASE TABLE'
        AND
        ENGINE='InnoDB'
        AND
        TABLE_SCHEMA<>'mysql'
        AND
        TABLE_SCHEMA<>'sys'
ORDER BY 3
;" | mysql -t
 
#------------------------------------------------------------------------------#

Index

Die Regel sagt, dass man einen Index auf einer Tabelle dort braucht, wo man eine WHERE-Bedingung im Statement verwende.

Das heißt, das folgende SQL-Statement:

# SELECT * FROM Syslog.SystemEvents WHERE FromHost = 'fritz01' AND LEFT(ReceivedAt,10) = '2011-10-05' AND Facility = 1

benötigt einen Index auf den spalten "FromHost", "ReceivedAt" und "Facility".

Um die Tabelle entsprechend zu ändern, muss folgendes SQL-Statement abgesetzt werden:

# ALTER TABLE Syslog.SystemEvents ADD INDEX SearchIdx(Facility, FromHost, ReceivedAt);

damit wird ein einzener Index über die drei betreffenden spalten gelegt. Dann sollte man bei einer Abfrage immer alle drei Spalten als WHERE-Bedingung angeben!

Alternativ kann man auch jeweils einen einzelnen index pro spalte verwenden:

# ALTER TABLE Syslog.SystemEvents ADD INDEX facilityIdx(Facility), ADD INDEX fromHostIdx(FromHost), ADD INDEX receivedAt(ReceivedAt);

Das ist besser, wenn man nicht immer alle drei Spalten als WHERE-Bedingung angeben möchte.

Ein Index kann aber auch Nachteile haben: INSERT-Statements werden, mit steigender anzahl von Indizies, langsamer.

Man sollte es also nicht übertreiben und nur dort einen Index setzen, wo es wirklich nötig ist.

verweiste (orphan) Tabellen finden und beseitigen

verweiste Tabellen anzeigen:

> echo 'SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%#sql%";' | mysql -t

Aus der Ausgabe kann man den Datenbanknamen erkennen.

Da wir oftmals diese verweiste Tabelle nicht direkt löschen können, werden wir die Datenbank löschen. Vorher ist es aber ratsam, die heilen Tabellen vorübergehend woanders zwischen zu lagern.

Wen die Datenbank, in der die verweiste Tabelle erwartet wird "Syslog" heißt, dann geht man wie folgt vor:

> echo 'SELECT COUNT(*) FROM Syslog.SystemEvents;' | mysql -t
+----------+
| COUNT(*) |
+----------+
|   512946 |
+----------+

> echo 'USE Syslog; SHOW TABLES;' | mysql -t
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+

> echo 'CREATE DATABASE temp;' | mysql
> echo 'RENAME TABLE Syslog.SystemEvents TO temp.SystemEvents;' | mysql -t
> echo 'RENAME TABLE Syslog.SystemEventsProperties TO temp.SystemEventsProperties;' | mysql -t
> echo 'USE Syslog; SHOW TABLES;' | mysql -t
> echo 'USE temp; SHOW TABLES;' | mysql -t
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+

> echo 'DROP DATABASE Syslog;' | mysql
> echo 'CREATE DATABASE Syslog;' | mysql
> echo 'SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%#sql%";' | mysql -t
> echo 'RENAME TABLE temp.SystemEventsProperties TO Syslog.SystemEventsProperties;' | mysql -t
> echo 'RENAME TABLE temp.SystemEvents TO Syslog.SystemEvents;' | mysql -t
> echo 'USE temp; SHOW TABLES;' | mysql -t
> echo 'USE Syslog; SHOW TABLES;' | mysql -t
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+

> echo 'SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%#sql%";' | mysql -t
> echo 'DROP DATABASE temp;' | mysql
> echo 'SELECT COUNT(*) FROM Syslog.SystemEvents;' | mysql -t
+----------+
| COUNT(*) |
+----------+
|   512946 |
+----------+

MySQL Tuning

Variablen/Einstellungen des DBMS:

SELECT * FROM sys.metrics;

Speicherplatzverbrauch:

SELECT * FROM sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 1.59 GiB        |
+-----------------+

wieviel Speicherplatz eine bestehende Verbindung verbraucht/belegt:

SELECT * FROM sys.host_summary;

wieviel Speicherplatz eine bestehende Verbindung für die einzelnen Tabellendateien verbraucht/belegt:

SELECT * FROM sys.host_summary_by_file_io_type;

hier kann man sogar Infos über einzelne Statements sehen:

SELECT * FROM sys.host_summary_by_statement_type;

hier, wieviele Daten gerade von einer Tabelle im RAM liegen:

SELECT * FROM sys.innodb_buffer_stats_by_schema;

…und diese ist bei Fehlersuche ganz wichtig, die sollte immer leer sein:

SELECT * FROM sys.innodb_lock_waits;

IO der einzelnen Tabellendateien im FS:

SELECT * FROM sys.io_global_by_file_by_bytes;

Zugriffswartezeiten je Tabellendatei:

SELECT * FROM sys.io_global_by_file_by_latency;

die Autoincrement-Werte für jede Tabelle:

SELECT * FROM sys.schema_auto_increment_columns;

Beispiel MySQL 8.0 mit 16 GiB RAM

Es sollten von der MySQL-DB nur max. 84% des RAM belegt werden.

innodb_buffer_pool_size + ( max_allowed_packet * max_connections ) <= 84% RAM

beide Konfigurationen belegen so ziehmlich die gleiche Menge RAM:

max_allowed_packet              = 16M
max_connections                 = 70
innodb_buffer_pool_size         = 12G

max_allowed_packet              = 64M
max_connections                 = 145
innodb_buffer_pool_size         = 4G

Als erstes sollte man ermitteln, wie groß das benötigte "max_allowed_packet" sein muß.
- Hier gilt die Regel, nur so groß wie nötig aber so klein wie möglich.
Als zweites sollte man ermitteln, wieviele Verbindungen maximal zur Datenbank gleichzeitig gehalten werden sollen (max_connections).
- Hier gilt die Regel, nur soviele wie nötig aber sowenig wie möglich.
Als letztes kann man dann damit errechnen, wieviel Speicher noch für den "innodb_buffer_pool_size" zur verfügung steht:
- "RAM" * 0,84 - ( max_allowed_packet * max_connections ) = innodb_buffer_pool_size
- 16384MB * 0,84 - ( 16M * 70 ) = 12642,56 ~ 12GB
- 16384MB * 0,84 - ( 64M * 145 ) = 4482,56 ~ 4GB

max_allowed_packet auf 128 MB setzen:
> echo "SET GLOBAL max_connections=300; SET GLOBAL max_allowed_packet=$((128*1024*1024));" | mysql -t
> echo "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'max_allowed_packet'; SHOW VARIABLES LIKE 'max_connections';" | mysql -t
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 16106127360 |
+-------------------------+-------------+
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 134217728 |
+--------------------+-----------+
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+

alle Infos von jeder einzelnen Tabelle aus dem gesamten DBMS anzeigen:
> for A in $(echo "USE information_schema; SHOW TABLES;" | mysql -N); do echo "# SELECT * FROM information_schema.${A};"; echo "SELECT * FROM information_schema.${A};" | mysql -t ; done

alle Einstellungen von jeder einzelnen Tabelle aus dem gesamten DBMS anzeigen:
> for A in $(echo "USE performance_schema; SHOW TABLES;" | mysql -N); do echo "# SELECT * FROM performance_schema.${A};"; echo "SELECT * FROM performance_schema.${A};" | mysql -t ; done

Speicherplatz (und weitere Infos) von jeder einzelnen Tabelle aus dem gesamten DBMS anzeigen:
> for A in $(echo "USE sys; SHOW TABLES;" | mysql -N); do echo "# SELECT * FROM sys.${A};"; echo "SELECT * FROM sys.${A};" | mysql -t ; done

Beispiel MySQL 8.0 mit 32 GiB RAM

# echo "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'max_allowed_packet'; SHOW VARIABLES LIKE 'max_connections';" | mysql -t
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 268435456 |
+--------------------+-----------+
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

# echo "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'max_allowed_packet'; SHOW VARIABLES LIKE 'max_connections';" | mysql -N | awk '{print $NF}' | tr -s '\n' ' ' | awk '{a=($1 + ($2 * $3)) / 1024^3; print a,"GiB /",a*100/84,"GiB"}'
26 GiB / 30.9524 GiB

# echo "1024 256 100" | awk '{a=($1 + ($2 * $3))/1024; print a,"GiB /",a*100/84,"GiB"}'
26 GiB / 30.9524 GiB

oder auch so möglich:

# echo "1024 128 200" | awk '{a=($1 + ($2 * $3))/1024; print a,"GiB /",a*100/84,"GiB"}'
26 GiB / 30.9524 GiB

Fehlermeldungen

ERROR 2006 (HY000) at line 13135: MySQL server has gone away

Diesen Fehler bekommt man gelegentlich, wenn man einen Dump einspielt. Im Internet findet man verschiedenen Lösungsvorschläge, die vereinzelt auch funktionieren. Hier alle Anpassungen, die ich dazu gefunden habe.

/etc/mysql/mariadb.conf.d/99-basic_common.cnf

[mysqld]
### mehr als 1G gehen nicht
max_allowed_packet             = 1G
### mehr als 1G gehen nicht
net_buffer_length              = 1G
net_read_timeout               = 28800
innodb_lock_wait_timeout       = 28800
delayed_insert_timeout         = 28800
connect_timeout                = 28800
wait_timeout                   = 28800

Allerdings funktioniert es nicht immer.

Start eines 2. MySQL-DBMS

Dirty Start

#!/bin/bash
 
(/bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/mysql/my1.cnf) &
 
sleep 3
set -x
ps alxwww | grep -Fi mysql | grep -F port=3307
netstat -antpwww | grep -F :3307
mysqlshow -S /var/lib/mysql/data1/mysqld.sock
wc -l /var/lib/mysql/mysqlerr1.log

EVENTS + ROUTINES

alle Proceduren anzeigen:

SELECT SPECIFIC_NAME,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE FROM information_schema.routines WHERE ROUTINE_SCHEMA!='sys';

alle Events anzeigen:

USE Datenbankname;
SHOW EVENTS;

einen Event auf einem Knoten aktivieren:

ALTER EVENT Eventname ON SCHEDULE EVERY 1 MINUTE ON COMPLETION PRESERVE ENABLE;