How to create a MySQL user on CentOS server and Grant permissions to it through command line ?

How to create a MySQL user on CentOS server and Grant permissions to it through command line ?

To create MySQL user you should have root access for SSH. First of all you have to login to MySQL server through command line.
-----
# mysql -u root -p ******
-----
Here, -u option is for username and -p option is for password.

Now, you will be at MySQL prompt and it looks like this :
-----
# mysql>
-----

Now, to create a MySQL user on server you have to run this command :
-----
# CREATE USER 'new-mysql-user'@'localhost' IDENTIFIED BY 'password';
-----

To List MySQL users including host :
-----
# SELECT User,Host FROM mysql.user;
-----

After creation of a MySQL user we have to grant him permissions too because by default it has no permissions while creaetion. Before we proceed, let us check what all privilages does a MySQL usaer can have ?

List of commonly used permissions :
  • ALL – Allow complete access to a specific database. If database is not mentioned, then allow complete access to MySQL.
  • CREATE – Allow to create databases and tables.
  • DELETE – Allow to delete rows from a table.
  • DROP – Allow to drop databases and tables.
  • EXECUTE – Allow to execute stored routines.
  • GRANT OPTION – Allow to grant or remove another user’s privileges.
  • INSERT – Allow to insert rows from a table.
  • SELECT – Allow to select data from a database.
  • SHOW DATABASES- Allow to view list of all databases.
  • UPDATE – Allow to update rows in a table.

Grant permissions to a MySQL user:

Syntax:
-----
# GRANT permission ON database.table TO 'mysqluser'@'localhost';
-----

To grant create permissions for all databases and all tables to a user, run this command :
-----
# GRANT CREATE ON *.* TO 'mysqluser'@'localhost';
-----

To grant all permissions to a user, run this command :
-----
# GRANT ALL ON example_database.* TO 'mysqluser'@'localhost';
-----

To finish all permission changes, run this command :
-----
# FLUSH PRIVILAGES;
-----

To view permissions of a MySQL user, run this command :
-----
# SHOW GRANTS FOR 'mysqluser'@'localhost';
-----
Author
bhawanisingh
Views
2,373
First release
Last update
Rating
0.00 star(s) 0 ratings
Top