Hello beautiful peoples!
Every server has that one database nobody wants to talk about. It was created for a test project, it worked for a while, and now it just sits there eating disk space and judging you. Today we are going to end it. Cleanly, politely, and with zero chance of resurrection.
Before we start swinging the axe, one important thing. DROP DATABASE has no undo button, no recycle bin and no “are you sure?” popup. It is gone the moment you hit Enter. So make a backup first, even if you are 100% sure you will never need it. Especially then, because that is exactly when you will need it.
The dump file can contain sensitive data like password hashes or tokens stored by your app, so keep it somewhere safe and do not paste it anywhere.
In my case the victims are a database called test_database and a user called test_user. Swap these names for your own. Before deleting anything I check what the user can actually touch with SHOW GRANTS. This is also where the GRANT ALL PRIVILEGES line that gave the user access to the database shows up, and it is a nice confirmation that you are about to remove the right user. Then we drop the database, then the user, and then we check that both are really gone.
You may see guides telling you to run FLUSH PRIVILEGES after DROP USER. You do not need it here. It is only required when you edit the grant tables by hand with INSERT, UPDATE or DELETE. DROP USER takes care of everything on its own, so I skip it. It will not hurt if you run it anyway, it just does nothing useful.
### Slaughtering the test database... and its user
# Check if the database exists (empty output means it does not exist, stop here)
mariadb -u root -p -e "SHOW DATABASES LIKE 'test_database';"
# Backup (the dump may contain sensitive data, keep it safe)
mkdir -p /root/backup
mariadb-dump -u root -p test_database > /root/backup/test_database.sql.bak_$(date +%Y-%m-%d_%H-%M-%S)
# Log in as root
mariadb -u root -p
# Check what the victim can do
SHOW GRANTS FOR 'test_user'@'localhost';
# Kill the database
DROP DATABASE test_database;
# Kill the user
DROP USER 'test_user'@'localhost';
# Make sure both are gone
SHOW DATABASES;
SELECT user, host FROM mysql.user;If test_database and test_user do not show up in those two lists, congratulations, the crime is complete. If you get an error saying the user does not exist, check the host part. In MariaDB ‘user’@’localhost’ and ‘user’@’%’ are two different accounts, and you may have to drop each one separately. You can list them with the SELECT above.
Also remember that your app still has the old credentials in some config file. Remove them too, otherwise the next time it starts it will cry about a missing database and you will have a nice error in the logs to remind you of what you did.
I hope this helps!
Catch you on the flip side,
AndrzejL
P.S. For anyone who is about to comment “Ewww mariadb – just use postgres”… Postgres sucks golf balls through a garden hose. Yes it does. Period.
