BAZOMBO: a discursive miscellany of topics

Things I have learned about MySQL today

Monday, October 29 2007

• If you use InnoDB tables (and who doesn’t want referential integrity and row-level locking?) MySQL will keep your data in files that only ever grow, but never shrink. All MySQL does when you delete data is mark the space free, but as far as the file system is concerned, your disk usage is unchanged.
• By default, your InnoDB tables will all live in a file called ibdata.
• There is no easy, risk-free way to shrink the ibdata file.
• This is a problem when you are trying to import a large database on your workstation and you desperately need free disk.
• You lose a crapload of disk to MySQL’s binary log files, and bugger me, your failed imports from running out of disk before have paradoxically left very large ones lying around.

This is one way to deal with it. Don’t forget to flush privileges after the restore if you want to have all your users working again. Then you can do a mysqladmin refresh, which flushes the database and starts new log files, making it safe to delete all the older ones (assuming that this is your workstation and you won’t need to replay them).

no comments

Tags: mysql ~ annoyance

---