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!

Comments

Popular posts from this blog

Switching from Notepad++ FTP to Visual Studio Code FTP

jTable to edit and process MySQL table using CodeIgnitor : Part 2

Simple PHP Script to read Tab Delimited File