How to Change Root Password of MySQL or MariaDB in Linux?

Introduction

As with any password, the root user password for a MySQL or MariaDB database should be changed regularly for security reasons. The root user has complete access and control over all databases and tables within the system, making it a prime target for any potential attacks. Regularly changing the root password can help prevent unauthorized access to your data, as well as provide an added layer of security against potential server breaches.

Checking Current Root Password

A root user is a powerful administrator account that has full access to the MySQL or MariaDB server. It is important to regularly check or change the root password for security reasons, especially if you suspect that someone has gained unauthorized access to your system. In this section, we will cover how to check the current root password of your MySQL or MariaDB server using the SHOW command.

Accessing MySQL or MariaDB Server as Root User

Before checking the current root password, you need to log in to your MySQL or MariaDB server as a root user. To do this, open a terminal window and type −

$ sudo mysql -u root -p

You will be prompted for your sudo password and then for the password associated with the root user account. After entering both passwords correctly, you will gain access to the MySQL or MariaDB shell prompt, which should look similar to −

mysql> 

Using SHOW Command to Display Current Root Password

To check your current root password in MySQL or MariaDB using the SHOW command, enter −

mysql> SHOW VARIABLES LIKE 'password_lifetime';

This command will display information about how long passwords are stored before they expire. If you see a message like "Empty set", it means that there is no expiration date set for passwords on your system. You can also use the following command to see all users and their corresponding permissions −

mysql> SELECT User, Host, authentication_string FROM mysql.user;

The output of this command should include information about all users currently defined on your MySQL or MariaDB system. Now that you have successfully checked your current root password, it is time to move on to the next section and learn how to change it.

Changing Root Password

It is important to change the root password regularly to ensure that your MySQL or MariaDB database remains secure. To do so, you will need to access the MySQL or MariaDB server as the root user and use the ALTER USER command to change the password.

Accessing MySQL or MariaDB Server as Root User

To access your MySQL or MariaDB server as a root user, you will need to log in with the following command −

sudo mysql -u root -p

This command will prompt you for your current root password. Once you enter it correctly, you will be logged in as a root user.

Using ALTER USER Command to Change Root Password

To change your root password using ALTER USER command, enter the following command −

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

The 'root'@'localhost' part of this command specifies that we are changing the password for the root user at localhost. The IDENTIFIED BY 'new_password' part specifies that we are setting a new password for this user.

Choosing a Strong and Secure Password

When choosing a new password for your MySQL or MariaDB database's root account, it is important to choose a strong and secure one. A strong password should have at least 8 characters and include uppercase letters, lowercase letters, numbers, and special characters.

Encrypting the new Password for Added Security

To add an extra layer of security, it is recommended that you encrypt your newly created password before storing it in your database. You can use different encryption algorithms such as SHA256, SHA512, or other strong encryption methods. By following these steps for changing the root password of MySQL or MariaDB in Linux, you can ensure the security and integrity of your database.

Testing New Root Password

Once the new root password has been set, it's important to test that it works properly. Attempt to log in as root using the new password to ensure that it is working correctly. To log in as root from the command line, use the following command −

mysql -u root -p

You will be prompted for your new password. If you are able to successfully log in, then congratulations! Your new password is working and your MySQL or MariaDB server is now more secure.

A Cautionary Tale: Testing with Care

It's important to remember that changing a root password can have significant consequences if not done correctly. Before proceeding with testing, make sure that you have backed up all data and configurations on your server. Additionally, be cautious with testing and avoid breaking important applications or scripts.

Verifying All Applications and Scripts That Use the Old Password Have Been Updated

If you have any applications or scripts that use the old root password, they will need to be updated with the new one after it has been changed. This includes web applications, desktop applications, scripts running on cron jobs or other scheduled tasks.

To ensure that all applications and scripts are updated −

• Identify all systems (application servers, web servers) where MySQL/MariaDB is used.

• Create a list of all user accounts (including system accounts) configured on these systems which require access to MySQL/MariaDB databases using the ‘root’ account credentials

• Update each user account with the newly assigned credentials i.e., change their passwords.

• Edit configuration files for any applications/scripts running on these systems which require access to MySQL/MariaDB databases and update the root password accordingly.

• Test the applications/scripts to ensure they are working as expected using the newly assigned credentials.

It's important to carefully verify that all applications and scripts have been updated with the new root password to ensure that there are no issues with access to your MySQL or MariaDB server. This will ensure that your server is secure and protected from unauthorized access going forward.

Conclusion

The importance of regularly changing passwords cannot be overstated, especially when it comes to sensitive information such as root passwords for MySQL or MariaDB. Regular password changes can help prevent brute force attacks and unauthorized access to your database. Additionally, updating passwords regularly ensures that old passwords are not inadvertently shared or used by unauthorized users.

Throughout this tutorial, we have covered the essential steps involved in changing the root password in MySQL or MariaDB. First, we learned how to check the current root password using the SHOW command. Next, we covered how to change the root password using ALTER USER command and emphasized the importance of choosing a strong and secure password.