How to Create a MySQL User and Grant Privileges

In previous guide, I described the procedure of creating a MySQL user and granting privileges with the help of VPSrobots. In this article, let’s see how to create a MySQL user and grant privileges with command line.

Create a MySQL user and grant privileges

1. Log in MySQL

Tipically, root is the primary MySQL account by default when MySQL is installed. Type the command line below to Log in MySQL:

mysql -u root -p

2. Create a new user

Type the following command line to create new user:

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;

Please replace newuser with the name of user you are creating and localhost can be remained or changed any host from which the user will be accessing MySQL, which means to allow all host. password is the password for database login with the new created user.

3. Grant privileges to MySQL user

To grant all privileges to a user, type the following command line:

GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@’localhost’;

“.” represents the databse.table and replace newuser and localhost with the new created user and host. To grant a user all privileges to a specific database, type the following command line:

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

The user has all privileges to all tables in the specific database. If you want to target the individual table, replace database_name.* with database_name.table_name.

4. Flush privileges

To take effect for saving changes, please type the command line below:

flush privileges

Conclusion

I hope this article helped you learn how to set up a MySQL user. You may also want to see our guide on how to display estimated post reading time in WordPress.