Hi, I’m MySQL and I’m greedy, I WANNA ALL YOUR BYTES.
If you have a huge ibdata1 file (/var/lib/mysql/ibdata1) probably is because you don’t have innodb_file_per_table option in your my.cnf. To fix this follow this simple method (it requires to have free disk space):
- Add innodb_file_per_table to your my.cnf and restart MySQL
- Create list of innodb tables to migrate (all of them!)
SELECT `TABLE_SCHEMA`,`TABLE_NAME` FROM TABLES WHERE ENGINE = ‘InnoDB’; - For each table execute this:
ALTER TABLE `table_name` ENGINE=InnoDB
With that the table will be regenerated in its own file. But ibdata1 won’t shrink because MySQL is greedy and will keep that (empty) space for him - Free space deleting ibdata1
- Profit