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'; ...