Skip to main content

How to change the MySQL password for root and regular users

· 3 min read
Andres Saar
Customer Care Engineer

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. 

info

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:

mysql -u root -p 

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 Debian and Ubuntu:
systemctl stop mysql
  • For CentOS/RockyLinux/AlmaLinux:
systemctl stop mysqld

Step 2: Restart the server in safe mode​

  • For Debian and Ubuntu:

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:
FLUSH PRIVILEGES;

 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:

FLUSH PRIVILEGES;

Replace NewPassword with your desired new password.

info

'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:
FLUSH PRIVILEGES;

 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:

FLUSH PRIVILEGES;

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 Debian and Ubuntu:
systemctl start mysql
  • For CentOS/RockyLinux/AlmaLinux:
systemctl start mysqld

 How to reset the password for regular users​

If you forgot the password for a user other than root, the approach is similar:

  1. Connect to the MySQL server as root:
mysql -u root -p

 Enter your MySQL root password.

  1. Execute the command to change the password:
  • MySQL 5.7:

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:

FLUSH PRIVILEGES;

Replace username with the existing username and NewUserPassword with the new desired password.

Exit MySQL using the exit command or by pressing “Ctrl + D”.

  • MySQL 8.0+:

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:

nano /root/.my.cnf

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:

chmod 600 /root/.my.cnf

After this setup, you can connect by simply running the command:

mysql -u root