Archiv des Autors: admin

Port über ssh tunneln

Veröffentlicht am von

Um einen Port eines entfernten Servers über eine ssh-Verbindung zum lokalen Client zu tunneln baut man diese Verbindung wie folgt auf:

ssh -v -L <localhost/ip>:<localport>:<serverhost/ip>:<serverport> <user>@<server>

Änderung der Funktionsowner unter PostgreSQL

Veröffentlicht am von

Folgende Abfrage gibt ein SQL-Script zu Änderung der Owner aller Funktionen des Schemas „public“ aus. Der neue Owner wird „<neuer_owner>“

select 'alter function '||nsp.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to <neuer_owner>;'
from pg_proc p
join pg_namespace nsp ON p.pronamespace = nsp.oid
where nsp.nspname = 'public';

PostgreSQL Performanceprobleme trotz korrekter Indices

Veröffentlicht am von

Wenn eine Datenbank trotz korrekt vorhandener Indices ein Performanceverhalten hat als ob kein Index vorhanden ist, so kann es sein das die Indices, oder ein einzelner Index defekt oder unaktuell ist. Man kann PostgreSQL anweisen seine Indices neu aufzubauen. Dieses funktioniert auf Datenbankebene mit

REINDEX DATABASE <Datenbank>;

auf Tabellenebene mit

REINDEX TABLE <Tabellenname>;

und auf Indexebene mit

REINDEX INDEX <Index>;

Weitergehende Informationen dazu gibt es unter
http://www.postgresql.org/docs/9.4/static/sql-reindex.html

Verbindung zu einer hängenden Oracle-Instanz

Veröffentlicht am von

Wenn eine Oracle-Instanz keine Verbindungen zuläßt, z.B. mit dem Fehler „ORA-00020: maximum number of processes exceeded“, dann hat man unter Umständen die Chance sich mit der Datenbank mit einer „light“-Verbindung zu connecten und somit den bösen „kill -9“ zu vermeiden.

sqlplus  -prelim / as sysdba

Aus diesen Verbindung heraus, die als Debugging-Verbindung gedacht ist, kann man die Datenbank herunter fahren.

SQL> shutdown abort

PostgreSQL-Sessions killen

Veröffentlicht am von 
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE 
  -- don't kill my own connection!
  pid <> pg_backend_pid()
  -- don't kill the connections to other databases
  AND datname = 'database_name';

MySQL-Verbindung verschlüsseln mit autossh

Veröffentlicht am von

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

Veröffentlicht am von

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.