How to Reset MySQL or MariaDB Root Password

MySQL and MariaDB using root passwords

Switching the MySQL or MariaDB root user from `auth_socket` to a password simplifies access management. This method helps if you forget the root password or need to change it later. The password method works for MySQL 5.7 and newer, and MariaDB 10.1 and newer.

MySQL:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'type_strong_password_here';

MariaDB:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User='root';

Now you can reset the forgotten password using the steps below.

How to reset MySQL or MariaDB root password

You can reset your MySQL or MariaDB root password by stopping the database server and starting the database server in a special mode. This special mode lets you set a new password without needing the old password, skipping normal login checks. This password reset process works for both MySQL and MariaDB, versions 5.7 and later.

This can be done for MySQL or MariaDB.

sudo systemctl stop mysql
sudo systemctl stop mariadb

After that, run the commands below to start MySQL in safe mode, bypassing the standard authentication process without loading the grant tables:

sudo mysqld_safe --skip-grant-tables &

Using the --skip-grant-tables option lets anyone connect to the database server without a password, granting them all privileges. However, this is only possible if you connect from the local server terminal console, so be mindful of this limitation.

You may need to run these commands for MySQL servers to resolve certain errors.

These commands create a new `mysqld` directory. The `mysql` user then receives ownership of this directory. This prepares the MySQL or MariaDB system for the root password reset, ensuring it’s ready for the next step.

sudo mkdir /var/run/mysqld/
sudo chown mysql /var/run/mysqld

While on the local server, run the commands below to log on as root to the database server.

MySQL 5.7.6+ and MariaDB 10.1.20+ require specific commands for resetting the root password. These commands handle password changes correctly for these newer database versions.

UPDATE mysql.user SET authentication_string = PASSWORD('type_new_password_here')
 WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

If the SQL commands above don’t work, try the one below.

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'type_new_password_here';
FLUSH PRIVILEGES;

If you’re running earlier database server versions, run the commands below to reset the password.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('type_new_password_here');
FLUSH PRIVILEGES;

You should see the following output without errors if everything went well above.

The MySQL or MariaDB database will ask for the new root password you just created. Typing this correct password continues the database shutdown process.

Next, you can start up either server using the commands below.

sudo systemctl start mysql
sudo systemctl start mariadb

You can now try to log in with the newly created password.

sudo mysql -u root -p

If it works, then you’re all set.

Conclusion:

• Resetting the root password for MySQL or MariaDB is a straightforward process that can be completed without reinstalling the database server.
• Always take note of your MySQL or MariaDB version as the steps may vary slightly based on the version you are using.
• The default authentication method for root users is auth_socket, and switching to mysql_native_password allows password authentication.
• Always ensure that you execute commands with caution, especially when working in safe mode with --skip-grant-tables.
• Regularly updating your passwords and managing user privileges can contribute to the security of your database.
• If you encounter issues, consult the official documentation or community forums for guidance.