Extracting a Database From a mysqldump File

Restoring a single database from a full dump is pretty easy, using the mysql command line client’s –one-database option:

But what if you don’t want to restore the database, you just want to extract it out of the dump file? Well, that happens to be easy as well, thanks to the magic of sed: […]

Don’t put a NULL in the IN clause in 5.1

There seems to be an optimizer problem in 5.1, if you put a NULL in the IN clause of a SELECT. For example, given the following table:

Compare these two EXPLAINs:

In the query with the NULL, it does a full table scan. So, if you’ve run into this problem under MySQL 5.1, […]

JOIN and comma precedence

Here’s a little something that might trip you up occasionally. Have a look at this test scenario:

Q1 and Q2 will produce the same result, right? Wrong! As of MySQL 5.0.12, per the SQL standard, JOIN has had higher precedence than comma ‘,’. So, you get the following:

This is because, in earlier […]

Tools of a Support Engineer

So, you’ve emailed MySQL Support, they’re working on the problem you’re having. How are they working? What tools do they use? Well, here’s my list: IRC – All MySQL Support Engineers work on IRC, it’s our main communication medium. While you’re only getting emails from one Engineer, it’s quite likely they’re consulting with several others […]

Backing up permissions for individual databases

Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump:

The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to help you out.

Then, re-loading the permissions onto the […]