Sometimes you may want to drop or delete old databases and user accounts on MySQL (MariaDB) server. When running a website powered by WordPress or other 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 used, you can use the steps below to do that.
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 MySQL database and user on Linux via the command line only. and you should be 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 positively sure that you want to do that. When you mistakenly dropped a database and there are no backups to restore from, you may never be able to restore the database.
All content of the database will be lost forever!
So, make sure to backup your database or be sure you have no use for the database before dropping.
Login to MySQL
First we’ll login 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. 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 users associated with the database. 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, to remove a user from MySQL we’ll use the DROP command. It only takes one simple command to delete a user in MySQL, 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:
