MySQL – Recovery

Letztens bekam ich kurzzeitig einen Herzaussetzer, weil ich beim Restore des Datenbankbackups (LVM) bemerkte, dass doch die ib_logfiles nicht mit zurückgesichert wurden. Nachdem ich kurz nachgeschaut und fetsgestellt habe, dass die im Standardverzeichnis unter /var/lib/mysql herumdümpelten und nicht auf dem Logical Volume wo sie gesichert werden, wurde es erstmal finster ums Hirn. Hmm das passiert und zwar jeden Tag und wenn es solche Momente nicht gäbe, wäre ja alles irgendwie zu einfach …

Was tun, sprach Zeus?

Die Antwort findet sich in der MySQL Dokumentation. Allerdings für meine Begriffe völlig zu unrecht in „2 Nebensätzen“ abgehandelt und wenn es brennt findet man das sowieso nie. Also aus diesen Anlass hier ein paar hilfreiche Tipps, Tricks und Anwendungsbeispiele um gecrashte, falsch gesicherte und sonstwie misshandelte Tabellen oder MySQL Instanzen wiederherzustellen, aber der Reihe nach.

1. Der Windows Weg Neustart

Manchmal hilft der ja schon ….

mysqladmin -uUSER -pPASSWORD shutdown
mysql/bin/mysqld_safe &

oder

 /etc/init.d/mysql restart

2. Beschädigte MyISAM Tabellen

MyISAM Tabellen zu reparieren ist in den meisten Fällen relativ einfach zu bewerkstelligen. Um eine einzelne Tabelle zu reparieren, benutzt man

repair TABLENAME

Damit alle MyIAM Tabellen berücksichtigt werden:

mysqlcheck --all-databases -uUSER -pPASSWORD -r

Eine einfach Lösung die diese Vorgänge automatisiert, ist ein Eintrag in der /etc/my.cnf.

[mysqld]
 myisam-recover=backup,force

Dieser Eintrag automatisiert beim Start die Wiederherstellung beschädigter MyISAM Tabellen. Dabei bewirkt die Option backup, dass beim Wiederherstellungsprozeß Dateien tbl_name-datetime.bak angelegt werden. Force verhindert, dass die automatische Reperatur bei einem etwaigen Fehler abgebrochen wird.

2. Multiple Instanzen von MySQL

Was mir hin und wieder unterkommt, ist dass bei einem Restart der MySQL Instanz der Prozess sofort wieder im Nirvana verschwindet. Ein Blick in die Logs zeigt dann in der Regel so etwas ähnliches wie:

050615 20:40:43 [ERROR] Can't start server : Bild on unix socket: Permission denied
050615 20:40:43 [ERROR] Do you already have another mysqld server running on socket:/tmp/mysql.sock ?
050615 20:40:43 [ERROR] Aborting

Um alle laufenden MySQL Prozesse abzuschliessen führen wir folgendes aus:

mysqladmin -uUSER -pPASSWORD shutdown
killall mysql
killall mysqld

Nun kann die Instanz wieder ganz normal gestartet werden.

3. Geänderte InnoDB Optionen in der my.cnf

Folgende Optionen sollten niemals geändert werden, wenn wir InnoDB Tabellen in unsere Datenbank haben. Werden diese Werte geändert, führt es dazu, dass die InnoDB Engine abgeschaltet wird oder MySQL den Start verweigert. Siehe dazu 7. beschädigte InnoDB Tabellen.

datadir =
innodb_data_home_dir =
innodb_data_file_path =
innodb_log_group_home_dir =
innodb_log_files_in_group =
innodb_log_file_size =

4. Probleme mit den MySQL host Tabellen

Updates bzw. Fehler in der MyISAM Storage Engine sorg(t)en öfter dafür, das folgende Fehlermeldung in den Logs auftaucht

System error 1067 has occurred.
Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist

Das Problem bekommen wir mittels

mysql_install_db

gelöst.

5. MyISAM bad auto_increment

MyISAM hat manchmal die pubertäre Angewohnheit die Autoinkrementwerte mit der Glaskugel festzulegen, ja es sind immer die anderen Schuld 😉 . Wenn dieser Zustand auftritt, führt dies letztlich dazu, dass keine weiteren INSERTS in der Tabelle ausgeführt werden können. Wann solch ein Problem aufgetreten ist, können wir daran feststellen, dass der letzte eingefügte Wert mit einer -1 im zu inkrementierenden Feld versehen ist. Den letzten gültigen autoinkrementierten Wert finden man via

SELECT max(id) from tablename;

reparieren kann man die Tabelle mittels

ALTER TABLE tablename AUTO_INCREMENT = id+1;

6. Zu viele Verbindungen – Too many connections

prinzipiell kann dies viele Ursachen haben. Hier einige Beispiele:

1) In der php.ini sind die Verbindungen zur Datenbank als Persistent deklariert(Neustart nicht vergessen).

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = On

Das beheben wir mit:

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = Off

2) Beim Aufsetzen der Datenbank haben wir die Anzahl der User/Verbindungen Unterschätzt, dies können wir mittels der Erhöhung des Wertes max_connections in der my.cnf und einem Neustart des MySQL Dienstes beheben.

alt:

max_connections = 100

neu:

max_connections = 300

Hier sollte man Vorsichtig erhöhen! Immer daran denken, dass die Datenbank Speicher je Verbindung reserviert und wenn man hier zu forsch zu werke geht, schiesst man sich den kompletten Server ab.

3) Irgendjemand versucht sich gerade an unserer Datenbank …

iptables -I INPUT -p tcp --dport 3306 -J REJECT

mit Angabe des Angreifers

iptables -I INPUT -s 1.2.3.4 -p tcp --dport 3306 -J REJECT

7. Beschädigte InnoDB Tabellen

InnoDB Tabellen sind  denke ich am häufigsten im Gebrauch. Transaktional, zuverlässig und im Gegensatz zu MyISAM, unterstützt InnoDB das gleichzeitige schreiben in der gleichen Tabelle. Der InnoDB interne Recovery-Mechanismus ist so schon recht gut. Wenn die Datenbank abstürzt, wird InnoDB immer versuchen anhand des Logfiles das Problem zu beheben. In den meisten Fällen geht es gut und der gesamte Prozess ist für den User transparent. Im Fall, dass es InnoDB nicht gelingt die Tabellen und oder die Files zu reparieren, startet die gesamte Datenbank nicht! Eine Möglichkeit dies zu Umgehen ist eine Replikation (Master-Master). Im Zweifel schwenkt man auf den zweiten Master, oder auf den Slave und richtet den Ersten komplett neu ein.

Wenn wir nun die InnoDB Tabellen wie z.B. in meinem obigen Fall reparieren müssen, gehen wir wie folgt vor:

Wir tragen in die /etc/my.cnf folgendes ein:

[mysqld]
innodb_force_recovery = 4

Als nächstes starten wir MySQL. Dabei gibt uns die innodb_force_recovery = 4 Option an, das alle INSERTs und UPDATEs ignoriert werden. Mittels mysqldump ziehen wir uns ein Dump aller Daten und Datenbanken.

mysqldump --force --compress --triggers --routines --create-options -uUSER -pPASSWORD --all-databases > /data/alldatabase.sql

Wir stoppen die Datenbank und löschen das datadir. Damit wir den MySQL-Dienst in einer Grundinstallation starten können, führen wir mysql_install_db aus(chown für den mysql user auf die angelegten Dateien und Verzeichnisse nicht vergessen). Ist dies geschehen entfernen wir die innodb_force_recovery Option und starten den MySQL Dienst neu. Nun können wir das Backup einspielen.

mysql -uroot --compress < /data/alldatabase.sql

Zum Schluss führen wir noch ein flush privileges; aus und alles wird gut.

Allgemein

Prinzipiell ist anzuraten, wenn man den Zugriff auf die Datenbank nicht via Firewall oder Unterbrechung des physischen Connects verhindern kann, den Port in der my.cnf zu ändern (Eintrag: port=3307). Damit wird verhindert, dass während des Recoveryprozesses zusätzlich Probleme auftreten, weil eine Applikation oder ein User versucht Daten abzurufen oder einzufügen.