Replace MySQL Use Of ibdata1 With innodb_file_per_table

Overview

By default, MySQL versions prior to 5.6.6 store all table data in a single file called ibdata1. Since that version, the default has changed to store each table in its own set of files.  This is specified using the innodb_file_per_table setting.

The following steps will replace the use of the single file with that of one file per table.

This process has been successfully tested on Ubuntu 16.04 using MySQL 5.7.20.

1. Make a cold backup of the database in case anything goes wrong

Make a cold backup in case of a problem. Make sure there is enough space on the target partition/folder.

2. Export and drop non-system databases

3. Configure MySQL to use innodb_file_per_table

Add to /etc/mysql/my.cnf

4. Reinstall MySQL Server

5. Import data back into database


To stop the change and restore the database to its previous state, do the following

1. Undo configuration change

Comment out the innodb_file_per_table option in /etc/mysql/my.cnf

2. Restore database from cold backup

Leave a Reply

Your email address will not be published. Required fields are marked *