That 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. According to Bill Karwin's comment 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
Delete ibdata1 and ib_log files
Restore from dump
When you start MySQL in step 5 the ibdata1 and 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.
You have probably seen this:
By using the command ALTER TABLE ENGINE=innodb or OPTIMIZE TABLE one can extract data and index pages from ibdata1 to separate files. However, ibdata1 will not shrink unless you do the steps above.
We are a specialist web development agency experienced in all aspects of web design, content management systems like Wordpress and Drupal, e-commerce, hosting, conversions, reporting, SEO, and more. Our services are used across multiple industries and although most our customers are in Oregon, we also serve customers in other states such as California, Colorado, Washington, and Illinois.