In a previous article, we created a Remote MySQL Database Server on one of our NVMe VPS Servers, then we installed a copy of WordPress on a different server. We created a database and user and connected them. Now we are going to look at how we manage that user. Specifically, we want to learn how to delete the MySQL databases and how to delete MySQL users.
This guide will also work on MariaDB servers as MariaDB is a copy of MySQL. Login to your MySQL/MariaDB database server as the root user to proceed. Next, issue the below command to log in to MySQL. You will need to Reset The MySQL Root Password if you have forgotten it or you don’t know it.
mysql -u root -p
List MySQL Users
Your console will now display MySQL>.
We can see a list of MySQL users on this server by issuing the following command;
SELECT User,Host FROM mysql.user;
So, here we can see the database users that we created in the previous article, deltadb. This is the user that we want to delete from our MySQL server. To delete the user we use the DROP command.
DROP USER 'USER'@'HOST';
So, our command to delete the deltadb user would be
DROP USER 'deltadb'@'localhost';
DROP USER 'deltadb'@'%';
Now, if we list all users in the database server we can see the deltadb user has been removed.
Delete MySQL Databases
Now we have deleted the MySQL user, we also want to delete the MySQL database. When you issue the next command all data in the database will be deleted. If you need a copy of the data ensure you Backup the MySQL database before issuing the command to delete. If possible, take a Snapshot of the entire server. A Snapshot will allow near-instantaneous restoration of the server and is much easer than restoring the MySQL database.
First, to find out which database to delete, list all of the databases on the MySQL/Mariadb server with the below command;
In the return, we can see the full list of databases used on this server. We’re interested in the deltadb and delta databases. We want to delete both MySQL Databases. To achieve this we use the drop command.
Take care here. Once you run these commands any data in the MySQL Database will be deleted. If you need data in the databases, drop the database tables or take a Snapshot.
DROP DATABASE delta;
DROP DATABASE deltadb;
That’s the process complete. We have deleted the MySQL user we created in our previous guide and now we have also deleted the MySQL database.