How to create a database, database user and grant permissions in MySQL

MySQL is an opensource database management software which is very famous everybody knows and its user friendly. This will allow the user to create a database, database user, grant different permission based on your requirement and you can access it anywhere.

1) How to Login MySql Server

To Login mysql server use the below command.

# mysql -u root -p

Enter password: [Enter The mysql root password to login mysql server]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 153536
Server version: 5.1.70-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

OR “go to mysql installation directory like below and login

[email protected] [/u01/mysql/bin/]# mysql -u root -p

Enter password: [Enter The mysql root password to login mysql server]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 153536
Server version: 5.1.70-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2) How to Create a Database

To create a database use the below syntax , [DB Name] add your database name instead of [DB Name] which you wanted to create.

Common Syntax

To create the mysql database, use the below syntax. This is the common syntax for database creation.

Syntax :# create database [DB name];

Example

The below example is create “demodb” database on your server

mysql> create database demodb;

3) To check created Database

Use the below command to check the database whether its created or not. Here i can see my database “demodb” being created

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| cphulkd                 |
| demodb                  |
| eximstats               |
| horde                   |
| leechprotect            |
| modsec                  |
| mysql                   |
| roundcube               |
+-------------------------+
9 rows in set (0.00 sec)

4) How to Create a New User

To create a database user use the below syntax , [DB Name] add your database name instead of [DB Name] which you wanted to create.

Syntax :# CREATE USER ‘[DB user name]’@’localhost’ IDENTIFIED BY ‘[DB password]’;

Example

The below example is create “demouser” on your database server

mysql> CREATE USER 'demouser'@'localhost' IDENTIFIED BY 'demo$#123';
Query OK, 0 rows affected (0.00 sec)

5) How to Check created User

Use the below command to check the database user which is created by you.

mysql> select user,host from mysql.user;
+----------------+------------------------+
| user           | host                   |
+----------------+------------------------+
| root           | 127.0.0.1              |
| cphulkd        | localhost              |
| cpldap         | localhost              |
| demouser       | localhost              |
| eximstats      | localhost              |
| horde          | localhost              |
| leechprotect   | localhost              |
| logaholic      | localhost              |
| modsec         | localhost              |
| root           | localhost              |
| roundcube      | localhost              |
+----------------+------------------------+
11 rows in set (0.00 sec)
The above output shows a list of MySQL user accounts on your server. MySQL account has two components: user and host. This allows the same user to use different MySQL accounts without any issues.

6) Assign the DBname to DB and grant the privilege.

To Assign Privileges to database user, use the below syntax to do it.

Syntax :# GRANT ALL PRIVILEGES ON [DB name].* TO ‘[DB user name]’@’localhost’;

Example :

In the below example assign the database name “demouser” to database “demodb” and grant all privileges to database user.

mysql> GRANT ALL PRIVILEGES ON demodb.* TO 'demouser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

7) How to view the assigned privilege to all the users.

To check the assigned privilege of all the MySql user which is currently on your server use the below command.

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*1A117D221D3DFC59895ECD96DBE8124657C2533B' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

8) How to view the assigned privilege to particular users.

To check the assigned privilege of corresponding MySql user on your server, use the below command.

mysql> show grants for 'demouser'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*F28DA7C2C4641EA9AA37C9BBD4E9D0B3686F7FA5' |
| GRANT ALL PRIVILEGES ON `demodb`.* TO 'demouser'@'localhost'                                                    |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

9) To Flush Privileges

Once you have done all the things, you should flush privileges option,only after that your changes will take effect.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Hope this article was useful for you. Kindly provide your valuable feedback/comments in the commenting section.

Stay tuned with us !!

Magesh Maruthamuthu

Love to play with all Linux distribution

You may also like...

Shares
Close
Please support the site
By clicking any of these buttons you help our site to get better