Getting rid of the ever growing /var/lib/mysql/ibdata1 by getting rid of InnoDB itself

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:

cd /var/lib/mysql

mysqlshow # list databases
mysqldump database1 > database1.dump
mysqldump database2 > database2.dump

We add a configuration snippet to switch off innodb:

echo '[mysqld]
ignore-builtin-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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s