How to create a new MySQL user account?

A default MySQL server will only have a ROOT user account to access and manage the databases. If you have different users and applications accessing the database server, it’s always recommended to create non-root MySQL users and grant specific privileges to those. Following are the steps to do so:

1. Login to MySQL by running the following command:

mysql -u root -p

Enter the root password to launch the MySQL shell.

2. Create the new user and set a password by running the query:

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

This will create a user ‘myuser1‘ with the given password in the user table of MySQL.

3. Grant privileges to the new user:

GRANT ALL PRIVILEGES ON my_database.* TO 'myuser1'@'localhost';

With this, myuser1 is granted all the privileges to perform any action in ‘my_database‘. Depending on the user types and data confidentiality, you can grant them all or any number of available privileges like CREATE, INSERT, UPDATE, DELETE, DROP, etc.

4. Flush the privileges to reload the user table:

FLUSH PRIVILEGES;

That’s all required to create a new MySQL user account. To make sure that everything went well, login to MySQL as the new user:

mysql -u myuser1 -p

myuser1 will only be able to view and perform actions in my_database. Anything else will result in the “ERROR 1044 (42000): Access denied” error!

To delete a MySQL user, login as ROOT and execute the following query:

DROP USER 'myuser1'@'localhost';