mysql Replikation

wall < "MySQL: nützliche Kommandos"

Broadcast message from spillerm@unixe.de (pts/1) (Sa Jan 08 14:03:44 2011):
4
Diesen Beitrag schrieb ich vor 8 Jahren. Behalte das beim Lesen bitte im Hinterkopf.

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össen 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)

Der Prompt

Der Output sieht bei erfolgreichem Connect dann folgendermassen 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:

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:

SHOW TABLES;

Im Beispiel der Datenbank mysql führt das zu einem Output ähnlich diesem:

+---------------------------+
| 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:

DESCRIBE $table;

SELECT-Statements

Zur Auflistung aller Inhalte innerhalb der Struktur:

SELECT * FROM $table;

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

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

Am Beispiel der Tabelle mysql:

SELECT * FROM user WHERE User='root';

Nun lassen wir uns alle User anzeigen, die nicht root heissen — sortiert nach den Hostnamen:

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:

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:

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:

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:

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:

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) :

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:

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:

DROP DATABASE $dbname;

Zum Löschen einer Tabelle:

DROP TABLE $table;

Eine bestimmte Zeile einer Tabelle löschen:

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

Zum Löschen einer Spalte:

ALTER TABLE $table DROP COLUMN $column;

Hinzufügen einer Spalte zu einer Tabelle:

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

Und um den Namen einer Spalte zu ändern:

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

User und Passwörter

Jetzt nochmal für alle 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:

CREATE DATABASE blafasel;
Query OK, 1 row affected (0.15 sec)
CREATE USER 'blafasel'@'localhost' IDENTIFIED BY 'passwort';
Query OK, 0 rows affected (0.03 sec)
GRANT ALL PRIVILEGES ON blafasel.* TO 'blafasel'@'localhost';
Query OK, 0 rows affected (0.00 sec)
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. Folgendermassen ändern wir sein Passwort:

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

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

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

Wenn noch kein root-Passwort gesetzt ist, macht man das folgendermassen (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 Spass!

4
  1. Pingback: Netzfänge – Uwe

  2. ah ja, sehr schön :-) immer gut zu wissen wo sowas zufinden ist, auch wenn ich es so gut wie nie benötige :)

  3. Vielleicht noch sinnvoll für ein automatisches Backup:

    Alle existierenden Datenbanken:
    DBS="$(${MYSQL_BIN} -u ${MYSQL_USER} -h ${MYSQL_HOST} -p${MYSQL_PASS} -Bse 'show databases')"
    jede DB in ein separates File sichern:

    BACKUP_DIR="/backup/mysql"
    NOW=$(date +"%Y-%m-%d_%H-%M")
    GZIP="/bin/gzip"
    MYSQLDUMP="/usr/bin/mysqldump"
    for db in $DBS
    do
    $MYSQLDUMP -u ${MYSQL_USER} -h ${MYSQL_HOST} -p${MYSQL_PASS} ${db} | ${GZIP} -9 > "${BACKUP_DIR}/${NOW}_${db}.sql.gz"
    done

Keine weitere Reaktionen mehr möglich.