MySQL Root User: Set the Root Password and Create a New User

To set the MySQL root password, log in with sudo mysql and run:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_strong_password';

Then run mysql_secure_installation and create a separate admin user with CREATE USER and GRANT, so you don't log in as root day to day. Every step is below.

Prerequisites

• MySQL server installed on your Linux server/VPS.
• SSH access to your server with root privileges.

Important Note

• Avoid Root for Everyday Use: It’s highly recommended to avoid using the root user for everyday database operations. Instead, create a new user for regular tasks.
• Grant Least Privileges: Only grant the necessary privileges to new users based on their specific needs.

What is mysql_secure_installation?

This tool enhances the security of your MySQL installation by allowing you to:

• Set a password for root accounts.
• Remove root accounts accessible from outside the local host.
• Remove anonymous-user accounts.
• Delete the test database and privileges allowing access to databases starting with test_.

What is caching_sha2_password?

caching_sha2_password is an authentication plugin using SHA-256 password hashing, offering stronger encryption than the native authentication method. Other plugins, such as mysql_native_password, are also available.

Install MySQL (If Not Installed)

apt update
apt upgrade
apt install mysql-server

Login to MySQL

mysql

Set Password for Root

• Syntax:

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

  Copy
• Example:

  ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Hello123456#';
  

  Copy

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';
  

  Copy
• Example:

  CREATE USER 'kumar'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Hello123456#';
  

  Copy

Grant New User All Privileges

• Syntax:

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

  Copy
• Example:

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

  Copy

Exit from MySQL

exit

Restart Web Server

• If Apache:

  service apache2 restart
  

  Copy

• If Nginx:

  service nginx restart
  

  Copy