MySQL Cookbook, 2nd Edition

Setting Up a MySQL User Account

Problem

You need to create an account to use for connecting to the MySQL server running on a given host.

Solution

Use the GRANT statement to set up the MySQL user account. Then use the account’s name and password to make connections to the server.

Discussion

Connecting to a MySQL server requires a username and password. You can also specify the name of the host on which the server is running. If you don’t specify connection parameters explicitly, mysql assumes default values. For example, if you specify no hostname, mysql typically assumes that the server is running on the local host.

If someone else has set you up with an account, just use that account to create and use databases. If not, the following example shows how to use the mysql program to connect to the server and issue a GRANT statement that sets up a user account with privileges for accessing a database named cookbook. In the commands shown, the % represents the prompt displayed by your shell or command interpreter, and mysql> is the prompt displayed by mysql. Text that you type is shown in bold. Nonbold text (including the prompts) is program output; you do not type it. The arguments to mysql include -h localhost to connect to the MySQL server running on the local host, -p to tell mysql to prompt for a password, and -u root to connect as the MySQL root user.

%mysql -h localhost -p -u root
Enter password: ******
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
Query OK, 0 rows affected (0.09 sec)
mysql> QUIT
Bye

If you get a message indicating that mysql cannot be found or that it is a bad command when you enter the mysql command shown on the first line, see What to Do if mysql Cannot Be Found. Otherwise, when mysql prints the password prompt, enter the MySQL root password where you see the ******. (If the MySQL root user has no password, just press the Enter (or Return) key at the password prompt.) Then issue a GRANT statement like the one shown.

To grant the cbuser account access to a database other than cookbook, substitute the database name where you see cookbook in the GRANT statement. To grant access for the cookbook database to an existing account, substitute that account for 'cbuser'@'localhost'. However, in this case, omit the IDENTIFIED BY 'cbpass' part of the statement because otherwise you’ll change the existing account’s current password.

The hostname part of 'cbuser'@'localhost' indicates the host from which you’ll be connecting to the MySQL server when you want to access the cookbook database. To set up an account that will connect to a server running on the local host, use localhost, as shown. If you plan to make connections to the server from another host, substitute that host in the GRANT statement. For example, if you’ll be connecting to the server from a host named xyz.com, the GRANT statement should look like this:

mysql>GRANT ALL ON cookbook.* TO 'cbuser'@'xyz.com' IDENTIFIED BY 'cbpass';

It may have occurred to you that there’s a bit of a paradox involved in the procedure just described. That is, to set up a cbuser account that can make connections to the MySQL server, you must connect to the server first so that you can issue the GRANT statement. I’m assuming that you can already connect as the MySQL root user, because GRANT can be used only by a user such as root that has the administrative privileges needed to set up other user accounts. If you can’t connect to the server as root, ask your MySQL administrator to set up the cbuser account for you.

After the cbuser account has been set up, verify that you can use it to connect to the MySQL server. From the host that was named in the GRANT statement, run the following command to do this (the host named after -h should be the host that is running the MySQL server):

%mysql -h localhost -p -u cbuser
Enter password: cbpass

Now you can proceed to create the cookbook database and tables within it, as described in Creating a Database and a Sample Table. (To make it easier to start mysql without specifying connection parameters each time, you can put them in an option file. See Specifying Connection Parameters Using Option Files.)

MySQL Accounts and Login Accounts

MySQL accounts are different from login accounts for your operating system. For example, the MySQL root user and the Unix root user are separate and have nothing to do with each other, even though the username is the same in each case. This means they are very likely to have different passwords. It also means you cannot create new MySQL accounts by creating login accounts for your operating system; use the GRANT statement instead.