Shrink/Purge/Delete ibdata1 file in MySQL

While dealing with large data, I realised ibdata file was rapidly increasing. I did some research and found out ibdata1 not shrinking is a particularly annoying feature of MySQL. The ibdata1 file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump.

Here are the steps for doing so:

1. Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases

   mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
   

2. Drop all databases except the above 2 databases

   (in MySQL console) DROP DATABASE [database_name]
   

3. Stop mysql

   sudo service mysql stop
   sudo service mariadb stop
   

4. Delete ibdata1 and ib_log files

   sudo rm /var/lib/mysql/ibdata1 
   sudo rm /var/lib/mysql/ib_logfile 
   

5. Start mysql

   sudo service mysql start
   sudo service mariadb start
   

6. Restore from dump

   (in MySQL console) create database [database_name]
   mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql
   

Thats it!