On your MySQL server, what’s is the size of /var/lib/mysql/ibdata1 ? Even if you completely drop all your databases, it won’t shrink. It surely fits a purpose: MySQL people wrote that “the fact that InnoDB tablespaces can only grow and never shrink is docummented design decision”. But the notion of a file (ibdata1 – InnoDB tablespaces) that can only grow even if you remove permanently all of your data is odd nonetheless.
The solution is not to use InnoDB. Here’s a fast way to get rid of all of it:
First we switch to MariaDB (project under the umbrella of MySQL founder Michael Widenius):
apt-get install mariadb-server mariadb-client
We make a dump of every database we want to keep:
mysqlshow # list databases
mysqldump database1 > database1.dump
mysqldump database2 > database2.dump
We add a configuration snippet to switch off innodb:
default-storage-engine = myisam' > /etc/mysql/conf.d/noinnodb.cnf
We restart the SQL server:
invoke-rc.d mysql restart
mysql -e "SHOW ENGINES" # should no longer show InnoDB
We remove databases with InnoDB tablespaces:
mysql -e "DROP DATABASE database1"
mysql -e "DROP DATABASE database2"
We recreate them with the dumps:
mysql -e "CREATE DATABASE database1"
mysql -e "CREATE DATABASE database2"
mysql database1 < database1.dump
mysql database2 < database2.dump
We remove the now useless InnoDB crap:
rm -f ib_logfile* ibdata1
And if everything is fine, we also remove the SQL dumps:
rm -f *.dump