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

3. 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.

4. Make the appropriate changes in my.cnf.

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

   sudo -u root mysql_install_db

6. Start the MySQL server;

/etc/init.d/mysql start

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

   SET FOREIGN_KEY_CHECKS=0;
   SOURCE all-databases.sql;
   SET FOREIGN_KEY_CHECKS=1;

8. Restart the MySQL server.

At this point of time everything should be running fine.