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.
Table of Contents
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:
SHOW DATABASES;
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 (
id INT AUTO_INCREMENT,
name varchar(200) NOT NULL,
address varchar(500),
email varchar(200),
sex varchar(100),
PRIMARY KEY(id)
) ENGINE=InnoDB;
The command should look like this on your screen:
MariaDB [(none)]> use new_database;
Database changed
MariaDB [new_database]> CREATE TABLE clients (
-> id INT AUTO_INCREMENT,
-> name varchar(200) NOT NULL,
-> address varchar(500),
-> email varchar(200),
-> sex varchar(100),
-> PRIMARY KEY(id)
-> ) 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:
SHOW TABLES;
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','john@doe.com','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 | john@doe.com | 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’
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:
FLUSH PRIVILEGES;
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.