MySQL: nützliche Kommandos

Diesen Beitrag schrieb ich 13 Jahre und 8 Monate zuvor; die nachfolgenden Ausführungen müssen heute weder genau so nach wie vor funktionieren, noch meiner heutigen Meinung entsprechen. Behalte das beim Lesen (und vor allem: beim Nachmachen!) bitte stets im Hinterkopf.

Geschätzte Lesezeit: 3 Minuten

Wenn man die Möglichkeit hat, sie auch zu nutzen, dann ist die MySQL-Shell in jedem Fall phpMySQLadmin vorzuziehen. Deshalb habe ich hier mal zusammengetragen, welche Kommandos ich immer wieder verwende – und vielleicht hilft euch sowas ja auch weiter.

Grundlegendes

Die Datenbankverbindung aufbauen (den -h-Parameter kann man sich bei localhost auch schenken):

$ mysql -h $hostname -u $username -p'$passwort'

Oder auch mit direkter Angabe des Passwortes – aber Vorsicht, das kann dann per history abgerufen werden! Wenn ihr scriptet müsst ihr unbedingt daran denken, dass zwischen -p und dem Passwort kein Leerzeichen stehen darf! Zur Sicherheit sollte man sich ohnehin angewöhnen, das Passwort immer zu quoten, da es sonst zu unschönen Zusammenstößen bei Verwendung wirrer Sonderzeichen kommen kann. Wenn was schief geht, kommt eine Meldung wie die folgende:

ERROR 1045 (28000): Access denied for user '$username'@'$hosname' (using password: YES)

Prompt

Der Output sieht bei erfolgreichem Connect dann folgendermaßen aus:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is $CONNECTION_ID
Server version: $SERVER_VERSION

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Im laufenden Betrieb

Eine Datenbank auf dem MySQL-Server erstellen:

CREATE DATABASE $dbname;

Alle Datenbanken, die auf dem Server liegen, auflisten – die Ausgabe erfolgt in Tabellenform:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)

Zu einer bestimmten Datenbank überwechseln, im Beispiel mal zur Datenbank information_schema:

USE information_schema;

Alle Tabellen der Datenbank auflisten – im Beispiel der Datenbank mysql führt das zu einem Output ähnlich diesem:

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              | 
| db                        | 
| func                      | 
| help_category             | 
| help_keyword              | 
| help_relation             | 
| help_topic                | 
| host                      | 
| proc                      | 
| procs_priv                | 
| tables_priv               | 
| time_zone                 | 
| time_zone_leap_second     | 
| time_zone_name            | 
| time_zone_transition      | 
| time_zone_transition_type | 
| user                      | 
+---------------------------+
17 rows in set (0.00 sec)

Um sich das Format der Datenbank näher zu betrachten:

mysql> DESCRIBE $table;

SELECT-Statements

Zur Auflistung aller Inhalte innerhalb der Struktur:

mysql> SELECT * FROM $table;

Um sich nur bestimmte Zeilen anzeigen zu lassen, in denen ein Feld gleich whatever ist:

mysql> SELECT * FROM $table WHERE $field = 'whatever';

Am Beispiel der Tabelle mysql:

mysql> SELECT * FROM user WHERE User='root';

Nun lassen wir uns alle User anzeigen, die nicht root heißen – sortiert nach den Hostnamen:

mysql> SELECT * FROM user WHERE User != 'root' ORDER BY Host;

Nun lassen wir uns alle Einträge für root anzeigen, die sich zugleich auf den Host localhost beziehen:

mysql> SELECT * FROM user WHERE User='root' AND Host='localhost';

Jetzt lassen wir uns mal alle Usernamen auflisten, die mit ro beginnen und deren Host localhost ist:

mysql> SELECT * FROM user WHERE User like "ro%" AND Host='localhost';

Dann lassen wir uns jetzt das selbe nochmal ausgeben – aber auf die ersten drei Einträge beschränkt:

mysql> SELECT * FROM user WHERE User like "ro%" AND Host='localhost' LIMIT 3;

Zum Anzeigen eindeutiger Einträge, hier mal am Beispiel der User-Spalte der user-Table:

mysql> SELECT DISTINCT User from user;
+------------------+
| User             |
+------------------+
| root             | 
| debian-sys-maint | 
+------------------+
2 rows in set (0.00 sec)

Nun schränken wir den Zugriff mal auf bestimmte Bereiche der Datenbank ein:

mysql> SELECT User,Host from user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | 127.0.0.1 | 
| debian-sys-maint | localhost | 
| root             | localhost | 
| root             | ux        | 
+------------------+-----------+
4 rows in set (0.01 sec)

Und jetzt lassen wir das anhand der Hostnamen mal aufsteigend (ASC, ascending) sortieren (absteigend wäre DESC, descending) :

mysql> SELECT User,Host from user ORDER BY Host ASC;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | 127.0.0.1 | 
| debian-sys-maint | localhost | 
| root             | localhost | 
| root             | ux        | 
+------------------+-----------+
4 rows in set (0.00 sec)

Dann lassen wir mysql jetzt mal zählen, wie viele User eigentlich im System vorhanden sind:

mysql> SELECT COUNT(*) FROM user;
+----------+
| COUNT(*) |
+----------+
|        4 | 
+----------+
1 row in set (0.00 sec)

DROP, DELETE, ALTER und ADD

Zum vollständigen Löschen einer Datenbank:

mysql> DROP DATABASE $dbname;

Zum Löschen einer Tabelle:

mysql> DROP TABLE $table;

Eine bestimmte Zeile einer Tabelle löschen:

mysql> DELETE FROM $table WHERE $field='whatever';

Zum Löschen einer Spalte:

mysql> ALTER TABLE $table DROP COLUMN $column;

Hinzufügen einer Spalte zu einer Tabelle:

mysql> ALTER TABLE $table ADD COLUMN $new_column varchar (20);

Und um den Namen einer Spalte zu ändern:

mysql> ALTER TABLE $table CHANGE $old_name $new_name varchar (50);

User und Passwörter

Jetzt nochmal für alle und zum Mitschreiben: wir erstellen einen neuen User blafasel und eine Datenbank mit Namen blafasel; der User soll vollen Zugriff auf diese Datenbank haben. Hierzu loggen wir uns als privilegierter User ins MySQL-Backend ein und führen die folgenden Befehle aus:

mysql> CREATE DATABASE blafasel;
Query OK, 1 row affected (0.15 sec)

mysql> CREATE USER 'blafasel'@'localhost' IDENTIFIED BY 'passwort';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON blafasel.* TO 'blafasel'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

User blafasel kann sich nun mit seinem Passwort auch einloggen, aber bei dem Passwort haben wir Mist gebaut: ist zu unsicher. Folgendermaßen ändern wir sein es:

mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('neuessicherespasswort') WHERE User='blafasel' AND Host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> FLUSH PRIVILEGES;

Dummerweise haben wir das root-Passwort für unseren MySQL-Server vergessen, doch folgendermaßen lässt es sich zurücksetzen:

$ /etc/init.d/mysqld stop
$ mysqld_safe --skip-grant-tables &
$ mysql -u root
mysql> USE mysql;
mysql>  UPDATE user SET password=PASSWORD("neuesrootpasswort") WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
$ /etc/init.d/mysqld stop
$ /etc/init.d/mysqld start

Wenn noch kein root-Passwort gesetzt ist, macht man das folgendermaßen (bei der Installation von MySQL über Pakete wird aber idR. schon eines angelegt):

$ mysqladmin -u root password newpassword

Und um das root-Passwort zu ändern (Achtung: hier wirklich old-password hinschreiben, nicht das alte Passwort!):

$ mysqladmin -u root -p old-password 'neuessicherespasswort'

Backup und Restore

So legen wir einen Dump von allen Datenbanken an:

$ mysqldump -u root -p'$password' --all-databases > /tmp/sqldump_complete.sql

Wenn nur eine bestimmte Datenbank ins Backup soll:

$ mysqldump -u $username -p'$password' --databases $dbname > /tmp/$dbname_dump.sql

Um nur eine bestimmte Tabelle zu dumpen:

$ mysqldump -c -u $username -p'$password' $dbname $table > /tmp/$dbname_$table_dump.sql

Und um aus einem (beliebigen) SQL-File eine Datenbank oder Tabelle wieder herzustellen:

$ mysql -u $username -p'$password' $dbname < /tmp/$dbname_dump.sql

Beispiel: ein Shell-Script für MySQL

#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="password"
MYSQL_DB="mysql"
MYSQL_HOST="localhost"
MYSQL_BIN="/usr/bin/mysql"
CONNECT="$MYSQL_BIN --skip-column-names --batch -h $MYSQL_HOST -u $MYSQL_USER -p'$MYSQL_PASS $MYSQL_DB'"
usernames=`$CONNECT -e "SELECT User FROM user ORDER BY user ASC"`
echo $usernames

Das Script ausgeführt ergibt eine Liste aller User im System:

$ ./mysql_test.sh 
debian-sys-maint root root root

Dies soll nur eine kleine Idee sein, wie man auch an die Sache herangehen kann – MySQL aus Shell-Scripten heraus anzusteuern ist nämlich erstaunlich wenig schmerzhaft und macht durchaus Spaß!

Alle Bilder dieser Seite: © Marianne Spiller – Alle Rechte vorbehalten
Hintergrundbild: Bild genauer anschauen – © Marianne Spiller – Alle Rechte vorbehalten

Eure Gedanken zu „MySQL: nützliche Kommandos“

Ich freue mich über jeden Kommentar, es sei denn, er ist blöd. Deshalb behalte ich mir auch vor, die richtig blöden kurzerhand wieder zu löschen. Die Kommentarfunktion ist über GitHub realisiert, weshalb ihr euch zunächst dort einloggen und „utterances“ bestätigen müsst. Die Kommentare selbst werden im Issue-Tracker und mit dem Label „✨💬✨ comment“ erfasst – jeder Blogartikel ist ein eigenes Issue. Über GitHub könnt ihr eure Kommentare somit jederzeit bearbeiten oder löschen.