Traps of ibdata1

My working machine is ubuntu 14.04 and I've installed MySQL with apt-get (package manager of ubuntu). I wish I knew! There are some problems with the default install of mysql which are the followings:

  • When you drop a database on mysql, it doesn't free your hard disk
  • All the innodb tables are stored by default in only one file, the ibdata1, which it is impossible to delete it by yourself without destroying everything
  • By default, there isn't a limit on how much it will grow the above file (you can set a limit)
All of the above resulted my working machine to have a file, the ibdata1 to be 150GB even if my databases wasn't so big and I couldn't shrink it easily! How can you do it? Is there a solution? Well the solution is to export all the databases you have, stop mysql, delete ibdata1 manually and start it (read a better description here). OK but this doesn't mean it won't be done again right? Yes, so to stop that you have to add the following: [mysqld] innodb_file_per_table
Inside the my.cnf. This will store each innodb in separate files and you can delete it manually if there is a need (this config will be default in mysql v5.6 and above). Before that, be sure to do it by yourself! Oh and check your ibdata1 file if you weren't aware and laugh or cry :D
Max one mail per week.