Manage MySQL Databases From Command Line

MySQL is one of the most important and must have software when it comes to Linux server administration. Especially when you’re using content management systems like WordPress, Drupla, Joomla, etc… This post features some basic MySQL command to manage MySQL on VPS.

Most content management systems out there are using MySQL to store its important information like passwords, usernames and lots of other things. So it’s essential to know basic commands to manage MySQL. There’s even dedicated software out there to manage MySQL. Most popular one is phpMyAdmin. Although I have a post explaining how to install phpMyAdmin on Nginx, I don’t recommend using it if you’re serious about server administration field.

For those who are not familiar with MySQL commands, I’m going to list some essential commands on this page. For those who want to create/ delete MySQL databases or users, I got it covered on my previous post Create/Delete MySQL Databases with CLI.

Before you can perform any of actions below, you’ll need to logged into your VPS and connected to MySQL server. To connect to MySQL, give following command on CLI.

mysql -u root -p

It’ll ask you for MySQL root password. Give it and you’re in.

View List of Existing MySQL Databases

show databases;

Above command will list all existing databases on CLI

Use (Switch) MySQL Databases

use my_db;

It’ll change in-use database to my_db. Replace my_db with your database name.

View List of Tables in Selected Database

show tables;

This command will list all tables of in-use database.

View all Content of a Table

SELECT * FROM table1;

This will display everything in the table.

Drop Single or Multiple Tables in a Database

DROP TABLE table1;

Replace table1 with existing table name to drop it. Separate table names with comma marks(,) to drop multiple tables.

Drop all Tables in a Database

There’s no single command to do this on MySQL. Infect there’s one, you could use DROP TABLE command to do it with naming all available tables. But it’ll be a headache if you have lots of tables in database. In that case, easiest way to do it is deleting database and creating it again with instructions on this post. Remember, you’ll have to assign database privileges to user again as well.
So that’s it for today. There’s other commands available as well. If you need anything that already not listed on this post, please leave a comment below. I’ll update this post.

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

4 Comments

  1. hey there, i am trying out your instructions. the first one says mysql -u root ““p

    but i dont know what that last bit of writing is or where you get it from or if its a misprint or what. can you please clarify what that is? ““p ???

  2. hey there, i am trying out your instructions. the first one says mysql -u root ““p

    but i dont know what that last bit of writing is or where you get it from or if its a misprint or what. can you please clarify what that is? ““p ???

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.