Reduce the size of MySQL ibdata1 on OSX

2010-11-18

So I finally figured out why my TimeMachine backups were becoming bigger and bigger, 3-4GB backing up every day when I come home from work… It seems that my MySQL database file (/usr/local/mysql/data/ibdata1) keeps getting larger and larger, unnecessarily, even if I deleted databases, tables, etc. It seems even if I only update a few rows in a table in a small database, the ginormous idbdata1 file grows and then get marks as a candidate for backup into TimeMachine. Ugh.

I did some digging and found this interesting tutorial on how to clean up InnoDB storage files. Here I’ll explain what I specifically did on my OSX 10.6.5 machine with MySQL v5.1.38.

If you’re not a cowboy, stop MySQL, backup all files, then start MySQL again (I used the System pref to stop/start MySQL, feel free to use the command-line instead):
```
$ sudo cp -R /usr/local/mysql/data /usr/local/mysql/data.bak
```

Export all data from MySQL:

$ mysqldump -u root -p --all-databases > alldatabases.sql

Drop databases in MySQL (except “mysql” and “information_schema”):

$ mysql -u root -p
mysql> show databases;
mysql> drop database XXXX;

or use this great one-liner to delete all databases, modded a bit so it would work for me:

# measure twice, cut once... make sure we are deleting what we should be deleting
$ mysql -u root -p  -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema | awk '{print "drop database " $1 ";select sleep(0.1);"}'
# now delete them
$ mysql -u root -p  -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema | awk '{print "drop database " $1 ";select sleep(0.1);"}' | mysql -uroot -ppassword

Stop MySQL again
Add the following to the [mysqld] section in /etc/my.cnf:
```
[mysqld]
innodb_file_per_table
```

Remove the files:

$ sudo rm /usr/local/mysql/data/ibdata1
$ sudo rm /usr/local/mysql/data/ib_logfile0
$ sudo rm /usr/local/mysql/data/ib_logfile1

Start MySQL again
Reload the databases from the sql dump-file:
```
$ mysql -u root -p < alldatabases.sql
```
Verify that your database(s) are working properly

Delete the backup

$ sudo rm -rf /usr/local/mysql/data.bak

Done!

After this modification my TimeMachine backups are much more reasonably-sized. Very nice!

Furthermore, I noticed the same infamously large ibdata1 file on our continuous-integration build server at work: it was 40GB! I applied the same modifications as above. That server runs Ubuntu 10.10, the MySQL files are located in /var/lib/mysql/data, but otherwise the steps are pretty much the same. Even build-server bongo seems snappier now, and the total size of the MySQL data folder is 1GB (insane that there was 39GB of “dead” data in the ibdata1-file…)

Very happy.