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:
OR
Sample outputs:
$ mysql -u root -p mysql
OR
$ mysql -u root -h server-name-here -p mysql
Sample outputs:
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:
Sample outputs:
mariadb> SELECT User,Host FROM mysql.user;
Sample outputs:
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:
Sample outputs:
mariadb> SHOW GRANTS FOR 'bloguser'@'localhost';
Sample outputs:
Where,
- bloguser – Mysql/Maridb user name
- localhost – Mysql/Mariadb host name
- mywpblog – Database name
Step 4 – Revoke all grants for a mysql user
Type the following sql command:
Sample outputs:
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:
Sample outputs:
mariadb> DROP USER 'bloguser'@'localhost';
Sample outputs:
Query OK, 0 rows affected (0.00 sec)
Step 6 – Delete the database
Type the following command:
Sample outputs:
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
No comments:
Post a Comment