Tool Logo
MySQL

MySQL Installation: Manage Root and User Accounts

MySQL Installation: Manage Root and User Accounts
2 min read
#MySQL

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