MariaDB / MySQL: How to vacuum your deleted database (shrink size of ibdata1 file)

After checking out the data from a well known leak, I realised that MySQL poorly handles the deletion of databases and that the default configuration doesn't make it very easy to vacuum the obsolete data.

(5.6 changes the default so it's better, but still an issue)

Have no fear, twig (and the internet's collective knowledge) to the rescue!

  • Get a list of all databases on your server

SHOW DATABASES;

  • Make a backup of all the data. You're going to need it. You can skip internal databases information_schema, mysql, and performance_schema.

mysqldump -u root -p database_name > database_name.sql

  • If you're lazy you can just use this instead:

mysqldump -u root -p --all-databases > everything.sql

  • Now drop every single table EXCEPT for the internal databases; databases information_schema, mysql, and performance_schema.

DROP DATABASE database_name;

  • Now terminate the server process.
  • Open up my.cfg or my.ini (Windows)
  • Under "mysqld", add in "innodb_file_per_table". This changes the setting so the information for each database goes into a separate file.
  • From your MySQL data directory (MySQL\5.5\data or /var/lib/mysql/), delete the ibdata1 and ib_logfile* files.
  • Restart the MySQL service.
  • Log back into the SQL shell and create each database again.

CREATE DATABASE database_name;

  • And finally, reimport the data.

mysql -u root -p database_name < database_name.sql

Once again, thank you technology for keeping me up way past my bedtime. Hopefully this has helped you avoid the situation I'm in right now... Looking for suitable gif animations can be hard, but I've found a photo that's surmised how I feel about the way MySQL has been built.

1484391_10153354869076264_2873289967254645255_n

Sources

 
Copyright © Twig's Tech Tips
Theme by BloggerThemes & TopWPThemes Sponsored by iBlogtoBlog