Change mysql user password using command line
How to Change Your MySQL User Password from the Command Line
Learn the essential command-line methods to securely reset or update MySQL user passwords, crucial for database administration and security.
Changing a MySQL user's password is a fundamental administrative task, whether you're setting up a new user, rotating credentials for security, or recovering access to a forgotten account. This guide will walk you through the most common and secure methods to achieve this directly from the command line, which is particularly useful for server environments or when using tools like PuTTY for remote access.
Prerequisites and Initial Access
Before you can change a MySQL user's password, you need to have appropriate administrative privileges. This usually means logging in as the root user or another user with ALTER USER or UPDATE privileges on the mysql.user table. You'll typically access the MySQL command line client via your server's terminal or an SSH client like PuTTY.
flowchart TD
A[Start] --> B{Access MySQL Server};
B --> C{Login as Root or Admin User};
C --> D{Choose Password Change Method};
D -- Method 1 --> E[ALTER USER Statement];
D -- Method 2 --> F[UPDATE mysql.user Table];
E --> G[Flush Privileges];
F --> G[Flush Privileges];
G --> H[Verify New Password];
H --> I[End];Workflow for changing a MySQL user password.
1. Connect to MySQL
Open your terminal or SSH client (e.g., PuTTY) and connect to your server. Then, log in to the MySQL command-line client as a user with administrative privileges (e.g., root).
2. Enter Password
When prompted, enter the password for the MySQL user you are logging in as. If you are logging in as root and have no password set, you can just press Enter.
mysql -u root -p
Command to log in to MySQL as the root user.
Method 1: Using ALTER USER (Recommended for MySQL 5.7.6+)
The ALTER USER statement is the most secure and recommended way to change passwords in modern MySQL versions (5.7.6 and later). It handles password hashing and security best practices automatically.
ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewStrongPassword!';
FLUSH PRIVILEGES;
Changing a user password using ALTER USER and flushing privileges.
'username' with the actual MySQL username and 'localhost' with the correct host (e.g., '%' for any host, or a specific IP address). Always choose a strong, unique password.Method 2: Using UPDATE on mysql.user Table (Older Versions or Specific Scenarios)
For older MySQL versions (prior to 5.7.6) or in specific recovery scenarios, you might need to directly update the mysql.user table. This method requires you to manually hash the password using PASSWORD() or SHA2() functions, depending on your MySQL version and authentication plugin.
MySQL 5.6 and earlier
UPDATE mysql.user SET Password=PASSWORD('NewStrongPassword!') WHERE User='username' AND Host='localhost'; FLUSH PRIVILEGES;
MySQL 5.7.5 and earlier (SHA1)
UPDATE mysql.user SET authentication_string=PASSWORD('NewStrongPassword!') WHERE User='username' AND Host='localhost'; FLUSH PRIVILEGES;
MySQL 5.7.6+ (SHA2 - if not using ALTER USER)
UPDATE mysql.user SET authentication_string=SHA2('NewStrongPassword!', 256) WHERE User='username' AND Host='localhost'; FLUSH PRIVILEGES;
mysql.user table is less secure and more prone to errors than ALTER USER. Ensure you use the correct hashing function for your MySQL version and authentication plugin. Always FLUSH PRIVILEGES after direct table updates for changes to take effect.Verifying the Password Change
After changing the password, it's crucial to verify that the new credentials work correctly. Exit the root session and try logging in with the updated user and new password.
exit;
mysql -u username -p
Exiting the current MySQL session and attempting to log in with the updated user.
If you can log in successfully, your password change was successful. If not, double-check your commands, the username, host, and the new password for any typos.