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