Forgot the MySQL root password? Don't worry, it happens even to experienced administrators. In this article, we’ll explain how to reset the MySQL root password for different versions: MySQL 5.7 and MySQL 8.0+. Additionally, we’ll cover how to reset the password for a regular database user.
For MariaDB please, refer to our separate guide.
Root is the main database administrator. They have full access to all data and settings. If you lose this password, modifying certain settings and executing some commands will no longer be possible.
All operations will be performed via the command line over SSH. You can find more detailed instructions on connecting to your server using this protocol in this article.
Before proceeding, check the contents of the /root/.my.cnf file. Often, it contains the current root password for accessing MySQL.
You can test the connection using the command:
Then, enter the password from the .my.cnf file.
If the password doesn’t work, follow the instructions below.
How to reset root MySQL password
Step 1: Stop MySQL
To reset the password, you first need to stop the MySQL. Enter the following command:
- For CentOS/RockyLinux/AlmaLinux:
Step 2: Restart the server in safe mode
Make sure that the directory from which MySQL will be run exists and has the appropriate owner:
mkdir -p /var/run/mysqld/ && chown -R mysql: /var/run/mysqld/
Start the MySQL server without access control:
mysqld_safe --skip-grant-tables --socket=/var/run/mysqld/mysqld.sock &
If MySQL starts correctly, you’ll see a message similar to:
2024-11-28T23:50:19.298141Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
To continue working in the command line, press “Ctrl + C”.
- For CentOS/RockyLinux/AlmaLinux:
sudo -u mysql mysqld --skip-grant-tables --socket=/var/run/mysqld/mysqld.sock &
Step 3: Connect to MySQL
mysql --socket=/var/run/mysqld/mysqld.sock
Step 4: Reset the password
- For MySQL 5.7, execute the following commands sequentially:
For a local user:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
For a network user (if it exists):
ALTER USER 'root'@'%' IDENTIFIED BY 'NewPassword';
Then:
Replace NewPassword with your desired new password.
'user'@'localhost' refers to a local user connecting via socket or localhost.
'user'@'%' refers to a network user with access from any IP address.
You can set the same or different passwords for these users. If the network user doesn’t exist, the @'%' command will result in an error.
Exit MySQL using the exit command or by pressing “Ctrl + D”.
- For MySQL 8.0+, execute the following commands:
For a local user:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassword';
For a network user (if it exists):
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'NewPassword';
Then:
Replace NewPassword with your desired new password.
Exit MySQL using the exit command or by pressing “Ctrl + D”.
Step 5: Restart MySQL
Stop MySQL:
mysqladmin shutdown --socket=/var/run/mysqld/mysqld.sock -p
Then, enter the previously set root password.
Start MySQL in normal mode:
- For CentOS/RockyLinux/AlmaLinux:
How to reset the password for regular users
If you forgot the password for a user other than root, the approach is similar:
- Connect to the MySQL server as root:
Enter your MySQL root password.
- Execute the command to change the password:
For a local user:
ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewUserPassword';
For a network user (if it exists):
ALTER USER 'username'@'%' IDENTIFIED BY 'NewUserPassword';
Then:
Replace username with the existing username and NewUserPassword with the new desired password.
Exit MySQL using the exit command or by pressing “Ctrl + D”.
For a local user:
ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassword';
For a network user (if it exists):
ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'NewPassword';
Replace username with the existing username and NewUserPassword with the new desired password.
Then exit MySQL using the exit command or the “Ctrl + D” key combination.
If necessary, you can list all users with the following query:
SELECT User, Host FROM mysql.user;
How to connect to MySQL locally without entering a password
To avoid entering the password manually every time you connect to MySQL from the command line on your server, you can save it in the /root/.my.cnf file. Open the file in a text editor:
Add the following lines:
[client]
user = root
password = YourRootPassword
Replace YourRootPassword with your actual root password.
Then save the file using the “Ctrl + O” key combination and exit the text editor with “Ctrl + X”.
For security, set stricter permissions on the .my.cnf file:
After this setup, you can connect by simply running the command:
