Create user in mysql

CREATE USER Syntax

CREATE USER user_specification
    [, user_specification] ...

user_specification:
    user [IDENTIFIED BY [PASSWORD] 'password']

The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in the mysql.user table and assigns the account no privileges. An error occurs if the account already exists.
Each account name uses the format described in Section 6.2.3, “Specifying Account Names”. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

If you specify only the user name part of the account name, a host name part of '%' is used.
The user specification may indicate how the user should authenticate when connecting to the server:

• To enable the user to connect with no password (which is insecure), include no IDENTIFIED BY clause:
  

  CREATE USER 'jeffrey'@'localhost';

• To assign a password, use IDENTIFIED BY with the literal plaintext password value:
  

  CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

• To avoid specifying the plaintext password if you know its hash value (the value that PASSWORD() would return for the password), specify the hash value preceded by the keyword PASSWORD:
  

  CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';

For additional information about setting passwords, see Section 6.3.5, “Assigning Account Passwords”.

Important

CREATE USER may be recorded in server logs or in a history file such as ~/.mysql_history, which means that cleartext passwords may be read by anyone having read access to that information. See Section 6.1.2, “Keeping Passwords Secure”.

Important

Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.

How to create new user account in mysql ?

Mysql user creation – setting up a MySQL new user account

When you try to access MySQL database server from client such as mysql or even programming language such as php or perl you need a user account. MySQL has sophisticated user management system that controls who can access server and from which client system. It uses special tables in mysql database. In order to create a new user account you need a MySQL root account password.
Next you need to use the GRANT SQL command to set up the MySQL user account.
Finally, use the account's name and password to make connections to the MySQL server.
Please note that MySQL user accounts are different from UNIX/Linux login accounts. For example, the MySQL root user and the Linux/Unix root user are separate and have nothing to do with each other, even though the username is the same in each case.

Procedure for setting up a MySQL user account

Login in as mysql root user (at shell prompt type following command:):

$ mysql -u root -p

OR

$ mysql -u root -h myserver-sever.com -p

Create a new mysql database called demo

mysql> CREATE DATABASE demo;

Create a new user called user1 for database demo

mysql> GRANT ALL ON demo.* TO user1@localhost IDENTIFIED BY 'mypassword';

Note: GRANT ALL means all privileges i.e. user is permitted do anything. She can read, modify or delete data, but only on tables in the demo database. She cannot access any other database.

How do I connect to MySQL server using user1 account?

User user1 can connect to mysql server demo database using following command:

$ mysql -u user1 -p demo

OR

$ mysql -u user1 -h mysql.server.com -p demo

Where,

• -u user1: MySQL Username
• -h : MySQL server name (default is localhost)
• -p : Prompt for password
• demo: demo is name of mysql database (optional)