Problem
You need an account for connecting to your MySQL server.
Solution
Use CREATE USER
and GRANT
statements to set up the account. Then use the account name and password to make connections to the server.
Discussion
Connecting to a MySQL server requires a username and password. You may also need to 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, given no explicit hostname, mysql assumes that the server is running on the local host.
If someone else has already set up an account for you, just use that account. Otherwise, the following example shows how to use the mysql program to connect to the server and issue the statements that set 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, -u root
to connect as the MySQL root user, and -p
to tell mysql to prompt for a password:
% mysql -h localhost -u root -p
Enter password: ******
mysql> CREATE USER 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost';
Query OK, 0 rows affected (0.09 sec)
mysql> quit
Bye
If when you attempt to invoke mysql the result is an error message that it cannot be found or is an invalid command, that means your command interpreter doesn't know where mysql is installed. Use PATH
environment variable that the interpreter uses to find commands.
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; don't type any of that.
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 enter the CREATE USER
and GRANT
statements as shown. The quit command terminates your mysql session. You can also terminate a session by using an exit command or (under Unix) by typing Ctrl-D
.
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, omit the CREATE USER
statement and substitute that account for 'cbuser'@'localhost' in the GRANT
statement.
The hostname part of 'cbuser'@'localhost' indicates the host from which you'll connect to the MySQL server. To set up an account that will connect to a server running on the local host, use localhost, as shown. If you plan to connect to the server from another host, substitute that host in the CREATE USER
and GRANT
statements. For example, if you'll connect to the server from a host named myhost.example.com, the statements look like this:
mysql> CREATE USER 'cbuser'@'myhost.example.com' IDENTIFIED BY 'cbpass'; mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'myhost.example.com';
It may have occurred to you that there's a paradox in the procedure just described: to set up a cbuser account that can connect to the MySQL server, you must first connect to the server so that you can execute the CREATE USER
and GRANT
statements. I'm assuming that you can already connect as the MySQL root user because CREATE USER
and 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 create the cbuser account for you.
MySQL Accounts and Login Accounts
MySQL accounts differ 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 very likely have different passwords. It also means you don't create new MySQL accounts by creating login accounts for your operating system; use
CREATE USER
andGRANT
instead.
After creating the cbuser account, verify that you can use it to connect to the MySQL server. From the host that was named in the CREATE USER
statement, run the following command to do this (the host named after -h
should be the host where the MySQL server is running):
% mysql -h localhost -u cbuser -p
Enter password: cbpass
Now you can proceed to create