How to Create and Delete MySQL Databases with Command Line (CLI)

This is a pretty basic tutorial about creating and deleting MySQL databases with command terminal for Linux VPS beginners. In this tutorial I’ll show you how to create a new MySQL database, MySQL user and assign database privileges to new user with CLI.

This post is going to be very short as this is a ridiculously easy task to do in Linux-based VPS. And for those how landed here incorrectly looking for how to create MySQL databases in shared hosting, please navigate to my MySQL cPanel video tutorial to do it on shared hosting. This is for guys with VPS.

And before we start, I would like to say that this is not the only way to do it on VPS. You can use phpMyAdmin to do this on your VPS. But first you need to install it on your server. You can follow my Nginx phpMyAdmin setup tutorial to set it up on your Nginx server if you like. But using CLI is the best and fastest way to do it. It’s the far easier method as well. So let’s begin,

Creating New MySQL Database

First, you must connect to MySQL shell using your CLI. Following comma will do it for you.

mysql -u root -p

You’ll be asked to provide password for MySQL root user. And this not the password of your VPS root. This is the password you set when you were installing MySQL on your VPS.

We’ll first create database user and password with following command,

grant all privileges on *.* to db_user@localhost identified by "db_pass";

Replace db_user with your chosen username and db_pass with your new password.

Now let’s create Database. Once again replace db_name with your database name.

create database db_name;

Finally disconnect from MySQL shell.

quit

That’s it for creating part. Let’s move to deleting part.

Removing MySQL Database

The codes below this line will drop database from your VPS permanently and there’s no way to recover it once deleted. You are warned.

We’ll again have to connect to MySQL shell,

mysql -u root -p

After logged in as MySQL root, run following command with replacing db_name with your database name.

drop database db_name;

Simple as that, database is now no longer available on your VPS. And finally exit from MySQL shell.

quit
Tags
Show More

Tharindu

Hey!! I'm Tharindu. I'm from Sri Lanka. I'm a part time freelancer and this is my blog where I write about everything I think might be useful to readers. If you read a tutorial here and want to hire me, contact me here.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button

We use cookies to give you the best online experience. By agreeing you accept the use of cookies in accordance with our cookie policy.

Close