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, health 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, case 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.

3 thoughts on “Quick Tip: Delete old log-files if you use mySQL replication

  1. 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

  2. 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 :)

  3. Pingback: Don’t get pwned like we did! « LyricWiki.org

Leave a Reply

Your email address will not be published.