MySQL Replikation (Master/Slave) einrichten

Im Artikel „MySQL Cluster oder Hot-Stand-By Lösung? Eine Frage der Hochverfügbarkeit“ haben wir die Vorüberlegung getroffen, wie wir unser System aufbauen möchten. Da wir einen Master-MySQL-Server und mehrere Slave-MySQL-Server haben und die Daten nicht jederzeit zu 100 Prozent synchron sein müssen, können wir die MySQL Replikation (Log-Shipping) nutzen. Ein weiterer Nachteil dieser Lösung ist, dass keine Daten auf dem Slave-Server verändert werden sollten/dürften, da sonst die Datenbestände auseinanderlaufen. Da in unserem Fall hauptsächlich Abfragen getätigt werden und SQL-Inserts nur zur Besucherstatistik dienen, kann das Zusammenführen dieser statistischen Daten über einen Cron-Job nachts erfolgen. Dieser sendet die Tagesdaten an den Master-Server, der diese Daten zentral aufbereitet. Die Replikation dieser Daten ist nicht gewünscht.

Weitere vorbereitende Überlegungen
Damit die MySQL-Replikation auch ordnungsgemäß funktioniert, sollten die MySQL-Versionen zwischen Master und Slave übereinstimmen. Kleine Versionsunterschiede können kein Problem sein, können aber auch zu zeitraubenden Fallen werden. In meinem Testsystem nutze ich unterschiedliche Versionen; es klappt einwandfrei. Allerdings muss dies nicht für alle Versionsdifferenzen gelten. Um die installierte MySQL-Version der einzelnen Server zu prüfen, können wir folgenden Befehl (Putty-Konsole) nutzen:

Prüfe die MySQL-Version
Putty: mysql -h localhost -V

Mit Netz und doppelten Boden
Wenn irgendwie möglich, sollte vor Start von jedem Member (Master und Slave) Images gemacht werden. Bei den folgenden Schritten kann die MySQL-Datenbank fehlerhaft werden, was bei softwaregestützten Webservern (Plesk, Confixx) zu einem Ausfall führen kann!

Einrichtung der MySQL-Replikation auf dem Master-Server
Als erstes müssen wir auf den MySQL-Master-Server einen Replikations-Benutzer anlegen. Weitere Informationen zu diesem Thema hält die MySQL-Seite vor. Wir öffnen eine Putty-Sitzung mit dem Master-Server und loggen uns in die MySQL-Konsole ein:

MASTER:
mysql -u root -p [RETURN]

MySQL-Replikations-Benutzer anlegen
Nehmen wir nun an, dass unsere Domäne mydomain.com heißt und wir ein Konto mit dem Benutzernamen „repl“ erstellen wollen. Dieses Konto nutzen die Slave-Server unter Angabe des Passworts „slavepass“ und stellen so die Verbindung Slave-Master her. Hier sehen wir also, dass der Zugriff (lesend) vom Slave oder den Slaves zum Master funktioniert.
Das Replikations-Konto erstellen wir mit folgender GRANT-Anweisung (Hinweis: Wir benötigen an dieser Stelle Root-Rechte):

mysql> GRANT REPLICATION SLAVE ON *.*
-> TO ‚repl’@’%.mydomain.com‘ IDENTIFIED BY ’slavepass‘;

My.conf auf dem Master anpassen
Nun öffnen wir mit einem Texteditor unserer Wahl die „etc/mysql/my.cnf“ und setzen die untenstehenden Werte (je nach Installation sind diese Werte vorhanden; wir entfernen lediglich die Auskommentierung „#“)

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

Was bewirkt diese Änderung: Wir teilen MySQL mit, dass dies der Server 1 ist. Alle an der Replikation beteiligen Server benötigen eine eigene (unterschiedliche ID). Per Eintrag „log_bin“ weisen wir MySQL an, dass alle Datenbankänderungen ab sofort geloggt werden sollen (an der Stelle: /var/log/mysql/mysql-bin.log)

Den MySQL-Server restarten
Damit die Änderungen aus der my.conf auch greifen, muss der MySQL-Dienst neu gestartet warden:

/etc/init.d/mysql restart

Nun müssen wir den aktuellen Stand der Datenbank sichern. Dieser Datenstand wird auf die Slaves verteilt. Alle folgenden Transaktionen auf den Slaves erfolgen dann über das Log-Shipping.

Vorbereitung für das Abbild der Master Datenbank erstellen
Über die MySQL-Konsole sperren wir alle Schreibzugriffe auf die Datenbank:

FLUSH TABLES WITH READ LOCK;

Position vom Master-Logfile notieren
Der folgende Schritt ist sehr wichtig. Wir notieren uns die aktuelle Position vom Master-Logfile. Diese Daten benötigen wir später beim Start der Slave-Synchronisation (Abschreiben oder Hardcopy)

SHOW MASTER STATUS;

Abbild vom aktuellen Stand der Master Datenbank erstellen
Wir öffnen via Putty eine weitere Sitzung. Die Putty-Sitzung mit dem „LOCK“ lassen wir bestehen und schließen sie nicht! Mit dem folgenden Code gehen wir in das MySQL-Verzeichnis und sichern die Datenbank:

cd /var/lib/mysql
tar -cvf /root/mysql-snapshot.tar

ACHTUNG: Zu beachten gilt hier, dass wir so ALLE Datenbanken des MySQL-Servers sichern (und später auch wiederherstellen). Je nach Server-Verwaltungssoftware (zum Beispiel Plesk) sind diese Daten auch in der MySQL-Datenbank gespeichert. Wenn wir diese Daten auf dem Slave-Server einspielen, überschreiben wir die MySQL-Plesk-Daten (oder andere MySQL-Einträge) auf dem Slave-Server. Ich gebe an dieser Stelle einmal zu, dass ich so schon einmal einen Plesk-Webserver „zerschossen“ habe).

Wollen wir also nur eine Datenbank replizieren („this_db“), nutzen wir folgenden Befehl:
shell> tar -cvf /root/mysql-snapshot.tar ./this_db

Die Datenbank auf dem Master wieder freigeben
Über unsere bestehende MySQL-Konsole setzen wir folgenden Befehl ab:

UNLOCK TABLES;

Nun haben wir also den Master-Server für die MySQL-Logfile-Replikation vorbereitet und ein Abbild der Master-Datenbank erstellt. Im Artikel „MySQL Replikation auf den Slave-Servern einrichten und starten“ beschäftigen wir uns mit den Slave-Servern.

MySQL Cluster oder Hot-Stand-By Lösung? Eine Frage der Hochverfügbarkeit

Gerade bei größeren Projekten ist die Frage nach einer Datenbank-Hochverfügbarkeit eine elementare Frage. Wenn bei einem Webshop der Datenbankserver wegbricht, entgeht dem Händler Umsatz. Vom Imageschaden ganz zu schweigen. Aus diesem Grund wird kaum ein größeres Webprojekt ohne einen Hot- oder Cold-Stand-By Datenbankserver auskommen.

Warum langt eine Datenbanksicherung nicht?
Im Artikel „MySQL Datenbank oder einzelne Tabelle kopieren“ habe ich eine Datenbanksicherung mit MySQL-Boardmitteln beschrieben. Prinzipiell ist dies schon mal ein guter Ansatz. Die Datenbank als Sicherung räumlich getrennt auf einem externen Datenträger aufzubewahren, kann im Bedarfsfall viel Arbeit ersparen. Löst aber unser Problem nicht. Wenn ein Datenbankserver ausfällt, muss sofort ein anderer Server die Arbeit übernehmen, so dass man keine Offline-Zeit hat und ich Ruhe sich um den ausgefallenen Server kümmern kann.

Was ist ein Cluster-System?
Cluster oder Clustering bezieht sich auf den Gedanken, die Verfügbarkeit von Systemen und Anwendungen über standardisierte und preiswerte Hardware zu erhöhen. Heißt also nichts anderes, als dass mehrere Standard-Rechner einen Verbund bilden, aber nach außen als ein Rechner oder Datenbank sich zu erkennen geben. Steigt die Last, teilt sich diese auf die einzelnen Maschinen (Cluster-Member oder Knoten genannt) auf. Fällt ein Cluster-Member aus, steigt die Last der verbleibenden Member, aber das System (Datenbank) bleibt nach außen verfügbar.

Was ist Hot-Stand-By, was ist Cold-Stand-By und was ist der Unterschied?
Ein Cluster ist der Königsweg, keine Frage. Je nach Projekt und Verfügbarkeit kann man sich auch mit einer Hot- oder Cold-Stand-By-Lösung behelfen. Beide Lösungen setzen auf einen zweiten Server auf. Während bei einer Hot-Stand-By-Lösung der zweite Server ständig in Betrieb ist und die Daten mit dem Master-Server ständig repliziert (also die Daten in Realzeit aktuell hält), ist der zweite Server bei einer Cold-Stand-By-Lösung aus. Erst im Bedarfsfall wird der zweite Server eingeschaltet und auf den aktuellen Stand gebracht (einige Lösungen gleichen sich zu definierten Zeitpunkten automatisiert ab). Beiden Lösungen gemein ist, dass die eigentliche Arbeit der Master-Server macht, der Slave-Server nur bei Ausfall des Masters ins Spiel kommt.

MySQL-Replikation, Überlegungen zur Hochverfügbarkeit
In diesem Artikel möchten wir folgenden Fall beschreiben: Wir haben eine Master-MySQL-Datenbank sowie mehrere Slave-Datenbanken. Die Aufbereitung der Daten auf dem Master-Server ist so aufwändig und CPU-hungrig, dass zu mehreren Zeitpunkten untertags ein Webzugriff auf die Datenbank nicht oder nur schwer möglich ist. Aus diesem Grund soll die fertige Datenbank auf einen Slave-Datenbank-Server gespiegelt werden; der Zugriff der Webfrontend-Server soll nur auf die gespiegelten Slave-Server erfolgen. Somit kann bei der Datenbankaufbereitung der Master-Server seine volle Leistung nutzen und kein Webzugriff stört ihn. Nachteil dieser Lösung: Die Daten der Slave-Server hinken in der Aktualität dem Master-Server hinterher, was aber in unserer Lösung akzeptiert werden kann.
Ferner muss sichergestellt werden, dass die Berechnungen durch die Replikation nicht noch von den Slave-Servern durchgeführt wird. Diese sollen nur das fertige Ergebnis replizieren. Dies kann durch die Einschränkungen der Replikation aus Datenbanken und Tabellen sichergestellt werden.
Wir replizieren also die Datenbank auf mehrere Slave-Server. Jeder Slave-Server ist zugleich ein Webfrontend-Server. Per Network-Load-Balancing (NLB) werden die Web-Besucher auf die einzelnen Webfrontend-Server, also auf unsere MySQL-Slave-Server, verwiesen.

Im nächsten Teil („MySQL Replikation (Master/Slave) einrichten“ #######) beschäftigen wir uns nach soviel Vorüberlegung mit der eigentlichen Einrichtung der MySQL-Replikation.

MySQL-Collation latin1_german1_ci oder latin1_german2_ci?

Die Collation oder Kollation in einem SQL-Server wird häufig mit dem verwendeten Zeichensatz verwechselt. Naheliegend, dass bei der Bezeichnung „latin1_german“ oder „utf-8“ sofort an den zugrundeliegenden Zeichensatz gedacht wird. Zumal der Gedanke keineswegs so abwegig ist, worauf ich später noch zurückkomme.

Was bestimmt die Collation in der MySQL-Datenbank?
Collations bestimmen die Sortierreihenfolgen eines SQL-Servers, beispielsweise in ORDER BY-Abfragen. Wenn eine SQL-Abfrage Textfelder auf- oder absteigend sortieren soll, ist es wichtig zu wissen, wie beispielsweise deutsche Umlaute behandelt werden sollen. Dies wird durch die verwendete Collation festgelegt.

Für den deutschen Sprachraum sind zwei Collations in MySQL vorhanden: „latin1_german1_ci“ und „latin1_german2_ci“. Diese basieren auf DIN-1 und DIN-2-Normen (Deutsches Institut für Normung) und werden gerne im Sprachgebrauch mit „Wörterbuchsortierung“ (DIN-1) und „Telefonbuchsortierung“ (DIN-2) bezeichnet. Vereinfacht kann gesagt werden, dass die „Wörterbuchsortierung“ („latin1_german1_ci“) einen Deutschen Umlaute wie beispielsweise das „Ä“ als „A“, das „Ö“ als „O“, das „Ü“ als „U“ und das „ß“ als „s“ bei der Sortierung behandelt. Die „Telefonbuchsortierung“ („latin1_german2_ci“) setzt das „Ä“ als „AE“, das „Ö“ als „OE“ und das „Ü“ als „UE“ für die Sortierung um. Weitere Informationen über die Collation und deren Unterschiede können im MySQL-Referenzhandbuch nachgelesen werden.

Warum verwendet MySQL per Standard die Collation „latin1_swedish_ci“?
Eine gute Frage, auf die ich auch keine erklärende Antwort habe. Je nach Datenbank ist die Angabe der Collation auch relativ unwichtig (eben nur wichtig, wenn per „ORDER BY text“ sortiert werden soll) und muss nur bei Bedarf geändert werden. In der my.ini kann die Collation für neu zu erstellende Tabellen geändert werden:

[mysqld]
default-character-set=latin1
default-collation=latin1_german1_ci

[mysql]
default-character-set=latin1
default-collation=latin1_german1_ci

Bereits bestehende Tabellen werden von dieser Änderung nicht erfasst. Wer „phpmysql“ auf seinem Datenbankserver installiert hat, kann die Collation mit wenigen Mausklicks je Tabelle ändern.

Ändern der Collation per Command-Line:
In die MySQL-Command möglichst per SSH einloggen:

mysql -h [LOCALHOST] -u [USER] – p [PASSWORD]

ALTER DATABASE db_ Tabellen_Name
CHARACTER SET [charset_name]
COLLATE [collation_name]

Also beispielsweise:

ALTER TABLE Tabellen_Name CHARACTER SET utf8 COLLATE utf8_general_ci;

Weitere Informationen über das Ändern der Collation im MySQL können direkt aus dem MySQL-Referenzhandbuch übernommen werden.

Ist die Collation „utf8_general_ci“ eine gute Wahl?
Mit einem Wort: Ja. UTF-8 ist sozusagen die „Eierlegende-Wollmilch-Sau“. Ich persönlich hinterlege die Collation immer als Standard und hatte bisher noch nie das Bedürfniss, diese dann noch einmal in „latin1_german1_ci“ oder „latin1_german2_ci“ zu ändern. Im Hinterkopf sollte man einfach behalten, dass die Sortierung der Umlaute UTF-8 nicht unbedingt so erfolgt, wie man dies vielleicht erwartet; bei Bedarf kann man dies jederzeit durch die Änderung der Collation ändern.
Und nun die Preisfrage: Wie genau sortiert die Collation „utf8_general_ci“? Einfach eine kleine Tabelle mit ein paar Begriffen inklusive deutschen Umlauten aufbauen und ausprobieren!

MySQL Datenbank oder einzelne Tabelle kopieren

Neben der Notwendigkeit, eine Datenbank ab und an zu sichern, kann auch das Problem auftauchen, dass man eine einzelne Tabelle unter MySQL kopieren muss. Zur automatisierten Vollsicherung empfehle ich MySQLDumper, dem Standardtool, das kaum Wünsche offenlässt.
Für eine gelegentliche Sicherung ist das Kommandozeilen-Tool „mysqldump“ zu empfehlen, das neben einer kompletten Datenbank auch einzelne Tabellen sichern und somit auch kopieren kann.

Hinweis (zwei Programme, nahezu gleicher Name)
Mysqldumper: PHP-Skriptsammlung zur automatisierten Sicherung/Wiederherstellung von MySQL Datenbanken auf einem Webserver
Mysqldump: MySQL-Tool zur Datensicherung (Kommandozeile)

So sichert man eine Datenbank unter MySQL
Das Tool „mysqldump“ ist in der Windows-Installation beispielsweise unter „C:ProgrammeMySQLMySQL Workbench 5.2 CE“ vorhanden. Da es sich bei mysqldump um ein Kommandozeilenprogramm handelt, sollte via „cmd“ in das Verzeichnis gewechselt und das Programm mit entsprechenden Parametern aufgerufen werden. Ein Doppelklick per Windows-Explorer bringt mangels Übergabeparameter und GUI nichts.
Unter Linux langt der Aufruf „mysqldump“ unter der Shell bzw. mit einer Putty-Verbindung.

Um eine Sicherung durchzuführen, ist das Tool wie folgt aufzurufen:

mysqldump  -hlocalhost  -uUser  -pPasswort [ZuSicherndeDatenbank] > datensicherung.sql

Nur einzelne Tabelle sichern:
mysqldump  -hlocalhost  -uUser  -pPasswort [ZuSicherndeDatenbank] [Tabellenname] > datensicherung.sql

Erklärung:
-h: Hostname, beispielsweise „localhost“
-u: Username
-p: Passwort
[ZuSicherndeDatenbank]: Name der Datenbank
[Tabellenname]: optional

Mysqldump sichert im obigen Beispiel die Datenbank [ZuSicherndeDatenbank] im File „datensicherung.sql“. Dieses File wird im aktuellen Verzeichnis geschrieben.

Rücksicherung der MySQL-Sicherung
Die Rücksicherung erfolgt nicht über mysqldump, sondern über mysql selbst. Wichtig ist, dass die Datenbank auf dem Zielsystem bereits bestehen muss. Die im Sicherungsfile enthaltenen Tabellen und Views dürfen jedoch auf dem Zielsystem nicht enthalten sein.

mysql -hlocalhost -uUser -pPasswort [ExisitierendeDatenbank]  < datensicherung.sql

Dieser Weg eignet sich auch hervorragend, um eine Datenbank über Servergrenzen hinweg zu sichern bzw. zu kopieren, da das Ausgabefile („datensicherung.sql“) hierfür einfach auf den Zielhost kopiert und importiert werden muss.

Kopieren einer einzelnen Tabelle unter MySQL innerhalb einer Datenbank
Wie wir oben gesehen haben, können wir per mysqldump auch einzelne Tabellen sichern. Um eine Tabelle zu kopieren, sichern wir hierfür via mysqldump die Tabelle in ein File („datensicherung.sql“). Dann ändern wir den Tabellenamen der Quelltabelle ab (zum Beispiel „tabelle_old“), denn beim Kopieren (richtigerweise: Rücksichern“) wird die Tabelle mit dem Originalnamen neu angelegt. Dies würde zu einem Fehler führen, wenn die Tabelle bereits existent ist.
Per „mysql“ wird das File importiert und wir haben nach erfolgreicher Ausführung eine Kopie der Tabelle in der Datenbank.

Warum nutzen wir nicht phpmyadmin?
Wer „phpmyadmin“ auf seinem Server installiert hat, kann natürlich eine Tabelle skripten, ggfls das Skript anpassen (neuer Tabellenname) und ausführen. Jedoch ist phpmyadmin nicht auf jedem Host vorhanden oder soll aus Sicherheitsgründen dort installiert sein. Wer große Tabellen oder Datenbanken kopieren oder sichern möchte, sollte die Kommandozeile via „mysqldump“ nutzen und nicht per Skript unter phpmysql eine Kopie anfertigen.
UPDATE: Wer phpmyadmin auf seinem MySQL-Server installiert hat, kann unter „Optionen“ auch große Tabellen kopieren.

MyISAM-Tabellen-Locks in Cache abfangen und nacharbeiten

Im Artikel „MySQL MyISAM: Performance-Probleme auf der Spur“ sind wir dem Problem der gesperrten MyISAM-Tabellen auf die Spur gekommen. Eine einfache Möglichkeit, die SQL-Locks mittels Timeout zu umgehen und letztlich auch zu protokollieren, haben wir ausprobiert. Im Artikel „Einfacher MySQL Fehler-Cache für MyISAM Lock Fehler“ haben wir mittels Textfile einen Fehlercache gebaut, so dass wir die fehlgeschlagenen Befehle nachholen können. Da dies nach wie vor nicht unseren Bedürfnissen genügt, werden wir heute einen automatisierten Cache auf Datenbankebene erstellen. Auf Datenbankebene? Ja, denn wenn ein Insert oder Update auf eine MyISAM-Tabelle gesperrt ist, bedeutet dies ja nicht, dass der SQL-Server nicht in eine andere Tabelle speichern kann. Und dieses verhalten machen wir uns heute zu Nutze.
In unserem C#-Programm haben wir aktuell folgende Update-Funktion:

string sSQL = „“;
try
{
using (MySqlConnection cn = new MySqlConnection(sConnection))
{
//Update-Befehl, der fehlschlagen kann
sSQL = @“UPDATE … „;

try
{
cn.Open();
using (MySqlCommand cmd = new MySqlCommand(sSQL, cn))
{
cmd.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
logfile(„MySQL FEHLER: “ + ex.Message);
MySQL_Error_logfile(sSQL);
}
}
}
catch (Exception ex)
{
logfile(„FEHLER: “ + ex.Message);
}

Im Fehlerfall schreiben wir ein Logfile; den eigentlichen SQL-Befehl schreiben in der Funktion „MySQL_Error_logfile“ in ein Textfile.

private static void MySQL_Error_logfile(string sText)
{
string sLogfilePfad = „logs\“;
try
{
//Abschliessendes Semikolon
if (!sText.EndsWith(„;“)) sText += „;“;

//Pfad hinzufügen
string sFile = @sLogfilePfad + „MySQL_“ + DateTime.Now.ToString(„yyyyMMdd“) + „.txt“;

//Erzeuge die Datei
FileStream outStream = new FileStream(sFile, FileMode.Append);
StreamWriter myWriter = new StreamWriter(outStream, System.Text.Encoding.Default);
myWriter.WriteLine(sText);
myWriter.Close();
}
catch (Exception ex)
{
Console.WriteLine(„FEHLER in MySQL-logfile: “ + ex.Message);
}
}

Die Funktion „MySQL_Error_logfile“ macht also nichts anderes, als ein Textfile zu schreiben. Es sorgt dafür, dass jeder SQL-Befehl ein abschließendes Semikolon hat für die Ausführung mehrerer Befehle hintereinander. Zudem erstellt es ein neues Logfile, sollte noch keines existieren. Um die bestehenden Programme möglichst wenig ändern zu müssen, wird der zu programmierende Cache auf der Basis der Funktion „MySQL_Error_logfile“ realisiert, da bereits jeder Task im Fehlerfall bereits hier seine Daten abliefert.

Wir legen also eine weitere Tabelle an. Ich habe sie „error_cache“ genannt. Es würde bereits eine Spalte genügen; ich persönlich bin jedoch ein Freund von eindeutigen ID-Feldern in einer Tabelle. Da ich MySQL 5.5.x nutze, habe ich die Standard-Storage-Engine „InnoDB“ für diese Tabelle nicht geändert.

DROP TABLE IF EXISTS `products`.`error_cache`;
CREATE TABLE  `products`.`error_cache` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`befehl` text NOT NULL,
`fehler` tinyint(3) unsigned NOT NULL DEFAULT ‚0‘,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Hier die modifzierte Funktion “MySQL_Error_logfile”. Diese schreibt nun einen MySQL-Fehler in den Cache. Die Hilfsfunktion „SQLEscape“ maskiert vorhandene Hochkommas.

private static void MySQL_Error_logfile(string sSQLFehler)
{
string sSQL = „“;
try
{
//Schreiben des fehlheschlagenen Befehls in die MySQL-Tabelle
using (MySqlConnection cn = new MySqlConnection(sConnection))
{
//Update-Befehl, der fehlschlagen ist
//Hochkommas müssen maskiert werden
sSQL = @“INSERT into error_cache (befehl) VALUES (‚“ + SQLEscape(sSQLFehler) + „‚);“;
try
{
cn.Open();
using (MySqlCommand cmd = new MySqlCommand(sSQL, cn))
{
cmd.ExecuteNonQuery();
}
}
catch
{
//Sollte beim Schreiben ein Fehler auftreten,
//schreiben wir ihn in ein Logfile
logfile(„MySQL FEHLER beim Schreiben in Cache: “ + sSQL);
}
}
}
catch (Exception ex)
{
Console.WriteLine(„FEHLER in MySQL-logfile: “ + ex.Message);
}
}

public static string SQLEscape(string sValue)
{
// SQL Encoding: r, n, x00, x1a, Backslash, einfache und doppelte Hochkommas
//benötigt: using System.Text.RegularExpressions;
if (sValue == null) return null;
else return Regex.Replace(sValue, @“[rnx00x1a\'““]“, @“$0″);
}

Doch damit ist es noch nicht getan. Denn uns hilft es wenig, wenn die MySQL-Fehler in einer Tabelle auflaufen; sie müssen auch nach dem Tabellen-Lock wieder abgearbeitet werden. Hierzu schreiben wir zwei weitere Funktionen:

private static void Clear_MySQL_Error_Cache()
{
//Hole die fehlerhaften Befehle
ArrayList list = GetErrorCache();
string sSQL=““;

if (list.Count > 0)
{
//Es sind Fehler vorhanden
foreach (ArrayList buffer in list)
{
using (MySqlConnection conn = new MySqlConnection(sConnection))
{
try
{
//Arbeite nun den SQL-Befehl ab
conn.Open();

//der fehlgeschlagene Befehl aus der Datenbank
sSQL= buffer[1].ToString();

using (MySqlCommand cmd = new MySqlCommand(sSQL, conn))
{
cmd.ExecuteNonQuery();
}

//Lösche nun den Eintrag
conn.Close();
conn.Open();
sSQL = „DELETE FROM error_cache WHERE id=“ + buffer[0].ToString();
using (MySqlCommand cmd = new MySqlCommand(sSQL, conn))
{
cmd.ExecuteNonQuery();
}
}
catch
{
//Der Befehl hat wieder nicht geklappt. Erhöhe den Fehlercount
//um Endlosschleife zu verhindern
conn.Close();
conn.Open();
sSQL = „UPDATE error_cache SET fehler=fehler+1 WHERE id=“ + buffer[0].ToString();
using (MySqlCommand cmd = new MySqlCommand(sSQL, conn))
{
cmd.ExecuteNonQuery();
}
}
}
}

}
}

private static ArrayList GetErrorCache()
{
ArrayList list = new ArrayList();
try
{
using (MySqlConnection conn = new MySqlConnection(sConnection))
{
MySqlDataReader rdr = null;
conn.Open();

//Fehleranzahl hier maximal 3
string sSQL = „SELECT id, befehl FROM error_cache WHERE fehler < 3 ORDER BY fehler“;
using (MySqlCommand cmd = new MySqlCommand(sSQL, conn))
{
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
ArrayList buffer = new ArrayList();
buffer.Add(rdr[„id“].ToString());
buffer.Add(rdr[„Befehl“].ToString());

//Der Rückgabe ArrayList hinzufügen
list.Add(buffer);
}
}
}
}
}
catch { }
return list;
}

Aufzurufen ist die Cache-Abarbeitung nun mit „Clear_MySQL_Error_Cache();“. Die Funktion holt sich durch die Hilfsfunktion „GetErrorCache()“ alle Befehle aus dem Cache, die eine Fehleranzahl < 3 besitzen. Somit werden alle Befehle maximal dreimal probiert. Es ist davon auszugehen, dass ein Befehl, der dreimal als fehlerhaft vom SQL-Server zurückgegeben wurde, einen Fehler im Befehl selbst beinhaltet. Eine Verbesserung wäre hier bereits beim Schreiben des Befehles in den Cache sicherzustellen, dass nur Befehle in den Cache gelangen, die aufgrund eines Lock-Timeout entstehen.
Ich persönlich bevorzuge diese Lösung, da ich nun an zentraler Stelle sehe, ob es auch fehlerhafte SQL-Statements aus den Jobs gibt.
Wenn die die Hilfsfunktion „GetErrorCache()“ der Funktion „Clear_MySQL_Error_Cache()“ Werte zurückgibt, arbeitet diese alle Werte hintereinander ab. Wenn kein anderer Task die Tabellen blockiert, werden alle Befehle nacheinander abgearbeitet. Wenn weiterhin ein Lock vorhanden ist, schlägt das SQL-Statement erneut fehl, der Fehler-Counter wird erhöht.

Bewertung dieses Vorschlages
Ich bin mir bewusst, dass dieses Vorgehen nicht optimal ist. Aber es erfüllt seinen Zweck. Der C#-Code kann noch weiter optimiert werden. Je nach Anwendungsfall ist auch eine Datenbank-Lösung vorzuziehen. Die Umstellung der Tabelle in eine InnoDB-Tabelle werden wird zu einem späteren Zeitpunkt beschreiten.
Wer sich noch weiter mit einem Tabellen-Lock oder Commit beschäftigen möchte, dem empfehle ich die ausführliche Dokumentation unter mysql.com.

Alle Artikel aus dieser Serie
MySQL MyISAM: Performance-Probleme auf der Spur
Einfacher MySQL Fehler-Cache für MyISAM Lock Fehler
MyISAM-Tabellen-Locks in Cache abfangen und nacharbeiten

Einfacher MySQL Fehler-Cache für MyISAM Lock Fehler

Im Artikel „MySQL MyISAM: Performance-Probleme auf der Spur“ haben wir festgestellt, dass MySQL MyISAM-Tabellen bei einem Schreibprozess die ganze Tabelle während des Schreibzuganges sperrt. Weitere Schreibprozesse werden in eine Warteschlange eingereiht. Wenn die vorangingen Schreibprozesse jedoch länger als der Timeout des hintenanstehenden Schreibprozesses dauern, erfolgt ein Abbruch (Fehler) des Prozesses.
Im hier untersuchten Projekt entstehen so rund 40 bis 50 Insert und Update-Fehler pro Tag. Alle Jobs sind in C# programmiert und schreiben ein ausführliches Logfile. Aktuell werden einmal am Tag die Logfiles geprüft und die fehlgeschlagenen SQL-Befehle, die im Logfile geschrieben sind, manuell nachgeholt. Eine unbefriedigende Lösung. Der nächste Schritt bestand darin, ein eigenes Logfile nur mit den fehlgeschlagenen Befehlen zu schreiben. Dies kann, wenn der MySQL-Server nicht ausgelastet ist, schnell nachgeholt werden. Da die Datenbankanwendung nur begrenzt auf Realtimedaten angewiesen ist (eine Verzögerung von einer Stunde ist hier akzeptabel), kann dies eine Lösung sein. Jedoch muss dies noch automatisiert werden. Eine weitere Möglichkeit, die Tabellen auf InnoDB umzustellen, werden wir in einem weiteren Schritt untersuchen.

Erster Lösungsansatz: Ein eigener MySQL-Fehler-Cache
Im vorliegenden Projekt bauen keine mehrere Insert und Updates nacheinander auf. Daher können einige fehlgeschlagene Insert oder Update-Befehle nachgeholt werden. In den vorliegenden C#-Programmen wurde jeder SQL-Zugriff mittels try-catch angefangen. Im Catch-Block schreiben wir ein zentrales Logfile rein mit den fehlgeschlagenen SQL-Befehlen. Diese Befehle können dann manuell nachgeholt werden:

string sSQL = "";
try
{
  using (MySqlConnection cn = new MySqlConnection(sConnection))
  {
     //Update-Befehl, der fehlschlagen kann
     sSQL = @"UPDATE [...];
     try
     {
         cn.Open();
         using (MySqlCommand cmd = new MySqlCommand(sSQL, cn))
         {
              cmd.ExecuteNonQuery();
          }
      }
      catch (MySqlException ex)
      {
         logfile("MySQL FEHLER: " + ex.Message);
         MySQL_Error_logfile(sSQL);
       }
     }
  }
catch (Exception ex)
{
   logfile("FEHLER: " + ex.Message);
}

Im obenstehenden C#-Code rufen wir im Fehlerfall die Funktion „logfile“ mit der SQL-Fehlermeldung auf. Die Funktion „MySQL_Error_logfile“ loggt zentral die fehlgeschlagenen MySQL-Befehle.

Zweiter Evolutionsschritt: MySQL_Error_logfile automatisch auslesen und abarbeiten
Da wir sicherlich alle keine Freunde von manuellem Eingreifen und Kontrolle sind, muss eine automatisierte Lösung gefunden werden. Also ist der folgende Schritt denkbar. Wir schreiben wie oben ein einzelnes Error-Logfile, auf das mehrere Tasks Zugriff haben. Dabei können ebenfalls Zugriffsprobleme auftauchen, die diese Lösung im Stadium des Gedankenexperimentes sterben lassen. Doch ich möchte es weiter ausführen.
Zu gegebener Zeit, spätestens am Ende eines Tasks, schaut dieser nach, ob sich ein „MySQL_Error_logfile“ am definierten Platz befindet. Wenn ja, liest dieser das gesamte Logfile in ein Array oder eine ArrayList ein. Wir machen uns den Vorteil der Zeilen zu nutze; jeder fehlgeschlagene SQL-Befehl ist in einer eigenen Zeile geschrieben und ist so schnell und einfach einzulesen.
Wenn das Logfile gelesen ist, wird es sofort gelöscht. So minimieren wir die Zugriffszeit. Wenn ein weiterer Task einen Fehler schreiben will, erstellt er automatisch ein neues Logfile.
Wir haben nun alle fehlgeschlagenen SQL-Befehle in einer ArrayList. Nun arbeiten wir in einer Schleife alle Befehle ab. Wenn der SQL-Server keine Locks auf die betroffenen Tabellen hat, werden die Befehle nun nachgeholt. Kommt es erneut zu einem Fehler, schreiben wir diese erneut in ein „MySQL_Error_logfile“, ein weiterer Task wird sich dann darum kümmern.

Im dritten Evolutionsschritt werden wir die SQL-Fehler in einer SQL-Datenbank schreiben? Was komisch klingt, verspricht spannend zu werden … Weiter: MyISAM-Tabellen-Locks in Cache abfangen und nacharbeiten

Alle Artikel aus dieser Serie
MySQL MyISAM: Performance-Probleme auf der Spur
Einfacher MySQL Fehler-Cache für MyISAM Lock Fehler
MyISAM-Tabellen-Locks in Cache abfangen und nacharbeiten

MySQL MyISAM: Performance-Probleme auf der Spur

Welche MySQL-Storage-Engine ist besser? MyISAM oder InnoDB? Eine oft gestellte Frage, auf die es keine eindeutige Lösung gibt. MyISAM wird oft als die modernere Engine angesehen, bis zur Version 5.4 war MyISAM auch die Standard-Engine von MySQL; nun ist die oftmals als veraltet beschimpfte InnoDB-Engine die Standard-Engine. Inzwischen haben die Entwickler von InnoDB viele Nachteile ausgebügelt. Aber auch ohne die neue Version der InnoDB-Engine lohnt sich vielleicht ein Umstieg. Einen Fall werde ich hier untersuchen.

Die vorliegende Datenbank ist über 5 GB groß. Sie gehört zu einem umfangreichen Projekt, zu dem mehrere Server, einige Clients und ein öffentliches Webprojekt gehören. Wir untersuchen zuerst einen reinen Datenbankserver im lokalen Netzwerk. MyISAM wurde am Anfang gewählt weil es einerseits bei der Anlage der Datenbank Standard war, andererseits der Fulltext-Index damals nur unter MyISAM möglich war. Inzwischen ist der Fulltext-Index auch unter InnoDB möglich.

Probleme mit der MyISAM-Tabelle
Wer MyISAM-Datenbanken betreibt, die einen größeren Umfang angenommen haben, wird von einer Tabellen-Beschädigung nicht verschont bleiben. Mittels des MySQL-Befehls „CHECK TABLE“ kann die Tabelle jederzeit geprüft, und mit dem Befehl „REPAIR TABLE“ auch wieder repariert werden. Beides kann in der MySQL-Webseite ausführlich nachgelesen werden. Eine korrupte MyISAM-Tabelle ist kein Problem; ärgerlich ist dies dennoch. Zumal das Reparieren der Tabelle je nach Tabellengröße mehrere Stunden in Anspruch nehmen kann. So lange ist die Datenbanktabelle offline.

Performance-Probleme durch gesperrte MyISAM-Tabellen
Fehlermeldungen wie „Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.“ beim Insert oder Update auf MyISAM-Tabellen kommen daher, da die Storage-Engine MyISAM bei Zugriff auf die Tabelle nicht die betreffenden Datensätze sperrt, wie die Storage-Engine InnoDB, sondern gleich die komplette Tabelle sperrt. Greifen mehrere Tasks gleichzeitig speichernd auf eine MyISAM-Tabelle zu, kann es zu einem Timeout kommen. Im hier zu untersuchenden Fall sind mehrere Jobs vorhanden, die leider einige Stunden am Tag gleichzeitig schreibend auf bis zu zwei MyISAM-Tabellen zugreifen. So „kämpfen“ mehrere Tasks um Schreibrechte auf den Tabellen. Der erste gewinnt. Der Zweite wird in eine Warteschlange eingereiht und darf schreiben, wenn der erste Task abgearbeitet wurde. Doch oftmals wird die Schreibanforderung mittels Timeout beendet. Die erste Möglichkeit, dieses Problem zu umgehen, ist den Timeout zu erhöhen.

Ein MySQL-Connection-String sieht ungefähr wie folgt aus:
ConnectionString=data source=mysql;DATABASE=mydatabase;UID=myuser;PASSWORD=mypassword;Connection Timeout=60

Wir können also ein “Connection Timeout” mit angeben. In diesem Connection-String wurde der Timeout bereits auf 60 Sekunden erhöht (wenn dieser Parameter nicht mit angegeben wird, verwendet MySQL den Standard-Timeout).
Im vorliegenden Fall haben alle Tasks diesen Timeout. Dennoch kommt es mehrmals am Tag zu Schreibproblemen. Eine weitere Erhöhung des Timeouts (größer 60 Sekunden) kann nicht die Lösung sein.

Wir suchen also eine Lösung für unser Performance-Problem. Im nächsten Teil werden wir einen Cache ausprobieren: Einfacher MySQL Fehler-Cache für MyISAM Lock Fehler

Alle Artikel aus dieser Serie
MySQL MyISAM: Performance-Probleme auf der Spur
Einfacher MySQL Fehler-Cache für MyISAM Lock Fehler
MyISAM-Tabellen-Locks in Cache abfangen und nacharbeiten

MySQL Volltextsuche: Such-Wort Mindestlänge festlegen (ft_min_word_len=3)

Die MySQL-Volltextsuche ist bei MyISAM-Tabellen eine wirklich tolle Sache. Der spezielle Index „FULLTEXT“ ermöglicht es, mittels einfacher SQL-Abfrage textbezogene Ergebnisinhalte zu generieren, die mit einer LIKE-Abfrage nicht oder nur schwer zu erreichen wären.

MySQL-Fulltext Volltextsuche Beispiel
Nehmen wir einmal eine Suche in einem Shop an. Der Hersteller BIG stellt bekanntlich das Bobby Car. Eine Suche nach den Keywords „big“, „bobby“ und „car“ würde mit dem Link-SQL-Befehl folgendermaßen lauten:

SELECT * FROM produkte WHERE produktname LIKE ‚%BIG%‘ OR produktname LIKE ‚%bobby%‘ OR produktname LIKE ‚%car%‘

Die Abfrage ist sicherlich noch zu verbessern. Zeigt aber eines: Es werden auch Produkte in der Ergebnismenge auftauchen, die nicht im entferntesten  etwas mit unserem Spielzeug „Bobby Car“ zu tun haben.

Die MySQL Volltextsuche schafft da ein wenig „Linderung“. Durch die Abfrage MATCH AGAINST und dem Index FULLTEXT wird die Ergebnismenge durch eine interne Relevanzbewertung wesentlich verfeinert.

SELECT * FROM produkte
WHERE MATCH (produktname) AGAINST (‚big‘,’bobby‘,’car‘)

MySQL-Volltextsuche: Minimale Wortlänge von vier Zeichen
Jedoch werden wir durch das oben gewählte Beispiel auch dank der Volltextsuche keine optimalen Ergebnisse erhalten. MySQL nimmt per Standard nur Worte in den Index, die eine Wortlänge von mindestens vier Zeichen aufweisen. Bei unserer Abfrage nach ‚big‘,’bobby‘ und ‚car‘ wird letztendlich nu rein Wort, nämlich “bobby” als Suchbegriff gewählt. Die Begriffe „Big“ und „Car“ werden aufgrund der Wortlänge unterdrückt.

MySQL-Volltextsuche: ft_min_word_len=3
Aus Gründen der Performance betrachtet MySQL bei einer Volltextsuche nur Begriffe ab einer Wortlänge von vier Zeichen. Überprüfen kann man dies mit folgender SQL-Abfrage:

SHOW VARIABLES LIKE ‚ft_min_word_len‘

Per Default ist hier der Wert “4” vorgegeben. Leider ist es aber oftmals unumgänglich, dass auch 3stellige Begriffe betrachtet werden. Dies ist nicht nur mit der deutschen Sprache ein Problem: Auch im Englischen sind (hauptsächlich) Abkürzungen relevante Suchbegriffe. So ist der Wunsch, auch 3-stellige Wörter mit in den Suchindex aufzunehmen, oftmals eine Bedingung für eine optimierte SQL-Abfrage.

HowTo: MySQL Volltext-Suche Min-Word ändern

  1. Vergewissern Sie sich mittels der Abfrage „SHOW VARIABLES LIKE ‚ft_min_word_len’“, dass die Wortlänge nicht Ihren Wünschen entspricht.
  2. Öffnen Sie die Datei „my.cnf“. Diese ist, abhängig von Ihrer Installation, beispielsweise im Ordner „/etc/mysql/“ zu finden.
  3. Navigieren Sie in den Abschnitt „[mysqld]“. Suchen Sie dort den Eintrag „ft_min_word_len“. Ändern Sie diesen auf „ft_min_word_len=3“, wenn Sie die minimale Wortlänge auf „3“ einstellen möchten (von einem geringeren Wert rate ich ab!). Wenn dieser Eintrag nicht vorhanden ist, tragen Sie diesen dort ein. Ist er mit einem Gatter „#“ versehen, entfernen Sie das Gatter. Speichern Sie die Datei.
  4. Damit die Änderung greift, muss der MySQL-Server neu gestartet werden.
  5. Im nächsten Schritt muss der Index neu aufgebaut werden, da der bisherige Index mit einer nicht mehr relevanten Wortlänge erstellt wurde. Am Einfachsten können Sie via phpmyadmin (sofern vorhanden) den Index löschen und neu erstellen. Der sicherlich bessere Weg geht über die MySQL-Konsole.
  6. Loggen Sie sich in die Konsole ein.  Geben Sie danach folgenden Befehl (tbl_name anpassen) ein: REPAIR TABLE tbl_name QUICK;
  7. Nachdem der Index neu aufgebaut wurde, sollte unsere Volltext-Abfrage nach nach ‚big‘,’bobby‘ und ‚car‘ neuere und vor allem treffsichere Werte anzeigen. Kontrollieren können Sie die minimale Wortlänge erneut über die Abfrage SHOW VARIABLES LIKE ‚ft_min_word_len‘

 

MySQL-Datenbanken und Tabellen über MySQL-Konsole verwalten

Die PHP-Anwendung phpmyadmin ist heute bei fast jedem Web inklusive und bietet uns eine einfache Möglichkeit, MySQL-Datenbanken zu administrieren. Doch nicht immer ist phpmyadmin verfügbar und nicht alles ist über diese PHP-Skriptsammlung änderbar. Und natürlich: Richtige SQL-Freaks möchten es „nativ“ machen ….
Die folgende Beschreibung ist für MySQL-Datenbanken unter Linux geschrieben. Sollten Sie einen MySQL-Datenbankserver unter Windows betreiben, können einzelne Schritte anweichen. Grundlegend ist jedoch die Bedienung gleich.

MySQL Konsolen-Verbindung aufbauen
Sie verfügen bereits über eine Konsolenverbindung (beispielsweise über putty) zu Ihrem Server. Nun müssen Sie eine Verbindung zu Ihrem MySQL-Server aufbauen. Dies geschieht einfach über den Befehl „mysql“ sowie der Angabe einiger Parameter:

[root@srv-mysql ~]$ mysql --user=BENUTZERNAME --password=MEIN_PASSWORT

Nach Eingabe des korrekten Benutzers und Passwortes sollte Sie die MySQL-Konsole mit einem freundlichen „srv-mysql>“ empfangen. Die Konsole wartet nun auf weitere Befehle.

Verbindung beenden
Mit dem Befehl „quit“ beenden Sie die Sitzung:

srv-mysql> QUIT
Bye

MySQL Datenbank erstellen
Nun erstellen wir eine Datenbank mit den Namen „db_name“:

srv-mysql> CREATE DATABASE db_name;

Anzeige der vorhandenen Datenbanken
Um alle Datenbanken aufzulisten, geben Sie einfachd en Befehl “SHOW DATABASES” ein:

srv-mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_name            |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

Tabellen einer Datenbank auflisten
Wenn Sie die Tabellen einer Datenbank auflisten möchten, geben Sie einfach folgenden Befehl ein:

srv-mysql> SHOW TABLES FROM db_name;
+-------------------+
| Tables_in_db_name |
+-------------------+
| tbl_name          |
+-------------------+
1 row in set (0.00 sec)

Datenbank auswählen
Um mit einer Datenbank zu arbeiten, müssen Sie diese auswählen (eine Ausnahme ist die Anzeige der Tabellen im obigen Beispiel). Mit dem Befehl „use“ spezifizieren Sie eine Datenbank für die weitere Verwendung:

srv-mysql> USE db_name;
Database changed

Datenbank löschen
Das Löschen einer Datenbank geht schnell von der Hand. Seien Sie also mit folgendem Befehl sehr vorsichtig!

srv-mysql> DROP DATABASE db_name;
Query OK, 1 row affected (0.03 sec)

Tabelle in einer MySQL-Datenbank erstellen
Um eine Tabelle in einer Datenbank zu erstellen, geben Sie den Befehl „CREATE TABLE“ ein. Sie leiten die Eingabe mit „CREATE TABLE tbl_name (“ [RETURN] ein. Danach geben Sie den Spaltennamen gefolgt von Parametern sowie den Datentyp der Spalte ein. Das folgende Beispiel zeigt die Anlage eine Tabelle mit drei Spalten:

srv-mysql> CREATE TABLE tbl_name (
    -> id int NOT NULL,
    -> spalte1 varchar(255),
    -> spalte2 tinyint()
    -> );
Query OK, 0 rows affected (0.01 sec)

MySQL-Tabelle löschen
Mittels „DROP TABLE“ können Sie schnell eine ganze Tabelle löschen. Auch hier gilt: Vorsicht!

srv-mysql> DROP TABLE tbl_name;
Query OK, 0 rows affected (0.00 sec)

Tabellen der aktiven Datenbank anzeigen
Wenn Sie die Datenbank mit „use“ bereits spezifiziert haben, können Sie die Tabellen der Datenbank einfach mit „SHOW TABLE“ anzeigen:

srv-mysql> SHOW TABLES;
+-------------------+
| Tables_in_db_name |
+-------------------+
| tbl_name          |
+-------------------+
1 row in set (0.00 sec)

Struktur der MySQL-Datenbank-Tabelle anzeigen
Um die Struktur der Tabelle anzuzeigen, wählen Sie den Befehl „SHOW FIELDS“ wie im folgenden Beispiel:

srv-mysql> SHOW FIELDS FROM tbl_name;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| spalte1 | varchar(255)     | YES  |     | NULL    |       |
| spalte2 | tinyint() | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Spalte in eine bestehende Tabelle einfügen
Mittels „ALTER TABLE“ und „ADD COLUMN“ können Sie eine Spalte in eine bestehende MySQL-Tabelle einfügen:

srv-mysql> ALTER TABLE tbl_name ADD COLUMN (spalte3 varchar(10));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Datentyp einer Spalte ändern
Wir haben im obigen Beispiel die „spalte3“ mit dem Datentyp „varchar(10)“ angelegt. Dies können wir natürlich wieder ändern:

srv-mysql> ALTER TABLE tbl_name MODIFY spalte3 varchar(25);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Spalte in einer MySQL-Tabelle löschen
Um eine Spalte in einer Tabelle zu löschen, wählen Sie den Befehl „DROP COLUMN“.

srv-mysql> ALTER TABLE tbl_name DROP COLUMN spalte3;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0