Person's Hand on Laptop

MySQL Installation: Manage Root and User Accounts

ByShubham Nimje

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

Enhance the security of your MySQL database by setting a strong password for the root user and creating a separate user with appropriate privileges. This guide walks you through the process using the mysql_secure_installation tool and secure password hashing.

Prerequisites:

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

Important Note:

  • Avoid Root for Everyday Use: It’s highly recommended to avoid using the root user for everyday database operations. This guide creates a new user for such purposes.
  • Grant Least Privileges: Grant only the necessary privileges to the new user based on their specific needs.

What is mysql_secure_installation ?

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

  • You can set a password for root accounts
  • You can remove root accounts that are accessible from outside the local host.
  • You can remove anonymous-user accounts.
  • 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.

  • 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';
Example:- ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password by 'Hello123456#';
  • 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 'kumar'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Hello123456#';
  • Grant New User All Privileges
Syntax:- GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;
Syntax:- GRANT ALL PRIVILEGES ON *.* TO 'kumar'@'localhost' WITH GRANT OPTION;
  • Exit from MySQL
exit
  • Restart Web Server
- If Apache
service apache2 restart

- If Nginx
service nginx restart