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.

mysql -u root -p

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.