How to add a mysql user and grant privileges

From the command line, log into mysql as root.

mysql -u root -p

You can create a brand new user by typing:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

newuser will be the name of the user you are creating. localhost can remain localhost or be changed to any host from which the user will be accessing mysql. You can use % to allow all hosts. password will be the password the user will use to login.

Now type this to grant privileges to a database:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

"." represents the database.table and newuser and localhost should be the user and host you just created. To grant this user all privileges to a single database type:

GRANT ALL PRIVILEGES ON database_name . * TO 'newuser'@'localhost';

where database_name is the name of the database.

Notice that database_name.* includes all tables in the database. You can also target individual tables but using database_name.table_name.

Sometimes you will need to flush privileges for this to take effect:

flush privileges

At some point, you may need to inspect existing privileges (or double check your work). To do so, you can use the following:

SHOW GRANTS FOR 'newuser'@'localhost';

If you found this useful, visit our guide on how to create a Mysql root password root to add a little more security to your project.