/

How to Create a User on MySQL: A Step-by-Step Guide

How to Create a User on MySQL: A Step-by-Step Guide

After installing MySQL, you will have the root user available. While this user can be used to test the functionality of MySQL, it is not recommended for other purposes due to its high level of access and associated risks. Similar to using Linux or Unix systems, it is best practice to create specific users with limited permissions for different tasks.

To create a new user on MySQL, follow these steps:

  1. Connect to MySQL using the root user:

    1
    mysql -u root -p

  2. Execute the following command to create a user named <username> with the corresponding <password>:

    1
    CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

    For example, to create a user named test_user with the password test_password12A, run the command:

    1
    CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test_password12A';

  3. The command should return a line confirming the successful execution, such as Query OK, 0 rows affected (X.XX sec).

Note: If you use an invalid password, you may encounter an error message stating that the password does not meet the current policy requirements. This could be due to the password complexity policy set during MySQL installation.

To connect to MySQL using the newly created user, exit the command line interface by typing QUIT, and then use the following command:

1
mysql -u test_user -p

Replace test_user with the desired username.

Once connected, you may encounter an error if the user does not have the necessary permissions to create a new database. Managing permissions will be covered in another tutorial.

It is important to mention the @'localhost' string used when creating the user. This specifies that the user can only connect from the localhost. This is suitable for testing purposes and when the application connecting to MySQL is running on the same computer as the DBMS. If the user needs to connect from a different IP, you will need to manually specify the IP or use the % wildcard.

To remove the user, use the following command:

1
DROP USER 'test_user'@'localhost';

By following these steps, you can create and manage users with specific permissions on MySQL.

tags: [“MySQL”, “user creation”, “database management”, “permissions”]