How to create new MySQL user and assign database

A person drawing a diagram on a piece of paper.

To create a new user in MySQL follow the below steps:

Create new MySQL user

CREATE USER 'demo'@'localhost' IDENTIFIED BY 'dem0123';

Once the user is created, you will need to set permissions to grant access to the database. Here I will first create a testdb to create a new database to set permissions.

Create new database
CREATE DATABASE testdb;
Assign permissions to database
GRANT ALL PRIVILEGES ON testdb.* TO 'demo'@'localhost';

Update changes by executing the below command:

FLUSH PRIVILEGES;

Drop user

DROP USER 'demo'@'localhost';

Validate permissions

SHOW GRANTS FOR 'demo'@'localhost';

Output:

+----------------------------------------------------------------+
| Grants for demo@localhost                                |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `demo`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `demo`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

Allowing remote access for MySQL 8 server

In MySQL 8 on Centos 8 server, edit /etc/my.cnf file and add the following line at the end and save:

bind-address            = 0.0.0.0

You can reference this tutorial here for any further details.

Create user

CREATE USER 'demo'@'10.1.1.%' IDENTIFIED BY 'password';

Grant privileges

GRANT ALL ON testdb.* TO 'demo'@'10.1.1.%';
FLUSH PRIVILEGES;

Leave a Comment

Scroll to Top