How to delete or remove a MySQL/MariaDB user account on Linux/Unix

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)