How to Create a Database, Database User and Grant Privileges in MySQL/MariaDB

This is a basic tutorial to help new users to learn about the MySQL/MariaDB database.

In this tutorial, you will learn about database creation, database user creation and how to grant permissions to users.

What is MySQL?

MySQL is an open-source relational database management system (RDBMS).

This is one of the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.

The SQL part of “MySQL” stands for “Structured Query Language”. SQL is the most common standardized language used to access databases.

What is MariaDB?

MariaDB is a drop-in replacement for MySQL. It is an enhanced, drop-in replacement for MySQL.

MariaDB is an opensource Relational Database Management System (RDBMS) which supports database access.

It’s made by the original developers of MySQL and guaranteed to stay open source.

MariaDB is used because it is fast, scalable and robust, with a rich ecosystem of storage engines, plugins and many other tools make it very versatile for a wide variety of use cases.

1) How to Login to MySql/MariaDB Server

Use the following command to log into the MySQL/MariaDB database.

# mysql -u root -p

Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.3.13-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 

2) How to Create a Database in MySQL/MariaDB

The syntax is simple and straight forward. To create a database use the below format.

Common syntax for database creation in MySQL/MariaDB.

create database [DB name];

In this example, we are going to create a “demodb” database in MySQL/MariaDB.

MariaDB [(none)]> create database demodb;

3) How to Check the Database Created in MySQL/MariaDB

You can easily view the database created in MySQL/MariaDB using the following command. Yes, it was successfully created and you can see the “demodb” database I created in the example above.

MariaDB [(none)]> 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 in MySQL/MariaDB

To create a database user, use the below format.

Common syntax for database user creation in MySQL/MariaDB.

CREATE USER '[DB_User_Name]'@'localhost' IDENTIFIED BY '[DB_Password]';

In this example, we are going to create a “demouser” database in MySQL/MariaDB.

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

5) How to Check the User Created in MySQL/MariaDB

Use the following command to verify the database user you created.

The output below shows a list of MySQL/MariaDB user accounts on your server. It has two components – user and host. It allows the same user to use different MySQL/MariaDB accounts.

MariaDB [(none)]> 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)

6) How to Assign and Privileges a User to the Database

To grant privileges to the database user, use the below command.

Common syntax for assigning privileges in MySQL/MariaDB.

GRANT ALL PRIVILEGES ON [DB_Name].* TO '[DB_User_Name]'@'localhost';

In this example, we assign the database name “demouser” to the “demodb” database in MySQL/MariaDB.

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

7) How to Check User Privileges in MySQL/MariaDB

Run the following command to view all user privileges in MySQL/MariaDB.

MariaDB [(none)]> 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 Privileges Assigned to a Specific User in MySQL/MariaDB

Run the following command to view user privileges for a specific user in MySQL/MariaDB.

MariaDB [(none)]> 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) How to Flush Privileges

If you run any command on the MySQL/MariaDB server you must run the below command each time.

All changes will take effect once you run the command below.

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

I hope this article was helpful for newcomers. Please provide your valuable feedback in the comment section.

Magesh Maruthamuthu

Love to play with all Linux distribution

You may also like...