Update: There is a small update to this article, as if lets say some one does not have any root password yet, then how to create it 1st and then use the following commands. So that’s simple, open terminal and type the following.

mysqladmin -u root password YOURPASSWORD

YOURPASSWORD = Your new root password

=================

Do you want to create a separate user for a database which only some users/user can access. Want to create user who can only have access to databases you assign them.

This will be a very simple tutorial about how to create a database 1st, a user and then assign some privileges to the user to allow them to perform some specific actions like insert,create,update,select etc etc.

I assume you are on Linux and command shell (terminal) is opened.

To enter to MySQL mode in terminal type this:

mysql -u root -p

Upon asking for the password enter your root password (not distro one, MySQL root password). -u means username and -p is password.

You will see output like this:

Enter password:
  
Welcome to the MySQL monitor.  Commands end with ; or \g.
  
Your MySQL connection id is 35614
  
Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)

Type help; or \h for help.

mysql>

Lets create a database (if you don’t have yet) with any name you desire (use text in red):

create database store;

You will see this output:

Query OK, 1 row affected (0.00 sec)

Now the fun part, lets create a user with password and assign some basic privileges on a database we created above:

grant CREATE,INSERT,DELETE,UPDATE,SELECT on store.* to asim@localhost identified by 'storeadminasim';

In the above command you need to change the following according to your need.

store = the database we created above.

asim = the user we want to create for the database store.

storeadminasim = the password assigned to asim.

We will not let him do whatever he want, this is good for security. This user will only access this database assigned to him and nothing else, and he can create tables in it, can update it, insert data into tables etc etc. If the above command runs successfully you will see this output in terminal:

Query OK, 0 rows affected (0.00 sec)

Now the last and important thing is to flush the privileges, type this:

flush privileges;

You will see this output:

Query OK, 0 rows affected (0.00 sec)

That’s it, you can now quit/exit from MySQL session:

exit

The user and database is created and ready for use.