Managing MySQL databases is a critical task for any database administrator or developer. One common task is changing or resetting a MySQL user’s password. This can be necessary for various reasons, such as security updates, forgotten passwords, or periodic password changes. In this guide, we’ll walk you through the process of changing or resetting a MySQL user’s password from the terminal, covering both MySQL and MariaDB, which is a popular fork of MySQL.

Prerequisites

Before we dive into the steps, ensure that you have the following prerequisites:

• Access to the Terminal: You need terminal access to the server where MySQL is running.
• MySQL Administrative Privileges: You should have root or administrative privileges to change or reset passwords.
• MySQL or MariaDB Installed: This guide assumes that you have either MySQL or MariaDB installed on your system.

Section 1: Changing a MySQL User Password

Step 1: Log in to MySQL

To start, you need to log in to the MySQL server using the terminal. Use the following command:

mysql -u root -p

You will be prompted to enter the root password. Once authenticated, you will be in the MySQL shell.

Step 2: Select the MySQL Database

In MySQL, user information is stored in the mysql user database. Switch to this database using the following command:

USE mysql;

Step 3: Change the Password

There are multiple ways to change the password. Here, we’ll cover the most common methods.

Method 1: Using the SET PASSWORD Statement

The SET PASSWORD statement is a straightforward way to change a user’s password. Replace yourusername with the actual username and newpassword with the new password:

SET PASSWORD FOR 'yourusername'@'localhost' = PASSWORD('newpassword');

Method 2: Using the UPDATE Statement

You can also change the password by directly updating the user table. This method involves updating the authentication_string column (or password column in older versions) in the user table. Here’s how:

UPDATE user SET authentication_string = PASSWORD('newpassword') WHERE User = 'yourusername' AND Host = 'localhost';

After running the update command, make sure to flush the privileges to apply the changes:

FLUSH PRIVILEGES;

Method 3: Using the ALTER USER Statement

The ALTER USER statement is the recommended method for MySQL 5.7.6 and newer:

ALTER USER 'yourusername'@'localhost' IDENTIFIED BY 'newpassword';

This method is preferred for its clarity and simplicity.

Step 4: Verify the Change

To ensure that the password has been successfully changed, try logging in with the new password:

mysql -u yourusername -p

Enter the new password when prompted. If you can log in, the password change was successful.

Section 2: Resetting a Forgotten MySQL User Password

In some cases, you may need to reset a password without knowing the current one. This typically requires restarting the MySQL server in a special mode.

Step 1: Stop the MySQL Service

First, stop the MySQL service using the following command. The exact command may vary depending on your Linux distribution:

sudo systemctl stop mysql

For systems using init.d:

sudo /etc/init.d/mysql stop

Step 2: Start MySQL in Safe Mode

Next, start the MySQL server in safe mode with the –skip-grant-tables option. This allows you to connect to the server without a password and with all privileges:

sudo mysqld_safe --skip-grant-tables &

Step 3: Log in to MySQL

Log in to the MySQL server as root without a password:

mysql -u root

You will now have full access without needing a password.

Step 4: Reset the Password

Use the UPDATE statement to reset the password. For example, to reset the root password:

UPDATE mysql.user SET authentication_string = PASSWORD('newpassword') WHERE User = 'root';

For older versions of MySQL, you may need to use the password column instead:

UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE User = 'root';

Flush the privileges to apply the change:

FLUSH PRIVILEGES;

Step 5: Restart MySQL

Stop the MySQL server again:

sudo systemctl stop mysql

Or for systems using init.d:

sudo /etc/init.d/mysql stop

Then, restart the MySQL service normally:

sudo systemctl start mysql

Or:

sudo /etc/init.d/mysql start

Step 6: Verify the Change

Try logging in with the new password to ensure that it works:

mysql -u root -p

Enter the new password when prompted.

Section 3: Security Best Practices

Changing or resetting MySQL passwords is crucial, but it’s also important to follow security best practices to protect your database.

1. Use Strong Passwords

Ensure that all MySQL user passwords are strong and complex. Avoid using simple or easily guessable passwords. Use a combination of upper and lower case letters, numbers, and special characters.

2. Regularly Update Passwords

Regularly update MySQL passwords to minimize the risk of unauthorized access. Implement a password rotation policy that requires users to change their passwords periodically.

3. Restrict User Privileges

Grant users the minimum privileges necessary to perform their tasks. Avoid using the root account for routine operations and create specific users with limited permissions.

4. Monitor Login Attempts

Monitor and log all login attempts to your MySQL server. Use tools and scripts to detect and alert on suspicious activities, such as multiple failed login attempts.

5. Use SSL/TLS for Connections

Enable SSL/TLS to encrypt connections between MySQL clients and the server. This ensures that sensitive information, including passwords, is transmitted securely.

Conclusion

Changing or resetting a MySQL user’s password from the terminal is a fundamental skill for database administrators. By following the steps outlined in this guide, you can effectively manage user passwords, ensuring the security and integrity of your MySQL databases. Always remember to adhere to security best practices to safeguard your data from unauthorized access and potential threats.