select table_schema "Database",round(sum(data_length+index_length)/1024/1024,4) "Size (MB)" from information_schema.tables group by table_schema;
Schlagwort-Archive: mysql
MySQL-Verbindung verschlüsseln mit autossh
In der Standardkonfiguration kommuniziert ein MySQL-Server unverschlüsselt. Neben der MySQL eigene SSL-Implementierung gibt es weitere Möglichkeiten eine solche Verbindung abzusichern. Ich bevorzuge hierzu einen ssh-Tunnel der mit autossh sehr performant und stabil läuft.
Autossh übernimmt hierbei die Funktion eine Whatchdogs, der den Tunnel überwacht und ihn ggf. bei Störungen wieder aufbaut.
Voraussetzung hierfür ist, das der User der den Tunnel aufbaut sich per ssh-Key ohne Passwort authentifiziert. Hierzu erstellt man unter dem entsprechenden User (auf dem Client nutze ich hierzu root) mit
ssh-keygen -t rsa -b 4096
auf dem Client ein Keypair. Die Keys findet man unter ~/.ssh. Den Public-Key hängt man nun in der Datei ~/.ssh/authorized_keys auf dem Host an. Ich empfehle hierzu einen gesonderten User anzulegen. Sollte die Datei noch nicht bestehen, so ist sie zu erstellen. Keinesfalls sollte der User auf dem Host root sein.
Nun kann man mit
autossh -M 0 -N -L 3307:localhost:3306 <user_auf_host>@<host>
den ssh-Tunnel aufbauen.
Wenn man auf dem Client keine eigene MySQL-Instanz installiert hat, so kann man den lokalen Port auch von 3307 auf 3306 ändern, man spart sich dann bei der MySQL-Verbindung die Angabe des Ports.
Die Option -M 0 gibt an das autossh keinen Monitoringport nutzt und somit den Tunnel nur auf ssh-exit neu aufbaut. Wenn autossh ohne Monitoring genutz wird sollten in der Datei /etc/ssh/ssh_config die Parameter ServerAliveInterval und ServerAliveCountMax gesetzt werden. ServerAliveInterval gibt an wie lange der ssh-Client untätig sein muß bevor ein „keepalive-Pakete“ gesendet wird. ServerAliveCountMax gibt an, wieviele „keepalive-Pakete“ gesendet werden ohne eine Antwort vom Server zu bekommen bevor die Verbindung terminiert wird. Man sollte diese Werte, wenn man kein autossh-Monitoring benutzt, möglichst klein wählen. Ich setzte diese Werte normalerweise Folgendermaßen:
ServerAliveInterval = 60 ServerAliveCountMax = 1
Als nächstes muß der User auf der Datenbank für die Verbindung vorbereitet werden. Soll der User <user> über den Tunnel auf die Datenbank zugreifen, so muß er in MySQL als Host 127.0.0.1 eingetragen haben, localhost funktioniert hier nicht, da mysql dann automatisch Socket statt TCP benutzt, selbst wenn ein Port angegeben wird. Der user muß also mit
GRANT <RECHTE> ON <DATENBANK>.* TO '<user>'@'127.0.0.1' IDENTIFIED BY '<passwort>';
angelegt werden und kann dann vom Client mit
mysql -u <user> -p -h 127.0.0.1 -P 3307
auf die Datenbank zugreifen. Falls auf dem Client der Port 3306 statt 3307 genutzt wird so ist natürlich die Angabe des Ports nicht notwendig.
Logging von Userconnections
MySQL bringt von Haus aus keine Loggingfunktion für Userconnections mit, mit einigen wenigen Schritten läßt sich diese Funktion allerdings sehr einfach nachbilden. Grundsätzlich besteht auch die Möglichkeit in eine Datei zu loggen, im folgenden wird aber die, meiner Meinung nach, elegantere Möglichkeit des Loggings in eine Datenbanktabelle beschrieben.
Als erstes braucht man natürlich eine Datenbank und eine Tabelle in die man loggt. Die Datenbank (in diesem Beispiel „admin“) erstellt man mit:
CREATE DATABASE admin;
Als nächstes erzeugt man die Tabelle in die geloggt werden soll:
CREATE TABLE `userlog` ( `user` VARCHAR(50) NOT NULL, `connections` INT(10) UNSIGNED NOT NULL DEFAULT '0', `last` DATETIME NOT NULL, PRIMARY KEY (`user`) )
Nun wird die Variabele init_connect gesetzt. Diese Variabele enthält eine Abfrage die bei jeder neuen Verbindung ausgeführt wird.
SET GLOBAL init_connect = " INSERT INTO admin.userlog (`user`, `connections`, `last`) VALUES (CURRENT_USER(), 1, NOW()) ON DUPLICATE KEY UPDATE `connections`=`connections`+1, lastconnect=NOW();"
Auf diese Weise wird mitgeloggt, welcher User sich wie oft und wann zuletzt angemeldet hat.
Natürlich kann ich auch jeder einzelnen Loginvorgang mit Loginzeitpunkt geloggt werden, dieses kann aber zu einem Recht hohen Datenaufkommen führen. Hierfür wird die Tabelle mit eine leicht anderen Struktur angelegt:
CREATE TABLE `userlog` ( `user` VARCHAR(50) NOT NULL, `time` DATETIME NOT NULL)
Und die Variabele mit folgender Abfrage gefüllt:
SET GLOBAL init_connect = " INSERT INTO admin.userlog (`user`, `time`) VALUES (CURRENT_USER(), NOW())"
Für die erste Lösung muß jeder User SELECT-, INSERT- und UPDATE-Rechte auf die Tabelle admin.userlog gewährt bekommen, für die zweite Lösung reichen INSERT-Rechte.
Ein SET GLOBAL ist immer nur bis zum nächsten Neustart des Datenbankprozesses gültig, daher sollte man die Variabele init_connect in die my.cnf datei eintragen.
MySQL Datenbank in utf8 konvertieren
Als Erstes wird ein Dump der Datenbank erstellt. Mit folgendem Oneliner werden auch gleichzeitig alle Definitionen in utf8 geändert:
mysqldump --opt -Q -u <deinuser> -p<deinpass> -h <deinhost> <deinedatenbank> | sed s'/DEFAULT CHARSET=.*;/DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/g''> mysql_dump.sql
Als nächstes muß die aktuelle Kodierung der Dump-Datei festgestellt werden:
file -i mysql_dump.sql
Sollte die Datei bereits in utf8 vorliegen muß diese nicht mehr geändert werden, andernfalls wird die Kodierung der Datei mit folgendem Befehl auf utf8 geändert:
iconv -f <altescharset> -t utf8 mysql_dump.sql > mysql_dump.utf8.sql
Um den Dump wieder einzuspielen wird nun folgender Befehl genutzt:
mysql -u <deinuser> -p<deinpass> -h <deinhost> --default-character-set=utf8 <deinedatenbank> < mysql_dump.sql (oder mysql_dump.utf8.sql)
In MySQL sollte die Datenbank nun in utf8 vorliegen.
Alle MySQL-Verbindungen eines users beenden
Oft hat man das Problem in MySQL aus irgendeinem Grund alle Verbindungen eines users beenden zu müssen. Da eine Syntax wie „kill where user like …“ nicht existiert muß man sich mit einem kleinen Workaround behelfen. Man läßt MySQL sozusagen selber ein Killscript schreiben und sendet dieses dann an die Datenbank. Das Killscript erhält man mit folgender Abfrage:
mysql: select concat("KILL ",id,";") from information_schema.processlist where user="root";
+------------------------+
| concat("KILL ",id,";") |
+------------------------+
| KILL 1234; |
| KILL 1235; |
+------------------------+
2 rows in set (0.00 sec)
Um das Script direkt in einer Form zu bekommen die ich sofort wieder an die Datenbank senden kann benutze ich „into outfile“:
mysql: select concat("KILL ",id,";") from information_schema.processlist where user="root" into outfile "/tmp/killscript.sql";
Query OK, 2 rows affected (0.00 sec)
Nun brauche ich das Ganze nur noch mit „source“ als Abfragefolge wieder an die Datenbank senden:
mysql: source /tmp/killscript.sql; Query OK, 0 rows affected (0.00 sec)
Diese Vorgehensweise funktioniert natürlich auch wenn ich z.B. alle Verbindungen zu einer Datenbank oder alle Verbindungen von einem Host beenden möchte, hierzu muß ich nur meinen Select entsprechend anpassen.
MySQL-Datenbanken leeren
Es existiert kein MySQL-Befehl mit dem man auf einen Schlag alle Tabellen einer MySQL Datenbank löschen kann, aber es gibt mehrere Workarounds für dieses Problem. Mit einem einfachen
DROP DATABASE zuleerendedatenbank; CREATE DATABASE zuleerendedatenbank;
kann man auf einfache und schnelle Weise die Datenbank leeren. Diese Vorgehensweise hat allerdings einige kleine Nachteile. Zum Einen benötigt man hierfür das Recht die Datenbank zu löschen bzw. auch wieder neu anzulegen (was in Hostingprovider-Umgebungen eher selten gegeben ist) zum Anderen wird wirklich die komplette Datenbank gelöscht und neu erstellt was natürlich auch sämtliche Einstellungen die man auf Datenbankebene getätigt hat (z.B. Zeichensatzeinstellungen der Datenbank) wieder auf default zurück setzt. Eine Möglichkeit das zu umgehen hat man mit folgendem Einzeiler
mysqldump -ubenutzer -ppasswort --no-data --add-drop-table zuleerendedatenbank | grep DROP | mysql -ubenutzer -ppasswort zuleerendedatenbank
bei dem man einfach mit mysqldump einen Datenbankdump mit DROP TABLE Anweisungen (–add-drop-table) und ohne Tabelleninhalte (–no-data) zieht und alle Zeilen die mit DROP beginnen mit mysql wieder an die Datenbank sendet.
Suchen und Ersetzen in MySQL-Datenbanken
Suchen und Ersetzen in einer Datenbanktabelle:
UPDATE tabelle SET spalte = REPLACE(spalte, suchmuster, ersatz);
Wobei:
tabelle = zu durchsuchende Tabelle
spalte = zu durchsuchende Spalte
suchmuster = zu ersetzender String
ersatz = Ersatzstring