Shrink/Purge/Delete ibdata1 file in MySQL

Saturday, November 14, 2015
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!

No comments: