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
- ALL – Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.
- CREATE – Allow a user to create databases and tables.
- DROP – Allow a user to drop databases and tables.
- INSERT – Allow a user to insert rows from a table.
- SELECT – Allow a user to select data from a database.
- SHOW DATABASES- Allow a user to view a list of all databases.
- DELETE – Allow a user to delete rows from a table.
- EXECUTE – Allow a user to execute stored routines.
- GRANT OPTION – Allow a user to grant or remove another user’s privileges.
- UPDATE – Allow a user to update rows in a table.
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:
