Mini-hiatus (maybe), MySQL

First, I want to mention that I may be more scarce today and tomorrow, just need to take a bit of a break from all the gaming. x.x

Sometime earlier (Sunday) I had to go and work on setting up a database for my sis. Thing is, I set up a shell account for her completely outside cpanel, so I had to also remember how to set up MySQL accounts through the command line.

Just so I won't have to look it up again, here's some useful commands.

First, to login to mysql. This depends on whether I've set mysql up for passwords or not (yes, I did). So to log in I have to at least type

mysql -p

or if I'm logging in as a specific user,

mysql -u [username] -p

Okay, that's fine and dandy, now I'm in mysql and I want to see a list of all my databases. That would be

mysql> SHOW DATABASES;

And if I wanted to start manipulating stuff in a specific database I'd type

mysql> USE dbname;

If I wanted to create a new database, though...

mysql> CREATE DATABASE dbname;

That creates a database with no tables, but I still need to create (or, if the user is already created, specify) a mysql user that can access that database.

Both of those things are covered with the GRANT command. The following statement will automatically create a user with the specified username if it isn't already existent (note: you can use shell account usernames here, keeps the user from having to use the -u option when starting mysql from the shell prompt).

mysql> GRANT ALL ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

This particular example will grant all priveleges to 'username' for all tables in dbname. I didn't actually have to use the 'WITH GRANT OPTION' part, though. Think you can also put ALL PRIVILEGES instead of just ALL, but I didn't do that either.

Note: REVOKE is the opposite of GRANT, but REVOKE does not remove the user. That's done with DELETE.

mysql> DELETE FROM mysql.user WHERE user='user_name@localhost'

Okay now what if you already created your user but want to change their (or your) password? You use this:

mysql> SET PASSWORD = PASSWORD('newpass');

if you're changing your own mysql account's password, or

mysql> SET PASSWORD FOR username = PASSWORD('newpass');

Finally to ensure new privileges are live immediately, type

mysql> FLUSH PRIVILEGES;

And there, I think that's all the mysql I needed to know to just setup my sister's database manually. She wanted it for Wordpress, and most apps of that kind automatically initialize all the SQL tables for you. All you need to put in is the username, host (usually localhost), password and dbname.

Some other possibly useful commands...

For listing mysql users:

mysql> SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user

For listing database info:

mysql> SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db

To list content of a single table:

mysql> DESCRIBE tablename;

Okay, that's all for now. Hopefully this post helps me later (I mean, it's easy enough to google mysql commands, but better if you can have them all in one place!)

Lastly, a reminder: need to get mysql 5 installed soon. D: But there's a lot that needs doing. Hope I won't chicken out...