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

Create a MySQL user on CentOS server and Grant permissions to it through command line.

  1. Bhawani Singh
    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';
    -----