ibdata1 isn’t shrinking is a particularly annoying feature of MySQL. The
ibdata1 file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump.
But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large. this is enabled by default as of version 5.6 of MySQL.
It was a while ago I did this. However, to setup your server to use separate files for each table you need to change
my.cnf in order to enable this:
As you want to reclaim the space from ibdata1 you actually have to delete the file:
- Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
- Drop all databases except the above 2 databases
- Stop mysql
- Start mysql
- Restore from dump
When you start MySQL in step 5 the
ib_log files will be recreated.
Now you’re fit to go. When you create a new database for analysis, the tables will be located in separate
ibd* files, not in
ibdata1. As you usually drop the database soon after, the
ibd* files will be deleted.