How to Create a MySQL User on Ubuntu 24.04 LTS

Managing users and their privileges is a critical part of administering a MySQL database. In this guide, we’ll cover the steps to create a new MySQL user, assign privileges, and test the setup on Ubuntu 24.04 LTS. Each command is explained in detail to ensure clarity for all users.

Prerequisites

Before you proceed:

1. Ensure MySQL is installed and running on your system.
2. You have administrative access to MySQL (root user or a user with sufficient privileges).
3. MySQL 8 is installed on Ubuntu 24.04 LTS.

Step 1: Log in to MySQL

Access the MySQL command-line interface using the root account or an account with administrative privileges.

sudo mysql -u root -p

• -u root: Specifies the root user.
• -p: Prompts you to enter the root password.

Step 2: Create a New User

To create a new user, use the following SQL command. Replace newuser with the desired username and password with a secure password.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

• 'newuser'@'localhost': Defines the username and specifies that this user can only connect from the local machine.
• IDENTIFIED BY 'password': Sets the password for the user.

Step 3: Grant Privileges to the User

Grant appropriate privileges to the new user. For full access to a specific database:

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

• ALL PRIVILEGES: Grants all permissions to the specified database.
• database_name.*: Specifies the database and all its tables. Replace database_name with your database name.

If you want to grant privileges to all databases, use:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

Step 4: Apply Changes

After granting privileges, reload the privilege tables to ensure the changes take effect.

FLUSH PRIVILEGES;

• This command updates the in-memory privileges table.

Step 5: Test the New User

1. Exit the MySQL shell:

EXIT;

2. Log in using the new user credentials:

mysql -u newuser -p

Enter the password you set for the user.

3. Verify the user’s access by running a simple SQL command, such as:

SHOW DATABASES;

The output should list the databases the user has access to.

Step 6: Remove or Modify User Privileges (Optional)

To revoke a specific privilege:

REVOKE privilege_name ON database_name.* FROM 'newuser'@'localhost';

To delete the user entirely:

DROP USER 'newuser'@'localhost';

Conclusion

You have successfully created a new MySQL user on Ubuntu 24.04 LTS, assigned privileges, and verified access. Properly managing user accounts and privileges is essential for maintaining database security and ensuring smooth operations.

If you have any questions or need further assistance, feel free to contact us at:

Email: devopsbyrushi@gmail.com