MySQL replication- defekte Tabellen?

[knebb]

Yohoo!

Ich habe zwischen zwei MySQL hosts eine Replikation aus Datensicherheitsgruenden installiert. Auf dem Replikationshost wird im Regelfall nichts abgefragt, lediglich das Backup laeuft darueber.

Master liegt auf einem Root Server (SLES9, mysql-4.0.18-32.28 ), der Slave ist ein CentOS 5 (mysql-server-5.0.22-2.1.0.1), der an den Master mit einer einfachen 1000/256 A-DSL angeschlossen ist. D.h. die Replikation erfolgt ueber 1000er DSL. Das geht, da auf der Kiste sehr wenig los ist.

Vom Slave wird dann per Skript und mysqldump das backup gezogen.

Jetzt passiert es aber immer wieder, dass genau eine Tabelle korrupt geht und ich durch mysqldump die folgende Fehlermeldung erhalte:

Code: Select all

mysqldump: mysqldump: Couldn't execute 'show create table `wikisearchindex`': Table './wikidb/wikisearchindex' is marked as crashed and should be repaired (145)

Der Aufrud selber sieht so aus:

Code: Select all

mysqldump --opt -h $HOST --skip-lock-tables -C  -u root -pXXXX --all-databases > mysql.dump

Sichere ich den Master direkt, bekomme ich keine Fehlermeldung, aber es dauert halt laenger.
Ein SHOW SLAVE STATUSg auf dem Slave ist aufgrund der langen Fehlermeldung nicht sinnvoll zu gebrauchen.

Jetzt meine Frage, warum dieser Fehler auf dem Slave kommt? Auf dem Master ist die Tabelle ja i.O. Und wie ich z.B. feststellen kann, ob die Replikation 100%ig laeuft? Und vor allem natuerlich, wie kann ich eine angeblich korrupte Datenbank auf dem Slave verhindern?

[Joe User]

Code: Select all

mysqldump --opt -h $HOST --skip-lock-tables -C  -u root -pXXXX --all-databases > mysql.dump

Ohne --skip-lock-tables sollten die korrupten Tabellen nicht auftreten.

[knebb]

Code: Select all

mysqldump --opt -h $HOST --skip-lock-tables -C  -u root -pXXXX --all-databases > mysql.dump

Ohne --skip-lock-tables sollten die korrupten Tabellen nicht auftreten.

Erklaerung? Hintergrund? Warum funktioniert das Backup von Master genau so problemlos, aber auf dem Slave nicht?

[knebb]

Erklaerung? Hintergrund? Warum funktioniert das Backup von Master genau so problemlos, aber auf dem Slave nicht?

Weil die Tabellen auf dem slave während der Replikation vom Master gelockt werden.
Beim Master trifft das so nicht zu.

Hmmm.....
Heisst das, dass ein Dump einer Slave-Datenbank nur ohne LOCK moeglich ist? Und wie bekomme ich dann einen atomaren, konsistenten Zustand? Wird das im Rahmen der Replikation abgedeckt, dass die Slave-DB IMMER konsistent ist?
[EDIT} Ach ja, dieser MySQL ist nicht ausschliesslich Slave, sondern hat auch ein paar Datenbanken, fuer die er alleine zustaendig ist (ohne Replik.). Was ist mit diesen DB? Muss ich jetzt jede DB einzeln dumpen, je nach Master/ Slave Verhaeltnis?

Und nochmal einer der initialen Fragen:

Und wie ich z.B. feststellen kann, ob die Replikation 100%ig laeuft?

[Joe User]

Heisst das, dass ein Dump einer Slave-Datenbank nur ohne LOCK moeglich ist?

Nein, im Gegenteil, es geht nur mit Locks oder gestopptem Slavethread.

Und wie bekomme ich dann einen atomaren, konsistenten Zustand?

Beim Dumpen sorgt --lock-all-tables dafür.

Wird das im Rahmen der Replikation abgedeckt, dass die Slave-DB IMMER konsistent ist?

Der Slave ist (mit leichtem Zeitversatz) immer so konsistent wie das Binlog des Masters.

[EDIT} Ach ja, dieser MySQL ist nicht ausschliesslich Slave, sondern hat auch ein paar Datenbanken, fuer die er alleine zustaendig ist (ohne Replik.). Was ist mit diesen DB? Muss ich jetzt jede DB einzeln dumpen, je nach Master/ Slave Verhaeltnis?

Du kannst die Datenbanken entweder einzelnd oder gemeinsam dumpen, wie auch ohne Replikation. Wichtig ist jedoch grundsätzlich --lock-all-tables beim Dumpen, egal ob eine Replikation im Spiel ist oder nicht.

Und wie ich z.B. feststellen kann, ob die Replikation 100%ig laeuft?

SHOW SLAVE STATUS;

[knebb]

Beim Dumpen sorgt --lock-all-tables dafür.

Sind dann nicht Schreibzugriffe fuer die Master waehren des Backups nicht moeglich? Ehm..meine, kann ein Client auf die DB schreiben, waehrend diese mit "--lock-all-tables" fuer den Dump gesperrt ist?

Und wie ich z.B. feststellen kann, ob die Replikation 100%ig laeuft?

SHOW SLAVE STATUS;

Ja, den Befehl kannte ich. Kann man die Ausgabe auch irgendwie lesbar formatieren? Wieviele Spalten braucht die? Das ist hier maechtig durcheinander, da kann ich nichts erkennen. Auch das "g" anstelle des ";" brachte nix.

[Joe User]

Ehm..meine, kann ein Client auf die DB schreiben, waehrend diese mit "--lock-all-tables" fuer den Dump gesperrt ist?

Nein, solange der Dump läuft, können keine Writes erfolgen und das ist gut so, da Writes während des Dumps zum inkostenten Backup und anderen Fehlern führt. Wenn Dir das Dumpen zu lang dauert, musst Du Snapshots mittels LVM2 fahren, siehe http://www.rootforum.org/forum/view ... hp?t=39481 und http://www.rootforum.org/forum/view ... hp?t=39333 und die anderen MySQL-Stickies von isotopp.

Kann man die Ausgabe auch irgendwie lesbar formatieren?

Code: Select all

SHOW SLAVE STATUSG

[knebb]

Nein, solange der Dump läuft, können keine Writes erfolgen und das ist gut so, da Writes während des Dumps zum inkostenten Backup und anderen Fehlern führt.

Ich haette erwartet, dass MySQl dafuer einen Mechanismus aehnlich ein Snapshot Funktion anbietet. So nach dem Motto: Das Backup sichert den (konsistenten) Snapshot, waehrend die Clients sorgenfrei alles aktualisieren/ lesen/ aendern koennen.
Nach (erneutem ) durchlesen von isotopps Artikel komme ich zu dem Schluss, dass mit MySQL unterbrechungsfreie Backups nicht moeglich sind. Richtig?
Na gut, mich stoert das fuer diese Anwendung wenig, aber ein Businesskritisches System sieht anders aus, oder?

Code: Select all

SHOW SLAVE STATUSG

Hrmpf, hatte was mit g im Kopf. Dass das natuerlich case sensitiv ist, daran habe ich nicht gedacht.

DANKE!

[Joe User]

Ich haette erwartet, dass MySQl dafuer einen Mechanismus aehnlich ein Snapshot Funktion anbietet. So nach dem Motto: Das Backup sichert den (konsistenten) Snapshot, waehrend die Clients sorgenfrei alles aktualisieren/ lesen/ aendern koennen.

Zum Schreiben geöffnete Daten, egal ob Datenbank oder andere Dateien, will man grundsätzlich nicht ins Backup aufnehmen, da man keinen Einfluss darauf hat, ob die alte, die neue oder gar eine kaputte Version im Backup landet. Mit einem (Write-)LOCK hingegen ist sichergestellt, dass die vollständige und zum Zeitpunkt des Backups aktuelle Version im Backup landet.

Nach (erneutem ) durchlesen von isotopps Artikel komme ich zu dem Schluss, dass mit MySQL unterbrechungsfreie Backups nicht moeglich sind. Richtig?
Na gut, mich stoert das fuer diese Anwendung wenig, aber ein Businesskritisches System sieht anders aus, oder?

Dafür gibt es ja die Replikation und gerade im durchdachten Business wird mindestens ein Slave exclusiv als reiner Slave, wie von isotopp beschrieben, betrieben.

[knebb]

Dafür gibt es ja die Replikation und gerade im durchdachten Business wird mindestens ein Slave exclusiv als reiner Slave, wie von isotopp beschrieben, betrieben.

Jau, recht haste. Dann macht das Ganze ja auch wieder Sinn. Danke nochmals fuer die Erleuchtung

[isotopp]

Jetzt passiert es aber immer wieder, dass genau eine Tabelle korrupt geht und ich durch mysqldump die folgende Fehlermeldung erhalte:

Code: Select all

mysqldump: mysqldump: Couldn't execute 'show create table `wikisearchindex`': Table './wikidb/wikisearchindex' is marked as crashed and should be repaired (145)

Deine my.cnf sollte

Code: Select all

myisam_recover = FORCE,BACKUP
myisam_repair_threads = 1

enthalten. Auf diese Weise wird MySQL die als crashed markierten Tabellen automatisch erkennen und reparieren.

Das Lock auf den Tabellen sorgt im übrigen nicht für den Table Crash, das ist Unsinn.

Ein Backup sollte konsistent sein - also genau einer Binlog-Position entsprechen. Es sollte außerdem genau dieser Binlog-Position zugeordnet sein.

Ein logisches Backup erzeugst Du mit "mysqldump", die Option "--master-data=2" impliziert --lock-all-tables und schreibt Dir außerdem die Binlog-Position mit ins Backup rein. Du solltest in MySQL auch "--routines --triggers" mit angeben. Wenn Du nur InnoDB-Tabellen hast, kannst Du "--single-transaction" mit angeben, wenn Du ein lockfreies Backup haben willst.

Ein physikalisches Backup erzeugst Du, indem Du "FLUSH TABLES WITH READ LOCK" auf einer Connection erzeugst und die Connection hältst. Du kannst dann datadir sichern oder mit LVM snapshotten. Erst danach darfst Du auf derselben Verbindung "UNLOCK TABLES" machen. Für LVM hat Lenz Grimmer dazu mylvmbackup geschrieben.

Das InnoDB-Backup, das mylvmbackup und die o.a. Methode erzeugen ist im übrigen nicht recovered, aber vollständig und konsistent, insbesondere bei innodb_flush_log_on_trx_commit = 1. Es ist aber so, daß InnoDB beim wiedereinspielen des physikalischen Backup erst einmal in die Recovery geht und das Redo Log abarbeitet. Das ist vollkommen normal und nicht schlimm - es dauert nur ein wenig Zeit.

[knebb]

Jetzt passiert es aber immer wieder, dass genau eine Tabelle korrupt geht und ich durch mysqldump die folgende Fehlermeldung erhalte:

Code: Select all

mysqldump: mysqldump: Couldn't execute 'show create table `wikisearchindex`': Table './wikidb/wikisearchindex' is marked as crashed and should be repaired (145)

Deine my.cnf sollte

Code: Select all

myisam_recover = FORCE,BACKUP
myisam_repair_threads = 1

enthalten. Auf diese Weise wird MySQL die als crashed markierten Tabellen automatisch erkennen und reparieren.

Hoert sich gut an, verstehe ich aber von der Logik her dann nicht ganz so. Setze ich diese Statements im Master, bringt das ja auf dem Slave nicht. Und der Master hat ja keine korrupten Tabellen.
Setze ich das auf dem Slave, repariert (schreibt) er Tabellen, fuer die er ja eigentlich nur lesend zustaendig ist

Das Lock auf den Tabellen sorgt im übrigen nicht für den Table Crash, das ist Unsinn.

Keine Ahnung. Aber woher sollen sie kommen? Aufgrund der langsamen (DSL1000) Leitung?

Ein logisches Backup erzeugst Du mit "mysqldump", die Option "--master-data=2" impliziert --lock-all-tables und schreibt Dir außerdem die Binlog-Position mit ins Backup rein.

Und damit kann ich den Slave sichern? Dachte, "--master-data" ist nur fuer den Master gedacht?

Du solltest in MySQL auch "--routines --triggers" mit angeben. Wenn Du nur InnoDB-Tabellen hast, kannst Du "--single-transaction" mit angeben, wenn Du ein lockfreies Backup haben willst.

Gilt das alles und das obige auch fuer einen gemischten Master/Slave Host?

Ein physikalisches Backup erzeugst Du, indem [...]

Ne, physikalisch will ich das nicht machen. Aber interessant ist das allemal mit den LVM Snapshots.

[isotopp]

Setze ich das auf dem Slave, repariert (schreibt) er Tabellen, fuer die er ja eigentlich nur lesend zustaendig ist :?:

Wieso glaubst Du, daß ein Slave nur lesen würde? Oder anders gefragt: Was glaubst Du, wie die Daten auf den Slave kommen?

Und damit kann ich den Slave sichern? Dachte, "--master-data" ist nur fuer den Master gedacht?

"master-data" notiert eine Binlog-Position, allerdings die des Slaves. Wenn Du einen Slave sicherst, tust Du in der Tat besser daran, den Output von "SHOW SLAVE STATUS" mit zu sichern. "mylvmbackup" sichert einfach beides.

[knebb]

Setze ich das auf dem Slave, repariert (schreibt) er Tabellen, fuer die er ja eigentlich nur lesend zustaendig ist

Wieso glaubst Du, daß ein Slave nur lesen würde? Oder anders gefragt: Was glaubst Du, wie die Daten auf den Slave kommen?

Man kann die Worte auch im Mund umdrehen, richtig.
Natuerlich "schreibt" er in seine Datenbank. Aber doch nicht selbststaendig. Und eine Reparatur einer Datenbank ist mehr als nur das zu tun, was ihm der Master befiehlt. Die Replikation soll ja einen einheitlichen Stand zwischen Master und Slave herstellen. Dieser Zustand besteht sicherlich nicht mehr, wenn der Slave Tabellen selbststaendig veraendert/ repariert.

[isotopp]

Setze ich das auf dem Slave, repariert (schreibt) er Tabellen, fuer die er ja eigentlich nur lesend zustaendig ist :?:

Wieso glaubst Du, daß ein Slave nur lesen würde? Oder anders gefragt: Was glaubst Du, wie die Daten auf den Slave kommen?

Man kann die Worte auch im Mund umdrehen, richtig. :o
Natuerlich "schreibt" er in seine Datenbank. Aber doch nicht selbststaendig.

Doch, genau das. Ein Master macht in einer Replikation nahezu gar nix. Er zeichnet seine Befehle im Binlog auf und er läßt Slaves bei sich einloggen. Mehr tut er nicht.

Der Slave dagegen ist ziemlich busy. Er startet zwei Threads: Der IO_THREAD connected sich an den Master und loggt sich ein. Er lädt mit dem Kommando "SHOW BINLOG EVENTS" das Binlog vom Master auf den Slave runter und packt es dort ins Relay Log um.

Der SQL_THREAD wiederum läuft auf dem Slave, liest das Relay-Log und die darin enthaltenen Kommandos (Du kannst sie Dir mit dem Kommando "mysqlbinlog" auf ein Relay-Logfile angewendet ansehen) und führt sie aus - ganz genau so als hättest Du sie auf einer Konsole eingegeben. Das bedeutet, daß der Slave ganz normale insert/update/delete/replace/optimize/analyze und so weiter Kommandos ausführt wie der Master auch.

Und eine Reparatur einer Datenbank ist mehr als nur das zu tun, was ihm der Master befiehlt. Die Replikation soll ja einen einheitlichen Stand zwischen Master und Slave herstellen.

Nein, das tut eine Replikation nicht. Es ist vielmehr Deine Aufgabe, diesen einheitlichen Zustand VOR dem Start der Replikation herzustellen. Etwa indem Du ein Vollbackup des Masters auf dem Slave einspielst und dem Slave dann die Binlog-Position mitteilst, die der Master hatte als das Vollbackup gemacht wurde.

Die Replikation nimmt diesen einheitlichen Zustand dann und wendet die Befehle aus dem Binlog darauf an. Das führt dazu, daß die Datenbestände auf dem Slave denselben Transformationen unterzogen werden wie auf dem Master, falls alles klappt. Mit einigen Befehlen ("INSERT INTO t (d) values (@@hostname)" etwa, oder "INSERT INTO t (d) values (sysdate())") klappt das nicht, weil diese Befehle auf dem Master und dem Slave unterschiedliche Ergebnisse liefern, wenn man sie ausführt. Dann divergieren Master und Slave über die Zeit (und man braucht ein Tool wie mysql-table-sync von Baron Schwartz, um das zu erkennen und zu korrigieren).

Dieser Zustand besteht sicherlich nicht mehr, wenn der Slave Tabellen selbststaendig veraendert/ repariert.

Der Slave ist für seine Tabellen selbst zuständig und muß sie auch selber reparieren. Sonst tut das nämlich keiner, und wenn sie kaputt sind, schlagen Schreibbefehle aus dem Binlog auf diese Tabellen fehl oder machen sie Tabellen noch mehr kaputt.

[knebb]

Ein Master macht in einer Replikation nahezu gar nix. Er zeichnet seine Befehle im Binlog auf und er läßt Slaves bei sich einloggen. Mehr tut er nicht.

Ja, da hast Du natuerlich recht. Mir ging/ geht es aber mehr um die Datenbankbestaende an sich. In dieser Hinsicht ist ein Slave ziemlich vom Master abhaengig. Und schreibt eben nicht selbststaendig neue Eintraege in eine Tabelle.

Das bedeutet, daß der Slave ganz normale insert/update/delete/replace/optimize/analyze und so weiter Kommandos ausführt wie der Master auch.

. Ok, soweit hatte ich das auch verstanden/ gesehen.

Die Replikation soll ja einen einheitlichen Stand zwischen Master und Slave herstellen.

Nein, das tut eine Replikation nicht.

Ok, auch hier falsche Wortrwahl von mir. Die Replikation soll sicherstellen, dass (von einem einheitlichen Stand ausgegangen) beide Datenbanken den gleichen Stand haben (von Ausnahmen wie den aufgefuehrten Statements mal abgesehen).

Es ist vielmehr Deine Aufgabe, diesen einheitlichen Zustand VOR dem Start der Replikation herzustellen.

Das ist klar und auch geschehen.

daß die Datenbestände auf dem Slave denselben Transformationen unterzogen werden wie auf dem Master, falls alles klappt.
[...]

Dieser Zustand besteht sicherlich nicht mehr, wenn der Slave Tabellen selbststaendig veraendert/ repariert.

[...]
Der Slave ist für seine Tabellen selbst zuständig und muß sie auch selber reparieren.

Da aber eine Reparatur einer Tabelle dazu fuehren kann, dass die Datenbestaende divergieren muss ich nach jeder Tabellenreparatur erneut eine manuelle Synchronistation durchfuehren, richtig? Demnach ist eine AUTO_REPAIR auf dem Slave eine ziemlich schlechte Idee, was?

Sonst tut das nämlich keiner, und wenn sie kaputt sind, schlagen Schreibbefehle aus dem Binlog auf diese Tabellen fehl oder machen sie Tabellen noch mehr kaputt.

Jau, auch logisch.

[isotopp]

Da aber eine Reparatur einer Tabelle dazu fuehren kann, dass die Datenbestaende divergieren muss ich nach jeder Tabellenreparatur erneut eine manuelle Synchronistation durchfuehren, richtig? Demnach ist eine AUTO_REPAIR auf dem Slave eine ziemlich schlechte Idee, was?

Das hängt davon ab, ob das FORCE wirksam wird und die Reparatur zu Zeilenverlust in der MYD führt oder nicht. Aber was läßt Dich glauben, daß das Schreiben in defekte Tabellen besser wäre oder nicht zu Datenverlust führen würde?

[kase]

Also eigentlich solltest du mit --single-transaction ein Backup vom Master machen können, ohne dass die anderen Connections geblockt werden.

[isotopp]

Also eigentlich solltest du mit --single-transaction ein Backup vom Master machen können, ohne dass die anderen Connections geblockt werden.

Aber wenn das ginge, hätte er keine gecrashten Tabellen. "--single-transaction" funktioniert nur, wenn alle Tabellen im Backup entweder transaktional oder read-only sind. Dann würde aber sowieso die Selbstreparatur von InnoDB wirksam werden, und das Crashed Table-Problem hätte sich erledigt.