Mysql user creation – setting up a MySQL new user account

by on January 1, 2006 · 14 comments· last updated at August 20, 2007

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)


You should follow me on twitter here or grab rss feed to keep track of new changes.

Featured Articles:

{ 14 comments… read them below or add one }

1 amol September 27, 2008 at 10:25 am

how to install & configure PHP on RHEL 4

Reply

2 herc April 17, 2010 at 4:59 pm

Thanks! Simple and clear

Reply

3 sisila November 21, 2011 at 4:22 am

its cool .v good

Reply

4 RK February 1, 2012 at 10:14 am

Excellent…!!
Its very easy and clear

Reply

5 saber April 1, 2012 at 7:51 am

hi , i want to configure a policy for users that expire password off users in specific time any one can help me ? (im using ubuntu 10.04 and mysql)

Reply

6 Betrand Njibamum May 30, 2012 at 10:54 pm

Please am trying to set up a mysql account to use in installing joomla to no avail . Please can someone help me with that ?

Reply

7 krishna June 12, 2012 at 9:38 am

thanx yar.its a simple installation

Reply

8 Sanjib July 2, 2012 at 8:39 am

Its very simple and valueable. Wroks fine for me. Thanks a lot.

Reply

9 yash September 18, 2012 at 3:25 pm

nice man :)

Reply

10 benariba February 11, 2013 at 11:46 am

very fine, thank you very match;

Reply

11 deepak February 12, 2013 at 6:55 am

Hi I am new to mysql,
When I try to connect from command line. I need to enter my root password. I have created the demo database, but when I type “mysql -u user1 -p demo”, It shows me ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘mysql
-u user1 -p demo’ at line 1. Please help, how can I connect with this user1 to demo database in command prompt.

Reply

12 Jia Ye March 28, 2013 at 12:54 pm

you need to grant the privilege of operating the database’demo’ to the user in the root model, like this: grant all on demo(name of your database) to user1(username)@ “localhost ” identified by “password”(your account’s password).

Reply

13 Jia Ye March 28, 2013 at 1:09 pm

if you don’t have the primaryl syntax error,it should be a problem of your MySql version,which may be too old,then you can change a new release.

Reply

14 Chase May 2, 2013 at 8:15 am

Excellent, quick how-tos. Thank you very Much!

Reply

Leave a Comment

You can use these HTML tags and attributes for your code and commands: <strong> <em> <ol> <li> <u> <ul> <kbd> <blockquote> <pre> <a href="" title="">

Tagged as: , , , , , ,

Previous Faq:

Next Faq: