Vielen Linuxanwendern sind Befehle wie top oder htop ein gängiger Begriff. Auch für den Datenbankserver MySQL gibt ein top-ähnliches Überwachungstool – mytop. Dieses kleine Programm bietet dem Admin einen schnellen Überblick der derzeitigen Serverauslastung und Nutzung.

mytop findet sich in den Ubuntu Paketquellen und lässt sich wie folgt installieren.

$ sudo apt-get install mytop

Nach der Installation könnte ein Beispielaufruf so aussehen:

mytop -u BENUTZER -p PASSWORT -h HOST-d DATENBANK

Alternativ zu diesem Aufruf kann man sich auch die Parameter sparen und diese in der Konfigurationsdatei ~/.mytop ablegen.

user=BENUTZER
pass=PASSWORT
host=HOST
db=DATENBANK
delay=5
port=3306
batchmode=0
header=1
color=1
idle=1

Achtung! Das Speichern von Passwörtern im Klartext kann gefährlich sein! Man sollte sicher gehen das kein Dritter Zugriff auf die Datei ~/.mytop erlangen kann! Verwendung also auf eigene Gefahr!

Nehmen wir folgenden Fall an: Ich habe eine Tabelle mit verschiedenen Werten.

CREATE TABLE `xyz` (
    `a` VARCHAR(50) NOT NULL,
    `b` INT(10) NOT NULL DEFAULT '0',
    `c` DATETIME NOT NULL
);

Bisher wurden die Werte wild hinzugefügt oder geändert.

INSERT INTO `xyz` (`a` ,`b` ,`c`) VALUES ('Testeintrag', '123', NOW());
oder
UPDATE `xyz` SET `a`='Testeintrag',`c`=NOW() WHERE b='123';

Jetzt aber möchte ich dieser Tabelle etwas hinzufügen – aber nur dann – wenn der Wert in Spalte b nicht existiert. Andernfalls muss der Eintrag geändert werden. Dieses Konstrukt über die Applikationslogik(en) abzubilden, wäre in meinem Fall aber recht aufwändig gewesen. Deshalb musste eine andere Lösung her.

MySQL bietet in diesem Fall eine einfache Lösung an INSERT … ON DUPLICATE KEY UPDATE. Hierzu muss die entscheidende Spalte b als UNIQUE definiert werden (Hinweis: Doppelte Eintrage in MySQL Tabelle finden) und die Query in der Applikation wie folgt angepasst werden.

INSERT INTO `xyz` (`a` ,`b` ,`c`) VALUES ('Testeintrag', '123', NOW())
ON DUPLICATE KEY UPDATE `a`='Testeintrag',`c`=NOW();

Wenn jetzt der Wert der Spalte b bereits in der Tabelle enthalten ist, wird diese Spalte einfach aktualisiert.

Weitere Informationen zum Thema:
http://dev.mysql.com/doc/refman/5.1/de/insert-on-duplicate.html

Ich denke der Titel verrät bereits um was es in diesem Artikel geht. Ausgangspunkt ist folgende Tabelle.

CREATE TABLE `xyz` (
    `a` VARCHAR(50) NOT NULL,
    `b` INT(10) NOT NULL DEFAULT '0',
    `c` DATETIME NOT NULL
);

Nun soll die Spalte b einen UNIQUE Key bekommen. Das Problem ist aber, das es in dieser Spalte bereits viele Dopplungen gibt. Um sie zu finden nehme ich folgende Abfrage.

SELECT `b` FROM `xyz` WHERE `b`
IN (
   SELECT `b`
   FROM `xyz`
   GROUP BY `b`
   HAVING Count( 1 ) >1
);

Die Abfrage zeigt mir jetzt alle Einträge der Spalte b die mehr als einmal vorkommen. Jetzt kann man die Einträge entweder manuell bereinigen oder die SELECT Abfrage in eine DELETE Abfrage umbauen. Fertig.

Die Verwaltung von Datenbankinhalten kann unter Umständen recht aufwändig werden. Gerade wenn man, wie in meinem aktuellen Fall, aus ca. 1,8 Mio Datensätze (Text) HTML Tags entfernen will. Als ersten kam mir die Überlegung hierfür ein Script in PERL oder PHP zu schreiben. Da ich allerdings nicht alle Applikationen anpassen will und kann, musste die Funktion vom MySQL übernommen werden.

Dank Google habe ich hier eine vorgefertigte Funktion für MySQL gefunden.

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC
BEGIN
  DECLARE iStart, iEnd, iLength int;
  WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
    BEGIN
      SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
      SET iLength = ( iEnd - iStart) + 1;
      IF iLength > 0 THEN
        BEGIN
          SET Dirty = Insert( Dirty, iStart, iLength, ' ');
        END;
      END IF;
    END;
  END WHILE;
  RETURN Dirty;
END;
|
DELIMITER ; 

Im folgenden Beispiel wird der übergebene Text ohne HTML ausgegeben.

mysql> SELECT fnStripTags(“Testtext mit <b>fettem HTML Text</b>.”);

Jeder der eine oder mehrere Datenbanken betreibt, sollte neben der Performance auch die belegten und benötigten Ressourcen im Blick haben. Mit einer einfachen Abfrage kann man sich direkt vom MySQL Server die Speicherbelegung anzeigen lassen.

SELECT
  table_schema AS 'Datenbankname',
  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 ;

Das Resultat sieht dann in etwa so aus …

+--------------------+------------+------------+-------------+----------------------+
| Datenbankname      | Daten (MB) | Index (MB) | Gesamt (MB) | Freier Speicher (MB) |
+--------------------+------------+------------+-------------+----------------------+
| shopdb             |   2457.729 |   1218.272 |    3676.002 |                4.000 |
| testdb             |      0.460 |      0.324 |       0.784 |                0.000 |
| information_schema |      0.000 |      0.008 |       0.008 |                0.000 |
| logging            |   1239.394 |    702.597 |    1941.991 |                0.000 |
| mysql              |      0.492 |      0.087 |       0.579 |                0.000 |
+--------------------+------------+------------+-------------+----------------------+
5 rows in set (0.00 sec)

(Die Abfrage stammt von http://www.artfulsoftware.com/infotree/queries.php und wurde von mir leicht angepasst.)