It may sound like a risky proposal to reclaim disk space from the Mysql database system, but it can be done. The procedure that Mysql documentation (and numerous Mysql experts) recommend is to remove all databases (yes, destroy it all) and rebuild it.
The reason for this method – that made me very nervous – is how Mysql system has been designed. Once you create a database and insert data into tables, your database size can only grow. It never shrinks. Even if you delete your database, the data is still there in Mysql’s internal file “ibdata1”. This relates to Innodb databases only.
On my server, a web site statistics/analysis tool had quickly amassed gigabytes worth of data on the hard drive. Once I found out the amount of data it was collecting from my web site, I removed the tool, deleted the database, but no disk space was freed. That’s when I decided to take the risky route and destroy all databases to recover disk space.
The server I have is running on Debian 8 and Mysql 5.5. Here is how I did it.
- I backed up all the databases that I needed using mysqldump.
- Stopped the mysql system.
- I renamed files “ibdata1”, “ib_logfile1” and “ib_logfile0” to something else (just in case). In Debian 8, they are located in /var/lib/mysql.
- Deleted all my databases. I didn’t touch mysql or performance_schema directories at all, although some advise to do so.
- Started Mysql.
- Recreated databases from mysqldump-backup files using mysql-command.
Everything went well, all databases were up and running. New ibdata1, ib_logfile0 and ib_logfile1 had been created. ibdata1 was only a fraction of the size it was before.
I wonder how many webmasters are paying for extra disk space to their hosting companies because of the way Mysql works?