How to create User Account for connecting to MySQL Database

MySQL Database User Account Creating

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 data­base 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 envi­ronment variable that the interpreter uses to find commands.

In the commands shown, the % represents the prompt displayed by your shell or com­mand 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 substi­tute that account for 'cbuser'@'localhost' in the GRANT statement.

The hostname part of 'cbuser'@'localhost' indicates the host from which you'll con­nect 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 as­suming 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 and GRANT 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

 

 

Вас заинтересует / Intresting for you:

Using MySQL Connector/Python f...
Using MySQL Connector/Python f... 1839 views Ирина Светлова Tue, 29 Dec 2020, 06:00:21
Installing MySQL 8 using YUM /...
Installing MySQL 8 using YUM /... 1096 views Doctor Thu, 06 Dec 2018, 06:47:43
Simple MySQL Querying from Pyt...
Simple MySQL Querying from Pyt... 1251 views Valerij Sun, 26 Aug 2018, 12:55:56
Determining characteristics of...
Determining characteristics of... 1297 views Valerij Sun, 26 Aug 2018, 12:52:37