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.
$ 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>
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
mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'localhost'; Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
mysql> exit Bye
$ 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>
Comment anonymously. Login not required.