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.
Sources
- database - How to shrink/purge ibdata1 file in MySQL - Stack Overflow
- innodb - What is the best way to reduce the size of ibdata in mysql? - Database Administrators Stack Exchange
- database - How to shrink/purge ibdata1 file in MySQL - Stack Overflow
- MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
- mysqldump - Export and Import all MySQL databases at one time - Stack Overflow
