Dotaz: Z inodb mysql databáze fašírka kvůli jediné nepodstatné tabulce

[xsouku04]

Před 13 lety kdy jsem s mysql začínal, bylo myisam defaultní engine. Nyní je to věc již hodně zastaralá, ale pořád má podle mne zásadní výhody. Hlavní výhoda je, že tabulky jsou na sobě nezávislé a lze je vždy opravit, když se poškodí a to nezávisle na sobě. Jako záloha je možné provést rsync přímo binárních souborů a to dokonce i za provozu. Tabulky sice možná budou poškozeny, ale půjdou opravit. U inodb je moje zkušenost taková, že pokud se to rozbije, mysqld už nejde ani spustit nebo při prvním pokusu o práci s poškozenou tabulkou spadne. I když je poškozená naprosto nedůležitá tabulka, která jen cosi loguje, nezbude než celý obsah databáze zahodit a nahrát ze zálohy. Což bohužel způsobí delší výpadek a komplikaci. Nebo jsem něco přehlídl? Mít databázi, která možná po výpadku elektřiny nepůjde spustit a opravit mi přijde jako zásadní vlastnost, kvůli které všechny další výhody inodb ztrácejí smysl. U myisam se neopravitelný stav nikdy nekoná, že teoreticky při opravě přijdu o jeden záznam v tabulce mi nevadí. Nebo jsem jen něco přehlídl?

Ano je zde možnost databázi replikovat třeba i do dvou dalších databází a v případě rozbití hlavní databáze prostě hlavní databázi vypnout a za hlavní prohlásit jeden z klonů. Následně opravit klonování. Jde o to mít co nejmenší výpadek. Přímočaré mi to ale nepřipadá.

Nyní se nám stalo, že mysql nešla spustit ani opravit jen kvůli jediné poškozené tabulce v inodb (ostatní tabulky byly myisam), i když tabulka obsahovala jen relativně bezcenné logy, nebylo ji možné odstranit nebo znovuvytvořit běžným způsobem. Mysql prostě jen padala. Napadá vás něco jako prevence?

---

---

Odpovědi

[bazil]

Základem je nastavení, aby MySQL všechny inodb tabulky ukládalo do samostatných souborů. Dojde potom k tomu, že jsou samostatně soubory s definicí tabulky, které lze v 99% případů obnovit (pokud děláte dump struktury db, který zálohujete ta, na 100%) a datové sobory. To vše zvlášť pro každou tabulku.

Následuje proces obnovení struktury databáze a pak pokus o obnovení dat. Pro detailnější postup googlete.

Jinak je dobrým nápadem zálohovat inodb pomocí dumpů databáze. Samostatně strukturu a samostatně data.

[dustin]

Souhlas. Proto má nová mysql to nastavení table per file již jako default.

[xsouku04]

Inodb databáze se nám rozbila zatím asi ve čtyřech případech. Vždy to byla totálka a návody na obnovení dat nefungovaly. Naposledy jsme opravu zkoušeli pomocí tohoto návodu. https://support.plesk.com/hc/en-us/articles/213939865-How-to-fix-InnoDB-corruption-cases-for-the-MySQL-database- Mysqld odmítal startovat a pokud jsme jej nastartovali v módu innodb_force_recovery = 1, tak spadnula při jakékoli operaci s polámanou tabulkou (jedinou tabulkou s inodb). Z naší zkušenosti to tedy to vypadá, že jev kdy je inodb neopravitelná je poměrně častý i když pravda vypadá to, že se s ním velká část lidí ještě nesetkala. To mne udivuje.

I onen plesk návod má jako poslední bod, že nic z výše nefungovalo obnovte databázi ze zálohy. Dělá to tedy na mne dojem, že každý uživatel inodb by měl počítat s tím, že mu z inodb databáze jednoho dne zbude jen fašírka a mít na to připravený postup. Pro uživatele zvyklého na myisam to může být překvaní, protože se tím prostě dříve nesetkával.

Vypadá to, že běžný postup pro tento případ je provádět obnovu z textového dumpu a smířím se s tím, že ztratím data od poslední zálohy. Pokud je to přílišná ztráta, je nutné dělat replikaci. Jsou ještě nějaké další možnosti?

Jak mi pomůže mít zálohovanou strukturu zvlášť?

[PetrHL]

Také jsem se bohužel s rozbitou databází několikrát setkal. Drtivá většina byla způsobena ztrátou napájení . Nastavení innodb_force_recovery na jedna často nepomáhalo a databáze padala na hubu skoro vždy. Při použití innodb_force_recovery = 6 se databázi povedlo spustit v embedded režimu (bez kontroly oprávnění atd.) a data dostat ven pomocí mysqldump, který se připojil přes existující socket. Jiné způsoby obnovy dat selhaly. Stejně se mi nepovedlo obnovit všechno, ale ta nejdůležitější data přežila.

Zkuste se mrknou na Perconu, mají speciální zálohovací utility pro InnoDB, obnova je pak podstatně rychlejší, než čekat na nasypání dumpu.

[xsouku04]

Zajímalo by mne, kde soudruzi propagující inodb udělali chybu a proč se o tomto závažném problému více nepíše. Teorie je jistě taková, že se inodb má po výpadku napájení opravit sama a ani se o tom nemáte dozvědět. Výpadek elektřiny je prostě něco s čím se musí počítat i v zálohované serverovně, protože i tam udělají tak jednou za tři roky pravděpodobně chybu v tom, že jím např. dostatečně rychle nenaběhnou agregáty. Vypnutí serverů natvrdo např. servervoně master internet Brno pamatuji minimálně dvakrát i když vždy se týkalo jen některých polic.

Ale neopravitelná inodb se nám dvakrát přihodila i bez výpadku elektřiny.

Problematické může být i udělat snapshot běžícího virtuálu, aby jste měli zálohu jinde. Snapshot jakoby simuluje vypnutí v jeden okamžik, pravděpodobně je to i lepší než skutečné vypnutí. Snapshot by neměl být problém a mělo by dojít samoopravě. Dá se ale na to spolehnout? Nejspíše nedá, ale o důvodech co v praxi selhává nikdo moc nepíše. Řešením by mohlo být udělat zálohu perconou (nebo obyčejný dump v případě menší databáze) a hned poté ten snapshot, kdyby z hlavní databáze zbyla fašírka, obnovíte zálohu.

Replikace či fidlání s perconou je způsob jak minimalizovat škody, ale jednoho dne stejně budete mít nechutný problém, který budete muset ručně řešit a nejspíše to nebude výpadek na 5 minut.

Pro jednodušší aplikace, kde nepoužíváte složité pomalé dotazy přes hodně tabulek, je asi dobrým řešením zůstat u myisam. Hlavní nevýhoda myisam je to, že jeden déle probíhající select může blokovat celou tabulku pro cokoli dalšího, tedy i další select. Proč by měl select blokovat další select nechápu. Možná je to tím, že mezi jedním a druhým select je nějaký insert, který sice není důležitý (nemá to vliv na výsledek) ale provede to zablokování, protože rychlý insert nebo update se provede až poté co je hotov zdržující select. Kdyby byla nějaká možnost si zvolit, aby select nečekal na čekající insert či update, pravděpodobnost by byl problém lepší škálovatelnosti pro myisam vyřešen.

[dustin]

Nemáte nekorektně nastavený innodb_flush_log_at_trx_commit - např. na 1, ale třeba ve skutečnosti systém potvrzuje sync předčasně a data nejsou ještě uložená?

[razor]

Pro high availability mysql bych se podíval na: MySQL Group Replication či MySQL InnoDB Cluster.

[Andrej]

Tohle je jeden z asi tak tisíce důvodů, proč dát přednost skutečné databázi (PostgreSQL) před dětskou hračkou (MySQL).

K původnímu dotazu: Jediná možná prevence je solidní databáze. A samozřejmě solidní souborový systém, to se rozumí samo sebou. A taky nepoužívat pokud možno žádné úžasně rychlé, zato však data pojídající (v případě výpadku elektřiny) mount optiony jako nobarrier a podobné.

[xsouku04]

Asi stojí za zvážení. Zvláštní ale je, že když si člověk hledá rozdíly (výhody a nevýhody) mezi mysql (mariadb) a postgreSQL, většinou všichni básní o transakcích a podobně, ale že by byl rozdíl v tom že inodb dělá občas z dat fašírku a PostgreSQL nikoli jsem ještě neslyšel. Pravda ale že fakt, že musím i inodb použít jakýsi externí nástroj (percona ?) abych vůbec mohl udělat zálohu za chodu (jinou než dump), tedy i nastartovat replikaci, svědčí o tom, že to moc promyšlené nemají. Nebo očekávají pravidelné noční odstávky, které je možné využít pro nastartování replikace a zálohování? A když replikace chcípne během dne tak počkám prostě do večera? A v případě fašírky zkopíruji aktuální data z replikované databáze? (minimálně desítky minut). A nebo prostě za hlavní prohlásím repliku a budu čekat jaké že problémy by z toho mohly vzniknout, protože pokud nechci mít noční odstávky těžko si to mohu vyzkoušet předem?

Nebo jsem něco přehlídl?

[ZiGi]

Celkom rozumne mi pride robit snapshot suborov za chodu, pokial predtym pozastavite DB. Samozrejme to predpoklada snapshotovaci FS, btrfs, pre velke nasadenia zfs. Ak taky FS nie je mozne pouzit, treba robit snapshoty celej virtualky alebo LVM. Pocas zalohovania je DB v read-only stave, snapshot trva iba okamih. Aj ked je pravda, ze vela aplikacii nepobezi s RO databazou, ale 3-5s vypadok mi pride akceptovatelnejsi ako pripadne mnoho hodinove (potazmo viacdnove) obnovovanie z textovych dumpov. ja to robim nejako takto (pre-freeze-script):

<code>

sync mysql --defaults-extra-file=/etc/mysql/debian.cnf 2>&1 >>/tmp/backup/output <<EOF

FLUSH TABLES WITH READ LOCK;

SET GLOBAL read_only = ON;

EOF

echo 1 >/tmp/backup/db_lock sync

btrfs subvolume snapshot -r "/var/lib/mysql" "/var/lib/mysql/snapshots/`date "+%Y_%m_%d__%H_%M_%S"`"

if [ -e /tmp/backup/db_lock ]; then

mysql --defaults-extra-file=/etc/mysql/debian.cnf 2>&1 >>/tmp/backup/output <<EOF

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

EOF

rm -f /tmp/backup/db_lock fi

</code>

ps. niektore aplikacie s MYISAM nebudu fungovat.

pps. overit zalohovanu DB je pomerne jednoduche, staci spustit dalsiu instanciu mysql s upravenym konfigurakom (port, ulozisko) ci uz v RO rezime alebo nad dalsim RW snapshotom. Dokonca je mozne potom z takeho snapshotu vydumpovat textove zalohy.

[ZiGi]

no, ako pouzit som zjavne nepochopil ...

[dustin]

Zálohovací nástroj od percony https://www.percona.com/doc/percona-xtrabackup/2.4/index.html funguje úplně v pohodě, není potřeba nic vypínat/přepínat do RO. Vygeneruje to kopii souborů, na kterých se může záložní DB rovnou spustit. Běžně se používá při vytváření zreplikované slave instance za plného chodu masteru.

Pro mariadb je jeho fork https://mariadb.com/kb/en/library/mariadb-backup-overview/

[xsouku04]

V praxi je třeba asi umět obojí. Jak udělat binární kopii celého virutálu, kde běží databáze - za chodu a bez rozbití - to je ten snapšot ideálně během pár vteřin kdy je databáze jen v režimu čtení. Bez tohoto budete jen obtížně klonovat virutál.

Je třeba zdá se umět používat i perconou, kterou použiji pro zálohy i startování klonů. Teoreticky bych se mohl obejít jen s perconou, kde si udělám nejdříve zálohu celé databáze a pak teprve vytvořím snapshot, který bude sice rozbitý, ale budu moci obnovit data ze zálohy, kterou vytvořila percona. Trošku hloupé, ale asi tak nějak si to vývojáři představují.

Ve všech případech ale platí, že až se z dat stane neopravitelná fašírka bude to velmi nepříjemný problém a náprava bude zjevně trvat desítky minut. A to v případě, že půjde vše podle plánů a technik co to umí bude k dispozici ...

Pokud někdo používá jen myisam, tak se to prostě nestane. Jestli se to stává i uživatelům postgreSQL netuším. Zdá se, že ti mají přinejmenším možností mnohem více a nemusí používat externí nástroj. Mohou např. obnovit databázi z noční zálohy + logů změny tak, aby obnova byla provedena k jistému časovému okamžiku. To může být užitečné, např. pokud si omylem nějaká data smažete (nebo nějaký záškodník), můžete vše obnovit na stav jaký byl vteřinu předtím než jste si je smazali. To vám klonování ani samotné pravidelné zálohy neumožní. Nebo to lze i v mysql? Jestli ale někdo v postgreSQL občas řeší totální fašírku nevíme, nikdo se tím jistě chlubit nebude stejně jako se tím nechlubí vývojáři ani uživatelé inodb. To člověk zjistí nejspíše až po několika letech provozu.

[okbob]

Je potřeba si uvědomit, vůči čemu jsou a nejsou databáze chráněné. Transakční databáze by měly přežít bezproblémově svou havárii, případně havárii operačního systému (v tomto případě pokud nedojde k poškození filesystému). Proto se také u databázových serverů silně doporučuje UPSka.

MyISAM je netransakční - konzistenci Vám dokáže rozbít už zcanclovaný UPDATE. O pádu MySQL nemluvě. Na druhou stranu formát MyISAM je tak jednoduchý (není multigenerační), že pro něj existují nástroje, které dokáží rekonstruovat soubory (a těch pádů bylo tolik, že bylo nutné takovou aplikaci napsat).

U multigenerační architektury, kterou používá jak InnoDB, tak PostgreSQL, se mnohem obtížně dá zrekonstruovat obsah - to, co je v datovém souboru nemusí být validní záznam, atd. Multigenerační architektura to celé hodně interně komplikuje, nicméně SELECTy a UPDATE se přestanou blokovat (bez nutnosti použít dirty reading). Díky tomu se dá udělat konzistentní dump, aniž by se muselo nějak extrémně zamykat, atd. Dneska prakticky ve všech testech (na větších než malých datech) MyISAM výkonnostně nestačí na InnoDB.

Postgres, stejně tak InnoDB data neničí za předpokladu, že platí určité předpoklady - např. že si uživatel nevypne fsync, že fsync reálně funguje (a operační systém nekecá), že nedojde k poškození filesystému, atd. Při dnešní složitosti a miliónu různých optimalizacích je skoro zázrak, že to funguje. Nicméně to překvapivě funguje. Když člověk ví, co se může všechno rozbít, tak problémů s čitelností, konzistencí db několik málo za posledních 5 let.

Na rozdíl od MySQL jsou v Postgresu všechny nástroje pro zálohování (logickou i fyzickou zálohu, pro kontinuální zálohování a případnou Point In Time Recovery) v základu. MySQL je má ve své Enterprise edici (a musí se za ně platit - nezapomínejte, že MySQL vlastní a vývoj platí Oracle, který to nedělá z lásky ke komunitě). Viz https://www.mysql.com/products/enterprise/backup.html. Částečně (možná úplně - nejsem expert na MySQL) je možné tyto nástroje nahradit nástroji, které dodává Percona.

[xsouku04]

Postgres, stejně tak InnoDB data neničí za předpokladu, že platí určité předpoklady - např. že si uživatel nevypne fsync, že fsync reálně funguje (a operační systém nekecá), že nedojde k poškození filesystému, atd. Při dnešní složitosti a miliónu různých optimalizacích je skoro zázrak, že to funguje. Nicméně to překvapivě funguje. Když člověk ví, co se může všechno rozbít, tak problémů s čitelností, konzistencí db několik málo za posledních 5 let.

A to byla totálka nebo se to podařilo nějak opravit? Mít totálku několikrát za pět let je dost špatný výsledek pro databázi. U nás může být i odstávka na půl hodiny hrozný problém. I v noci. Na proti tomu, když se ztratí jeden záznam (poslední inzert či update) nás vůbec netrápí. Jsou to většinou nějaké haléře max. koruny škody. Myisam má problém, že se zamyká celá tabulka, a tak celou aplikaci může ucpat jeden delší select. Kromě toho rozdíl v rychlosti není až tak podstatný. To zamykání je klíčové. Řešíme to nyní tak, že čteme z klonu a déle trvající dotazy nestrpíme, musí se prostě optimalizovat, aby nebyly. Není to úplně dobré, ale žít se s tím dá.

Ohledně inodb fašírky.

Žádné změny defaultního nastavení file systému nebo databáze jsme pokud vím nikdy nedělali. Přesto několikrát inodb totálka i bez výpadku elektřiny. Máme strach to nyní nasadit a pokračujeme na myisam.

Kdesi jsem četl, že indodb je lepší a spolehlivější, ale když se pokazí, stojí to zato. Myisam je snadnější pokazit, ale není to problém.

[okbob]

Databáze se vždy dá zrekonstruovat, ale trvá to výrazně déle (dny) než obnova ze zálohy. Navíc se bavíme dost často o aplikacích, kde si nemůžete dovolit jen tak zahodit pár záznamů.

Nemohu mluvit za MySQL, ale co se týče PG, tak ta databáze spolehlivá je - ale za předpokladu, že uživatel něco nepohnojí, a na něco důležitého nevypne - případně, že nemá nakopnutý hw. Klidně být špatná virtualizace. Může záležet i na aplikaci - můžete hitovat bugu v MySQL, která se projeví párkrát za 5 let a je fatální ve Vaší konfiguraci. Chyby jsou všude.

Uvedu statistiku - V GoodData jsem za 3 roky při cca 80 serverech neměl žádný problém s nečitelností databáze. Ale to nic v podstatě neznamená pro Vás. Vždy záleží na konfiguraci, na železe, i na rukách. To, že se něco rozbije do nečitelného stavu je závažná buga - a skutečně se může jednat i o hw problém. A je nutné to řešit. Samo od sebe se nespraví nic.

[dustin]

Mysql používáme od r. 2001 a také nemám takovou zkušenost. Zpočátku bylo pár poškozených tabulek při vypnutí serveru natvrdo, pamatuju si i strašně dlouhý start, kdy ten server padnul při nějakém deletu v transakci a při startu se dělal několik hodin rollback. Ale v posledních cca 10 letech si nevzpomínám na větší poškození (ťuk ťuk). Samozřejmě při pádu serveru natvrdo se občas poškodí tabulky, ale repair je vždy dal do kupy. Rozhodně se nikdy neponičila bez pádu serveru, jen tak za běhu. Má cca 100GB, 1000 tabulek vše innodb. Replikace ze dvou masterů (vždy jen jeden je RW) přes dva uzly, každodenní kopírování na devel stroje (po obfuskaci pro bezpečnost a GDPR ), drží.

Také si tipuji na špatnou paměť nebo podobnou chybu HW. Ale stát se může leccos...

[xsouku04]

Ale pokud vím tak inodb nemá žádnou operaci rapair. Tu má jen my myisam. Inodb buď funguje (opraví se sám jak uzná za vhodné) nebo nefunguje a pak to bývá špatné, musím začít od nuly a možná zachráním (nějaká) data. Teorie je taková že inodb by se nikdy pokazit nemělo, v praxi to tak úplně neplatí a chybí příklady co se stává, jak minimalizovat škody a co proti tomu dělat.

Také si myslím, že i inodb není typické, že by se pokazila jen nějaká tabulka. Mysql celé spadne třeba i kvůli poškození naprosto nepodstatné tabulky, kterou bych klidně mohl celou smazat a vytvořit znovu, ale nejde to.

Můj problém je hlavně to, že i hodinová odstávka jednou za dva roky je neskutečný průser. To že nemůžu poškozenou tabulku prostě jednoduše přejmenovat (a opravovat později) a vytvořit novou prázdnou během minuty, aby to celé zase naběhlo je pro mne zásadní problém inodb.

Špatná paměť nebo něco takového by to asi mohla být, ale pokud se to projeví jednou za dva roky, hodně špatně se to zjišťuje.

[dustin]

Takže už to bylo tak dávno, že jsme ještě používali myisam. To už roky neplatí, protože už hodně dlouho klonujeme slave instance přes percona backup.

Jak říkám, s tímhle se nesetkávám.

Pokud bys měl files-per-table (což se mi velice osvědčilo a je již defaultní nastavení), stačilo by jen poškozený ibd soubor smazat https://www.devside.net/wamp-server/mysql-wont-start-because-of-innodb-table-corruption

[okbob]

Jelikož neexistuje repair, tak samozřejmě nejsou ani příklady co dělat. I když postup je jasný - pokud je záloha, tak obnova ze zálohy. Pokud není záloha, tak si jít někam poplakat.

[okbob]

samozřejmě, že existují testy CPU, paměť, ...

[dustin]

Z replikačních logů lze obnovit mysqldb k určitému timestampu také - viz např. http://frodo.looijaard.name/article/mysql-roll-forward-pitr

[karlik]

btrfs už je možné použít na kvm virtuální obrazy a snapšoty?

[xsouku04]

My používáme Openvz a nyní přecházíme na lxc - proxmos, který používá zfs. U openvz běžně rsyncujeme natvrdo, používáme myisam, které jen na chvíli zamkneme tabulky, nebo prostě na tvrdo za běhu s tím myisam kdyžtak opravíme. To lxc se zfs by mělo umět snapshoty, což je rozhdoně korektnější způsob jak si naklonovat/zazálohovat celý virtuál než jej prostě zkopírovat.

[Dalibor Smolík]

Mám jen tabulky typu myisam, naštěstí frekvence pohybu v databázi není velká, takže mi to nevadí. I obyčejné kopírování dat, včetně struktury tabulek způsobem cp funguje.