How to create a MySQL user and Grant permissions through command line ?

To create MySQL user you should have root access. First of all, you have to log in to MySQL server through a command line.

# mysql -u root -p ******

Here, -u option is for username and -p option is for the password.

Now, you will be at MySQL prompt and it looks like this :

# mysql>


Now, to create a MySQL user on the 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;


We have to grant permissions to the created USER. So let's,

To 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';
Was this answer helpful? 0 Users Found This Useful (0 Votes)