Magoo's Wise Words
MySQL Quickstart Guide

P
A
G
E

T
H
R
E
E

O
F

F
O
U
R

MySQL user and database setup

Now that we have MySQL installed, we need to do some basic setup. I'm going to use setting up a WordPress database as an example, but the process is the same for other applications.

Creating MySQL users

The first thing we need to do is create a new user. You could have your application access MySQL as the root user, but that has serious security risks. If this is just a testing server and you aren't worried about security, you can skip this step and use the root user. If you are running a public server or are going to have multiple people accessing this database, I highly recommend creating a new user.

To create a user, login to MySQL. To do that, open up a command window (available in Windows 2000 and XP by typing “cmd” into the run command or in Windows 98 and ME by typing “command” into the run command.) Change to the directory that you installed MySQL to, and then change to the \bin subdirectory. At the prompt, type “mysql –user root –p”. It will then prompt you for the password. There is no default password, so just push enter. You should now see the mysql> prompt. This is it; You are in MySQL.

Let’s create user “WordPress.” The easiest way to create a user is with the “grant” command. As an example of how the GRANT command works, you could type

GRANT ALL PRIVLIGES ON *.* TO ‘wordpress’@’localhost’
IDENTIFIED BY ‘password’;

Yes, that is a return in the middle of that command. The command doesn’t end until the semi-colon. Most mysql commands can be given on multiple lines and need to be terminated with a semi-colon. The command above creates the user “wordpress”, who will access the database from applications on this computer (localhost). His password is “password”, and he has all privileges on all databases (signified by *.*). Now, this user makes a great example, but using this user for your weblog would have similar security problems as using the root user.

Let's make a user with more limited permissions. Type the same command with one slight modification:

GRAT ALL PRIVLIGES ON blogname.* TO ‘wordpress’@’localhost’
IDENTIFIED BY ‘securepassword’;

Note that the single quotes are a necessary part of the command. This command gives all privileges on the blogname database (which may or may not exist yet) to user wordpress (who may or may not have existed before this command was executed.) We now have a user with acceptable permissions to run our database.

You could now either create the database as the root user, or logout and log back in as user ‘wordpress’ to create it. Logging out and back in serves two purposes. It gets us more comfortable with logging in, and it will prove weather or not our new user is working. Log out by typing QUIT. Log back in as wordpress by typing “mysql –u wordpress –p”. It will ask for your password, and if all goes well, log you in.

Creating MySQL databases

Now, let’s create the database WordPress will need. Type the following command:

CREATE DATABASE blogname;

Databases in MySQL are case sensitive, so stay on your toes with that. Now, let’s see if it actually did what we wanted it to. Type

SHOW DATABASES;

You should see a list of all the databases managed by this instance of mysql. There will be a few default databases created by the installation process, and the one we just created. Type

USE blogname;

If you get the response “DATABASE CHANGED,” then this user and database are both set up and ready for WordPress (or whatever software you want) to use. All you have to do now is configure your software to use this database and username. Instructions on how to do that should be included in your application's installation guide or documentation.

Next =>[Magoo's MySQL Tutorial - Page 4, MySQL Command Quick-reference]