How to create User and Grant Permission In MySQL on Linux Command Line

MySQL Linux Commands

We are going to take a quick tour of common Linux command to deal with MySQL database:

Login to MySQL in Linux using command line

To login to MySQL database run the following command:

By passing -u parameter as login username and -p to enter the user password.

Creating new MySQL user in Linux using command line

To create new MySQL user , use command CREATE USER by passing the new username and follow by IDENTIFIED BY with the new password as bellow:

The example attempting to create new username newmysqluser with password NiwPwd!955

Grant And Revoke Permissions to MySQL User

After you create MySQL user , the next step to grant permissions to database , the general syntax  for granting permission is

The permission syntax can be one of the following:

Example of Grant Create permission for user newmysqluser  to all MySQL objects:

Or Grant Drop permission to user newmysqluser on all tables of test_database

Now once finish granting the permission to the MySQL user, run the FLUSH to reload all the privileges

Now we can View Grants for newmysqluser MySQL User

Using Revoke permission command

Now To Use the Revoke command the syntax is identical to grant but this command will remove the permission of the selected user when you runt the command, for example lets remove Drop permission to user newmysqluser to all tables in test_database , that already grant drop before:

Remove a MySQL User In linux using command line

To remove a user from MySQL, we again use the DROP command.As example lets remove newmysqluser

if you are trying to remove none exists user you will get SQL error ERROR 1396 (HY000): Operation DROP USER failed for ‘newmysqluser’@’localhost’

View a List of MySQL Users

To view full list of MySQL users, including the host they’re associated with,run the following SQL: