MySQL: enable innodb_file_per_table with zero downtime

I thought that while my wife is preoccupied with the lemon pie, I might tell you this story.

InnoDB is a very good storage engine for MySQL that combines reasonable performance with wide popularity and, as a consequence, a good set of tools for diagnostics and fine-tuning. One of its downsides is that it is inefficient when it comes to the disk space management. While an extent of HDD space was added to the storage, InnoDB will not return it back even when you delete tables or databases. To add some flexibility, you should use innodb_file_per_table option. Unfortunately, if you have a running database, you cannot just enable this option. You will have to make a dump of the database and restore it on a new instance of MySQL with the option enabled from the very beginning. This scenario means that the database will be inaccessible from the moment you start mysqldump to the moment you finish restoring the data in the new instance. Is there a way to minimize the downtime?

Yes, you can run mysqldump on a backup of your database. But, then you lose the data written to the database from the moment you make the backup to the moment the new instance is ready. But that's a bit closer to the solution. You can also set up replication between the original database and the new one and then, when the new instance catches up with the old one, your task is completed. And the backup can be done online, without stopping MySQL, if you use Xtrabackup tool by Percona.

So, the basic steps you have to follow are:

• Configure your original database as master. Unless your database is already using binlogs for security, this is the only step that will require restarting MySQL.
• Make a backup of the original database using Xtrabackup.
• Restore the backup and run a second instance of MySQL.
• Run mysqldump on the second instance.
• Stop the second instance, but do not delete it yet.
• Create a new database and start the third instance of MySQL with the enabled option innodb_file_per_table.
• Restore the dump by feeding it into the third instance of MySQL.
• Configure the third instance as slave and run the replication.
• When the initial replication finishes and the slave catches up with the master, reconfigure your clients to use the new instance.
• That's it. You can stop the first instance now and delete it.

I wrote an even more detailed guide illustrated with example commands. It was published on Linux.com recently: HOWTO: Reconfigure MySQL to use innodb_file_per_table with zero downtime