You are here: Home how to... Shrinking ibdata

Shrinking ibdata

After doing some high performance MySQL work, i found that my big databases increased the size of ibdata (expected), but dropping the databases did not reduce ibdata again (oh?).

Here's what i did to remedy the problem.

Now i did this on the local database and this is my development machine. NOT a production machine! What i'm saying is that you should try this somewhere safe first. My system is a Kubuntu system. If this causes problems on other systems please let me know and i'll make adjustments to this doc.

With that out of the way here's what i did as root:

  • Dump all databases
mysqldump --opt --all-databases > database.sql

This assumes the default permissions of root@localhost being able to connect and do anything they want without a password. If this has been changed add -u user and -p to be prompted for the password.

  • Shut down mysql
/etc/init.d/mysql stop

This works on most Linux boxes. Your system may be different. NOTE: Be sure mysql stopped succesfully. It's a good idea to tail the yourhost.youdomain.err file in your mysql data directory while doing all of this as it keeps you informed on what's going on.

  • Change into your mysql data directory and move ibdata and friends out of the way to a safe backup location.
mv ib* /path/to/backup/directory

You'll have to find where the mysql data directory is on your installation.

  • You also need to move the directories of all your innodb databases otherwise the restore fails.
mv myinnodb /path/to/backup/directory

Do this for each of your innodbs.

  • Restart mysql
/etc/init.d/mysql start

  • Pipe the data back in as root
mysql> \. database.sql

  • Dump all databases again to a different file
mysqldump --opt --all-databases > database-after.sql
diff database.sql database-after.sql

The diff should come back the same indicating you did something right. :)

  • Test whatever systems use the database to insure that things are still working. When everything is fine you can delete the old bloated ibdata file.
Document Actions

Copyright © 2007-2015 Mario Theodoridis. All rights reserved. Content licensed under AFL.   Impressum   Datenschutz
Content from the underlying Plone CMS is © 2000-2009 by the Plone Foundation