I was doing some work administering MySQL databases earlier in the week. I haven’t done much with MySQL in over half a year since we use Postgres as the backend for phuser.com, needless to say I’d forgotten a lot of the commands! Here are a few MySQL tips which I’m putting up here for my own reference but if anyone else finds them useful then that’s great!

First thing I had to do was create a database, for this example we’ll call the DB stats.

CREATE DATABASE STATS

Normally you will want to create a new user to access your DB, here’s how to do this:

GRANT All PRIVILEGES ON stats.* TO ’statsBoy’@'localhost’ IDENTIFIED BY ‘rubbishPassword’;

Here I have created a new user called “statsBoy” with password “rubbishPassword”. I’m giving statsBoy all privileges to the DB and I’m only letting statsBoy connect from localhost so he can only to connect from the server the DB is on. Don’t forget to put the .* on the end of your DB otherwise you won’t be able to access your DB tables (this has screwed me over before!).

After doing any granting you’ve got to flush your privileges for them to take effect:

FLUSH PRIVILEGES;

Doing this will tell you “Query OK, 0 rows affected” it lies! Rows have been affected and your privileges should work now. I normally log on using the mySQL commandline interface and check.

Now my earlier password wasn’t very good, to change it do this:

SET PASSWORD FOR ’statsBoy’@'localhost’ = PASSWORD(’Rd1×3$k8′);

No flushing of privileges is required here. Well that’s all for today MySQL maniacs, I’m sure they’ll be more commandline Database Administration fun soon!

// Pete Graham