MySQL recipe #1

[elfs]

I’m putting this here because I’m tired of losing it.  I hate having to look up every time the exact same recipe for “how do I create a new empty database for Django or Rails in MySQL?’  It’s in two different sections of the O’Reilly MySQL in a Nutshell book, and while I have both bookdarted and know where to find them, it’s nice to have them in just one place!  The  phrase ‘to X’ on line 4 is the database username, and the ‘identified by’ string there is your database password, in case you were wondering.

# mysql -u admin -p
password:
mysql> create database secretproject;
mysql> grant all on secretproject.* to secretproject identified by 'redacted';
mysql> flush privileges;
mysql> quit;
#

This entry was automatically cross-posted from Elf's technical journal, ElfSternberg.com

Comments

[duskwuff.livejournal.com]

Incidentally, you don't need to FLUSH PRIVILEGES unless you're altering the permission tables (mysql.*) directly.

[elfs.livejournal.com]

That's good to know. I've always done it out of habit; it's what it shows in the O'Reilly book, and it's not clear why you would or wouldn't do it. The fact that it always says "0 rows updated" always puzzled me.

[duskwuff.livejournal.com]

The fact that it always says "0 rows updated" always puzzled me.

Probably because every statement that hits the server has to either return a count of result rows (SELECT, DESCRIBE, SHOW, etc) or a count of modified rows (UPDATE, DELETE, etc). Since FLUSH PRIVILEGES doesn't actually touch any rows, the 0 count is arbitrary.

[wookie-cd.livejournal.com]

*grin* I was wondering whether I should post just to point that out - luckily I thought up another reason too.

As I understand it, Flush privileges tells mysql to discard its compiled cache of the users/permissions/etc tables e.g. mysql.user, mysql.tables_prive etc - this causes it to recheck those tables when processing new authentication/authorisation events. This makes the command most useful after altering one of the mysql tables manually.

Sometimes I do something directly to mysql.users in which case the flush is needed to make the changes take effect. Grant, on the other hand, is like a meta-command that issues the flush on the user's behalf after updating the mysql tables.

Personally I'm a big fan of the Mysql documentation. I often have the PDF open while coding - http://dev.mysql.com/doc/#refman - section 12.5.1 deals with account management statements.