Managing large MySQL databases on a GB budget
Hello World of blogging!
I just wanted to quickly mention something about how MySQL handles out-of-disk-space situations when importing an SQL file. Here’s my situation:
- I have an 80GB hard disk,
- 7GB free space,
- and am loading a 2GB SQL database dump .
No problem, right? Well not always. I started it importing 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. But here’s the interesting thing that I discovered. After stopping the import on the command line, MySQL gave this error message:
ERROR 20 (HY000) at line 2373: Dis is full writing '.\mydatabase\mytable.MYD' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs
So apparently, it had been waiting all night rather than crashing. Unfortunately, I stopped it before freeing disk space and restarted it from the beginning. But I guess that’s good, because otherwise I would have never known that MySQL did this!