Managing large MySQL databases on a GB budget

What happens when MySQL runs out of disk space while importing a large SQL file?

Take a situation I experienced:

  • 80GB hard disk,
  • 7GB free space,
  • Loading a 2GB SQL database dump.

No problem, right? I started the import and went to bed. In the morning it hadn’t finished, and I was getting out-of-space notifications on my taskbar.

Thankfully, I’d seen this before and knew all I had to do was run this simple query:


While I don’t fully understand all the ins-and-outs of how it works, MySQL has a binary log that can grow considerably larger than your database. This simple query empties that log.

I ran that, and voila, 5GB free instantly.

All this led to an interesting discovery: after stopping the import on the command line, MySQL gave this error message:

ERROR 20 (HY000) at line 2373: Disk is full writing '.\mydatabase\mytable.MYD' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs

Apparently, it had been waiting all night rather than crashing, and would have continued had I freed up disk space before stopping the import.

But I guess it’s good that I did, because otherwise I would have never known that MySQL did this!

Written on December 13, 2008