How to Manage MySQL Databases and Users from the Command Line

How to Manage MySQL Databases and Users from the Command Line

MySQL is the most popular database server used by websites to store their important data. In addition to using phpMyAdmin to manage MySQL databases, we can do the same through MySQL shell commands. In this article, we will show you some basic commands to manage MySQL databases and Users.

How to Manage MySQL Databases

In this section, we will show you how to add a new database, list databases, list tables inside a database, and simple queries to show data from a database. First, we need to log in to MySQL shell as the root user. Make sure you run this command below after logging in to your server through SSH.

$ mysql -u root -p

You will be prompted to type your MySQL root password, and the password will not be shown on your screen. Just type the password and then hit ENTER. You will be in MySQL shell once connected.

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 129124
Server version: 10.3.38-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Create a Database

To create a database, you can execute this command in MySQL shell.

CREATE DATABASE new_database;

That’s it, a database named ‘new_database’ has been successfully created. You can replace ‘new_database’ with your database name.

List your MySQL Databases

After creating a database, you can check to verify if it’s created or not. To list the databases on your MySQL server, execute this command:


The command above will print something like this:

| Database |
| data2024 |
| information_schema |
| mysql |
| new_database |
| performance_schema |
| postfixadmin |
6 rows in set (0.001 sec)

Create a Table

To create a table inside a database, for example in the database new_database, we can run these commands.

USE new_database;

The command above is executed to choose the database we want to insert a table to. Let’s create the table now.

CREATE TABLE clients (
name varchar(200) NOT NULL,
address varchar(500),
email varchar(200),
sex varchar(100),

The command should look like this on your screen:

MariaDB [(none)]> use new_database;
Database changed
MariaDB [new_database]> CREATE TABLE clients (
-> name varchar(200) NOT NULL,
-> address varchar(500),
-> email varchar(200),
-> sex varchar(100),
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.001 sec)

That’s it, you have successfully added a table called ‘clients’ inside the database ‘new_database’

List Tables

To check the tables inside a database, you can invoke this command:


The command will print you an output like this:

MariaDB [new_database]> show tables;
| Tables_in_new_database |
| clients |
1 row in set (0.001 sec)

Check Table Details

To check the table details, we can execute this command.

DESCRIBE clients;

DESCRIBE is the command, while ‘clients’ is the table name. You will see an output like this:

MariaDB [new_database]> describe clients;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(200) | NO | | NULL | |
| address | varchar(500) | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| sex | varchar(100) | YES | | NULL | |
5 rows in set (0.004 sec)

Insert a Record

To insert a new record to a table, we can run a command like this:

INSERT INTO clients VALUES ('1','John Doe','New Street BLVD 01','','male');

Check Data Inside a Table

To check the data inside a table, we can run the simple SELECT command:

SELECT * FROM clients;

The command will return an output similar to this:

MariaDB [new_database]> SELECT * from clients;
| id | name | address | email | sex |
| 1 | John Doe | New Street BLVD 01 | | male |
1 row in set (0.003 sec)

How to Manage a User

In the previous step, we have learned how to manage a MySQL database. Now, in this part, we are going to show you how to manage MySQL users.

Add a New User

Let’s execute this command below.

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'm0d1fyth15';

The command above will create a new user called ‘new_user’ with the password ‘m0d1fyth15’

Need a fast and easy fix?
✔ Unlimited Managed Support
✔ Supports Your Software
✔ 2 CPU Cores
✔ 2 GB RAM
✔ 50 GB PCIe4 NVMe Disk
✔ 1854 GeekBench Score
✔ Unmetered Data Transfer

Now just $43 .99


Give access for the ‘new_user’ to the database ‘new_database’

Execute this command to give the privileges to the new user we created earlier to the database named ‘new_database’

GRANT ALL PRIVILEGES ON new_database.* TO 'new_user'@'localhost';

Flush the access right by running this command:


At this point, the ‘new_user’ has access to ‘new_database’

Revoke Privileges

If you want to remove the privileges you gave to user ‘new_user’, simply run this command

REVOKE ALL PRIVILEGES ON new_database.* FROM 'new_user'@'localhost';

Or, if you want to delete the MySQL user, run this simple command instead.

DROP USER 'new_user'@'localhost';

You can now manage MySQL Databases and Users using Command Line

If you are one of our web hosting customers and have an active server with us, you don’t have to follow this tutorial and learn how to manage MySQL databases and users from the command line yourself. Our expert Linux admins will help you set up and manage your MySQL hosting, databases, and users. They are available 24×7 and will take care of your request immediately, and all you need to do is submit a ticket.

If you liked this post please share it with your friends or leave a comment below.

Leave a Comment