How to Create Users and Databases in MySQL
Managing users and databases is a fundamental MySQL administration task. This guide shows how to create a user, create a database, and grant that user the permissions needed to work with the database.
1. Create a New MySQL User
Start by creating a user with its own login credentials.
Basic syntax:
CREATE USER 'user' IDENTIFIED BY 'password';'user': the username to create.'password': the password for that user.
Example:
CREATE USER 'newuser' IDENTIFIED BY 'securepassword';This creates a user named newuser with the password securepassword.
2. Create a Database
Create the database the user will work with.
Basic syntax:
CREATE DATABASE nameDatabase;nameDatabase: the database name.
Example:
CREATE DATABASE mydatabase;This creates a database named mydatabase.
3. Grant Permissions to the User
Grant only the permissions the application or user actually needs. Permissions can include SELECT, INSERT, UPDATE, DELETE, or broader privileges.
To grant all privileges on one database:
GRANT ALL PRIVILEGES ON nameDatabase.* TO 'user';ALL PRIVILEGES: grants all available privileges for the specified database scope.nameDatabase.*: targets every table in the database.'user': the account receiving the privileges.
Example:
GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser';This grants newuser full privileges on mydatabase.
4. About FLUSH PRIVILEGES
You may see tutorials run:
FLUSH PRIVILEGES;For privileges changed with account-management statements such as CREATE USER and GRANT, modern MySQL applies the changes immediately, so FLUSH PRIVILEGES is normally unnecessary. It is mainly relevant when privilege tables are modified directly, which should generally be avoided.
Conclusion
The usual workflow is straightforward:
- Create a dedicated user.
- Create the application database.
- Grant that user only the required privileges.
Using separate accounts and appropriately scoped permissions helps keep MySQL deployments easier to manage and more secure.