I have a large MySql DB (150GB) and only now I've noticed that the innodb_file_per_table is set to off which causes the entire DB to be hosted on one single file (ibdata1). I want to activate innodb_file_per_table and have it retroactively split the DB into several files, what's the best way to do this?

2 Answers 2


There is really only one way to pull this off. You will have to export the data using mysqldumps, drop all databases, shutdown mysqld, delete ib_logfile0, delete ib_logfile1, delete ibdata1, add innodb_file_per_table under the [mysqld] heading, start mysql.

I posted this answer in StackOverflow back in October 2010

Here are the steps listed vertically:

Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

Step 02) Drop all databases (except mysql schema)

Step 03) Shutdown mysql

CAVEAT: To totally clean out uncommitted transactions from the InnoDB Files, run this

mysql -uroot -p... -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Step 04) Add the following lines to /etc/my.cnf


Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

Step 06) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Step 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

  • mytable.frm (Storage Engine Header)
  • mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

There is an alternative that will extract the InnoDB table without shrinking ibdata1.

Step 01) Add the following lines to /etc/my.cnf


Step 02) service mysql restart

Step 03) To extract a single InnoDB table called mydb.mytable, do this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

This will create one file pleus keep the original structure file

  • /var/lib/mysql/mydb/mytable.frm
  • /var/lib/mysql/mydb/mytable.ibd

You can do this for every InnoDB table. Unfortunately, ibdata1 will stay 150GB.

  • while running the reload from the .sql files i got the following error ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes any ideas?
    – Ran
    Jan 23, 2012 at 15:45
  • @Ran please post that as a separate question. Jan 23, 2012 at 15:51
  • sure, dba.stackexchange.com/questions/11393/…
    – Ran
    Jan 23, 2012 at 15:54
  • If you set innodb_file_per_table and then do the ALTER TABLE on every single table, can you delete the ibdata1 file to reclaim the space without having to restore? Mar 10, 2015 at 13:14
  • 2
    @SystemParadox ABSOLUTELY NOT !!!!!!!! You will lose the data dictionary. Mar 10, 2015 at 13:19

If you want to reclaim the space of ibdata, a dump/restore is your only choice, as Rolando points out. It is also probably best for performance to do this.

However, if you just want to cut your losses and 'lose' that 150GB on the harddrive, you can simply enable innodb_file_per_table in the my.cnf and restart your server.

Then for each table, issue:


The problem here is that large tablespaces will take a while.

What I would suggest is setting up a slave of your live db, run the conversion on the slave, then either shut off the master/slave and copy the new dataspace to the master, or promote the slave to be master once it has caught up.

You are going to have difficulty making this change with no downtime at all.

  • +1 for being brutally honest and saying 'cut your losses.' You could have said 'bite the bullet' too. Jan 20, 2012 at 17:02
  • You can use pt-online-schema-change to avoid downtime while running the alter table.
    – cornernote
    Jul 1, 2017 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.