How to create a MySQL user and database with permissions?

To create a MySQL database and user, go through following steps:

Step 1: Through the command line, log in to MySQL as the root user.

mysql -u root -p

Step 2: Enter MySQL root user’s password and press enter.

Step 3: In order to create a database user run following command.
Remember to replace user credentials (username/password) with the one you want to have.

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Above command grants user all permissions. However you restric user to specific permissions. For example if you want MYSQL user to have readonly access, then he shoul only have access to select.

For more details look at MYSQL official documentation.
https://dev.mysql.com/doc/refman/5.6/en/grant.html

Step4: Once user is created, exit mysql program.

Step5: To log in to MySQL as the user you just created, type the following command and press enter and type the user password.

mysql -u newly-created-user -p

Step6: Once you are login, you can type following command to create new database.

CREATE DATABASE dbname;

Step7: To work with the new database, type the following command.

USE dbname;

Step8: You can now work with the database. For example, the following commands demonstrate how to create a new table and how to insert example data into the table:

CREATE TABLE example_tbl ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example_tbl primary key (id) );

INSERT INTO example_tbl ( id, name ) VALUES ( null, 'Mr. X' );

Drop Table:
DROP TABLE example_tbl;

Drop Database:
DROP DATABASE dbname;

About the author

Danyal Ali Butt

I'm working as programmer since 2002. Born, grew up, studied and worked in Pakistan now Im located in Denmark since 2006.

View all posts