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';
-----