We have a pretty normal single master MySQL setup.
Since we have a read heavy application it makes sense. Everyone writes to the master and reads from a large pool of read-only slaves.
But, with more and more slaves it becomes hard to manage what nodes read from what slaves. It can get unmanageable pretty quick when configuring the app servers.
If we lose a MySQL slave, we have to redirect all of those servers to the new one… which descends into a bunch of temporary app config or DNS changes that sometimes are not temporary :/
The stuff in this article isn’t my bit of magic, but it is what we have been using in one of our three datacenters for about a year now and am hoping to migrate the others to the scheme. My boss and an ex co-worker set it up an I think it is pretty nice.
So, expanding upon my previous post, I wrote a simple python script that would kill all queries running on a MySQL instance based on a host wildcard or runtime of a query.
Yes, yes, I know about mk-kill, but I enjoy writing little scripts. Writing trivial scripts is always a great way to get better.
Just a quick protip. I’m not a DBA, but I’m about to whack you with the clue hammer.
I had an errant application node hammering a MySQL slave with metric butt loads (engineering term) of junk queries that were locking the slave up.
If you find yourself in a similar position without your own surly DBA around, I’ll save 20 minutes of googling:
As a follow up to my previous post celebrating World Backup Day, I give you my MySQL LVM Snapshot script.
You can read more about the idea and its pluses and minuses here.
At each of our datacenters we have a MySQL slave dedicated to being a backup node.
It doesn’t take reads from applications servers, it just gets updates from it’s local master then makes a nightly LVM snapshot using a version of the script below.
(It is also running a second instance to backup our MogileFS MySQL instance using a modified version of the script too)
World Backup Day was last Thursday and in its honor I uploaded a few of my backup scripts to my github repository.
I thought I’d start off with modified versions of the scripts I use in production at Outbrain to backup my Hadoop NameNode and Hive Metastore.
First: OMFG WTF ARE YOU NOT BACKING UP YOUR NAMENODE AND HIVE METASTORE?
Second: No really, WTF IS WRONG WITH YOU!?!
So I wrote in an earlier post about alt-installing Python 2.6 from source on CentOS, which was easy enough. But, this made it more difficult to maintain and deploy as well as add modules. So, I was lucky enough to come across a nice little yum repository hosted by Rizwan Kassim (Geekymedia.com) that contained an RPM that would do the alt-install work for me
I’m aware that EPEL has a Python 2.6 package, but the Geekymedia RPMs have a whole flurry of modules you can add as well as an RPM for setuptools which will make your life immeasurably easier when running Python 2.4 and 2.6 side-by-side for installing python packages.
The only problems with the Geekymedia RPMs are that the binary packages are all 32-bit (I’m running servers here folks!) and I was unable to get the MySQL-python26 one to work right for me.
So, let’s get down to business.