How to set up a new MySQL User Account

To set up a MySQL user account, you’ll need access to the MySQL server with appropriate administrative privileges, such as the root user or an account with CREATE USER and GRANT privileges. Here’s a step-by-step guide on how to create a new user account in MySQL:

Connect to MySQL Server

Open a terminal or Command Prompt and log in to the MySQL server as a user with administrative privileges. You can use the following command and enter the password when prompted:

$mysql -u root -p

Create a new MySQL User

Once you’re logged in to the MySQL server, you can create a new user using the CREATE USER statement. Replace new_user with the desired username, and password with the password you want to set for the user (replace user_password with the actual password):

mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'user_password';

The 'localhost' part indicates that this user can only connect from the local machine. If you want the user to be able to connect from any host (not recommended for security reasons), you can use '%' instead of 'localhost'.

Grant Privileges to the MySQL User

After creating the user, you need to grant appropriate privileges to the user for the databases or tables they should access. For example, to grant all privileges on a specific database to the new user, you can use the GRANT statement:

mysql> GRANT ALL PRIVILEGES ON your_database_name.* TO 'new_user'@'localhost';

Replace your_database_name with the name of the database you want to grant access to.

If you want to grant different sets of privileges or restrict access to specific tables, you can modify the privileges accordingly. For example, to grant only SELECT privileges, you can use:

mysql> GRANT SELECT ON your_database_name.your_table_name TO 'new_user'@'localhost';

Apply Changes and Refresh Privileges

After creating the user and granting privileges, apply the changes and refresh MySQL privileges for them to take effect:

mysql> FLUSH PRIVILEGES;

Exit the MySQL Client

Once you have created the user and granted the necessary privileges, you can exit the MySQL client by typing:

mysql> EXIT;

That’s it! The new user account is now set up in MySQL with the specified privileges. They can use the provided credentials to connect to the MySQL server and access the granted resources. Remember to follow best practices for securing your MySQL server and creating strong passwords for user accounts.

Found this article interesting? Follow Brightwhiz on Facebook, Twitter, and YouTube to read and watch more content we post.