I recently needed to configure mysql failover on some of our test machines. Thanks to Sheeri’s helpful blog entry which provides a simple failover lua script, configuring failover is a simple matter. However, the machines are running centos-5.2 and centos doesn’t provide an rpm for mysql-proxy. This blog entry describes how to build your own.

The latest mysql-proxy (0.6.1) is apparently not backward-compatible with 0.6.0 and earlier. It incorrectly handles the case when one of the backend machines is down. Instead of just marking it as down, it errors out completely. This makes it rather difficult to use it for failover scenarios. People have complained about this for a while. Bugs 34793 and …

I have a small EC2 instance running with a 25GB EBS volume attached. It has a database on it that I need to manipulate by doing things like dropping indexes and creating new ones. This is on rather large (multi-GB, millions of rows) tables. After running one DROP INDEX operation that ran all day without finishing, I killed it and tried to see what was going on. Here’s the results of the first 10 minutes of testing:

-bash-3.2# dd if=/dev/zero of=/vol/128.txt bs=128k count=1000
1000+0 records in
1000+0 records out
131072000 bytes (131 MB) copied, 0.818328 seconds, 160 MB/s

This looks great. I’d love to get 160MB/s all the time. But wait! There’s more!

-bash-3.2# dd if=/dev/zero of=/vol/128.txt bs=128k count=100000
dd: writing `/vol/128.txt': No space left on device
86729+0 records in
86728+0 records out
11367641088 bytes (11 GB) copied, 268.191 seconds, 42.4 MB/s

Ok, well… that’s completely miserable. Let’s try …

Short Version: You can find a fantastic video here about bundling customized AMIs and registering them with Amazon so that you can launch as many instances of your new AMI as you want. The video is so good that I don’t bother writing out the steps to do the bundling (it would be pretty darn long). These are some notes about launching an AMI, customizing it, and mounting an EBS volume to it (the video linked above doesn’t cover EBS). Also, check out the ElasticFox tool which is a very good GUI for doing simple EC2 operations. Nice if you’re just getting started or doing some simple tests.

There are two ways you can go about creating a custom machine image (AMI) for use with Amazon EC2: You can create an image locally by dd’ing to a file, mounting it with “-o loop” creating a filesystem on it, …

Hi all,

The schedule for PyWorks has been posted! I’m really excited about three things:

1) there are some really cool talks that I’m looking forward to attending. There are a couple of sysadmin-related talks, AppEngine, TurboGears, Django, and an area I’ve been especially slow to move into: testing (I know, shame on me). There’s lots more so be sure to check it out.

2) the conference scheduling process is over

3) I get to meet a lot of people face-to-face that I’ve worked with in the past on Python Magazine developing articles, or interacted with on IRC, etc. One thing I like about conferences surrounding open source technologies is you get to thank people face-to-face for the sweat they poured into some of the tools I use regularly. Mark Ramm, Kevin Dangoor, Michael Foord, Brandon Rhodes, and a collection of Python Magazine authors …

This is the last week to participate in a database usage survey. If you haven't already done so, please take a few minutes to answer 25 questions.

LewisC

Technorati : database, mysql, survey

I have a lot of interaction with publishing types. I write a lot, and I edit some, and I do tech reviews and stuff for some publishers, and I co-authored a book, and I’ve worked on two magazines, and a newspaper, and I’m generally fascinated by the technical book market and stuff like that. I’m also someone who is lucky enough that his job is also his hobby. I work in technology, and am always doing something technology related at home in my spare time. Needless to say, I read tons upon tons of technical books.

I almost never post book reviews, in spite of the fact that I read all of these books. Why? Well, to be honest, I couldn’t tell you. It just hasn’t occurred to me to write a book review. Could be because I don’t really value book reviews too much myself I guess. I mean, if there’s a really obvious consensus across a huge number of reviews, I might be swayed. But in general, I find that book reviews are too often the …

Less than two weeks ago, I posted my Database Survey. As of just a few minutes ago, I have had 215 responses. That's pretty awesome. I'd like to get at least twice that though.

I haven't looked deeply at it yet to see if there are any trends. I think it will be best to wait until the survey is closed. I did look at some of the responses, kind of as a quality check. Looks like MySQL is fairly well represented. I didn't see any DB2 responses (for primary database). I did see plenty of Oracle and a few Postgres.

I will leave it up for another 2 1/2 weeks (for a total of 4 weeks). If you haven't taken it yet, please do so if you get a few minutes. It only takes 5-10 minutes as there is only 25 questions.

Also, if you have a blog, post on forums (without spamming), or have any other ways to spread the word, I would …

Infoworld published the 2008 Bossies, Best Of Open Source Software. There are 8 categories and none of them are database:

• Collaboration
• Developer tools
• Enterprise applications
• Networking
• Platforms and middleware
• Productivity applications
• Security
• Storage

I had to look through several of them before I found the database category under Platforms and middleware. Slide 4 is the magic slide:

It says:

Database

While SQLite3 is extremely convenient for development and …

If you decide to copy over one table from MySQL installation to another installation, this could be done as simple as copying over your-table-name.* in data directory. Ofcourse, to do this, the database should not be running at the time of copying.

For example, for tables created by MyISAM storage engine, the files to be copied for table mytable are mytable.frm, mytable.MYD and mytable.MYI. The ".frm" file contains table definition, the ".MYI" contains info about index and ".MYD" contains data.

If you do this kind of stuff often, you may want to dump the header of the ".frm" header file. I just wrote a script for this. You can download this php script "frmdump" from here. Note that it is a php script, but meant to be run from command line (not to be used as web page) :

   $  ./frmdump   mytable.frm
  
Dumping  mytable.frm using .frm header format ... …

If you decide to copy over one table from MySQL installation to another installation, this could be done as simple as copying over your-table-name.\* in data directory. Ofcourse, to do this, the database should not be running at the time of copying.

For example, for tables created by MyISAM storage engine, the files to be copied for table mytable are mytable.frm, mytable.MYD and mytable.MYI. The ".frm" file contains table definition, the ".MYI" contains info about index and ".MYD" contains data.

If you do this kind of stuff often, you may want to dump the header of the ".frm" header file. I just wrote a script for this. You can download this php script "frmdump" from here. Note that it is a php script, but meant to be run from command line (not to be used as web page) :

   $  ./frmdump   mytable.frm
  
Dumping  mytable.frm using .frm header format ... …