MySQL Cookbook

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 that 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 where 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 the server is running on the local host.

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. 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. Text that you type is shown in bold; non-bold text is program output:

% 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

After you enter the mysql command shown on the first line, if you get a message indicating that the program cannot be found or that it is a bad command, see Recipe 1.8. 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 Return at the password prompt.) Then issue a GRANT statement like the one shown.

To use a database name other than cookbook, substitute its name where you see cookbook in the GRANT statement. Note that you need to grant privileges for the database even if the user account already exists. However, in that case, you’ll likely want to omit the IDENTIFIED BY 'cbpass' part of the statement, because otherwise you’ll change that account’s current password.

The hostname part of 'cbuser'@'localhost' indicates the host from which you’ll be connecting to the MySQL server 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 as cbuser 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 user 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 issue the GRANT statement for you. Once that has been done, you should be able to use the new MySQL account to connect to the server, create your own database, and proceed from there on your own.

MySQL Accounts and Login Accounts

MySQL accounts and login accounts for your operating system are different. 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.