MySQL innodb_file_per_table upgrade

By default, MySQL installation on Debian comes without innodb_file_per_table option which spread tables in individual InnoDB files. Based on your usage patterns or backup strategies this might be better filesystem organization than one big /var/lib/mysql/ibdata1 file. I first heard about it in OurSQL Episode 36: It's Not Our (De)fault!. It's great podcast, but to be honest with each new episode I wish to have only PostgreSQL servers to maintain...

To enable this option you will need to create configuration file and restart MySQL server:

koha:/etc/mysql/conf.d# cat > file-per-table.cnf 
koha:/etc/mysql/conf.d# /etc/init.d/mysql restart

This won't change anything, because only new tables will be created in separate files. But, we can use ALTER TABLE table ENGINE=InnoDB on each table to force InnoDB to re-read tables and create separate files:

mysqlshow koha --status | grep InnoDB | cut -d'|' -f2 | sed -e 's/^/alter table/' -e 's/$/ engine=InnoDB;/' | mysql -v koha

If you replace grep InnoDB with grep MyISAM you might use same snippet to convert MyISAM tables into InnoDB (if you still have them or don't use fulltext search).

I think that system administration is like gardening. I don't know anything about gardening, but it seems to involve a lot of care here and there, seemingly without much pattern. In that sense, it's similar to wiki editing, you start somewhere and you really don't know where it lead you to.