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

I created a MySQL / MariaDB user account using this page. Now, I have deleted my wordpress blog and I want to delete that user account including database too. How do I delete or remove a MySQL or MariaDB user account on Linux or Unix-like system using mysql command line option?

Both MySQL and MariaDB is an open source database management system. In this quick tutorial, you will learn how to delete ore remove user account in MySQL or MariaDB database on Linux or Unix-like system.

ADVERTISEMENTS

How to delete or remove a MySQL/MariaDB user account

You must login as root user account on your MySQL or MariaDB server to delete user account. Let us see all steps in details.

Warning: Backup your database before you type any one of the following command.

Step 1 – Steps for removing a MySQL/MariaDB user

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
Sample outputs:

Fig.01: The MySQL/MariaDB shell

Fig.01: The MySQL/MariaDB shell

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> SELECT User,Host FROM mysql.user;
Sample outputs:

Fig.02: How to see/get a list of MySQL/MariaDB users accounts

Fig.02: How to see/get a list of MySQL/MariaDB users accounts

In this above example, I need to delete a mysql user named ‘bloguser’@’localhost’.

Step 3 – List grants for a mysql user

To see what grants bloguser have, enter:
mariadb> SHOW GRANTS FOR 'bloguser'@'localhost';
Sample outputs:

Fig.03: Display user grants

Fig.03: Display user grants

Where,

  1. bloguser – Mysql/Maridb user name
  2. localhost – Mysql/Mariadb host name
  3. mywpblog – Database name

Step 4 – Revoke all grants for a mysql user

Type the following sql command:
mariadb> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bloguser'@'localhost';
Sample outputs:

Query OK, 0 rows affected (0.00 sec)

Step 5 – Remove/Delete the user from the user table

Type the following sql command:
mariadb> DROP USER 'bloguser'@'localhost';
Sample outputs:

Query OK, 0 rows affected (0.00 sec)

Step 6 – Delete the database

Type the following command:
mariadb> DROP DATABASE mywpblog;
Sample outputs:

Query OK, 0 rows affected (0.00 sec)

And there you have it. A MySQL/MariaDB user deleted or removed from the server on Unix or Linux via command line option.

A note about DROP USER sql command to delete or remove a MySQL/MariaDB user account

The DROP USER statement removes one or more MariaDB accounts. It removes privilege rows for the account from all grant tables. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. The syntax is:
mariadb> DROP USER foo;
mariadb> DROP USER IF EXISTS bar;

Conclusion

Just learned how to delete or remove a MySQL/MariaDB user account running on a Linux or Unix-like server using the mysql command. For more information see this page or read mysql command man page by typing the following man command:
$ man 1 mysql

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
6 comments… add one
  • Foobar Feb 15, 2017 @ 22:04

    Using “mysql -u root -p mysql” in the commandline is a realy bad idea, the plain password will be visible in the history! just use “-p” without the password, will avoid this and asks for the password.

    • 🐧 Vivek Gite Feb 16, 2017 @ 2:59

      That is not a password. mysql is database name. For password syntax is:
      mysql -u root -p'password' mysql

      • Foobar Feb 16, 2017 @ 15:46

        Doh! Sorry, my fault – didn’t realize the space

  • Dmitriy Lyalyuev Feb 16, 2017 @ 10:05

    `Step 5 Delete the database`
    Step 5 is duplicate. You mean ‘Step 6’ ;)

  • Dmitriy Lyalyuev Feb 16, 2017 @ 10:07

    ‘mariadb> DROP DATABASE mywpblog;;’

    ‘;;’ – should be one ‘;’

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.