How to create a user in MySQL or MariaDB

Creating a user is a common task in managing MySQL or MariaDB servers. A user account with appropriate privileges provides access to the database and allows users to create tables, insert data, and perform other operations. Additionally, creating separate user accounts for different tasks and applications ensures each user has the appropriate level of access.

You can create a user in MySQL or MariaDB using the CREATE USER statement. You will then need to assign privileges to the user using the GRANT statement. User information is stored in the mysql.user table.

Creating a user in MySQL or MariaDB requires administrative privileges. You must log in to the database as the root user or another account with sufficient privileges to create new users. It is also best practice to use strong, unique passwords for each user account to protect your data and prevent unauthorized access.

1. Log in to your MySQL or MariaDB server with an administrative account using the mysql client.

   $ mysql -u root -p
   Enter password: 
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 10
   Server version: 8.0.30 Source distribution
   
   Copyright (c) 2000, 2022, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

2. Create new user and set password.

   mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
   Query OK, 0 rows affected (0.01 sec)

   Make sure your password meets the password validation policy requirement or you'll get the following error:

   ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

3. Assign privileges to the new user.

   mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'localhost';
   Query OK, 0 rows affected (0.00 sec)

   Related: How to assign permissions to MySQL or MariaDB users

4. Reload privileges from the grant tables for changes to take effect immediately.

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.00 sec)

5. Exit MySQL or MariaDB server session.

   mysql> exit
   Bye

6. Connect to MySQL or MariaDB server using newly created user to test.

   $ mysql -u username -p
   Enter password: 
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 11
   Server version: 8.0.30 Source distribution
   
   Copyright (c) 2000, 2022, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>