MySQL InnoDB Tabellen in separaten Dateien speichern

Zuletzt lief bei uns eine Kunden-VM voll, da eine Log-Tabelle in MySQL inzwischen 25 GB an Daten enthielt, die im Grunde gar nicht benötigt wurden. Ein volles Dateisystem führt leider zu unschönen Systemfehlern (hier fiel die Website aus) und so machte sich der zuständige Admin auf die Suche, fand die Ursache, leerte die Tabelle – aber nichts änderte sich. Die Wesbite lief auch weiterhin nicht.

Das fehlende Puzzlestück war, dass der Admin / Webmaster ausgiebig Gebrauch von InnoDB-Tabellen in MySQL machte. Standardmäßig speichert MySQL aber alle InnoDB-Daten in eine einzige Datei, typischerweise in /var/lib/mysql/ibdata1. Diese hat die Eigenschaft sich nicht wieder zu verkleinern, wenn Datensätze oder gar Tabellen oder ganze Datenbanken gelöscht werden.

Abhilfe schafft eine Konfigurationsoption von MySQL. Hierzu editiert man die my.cnf und fügt im Bereich [mysqld] einen Parameter hinzu:

[mysqld]
innodb_file_per_table

Dies veranlasst MySQL nach einem Reload der Konfiguration zukünftig im Ordner der jeweiligen Datenbank für jede InnoDB-Tabelle eine eigene .ibd-Datei anzulegen, die dann auch entsprechend Platz wieder freigeben kann – ganz so wie man es von MyISAM-Tabellen kennt.

Wer sein System nachträglich umstellen will und bereits InnoDB in Benutzung hat,

  • muss die betroffenen Daten zunächst exportieren (Dienste die schreibend zugreifen sollte man vorher ausschalten), z.B. mittels
    mysqldump -uroot -p –all-databases > dump.sql
  • stoppt dann den MySQL-Server
    /etc/init.d/mysql stop
  • löscht die ibdata1 und die Log-Dateien (ggf. nachdem ein Backup erstellt wurde)
    rm /var/lib/mysql/ibdata1
    rm /var/lib/mysql/ib_logfile*
  • stellt nun die Konfiguration in der my.cnf wie oben beschrieben um
  • startet MySQL neu
    /etc/init.d/mysql start
  • und importiert die zuvor exportierten Daten
    mysql -uroot -p < dump.sql

Im vorliegenden Fall war die das Disk-Image bereits so voll, dass wir den Export nicht mehr darin ablegen konnten. Daher haben wir temporär ein zusätzliches Disk-Image in die VM eingebunden und die Sicherungskopie und den SQL-Dump dort abgelegt. Nach Abschluss der Aktion waren die „verlorenen“ 25 GB in der VM wieder verfügbar.

Weiterführende Links:

Kommentare (5)

  1. Ein super Blog! Der hat mir jetzt doch schon viel weitergeholfen. Danke für die Tipps. Werde einiges davon anwenden!

  2. Ich finde es auch gar nicht falsch eine schnöde SATA Platte in den Server zu stecken und /var/log darauf schreiben zu lassen. So kann es nicht passieren das das eigentlich RAID für das Produktivsystem versehentlich voll läuft.

  3. Ich wusste bisher nicht, dass MySQL die InnoDB-Tabellendaten in einer Datei ablegt. Hast du Erfahrungen damit, wie es bei MariaDB funktioniert? Ich hatte mit Linux bisher ziemliche Probleme, sobald wenig Festplattenplatz vorliegt, damit kann das OS gar nicht umgehen.

    Gruß, Dieter

Kommentare sind geschlossen.