Mysql User Creation: Setting Up a New MySQL User Account

How do I create a user account on MySQL database server?

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. 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 root user account is different from UNIX/Linux root login account. 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.

Setup a root user password

To setup root password for first time, use mysqladmin command at shell prompt as follows:
$ mysqladmin -u root password NEWPASSWO
If you want to change or update a root user password, then you need to use the following command:
$ mysqladmin -u root -p'oldpassword' password newpass

Procedure for setting up a MySQL user account

Login in as mysql root user. At shell prompt type the following command:

$ mysql -u root -p

OR

$ mysql -u root -h your-mysql-server-host-name -p

Create a new mysql database called demo. Type the following command at mysql> prompt:

mysql> CREATE DATABASE demo;

Create a new user called user1 for database called demo:

mysql> GRANT ALL ON demo.* TO [email protected] IDENTIFIED BY 'mypassword';

How do I connect to MySQL database demo using user1 account?

User user1 can connect to demo database using the following shell command:

$ mysql -u user1 -p demo

OR

$ mysql -u user1 -h  your-mysql-server-host-name-here -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)

See also

• Man pages: mysqladmin(1)