Quick Tip: Delete old log-files if you use mySQL replication
There was a bit of a mess over on LyricWiki off and on for a few days. The culprit was a known bug in mySQL which messes up master-slave replication if you run out of hard-disk space (which you will if you’re using master-slave replication).
The preventative solution is to set up a daily cron-job which will find out what log the slave is using and delete all of the binary log-files that are older than that file. The alternative is an immense pile of unneeded files which will eventually cause you to run out of space and completely break your replication. To give you some idea, we filled up 100gigs of log-files from LyricWiki (which has hundreds of times as many reads as writes) in about 2.5 months.
Hope that helps!
UPDATE: I just wrote this script and figured I’d release it publicly to save others some time. You can get the code from deleteOldBinLogs.txt (that’s just a .txt so you can view the code… save it as a “.pl” file). Once you’ve filled out the “configuration” part at the top and have uploaded the file to the “/root” directory on your Master database server, add this line to your crontab file (by typing “crontab -e” into the command line):
0 4 * * * perl /root/deleteOldBinLogs.pl
That will make the script run at 4am each morning.
on May 28th, 2008 at 10:45 pm
Hey Sean,
Thanks for the update. I guessed you learned a little from our trials and tribulations at mSpoke and FeedHub, eh?
We wish you continued good fortune at LyricWiki!!
Have an excellent evening!
Dave
on May 31st, 2008 at 10:14 pm
Definitely! FeedHub was a beast to scale & the popularity after the launch made us have to scale it while under-the-gun. Great fun & a great way to force yourself to learn… everything :)
on May 31st, 2008 at 10:23 pm
[...] I mentioned that you’d need to write a script to prevent this problem from happening to you. The script would delete old, unneeded mySQL bin-log files. In the spirit of stopping this bug from affecting others, I’ve released a generic version of the script… you just have to set up a few variables in the “configuration” section at the top of the script and you should be good to go. For the code and info on how to make it run daily, see the “update” section of the post on my blog. It is, of course, free and open source. [...]