Je všeobecne známe, že MySQL umožňuje ukladať dáta do dvoch tradičných formátov (enginov) tabuliek: do staršieho, hlúpejšieho (a občas rýchlejšieho) MyISAM a do novšieho, konzistenciu-podporujúceho InnoDB, ktorý je dokonca už od verzie 5.5 implicitným formátom.

Samotné dáta jednotlivých schém pri použití InnoDB sú na Windowse uložené na viacerých miestach. Ako uvádza dokumentácia, používajú sa. * samotné dáta: uložené v ibdata súboroch (na Windowse 7 a MySQL 5.5 v C:\ProgramData\MySQL\MySQL Server 5.5\data) * transakčné logy: ib_logfile0, resp. ib_logfile1 atď (rovnako v adresári data vedľa ibdata) * definície štruktúr tabuliek alias .frm súbory, uložené v adresári nazvanom rovnako ako príslušná schéma, pričom pre každú tabuľku existuje separátny .frm súbor. Ak máte tabuľku auto v schéme autoservis, existuje súbor C:\ProgramData\MySQL\MySQL Server 5.5\data\autoservis\auto.frm.

Problém s nafukujúcim sa dátovým súborom

Pri štandardnom nastavení existuje jeden známy, ale zákerný problém: všetky InnoDB dáta (teda reálne hodnoty záznamov) sú uložené spoločne v jednom obrovskom ibdata súbore. Ak sa vám tento súbor stratí, prakticky prídete o dáta.

Štandardne má tento súbor 18 MB, lenže čím viac dát do tabuliek ládujete, tým viac sa nafukuje. To samo o sebe nie je problém… ibaže tento súbor sa nikdy nesfúkne. Môžete skúsiť zmazať záznamy z tabuliek, dokonca dropovať celé databázy: samotné dáta síce zmiznú, ale ibdata ostane taký, aký je a bude len bobtnať a bobtnať.

Takto som raz naloadoval 2,4 milióna záznamov do dočasnej InnoDB tabuľky, čo nafúklo ibdata o zhruba 4 GB. Problém je, že keď už dáta neboli potrebné, 4GB boli zabraté ničím.

Riešenie?

Toto je pomerne častý problém a inak veľký hit issue trackera (napr. 1341 z roku 2003) a rozumné riešenie spočíva len vo verejne tajnom nastavení servera MySQL. Môžete totiž použiť fintu: spustiť server s nastavením, kde nebudú všetky ibdata v jednom megamolochu veľkosti bývalého ZSSR, ale každá schéma bude mať vlastný separátny dátový súbor.

Predtým však ale potrebujete * odzálohovať dáta (pre istotu), * dumpnúť všetky InnoDB tabuľky, * stopnúť server, * vymazať ibdata a ib_log-y * aplikovať magické nastavenie (o tom o chvíľu), * štartnúť server, * obnoviť dáta

Záloha dát

Odzálohujte preventívne celý adresár data.

Dump tabuliek

Dump celej databázy

Tu pomôže mysqldump

mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers > all-databases.sql

Alebo: dump len InnoDB schém

Alternatívne môžete dumpnúť len tie schémy, ktoré aj reálne používajú InnoDB tabuľky a tie, ktoré fungujú len na MyISAM (napr. schéma mysql), preskočíte:

Hľa, mini shell-skript v Bashi (na Windowse si môžete nainštalovať Cygwin a získate slušnú sadu GNU nástrojov.

#!/bin/bash
password=plemplem1

echo "SELECT distinct table_schema FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB'" | mysql -u root -p${password} | sed 1d > tables.txt
for f in $(cat tables.txt); do 
    table=$(echo ${f} | tr -d [:space:])
    mysqldump -u root -p${password} ––extended-insert ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers ${table} > ${table}.sql
done

Komentár k skriptu:

• verejné heslo si zmeníte dľa ľubovole
• najprv získame zoznam InnoDB tabuliek tým, že sa pomocou SQL dopytu spýtame na metamodel uložený v schéme INFORMATION_SCHEMA. SQL pošleme do klienta MySQL. Výsledok bude dobre formátovaný, akurát prvý riadok bude obsahovať názov stĺpca (table_schema), ktorý vyšmaríme von sedom (1d znamená „zmaž prvý riadok“). Celý výsledok poznačíme do súboru tables.txt
• súbor preiterujeme, preventívne odrežeme biele miesta zo začiatku a konca (najmä na Windowse je problém s koncami riadkov)
• a celú databázu dumpneme do súboru s rovnakým názvom ako má schéma

Stop servera

Jednoducho net stop mysql

Vymazanie dát

Zmažte ibdata a ib_logy.

Magické nastavenie!

Stačí otvoriť my.ini, teda centrálny konfigurák (na Windowse defaultne v C:\Program Files\MySQL\MySQL Server 5.5\my.ini) a dodať nakoniec

innodb_file_per_table

Toto nie je parameter typu kľúč = hodnota, je to len jedna direktíva.

Štart servera

Jednoducho net start mysql

Obnovenie dát

Jednoducho sa lognite do klienta a source-nite všetky SQL súbory, ktoré ste získali pri zálohovaní

mysql> source autoservis.sql

Záver

Po tejto zmene má každá tabuľka svoj vlastný .ibd súbor s dátami, a keď ju dropnete, tento súbor sa zmaže. Megaveľký iblog ostáva spokojne na rozumnej veľkosti a žiadne 10GB gorily vám nebudú behať po disku.