How to Delete MySQL Database and User on Ubuntu Linux

This article explains how to delete and use the MySQL database on Ubuntu Linux.

Sometimes, you may want to drop or delete old databases and user accounts on the MySQL (MariaDB) server. For example, when running a website powered by WordPress or another content management system (CMS) that uses a database server like MySQL or MariaDB, and you want to delete old databases that are no longer in use, you can use the steps below.

This brief tutorial shows students and new users how to remove old MySQL databases and users associated with those databases using the command line terminal on Ubuntu and other Linux distributions.

The steps are intended for removing the MySQL database and user on Linux via the command line only. And it would be best if you were extremely careful when dropping MySQL databases.

When you’re ready to drop MySQL databases and associated users on Ubuntu, follow the steps below:

Drop MySQL (MariaDB) Databases

Before dropping or deleting a MySQL database, you should be sure you want to do that. When you mistakenly drop a database, and there are no backups to restore, you may never be able to restore the database.

All database content will be lost forever!

So, back up your database or be sure you have no use for the database before dropping.

First, we’ll log in to the MySQL server from the command line with the following command:

sudo mysql -u root -p

In this case, I’ve specified the user root with the -u flag and then used the -p flag so MySQL prompts for a password. Enter your current password to complete the login.

After successfully logging in, you should now be at a MySQL prompt that looks very similar to this:

mysql>

To show or list all current databases on the server, run the commands below:

SHOW DATABASES;

That should list all the databases on the server.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| wpdb               |
| wordpress_pro      |
| wpdemodb           |
| information_schema |
| wp_players         |
| mysql              |
| performance_schema |
| wp_mysuperdb       |
+--------------------+

Now, look at the databases and find the one you want to drop or delete. For example, to drop the database called wpdemodb, run the commands below:

DROP DATABASE wpdemodb;

The commands above will delete the database along with all its content.

Drop MySQL (MariaDB) User

Now that you have dropped a MySQL database, you can also drop all the associated users. To do that, run the commands below to show or list all the user accounts.

SELECT User,Host FROM mysql.user;

The commands above will list all the user accounts on the server.

MariaDB [(none)]> SELECT User,Host FROM mysql.user;
+------------------+---------------+
| User             | Host          |
+------------------+---------------+
| root             | 127.0.0.1     |
| root             | ::1           |
| wpdemouser       | localhost     |
| wpuserse         | localhost     |
| debian-sys-maint | localhost     |
| wordpressuse     | localhost     |
| root             | localhost     
+------------------+---------------+

Again, we’ll use the DROP command to remove a user from MySQL. Deleting a user in MySQL only takes one simple command, but BEWARE: dropping a user can not be undone!

To drop the user called wpdemouser, run the commands below:

DROP USER 'wpdemouser'@'localhost';

That should do it! Now, exit and continue with your other tasks.

Congratulations! You have successfully dropped a MySQL database and a user account.

You may also like the post below: