MySQL and MariaDB are popular relational database management systems used for storing and managing data. The root user in MySQL and MariaDB has extensive privileges and control over the databases, making it a prime target for potential security breaches. It is crucial to change the root password regularly to enhance the security of your system. In this article, we will explore the step-by-step process of changing the root password of MySQL or MariaDB in Linux.
How to Change Root Password of MySQL or MariaDB in Linux:
Changing the root password of MySQL or MariaDB in Linux is a fairly straightforward process. First, you need to log in to the machine as the root user. Once logged in, you can use the mysqladmin program to change the root password by running the command “mysqladmin -u root password ‘new_password'”. After entering the command, the root password of MySQL or MariaDB will be changed to the new_password you provided. You can also use the command line program mysql to change the root password by running the command “SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘new_password’);”. Once the command is executed, the root password will be changed to the new_password you provided. It is important to remember to use a strong password for your root user to ensure the security of your database.
Why Change Root Password Regularly?
Changing the root password at regular intervals is essential for maintaining the security of your MySQL or MariaDB server. The root user has complete access and control over all databases and tables within the system. By changing the root password periodically, you can prevent unauthorized access to your data and protect against potential server breaches.
Checking the Current Root Password
Before changing the root password, it is important to verify the current password. To do this, you need to log in to your MySQL or MariaDB server as a root user. Open a terminal window and enter the following command:
$ sudo mysql -u root -p
You will be prompted to enter your sudo password and then the password associated with the root user account. Once authenticated, you will gain access to the MySQL or MariaDB shell prompt, which should appear as follows:
mysql>
To check the current root password using the SHOW command, enter the following command at the prompt:
mysql> SHOW VARIABLES LIKE 'password_lifetime';
If the output shows “Empty set,” it means that no expiration date is set for passwords on your system. You can also use the following command to view all users and their corresponding permissions:
mysql> SELECT User, Host, authentication_string FROM mysql.user;
This command will display information about all users defined on your MySQL or MariaDB system.
Changing the Root Password
To change the root password, you need to access the MySQL or MariaDB server as the root user and use the ALTER USER command. Follow these steps:
- Log in to your MySQL or MariaDB server as the root user using the following command:
sudo mysql -u root -p
- Enter your current root password when prompted.
- Use the ALTER USER command to change the root password. The command syntax is as follows:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Replace ‘new_password’ with the desired password of your choice. It is crucial to choose a strong and secure password that includes a combination of uppercase and lowercase letters, numbers, and special characters.
- Once you have entered the ALTER USER command, press Enter to execute it.
Encrypting the New Password for Added Security
To add an extra layer of security, it is recommended to encrypt the newly created password before storing it in your database. Several encryption algorithms, such as SHA256 and SHA512, can be used for this purpose. Encrypting the password helps protect it from being easily compromised in case of a security breach.
Testing the New Root Password
After setting the new root password, it is essential to test whether it works correctly. Attempt to log in as the root user using the new password to ensure that it is functioning as expected. Use the following command to log in as root from the command line:
mysql -u root -p
You will be prompted to enter your new password. If you can successfully log in, congratulations! Your new password is working, and your MySQL or MariaDB server is now more secure.
A Cautionary Tale: Testing with Care
Changing the root password is a critical task that can have significant consequences if not done correctly. Before proceeding with testing, ensure that you have backed up all data and configurations on your server. Exercise caution during testing to avoid breaking any important applications or scripts.
Verifying All Applications and Scripts
After changing the root password, it is crucial to update all applications and scripts that use the old password. This includes web applications, desktop applications, and scripts running on cron jobs or other scheduled tasks. Follow these steps to ensure that all applications and scripts are updated:
- Identify all systems (application servers, web servers) where MySQL or MariaDB is used.
- Create a list of all user accounts (including system accounts) configured on these systems that require access to MySQL or MariaDB databases using the ‘root’ account credentials.
- Update each user account with the newly assigned credentials by changing their passwords.
- Edit configuration files for any applications/scripts running on these systems that require access to MySQL or MariaDB databases and update the root password accordingly.
- Test the applications/scripts to ensure they are working as expected using the newly assigned credentials.
Verifying that all applications and scripts have been updated with the new root password is crucial to ensure smooth access to your MySQL or MariaDB server and prevent any unauthorized access attempts.
Regularly changing the root password of your MySQL or MariaDB server is vital for maintaining the security and integrity of your data. By following the step-by-step process outlined in this article, you can effectively change the root password and enhance the protection of your system. Remember to choose a strong and secure password, encrypt it for added security, and update all applications and scripts using the old password. By taking these precautions, you can safeguard your MySQL or MariaDB server against potential security breaches.
