MySQL innodb file per table nachträglich einstellen

Hier eine kleine Anleitung wie man innodb file per table nachträglich auf auf eine bestehende Datenbank ausführt.
Dadurch wird der Inhalt der Datenbanken die in der ibdata1 liegen in einzelne InnoDB Files geschrieben.
Dies sollte man eigentlich von vornherein machen aber ab und zu kommt es vor das dies vergessen wurde.

Während der Ausführung wird der doppelte Speicherplatz benötigt, da alle Daten aus der ibdata1 in einzelne .ibd Files kopiert wird.
Der zugriff auf die Tabelle einer DB die gerade konvertiert wird, ist während der Konvertierung nicht möglich.

Es gibt zwei Möglichkeiten die man machen kann.
Die erste wäre ein MySQL dump zu machen und dann das ganze wieder einzuspielen.
Ich möchte aber hier auf eine „Online“ Variante eingehen.
Als erstes sollte von /var/lib/mysql ein Backup gemacht werden.

Dann fügt man die Zeile innodb_file_per_table = 1 entweder in die my.cnf unter dem Bereich [mysqld] ein oder erstellt eine erweiterte Config unter /etc/mysql/conf.d/custom.cnf

Wir machen das letzteres da dies von einem update nicht überschrieben wird.

[stextbox id=“bash“]nano /etc/mysql/conf.d/custom.cnf[/stextbox]

[mysqld]
innodb_file_per_table = 1

Dann starten wir MySQL einmal neu
[stextbox id=“bash“]/etc/init.d/mysql restart[/stextbox]

Jetzt werden alle neuen InnoDB Tabellen in einzelne Files geschrieben.

Als nächstes lassen wir uns in der MySQL Shell alle Datenbanken anzeigen die InnoDB sind und geben uns auch gleich den Befehl dazu aus diese zu konvertieren.
[stextbox id=“bash“]select concat(‚alter table ‚,TABLE_SCHEMA ,‘.‘,table_name,‘ ENGINE=InnoDB;‘) FROM INFORMATION_SCHEMA.tables where table_type=’BASE TABLE‘ and engine = ‚InnoDB‘;[/stextbox]

Hier die Beispielausgabe von meiner Roudcube Datenbank

alter table roundcube.cache ENGINE=InnoDB;
alter table roundcube.contacts ENGINE=InnoDB;
alter table roundcube.identities ENGINE=InnoDB;
alter table roundcube.messages ENGINE=InnoDB;
alter table roundcube.session ENGINE=InnoDB;
alter table roundcube.users ENGINE=InnoDB;

Jetzt müssen diese Query´s nur noch ausgeführt werden und MySQL konvertiert die Tabellen in einzelne Files.

Wenn alle Tabellen konvertiert wurden könnte man die ibdata1 löschen, ich empfehle aber diese erst mal nur umzubenennen.

Also MySQL stoppen. ibdata1 nach ibdata1_old verschieben und MySQL starten. Dann alles noch testen.