MySQL Cheat-Sheet

Da ich euch nicht mit Dingen wie SELECT, INSERT oder UPDATE nerven möchte, aber es auch immer wieder Dinge gibt die ich regelmäßig nachschlagen muss, hier mein persönliches MySQL-Cheat-Sheet in der Hoffnung das es euch auch helfen möge 😉
Mitschneiden der MySQL-Session, z. B. für Doku’s:
[user@host ~]$ mysql -u root -p --tee=/tmp/what_i_have_done.log
Logging to file '/tmp/what_i_have_done.log'
Enter password:

Anzeigen aller DB’s mit der jeweiligen Größe in MB:
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

Auflisten aller Benutzer und deren Datenbankberechtigungen:
SELECT grantee, table_schema, privilege_type FROM information_schema.schema_privileges;

Klonen von Tablellen (keys und index werden nicht automatisch übernommen!):
CREATE TABLE AS SELECT * FROM ;

Erstellen / Wiederherstellen einer Replikation:
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='myotherdbms', MASTER_USER='replication_user', MASTER_PASSWORD='nsa_will_never_guess', MASTER_LOG_FILE='mysql-bin.00000', MASTER_LOG_POS=414138;
START SLAVE;

Bei Fehlern in der Replikation auch gerne gesehen… Ignoriere eine Anzahl N an Fehlern:
SET GLOBAL sql_slave_skip_counter = N;

Sollte ich noch mehr coole Sachen finden, werde ich diese Liste selbstverständlich erweitern 🙂

Tobias Redel
Tobias Redel
Head of Professional Services

Tobias hat nach seiner Ausbildung als Fachinformatiker bei der Deutschen Telekom bei T-Systems gearbeitet. Seit August 2008 ist er bei NETWAYS, wo er in der Consulting-Truppe unsere Kunden in Sachen Open Source, Monitoring und Systems Management unterstützt. Insgeheim führt er jedoch ein Doppelleben als Travel-Hacker, arbeitet an seiner dritten Millionen Euro (aus den ersten beiden ist nix geworden) und versucht die Weltherrschaft an sich zu reißen.

MySQL Replikations-Integrität sicherstellen

Das Percona-Toolkit (Alias Maatkit) hatten wir ja schon einmal vorgestellt und heute möchten wir einen weiteren Aspekt der Tool-Sammlung hervorheben.
Denn wer kennt das nicht, eine Replikation unter MySQL mit Master-Slave oder Master-Master läuft über die Zeit hinweg stabil, doch nach und nach können sich Differenzen in den Daten einschleichen. Selbst wenn es nie einen Hinweis in der Replikation dafür gegeben hat. Dies kann durch Fehler jeder Art geschehen.
Genau dafür hat das Toolkit den ‘table-checksum‘ Befehl, der es einem ermöglicht, die Inhalte nach verschiedensten Algorithmen zu prüfen und mit Checksummen abzugleichen. Praktikabel lässt sich dies auch in Replikationen einbauen, welche durch die selbige die Befehle auch auf den Slaves zum exakt gleichen Stand ausführt.  Die Statemants dafür werden dann in einer gesonderten Tabelle und ggf. auch in einer eigenen Datenbank gespeichert und repliziert. Wenn Master und Slave die Abfragen durchgeführt haben ( Achtung, kann je nach Größe einige Zeit in Anspruch nehmen ), kann dann die Gegenprüfung beginnen. Ein schlichtes Beispiel Setup mit eigenem User würde wie folgt aussehen.
Erstellen der Checksummen:

pt-table-checksum --quiet --defaults-file PFAD --replicate DB.TABLE \
 --empty-replicate-table --create-replicate-table MASTER

Gegenprüfen der Summen:

pt-table-checksum --defaults-file PFAD --replicate DB.TABLE --replicate-check 1 MASTER

Sollte sich dann eine Differenz in den Daten aufzeigen, kann man dies über viele Wege wieder korrigieren, aber auch hierfür gibt es einen Befehl, dieser nennt sich ‘table-sync‘. Da es aber je nach Setup der Replikation und der Tabellen verschiedene Vorgehen gibt, wird hier nur kurz ein allgemeines Schema als Beispiel gezeigt, ausgehend von einer Master Slave Replikation, wobei der Master die korrekten Daten beinhaltet:

pt-table-sync --defaults-file PFAD --execute --replicate DB.TABLE MASTER

Alternativ würde auf folgendes gehen, wobei hier noch einmal alle Daten abgeglichen werden:

pt-table-sync --defaults-file PFAD --execute --sync-to-master SLAVE

Da die Überwachung der Inhalte mit ‘table-checksum’ automatisiert und die Return Codes ausgelesen werden können, eignet sich das ganze auch für ein Monitoring der Situation. Dies kann dann z.B. einmal in der Woche die Inhalte prüfen, je nach Größe der Daten.

Postgresql-Replikation mit pgpool-II

In Bezug auf Bernds Blogpost mit seinem Hinweis auf die gute Weiterentwicklung der Replikationsfunktionen in Postgresql stelle ich heute eine sehr komfortable und robuste Art der PGSQL-Replikation vor. Postgresql verfügt ab der Version 9.0 über eine Streaming-Replikation mit Hot-Stand-by. Eine Streaming-Replikation ist im Prinzip das gleiche Verfahren wie bei einem MySQL Master-Slave Setup mit Row-Based Replikation. Das WAL (Write-Ahead-Log) wird hier vom Read-only Slave Server abgerufen und ausgeführt. Im Falle eines Ausfalls des Masters besteht die Möglichkeit darauf zu reagieren und vom Read-Only- in Read-Write-Mode zu wechseln und somit den Slave zum Master zu propagieren.
Die third-party Software pgpool2 ist in der Lage diesen Failover zu steuern bzw. zu veranlassen. Pgpool2 ist eine Middleware die zwischen dem PGSQL-Client und Servern fungiert und bietet folgende Funktionen:

  • Connection-Pooling,

Verbindungen zu den PGSQL-Servern werden persistent erzeugt und werden wiederverwendet.

  • Replication,

DML-Statements können dupliziert werden und an die dahinter liegenden Postgres Server verteilt. Eine Streaming-Replikation wäre hierbei nicht notwendig.

  • Load-Balancing,

Client-Anfragen werden verteilt auf die Nodes, die sich im Pool befinden. In einer Streaming-Replikation besteht außerdem die Möglichkeit DML-Statements nur an den Master zu senden und SELECTS an die Slaves.

  • Limiting Exceeding Connections,

bei erreichen der maximalen Anzahl von Datenbankverbindungen gibt Postgres einen Fehler zurück. pgpool2 kann diese Verbindungen in eine Queue ablegen und abarbeiten.

  • Parallel Query

Queries können paralell über mehrere Datenbankserver hinweg ausgeführt werden, um ein Ergebnis schneller ausliefern zu können.

Für die Hot-Standby-Lösung muss der Replikationsmodus von pgpool2 deaktiviert werden. pgpool2 steuert nur die Verbindungen auf die Server, sucht sich seinen Master und kümmert sich um den Failover. Hierfür sind einige kleine Skripte notwendig. Ein weiteres sehr nützliches Feature ist die Überwachung des Slave-Lags. Ist der Slave-Lag höher als der eingestellte Schwellwert schwenkt pgpool2 die Verbindungen von diesem Slave weg bis dieser wieder aufgeholt hat. Auch sehr hilfreich ist das Online-Recovery das eigentlich durch Postgres zur Verfügung gestellt wird. Mit pgpool2 kann man dieses jedoch bequem starten und einen inkonsistenten Slave  von seinem Master im Produktivbetrieb ohne Downtime, Snapshots oder gelockten Tabellen wiederherstellen.
Die Steuerung von pgpool2 erfolgt über die i.d.R. mit installierten CLI-Tools. Zum Beispiel kann man mit pcp_recovery_node das Online-Recovery durchführen oder mit pcp_attach_node bzw. pcp_detach_node Hosts aus dem Pool entfernen bzw. hinzufügen.
Der Blogpost soll nur einen kurzen Überblick über Features und Möglichkeiten darstellen – vollständige Informationen findet man in den sehr guten Dokumentationen von pgpool2 und Postgresql.

Sebastian Saemann
Sebastian Saemann
Head of Managed Services

Sepp kam von einem großen deutschen Hostingprovider zu NETWAYS, weil ihm dort zu langweilig war. Bei uns kann er sich nun besser verwirklichen, denn er leitet zusammen mit Martin das Managed Services Team. Wenn er nicht gerade Server in MCollective einbindet, versucht er mit seinem Motorrad einen neuen Geschwindigkeitsrekord aufzustellen.