How to create user in MySQL

This is the quick and straight way how to create a user in MySQL with permissions on specified database. Although it is very good described in MySQL documentation, I was tired of searching the help everytime when I need this. So that's why this quick post emerged, hope that you will enjoy it.

First run the command line for MySQL. Linux user will simply run mysql binary from shell, windows user have to change actual directory to "bin" in your MySQL installation folder first and run the command from there (or include the full path to binary mysql.exe).

mysql -u root

Or if your root account is configured with password

mysql -u root -p

Now you are logged in MySQL console. Run following command for list all existing databases

show databases;

Then we will create database and then we will create our user and set him as a administrator of the database (we will give him all permissions on it). The user can access the databases only from local machine - if you need remote access, you have to change the command and include IP of the remote machine in 'username'@'localhost' to 'username'@'IP'.

mysql> create database our_new_database;

Query OK, 1 row affected (0.01 sec)

mysql> grant all on our_new_database.* to 'username'@'localhost' identified by 'password';Query OK, 0 rows affected (0.02 sec)

Okay, and it is done - now you can try to access your account with your new user.

C:Program FilesMySQLMySQL Server 5.0bin>mysql -u username -pEnter password: *******Welcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 13Server version: 5.0.45-community-nt MySQL Community Edition (GPL)Type 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql>