How To Create a New MySQL User?

Sometimes you need to create a new mysql user. This can be the case if you onboard a new employee or if you need to create a new user for a new project.

In this tutorial we will create a new mysql user. A mysql user is a user that has access to a mysql database. You can give them different permissions, like an administrator or a read-only user.

Create a new database (optional)

Here we create a new database. This is optional. If you don't want to create a new database, you can skip this step.

CREATE DATABASE new_database;

Log in to the MySQL server

Depending on your setup, you may need to log in to the mysql server. In my case, I use the root user to create and grant permissions to new users.

mysql -u root -p

Create a new user

Next we create a new user. In this step, we only create the login name and password. The user will have no access to any databases by default. Also, we use % as the hostname. This means that the user will have access to the mysql server on all hosts. If you want to restrict the user to a specific host, you can use a different hostname.

CREATE USER 'new_user'@'%' IDENTIFIED BY 'your_secure_password';

Grant permissions to the new user

In the next step, we grant permissions to our new created user. I will explain many scenarios, like admin, db full access, db ready-only.

Option 1: Grant all permissions to the new user (administrator)

GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'%' WITH GRANT OPTION;

Option 2: Grant full access to the database (db full access)

GRANT ALL PRIVILEGES ON new_database.* TO 'new_user'@'%';

Option 3: Grant read-only access to the database (db read-only)

GRANT SELECT ON new_database.* TO 'new_user'@'%';

Apply the changes

After modifying the user, we need to apply the changes. This is done by executing the FLUSH PRIVILEGES command. It's important to do this after modifying the user.

FLUSH PRIVILEGES;

Conclusion

Congratulations! You have created a new mysql user. You can now log in to the mysql server with the new user and password.