What are MySQL and MariaDB?

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system.


MySQL and MariaDB are similar DB management systems. These tools can be used on your VPS/shared server to manage the data from many different programs.

This guide will show you how you can setup database name and username on your server using Terminal

For the purposes of this guide, we will be using an Ubuntu 12.04, Ubuntu 14.04.x, Ubuntu 16.04.x, Ubuntu 18.04.x Ubuntu 18.10.x & Ubuntu 19.04

How to Create a Database in MySQL and MariaDB ?

First you need to login to the terminal using root credentials, after that you need to login into MySQL or MariaDB with the following command:

mysql -u root -p

Now you have to enter root passworth that you set up during install. You will be given a MySQL/MariaDB prompt.

Now you can proceed with creating database name by doing following command:

CREATE DATABASE your_database_name;
Query OK, 1 row affected (0.00 sec) 

You’re likely to get the error if database already exists in order to avoid this you want to use this conditional command line instead

CREATE DATABASE IF NOT EXISTS your_new_database;
Query OK, 1 row affected (0.00 sec)
In case database already exists you should get this error 
ERROR 1007 (HY000): Can't create database 'other_database'; database exists 
in this case you have to choose another name.  

Furthermore you can see current databases by doing following command 
SHOW DATABASES;


+--------------+
| database()   |
+--------------+
| your_new_database |
+--------------+
1 row in set (0.00 sec) 

How to create database user & assign privileges ?

First you need to create new user, in some cases it may require some custom permissions in that case you can create user with custom privileges

Let’s Begin by creating a user. Enter command line and hit Enter on your keyboard.

  mysql> CREATE USER 'yourootuser'@'localhost' IDENTIFIED BY 'passwordGoesHere'; 

Note: When adding users within the MySQL shell in this tutorial, we will specify the user’s host as localhost rather server’s IP address. localhost is a hostname which means “this computer,” and MySQL treats this particular hostname specially: when a user with that host logs into MySQL it will attempt to connect to the local server by using a Unix socket file. Thus, localhost is typically used when you plan to connect via terminal.

Now you need to assign this user privileges for either all databases or maybe one database

lets say if you want to grant access of all the databases to this new user then you have to do following command in the terminal.

mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; 

Last step should be to run following command in the terminal.

mysql>  FLUSH PRIVILEGES; 

Well done! You have performed all the steps to create database user and database name now you can use those details in your project. Good luck!

(Visited 21 times, 1 visits today)