This article based on https://easyengine.io/tutorials/mysql/enable-innodb-file-per-table/

enable-innodb-file-per-table-on-mysql.md

Note: You may wish to convert MyISAM tables to InnoDB tables before you proceed.

innodb_file_per_table is by default ON Mysql 5.6.6 and onwards. There is plenty of stuff on Google about pros & cons ofinnodb_file_per_table.

This post details how to enable innodb_file_per_table on an existing database. Because innodb_file_per_table affects new tables only, created after innodb_file_per_table is enabled, we need to recreate old databases to force innodb_file_per_table on old tables and reclaim some disk space.

Server info

• Percona Server 5.5.
• innodb_file_per_table was not enable.
• innodb_data_file_path = ibdata1:12G:autoextend
• Size of ibdata1 is 25GB. It's too big and we can lose our data when the server crash.

Target

• Reduce size of ibdata1 to 2GB.
• Enable innodb_file_per_table.
• All data is not affected by this change.

Backup First

Create a dir to take backups:

mkdir /data/backup_db/

For better results, shut down PHP and other apps/scripts which update mysql. You can keep Nginx running and server non-logged in visitors cached content.

service mysql stop
rsync -avr --progress /var/lib/mysql/ /data/backup_db/
rsync -avr /etc/my.cnf /etc/my.cnf.org

Take mysqldump

As soon as above line completes, take a mysqldump of all databases

service mysql start
mysqldump --routines --events --flush-privileges --all-databases > all-db.sql

Drop Databases

Create a sql file to drop all databases EXCEPT mysql database

mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 > drop.sql

Verify if drop.sql has correct database names and then execute drop.sql queries.

mysql < drop.sql

Verify all InnoDB tables gone

SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';

Remove InnoDB files

Stop mysql server first

service mysql stop

Then

rm /var/lib/mysql/ibdata1 -f && rm /var/lib/mysql/ib_logfile0 -f && rm /var/lib/mysql/ib_logfile1 -f

At this point most likely you will have only /var/lib/mysql/mysql directory only.

Enable innodb_file_per_table and change ibdata1 size

Open my.cnf file

vim /etc/mysql/my.cnf

Add following lines

innodb_data_file_path = ibdata1:2G:autoextend
innodb_file_per_table = 1
innodb_file_format = barracuda

Time to import from mysqldump

Start mysql server now

service mysql start

Run mysql import

mysql < all-db.sql

Force mysql_upgrade (to generate performance_schema)

mysql_upgrade --force

That’s All!