Jak opravit poškozenou innodb MySQL databázi

Včera se mi již podruhé za 18 měsíců provozování serveru stala velice nepříjemná věc – pokazila se mi jedna z innodb MySQL tabulek (zřejmě několikaset megová tabulka textů písní). Důsledkem této chyby bylo, že se zhruba každých 10 sekund kompletně restartoval démon MySQL serveru a po dobu restartu se na všech stránkách zobrazovaly chyby typu "Can't connect to local MySQL server through socket" nebo "MySQL server has gone away".

Po několika hodinách experimentování se mi konečně podařilo v půl čtvrté ráno server opět nahodit, s výsledným postupem (který zabere nakonec jen pár minut) jsem se rozhodl s Vámi nyní podělit (za půl roku zase jako když to najdu :-))

Identifikace poškozené tabulky není snadná

Kontrola tabulky pomocí CHECK TABLE (dostupná i v phpMyAdminu) je bohužel víceméně nepoužitelná – všechny podezřelé tabulky se po tomto testu zdály být OK.

O hodně sdílnější je v takovém případě log mysql serveru (mysqld.log), jeho jedinou nevýhodou je, že je (díky restartu každých pár sekund) opravdu hodně dlouhý (naštěstí existuje příkaz tail). Na každý restart v mém případě připadlo zhruba 100 řádků různých výpisů, povídání a dobře míněných, leč neúplných, rad. Poznal jsem, že je problém patrně s tabulkou lyrics, na druhou stranu bylo vidět, že server občas zhavaroval i kvůli jiné tabulce. Doporučené řešení v logu i na googlu zní – zazálohovat, spustit innodb v "recovery módu" a potom případně dropnout vadné tabulky a naimportovat je znovu.

Spuštění INNODB engine v recovery módu a záloha dat

Vzhledem k restartu mysql serveru při prakticky každém přístupu k poškozené tabulce (spíš asi šlo o modifikaci) bylo nemožné udělat zálohu "za chodu" pomocí phpMyAdmina (protože ostatní stránky do tabulek šahaly a pořád něco měnily). Ke slovu tedy přišla utilita mysqldump, která uměla data vyexportovat podstatně rychleji a nepotřebovala k tomu webový server puštěný.

Nevýhodou tohoto výstupu je, že obsahuje i věci, které tam třeba ani nechceme a že exportuje opravdu všechny tabulky (měl jsem obavu, abych při následném dropnutí úplně všeho nepřišel třeba o uživatelská jména pro přístup do MySQL). Proto se více hodí exportovat každou databázi zvlášť (nejsou-li jich samozřejmě desítky).

Po úspěšné záloze dat jsem si trochu oddechl a upravil konfigurační soubor my.cnf tak, aby se innodb engine spustil v tzv. recovery módu.

Databáze po restartu bez problému naběhla a držela se. Moje neskutečná radost, že se již 10 minut databáze nerestartovala, byla narušena výpisem chybových hlášek (#1030 - Got error -1 from storage engine) na ujdeto a vyplňto, ostatní weby se zdály být funkční.

Výše uvedená hláška se prý často ukazuje v momentě, kdy je plný disk, což však nebyl můj případ. Až za hodně dlouho se mi podařilo nalézt pravou příčinu této hlášky, a sice že innodb je v recovery módu pouze readonly, žádná data nelze zapisovat ani měnit. Recovery mód je určen pouze pro vytvoření zálohy a nikoli pro kontrolu a opravu enginu pro případ chyby, jak jsem si předtím mylně myslel. Zdá se, že se innodb snaží chybu opravit i pokud není v recovery módu, akorát to při běžném provozu nestihne (databáze se dříve restartuje než opraví).

Nicméně se mi innodb v recovery módu samo neopravilo a bylo zřejmě nutné veškerá data vyexportovat, dropnout a znovu naimportovat (nevěděl jsem na 100%, zda jsou vadné pouze lyrics, kromě lyrics jsou v databázi stejně už jen drobotiny). Díky docela rychlému internetu jsem si dopřál ten luxus a v phpMyAdminu v recovery módu v klidu vytvořil export každé databáze obsahující innodb tabulky (dohromady několik set MB).

Mazání a opětovný import dat do databáze - pozor na views!

Vymazání tabulky (drop table) je hned po drop database jeden z nejkritičtějších příkazů – nejsou-li k dispozici aktuální funkční zálohy, jedná se o nevratný proces. Proto jsem se snažil zálohy nejprve vyzkoušet na jiné databázi.

Import se však nezdařil, jelikož server každého VIEW zahlásil, že již existuje (přestože se na první pohled zdálo, že tomu tak není). Při pohledu na exportovaná data jsem zjistil, že se všechna VIEW exportují včetně názvu databáze (název databáze samozřejmě zůstává původní a pokud původní VIEW v původní databázi nedropnete, tak nebude možné nové ve stejné databázi vytvořit). Řešení je buď upravit export, nebo to risknout a zkušební import nedělat.

Zatímco export v phpMyAdminu je velmi pohodlný a výstup je kvalitní, rychlý a kompatibilní, importovat přes phpMyAdmina několik set MB je prakticky nemožné (když už si povolíte upload několika set MB souborů a nastavíte několik set MB RAM pro běh php skriptu, budete čekat celou noc, než soubor pomalé PHP zpracuje). Nabízí se použití utility mysqlimport, podle všeho však umí importovat pouze po tabulkách (každou tabulku zvlášť, navíc bez struktury). Daleko více se osvědčilo spuštění mysql konzole a předání obsahu souboru na standardní vstup (pozn.: před importem jsem v phpMyAdminu vždy z dané databáze všechny tabulky odstranil - dropnul). Import několika set MB dat trval i na starším stroji v řádu desítek sekund.

No a to je vše, po obnovení všech tabulek (asi to nebylo nutné dělat u všech, vím..) konečně zase všechno běží, jak má :-)