MySQL Installation: Manage Root and User Accounts

Enhance MySQL Security: Manage Root and User Accounts

Secure your MySQL database by setting a strong password for the root user and creating a new user with appropriate privileges. This guide covers using the mysql_secure_installation tool and secure password hashing to improve your MySQL installation.

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

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

• Example:

  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