Jak opravit poškozenou innodb MySQL databázi

26. září 2008

Programování

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 :-))

Následující text je určen pro zcela začínající správce serveru. Je mi jasné, že pro většinu správců se jedná o naprosto rutinní nekomplikovanou záležitost, vygooglit ji však nebývá vždy zcela snadné. Pro většinu úkonů budete potřebovat rootovská práva, takže pokud někde pouze hostujete stránky a zaznamenáváte podobné problémy, obraťte se na technickou podporu.

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ý.

Příkaz pro export všech databází do souboru zaloha.sql:
mysqldump --all-databases > zaloha.sql
Příkaz pro export databáze test do souboru test.sql:
mysqldump test > test.sql

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.

Výřez my.cnf pro spuštění innodb v recovery módu:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
innodb_force_recovery=4

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.

Tip: Během importu dat je dobré mít vypnutý webový server, aby nedocházelo ke zbytečným nekonzistencím v databázi. Nezapomeňte před importem vypnout recovery mod a restartovat mysql.

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.

Import všech tabulek ze souboru ujdetocz.sql do databáze ujdetocz:
mysql ujdetocz < ujdetocz.sql

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á :-)

Pro případné databázové odborníky: Přestože je úložiště MyIsam pro tabulku textů písní obecně vhodnější (více se čte než zapisuje), rozhodl jsem se prozatím pro innodb. Prvním důvodem jsou integritní omezení, druhým (podstatnějším) je blbý databázový návrh, ve kterém ukládám počet zobrazení přímo k textu (a tedy každé zobrazení je zároveň update). Po případné revizi db návrhu budu o MyIsam určitě uvažovat, přestože i ono občas "spadne".

Publikováno dne 26. 09. 2008 v kategorii Programování Odhadnutá klíčová slova BETA: mysql | innodb | oprava | databáze | opravit | mysql najít poškozenou tabulku | tabulky | oprava mysql databáze | oprava innodb
Mohlo by Vás zajímat BETA: CPU 100%, nefunkční klávesnice a touchpad

O kategorii Programování

V nejodbornější kategorii tohoto blogu jsou zařazeny články s mými programátorskými zkušenostmi získanými několikaletou praxí tvorby stránek a java aplikací pro mobilní telefony.

Komentáře k článku

POZOR: K článku zatím nebyl vložen žádný komentář, takže s velkou pravděpodobností nikoho neurazil, nikoho nepobavil a už vůbec nikomu nepomohl.

Přidat vlastní komentář:
Jméno:
E-mail:
Sledovat diskusi:
Web:
Kontrolní kód:

Komentáře jsou v prvé řadě určeny ke kladení dotazů k tématu, upozornění na chybu, rozšíření obsahu článku a vůbec ke zpětné reakci na obsah těchto stránek. Mé reakce jsou barevně odlišeny.

V současné době není umožněno vkládat HTML tagy - pokud vložíte HTML kód, bude převeden na entity. K Vašemu komentáři se do databáze uloží čas vložení a Vaše aktuální IP adresa (54.198.104.202). IP adresa se nebude zobrazovat čtenářům, nicméně v případě, že bude Váš odkaz shledán právně závadným, může být Vaše IP adresa předána příslušným státním orgánům. Emailové adresy jsou ochráněny před běžnými spam roboty.

© Marek Demčák 2007 - 2018
Všechna práva dle Autorského zákona (č. 121/2000 Sb.) vyhrazena.