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
