How to delete or remove a MySQL/MariaDB user account on Linux/Unix
Step 1 - Log as root or admin to MySQL/MariaDB
If you decided to remove open source application such as WordPress or Drupal you need to remove that user account. You need to remove all permissions/grants, and delete the user from the MySQL table. First, login as mysql root user to the MySQL/MariaDB server using the shell, run:
~] mysql -u root -p mysql
or
~] mysql -u root -h server-name-here -p mysql
Step 2 – List all mysql users
Once you have a MySQL or MariaDB prompt that looks very similar to fig.01, type the following command at mysql> or mariadb> prompt to see a list of MySQL/MariaDB users:
MariaDB [(none)]> SELECT User,Host FROM mysql.user;
+----------------+-----------+
| User | Host |
+----------------+-----------+
| historie.cz | localhost |
| intranet | localhost |
| manpages.cz | localhost |
| myredlinux_com | localhost |
| mysqlroot | localhost |
| phpmyadmin | localhost |
| pipelines_cz | localhost |
| piwik | localhost |
| root | localhost |
| zzz | localhost |
+----------------+-----------+
10 rows in set (0.001 sec)
In this above example, I need to delete a mysql user named piwik@localhost.
Step 3 – List grants for a mysql user
To see what grants bloguser have, enter:
MariaDB [(none)]> SHOW GRANTS FOR 'piwik'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for piwik@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `piwik`@`localhost` IDENTIFIED BY PASSWORD '*27C69B0FEF5B4EE1C94D905872ABC86CB7E17979' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `piwik`.* TO `piwik`@`localhost` |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Step 4 – Revoke all grants for a mysql user
Type the following sql command:
MariaDB [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'piwik'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Step 5 – Remove/Delete the user from the user table
MariaDB [(none)]> DROP USER 'piwik'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Step 6 – Delete the database (optional)
MariaDB [(none)]> DROP DATABASE db_name;
Query OK, 0 rows affected (0.00 sec)
