Restoring a single database from a full dump is pretty easy, using the mysql command line client’s –one-database option: mysql> mysql -u root -p –one-database db_to_restore < fulldump.sql 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… Continue reading Extracting a Database From a mysqldump File
Category: MySQL
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: CREATE TABLE foo ( a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a) ); Compare these two EXPLAINs: mysql> EXPLAIN * FROM foo WHERE a IN (160000, 160001, 160002)\G… Continue reading Don’t put a NULL in the IN clause in 5.1
JOIN and comma precedence
Here’s a little something that might trip you up occasionally. Have a look at this test scenario: USE test; DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS c; CREATE TABLE a ( a INT ); CREATE TABLE b ( b INT ); CREATE TABLE c ( c INT );… Continue reading JOIN and comma precedence
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… Continue reading Tools of a Support Engineer
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: shell> mysqldump -u root -p –databases db1 db2 … > backup.sql The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to… Continue reading Backing up permissions for individual databases