MySQL Create User

To create a new user in MySQL, you can use the CREATE USER statement, which allows you to specify the username and the host from which the user is allowed to connect to the MySQL server. After creating the user, you can grant specific privileges to the user, giving them access to certain databases and operations. Here are the basic steps to create a user in MySQL:

Step 1: Create the User

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• 'username': Replace this with the desired username for the new user.
• 'host': Specify the host or IP address from which the user is allowed to connect. You can use `’%’ to allow connections from any host or specify a specific host.
• 'password': Set the user’s password. Replace this with a strong and secure password.

Example:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

This creates a user named ‘myuser’ with the password ‘mypassword’ who can only connect from the localhost.

Step 2: Grant Privileges

By default, a newly created user has no privileges. You can grant specific privileges to the user using the GRANT statement. For example, to grant all privileges on a specific database to the user, use the following syntax:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

Replace 'database_name' with the name of the database to which you want to grant access.

Example:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

This grants the user ‘myuser’ all privileges on the ‘mydb’ database when connecting from the localhost.

Step 3: Flush Privileges

After granting privileges, you need to flush the privileges to make the changes take effect:

FLUSH PRIVILEGES;

This command ensures that the MySQL server reloads the user privileges from the grant tables.

Step 4: Verify User Creation

You can verify the user creation by trying to log in with the new user credentials:

mysql -u username -p

Replace 'username' with the username you created. You will be prompted to enter the password. If you can successfully log in, the user was created correctly.

Creating a user with appropriate privileges is essential for controlling access to your MySQL server and databases. Be sure to choose strong passwords and grant only the necessary privileges to maintain security.