Monday 10 October 2011

How to reduce the ibdata space in Mysql

Dump the Whole Database

This is the easiest method How to reclaim the ibdata space

In fact, this is the method I used to solve the problem. It requires much more space and time but it’s maybe the easiest one. So here it is:

  1. Dump all databases by using:

    mysqldump -uroot -proot --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > all-databases.sql
  2. Stop the MySQL server;

/etc/init.d/mysql stop


  1. Rename or remove the MySQL data directory and create an empty one with the same name and permissions;

i.e In case the datadir is /var/lib/mysql then remove the all content of that.

else remove /var/lib/mysql and create other one with the same permission and same name.

  1. Make the appropriate changes in my.cnf.


  1. Re-initialize the database with the following command.

    sudo -u root mysql_install_db
  2. Start the MySQL server;

/etc/init.d/mysql start


  1. Get into the ‘mysql‘ console and type:

    SET FOREIGN_KEY_CHECKS=0;
    SOURCE all-databases.sql;
    SET FOREIGN_KEY_CHECKS=1;
  2. Restart the MySQL server.

At this point of time everything should be running fine.

No comments:

Post a Comment