How to Set Password for MySQL Root User and Create a New User with Root Privileges

In this detailed tutorial, we'll walk you through the process of setting a password for the MySQL root user and creating a new user with root privileges. Whether you're new to MySQL or looking to enhance your database security, follow these steps to ensure your MySQL server is configured properly and securely. Let's get your database setup robust and ready for action!

Note:- You must login on your OS or VPS Remote server as root user (Not Sudo User). You can switch from sudo to root.

What is mysql_secure_installation ?

This program enables you to improve the security of your MySQL installation in the following ways:

• o You can set a password for root accounts
• o You can remove root accounts that are accessible from outside the local host.
• o You can remove anonymous-user accounts.
• o You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

What is caching_sha2_password ?

It is a Plugins that perform authentication using SHA-256 password hashing. This is stronger encryption than that available with native authentication. There are some other plugins e.g. mysql_native_password.

Get Access to Remote Server

Syntax:

ssh -p PORT USERNAME@HOSTIP

Example:

ssh -p 22 abhi@203.32.44.92
ssh root@194.87.34.23

Installing Necessary Packages

First, let's ensure our server has the required packages installed. Open a terminal and run the following commands:

apt update
apt upgrade
apt install mysql-server

Configure MySQL

Login to MySQL

mysql

Set Password for Root

Syntax:

ALTER USER 'user_name'@'localhost' IDENTIFIED WITH caching_sha2_password by 'user_password';

Example:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password by 'Password1234#';

Exit from MySQL

exit;

Run the mysql_secure_installation Program

mysql_secure_installation

Login to MySQL as Root User

mysql -u root -p

Create New User

Syntax:

CREATE USER 'user_name'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'user_password';

Example:

CREATE USER 'abhi'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Password1234#';

Grant New User All Privileges

Syntax:

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;

Example:

GRANT ALL PRIVILEGES ON *.* TO 'abhi'@'localhost' WITH GRANT OPTION;

Exit from MySQL

exit;

- Restart Web Server

If Apache

service apache2 restart

If Nginx

service nginx restart

Conclusion

In conclusion, securing your MySQL server by setting a password for the root user and creating a new user with root privileges is a crucial step in maintaining database security. This tutorial has provided a comprehensive guide to configuring your MySQL server, from initial setup to granting necessary privileges. By following these steps, you can ensure that your database is robust, secure, and ready for action. Remember, managing user access and employing strong encryption methods like caching_sha2_password are key practices in safeguarding your data. With your MySQL server properly configured, you can now confidently manage and secure your database environment.