How to Reset MySQL or MariaDB Root Password

MySQL and MariaDB using root passwords

MySQL and MariaDB sometimes use a special login called auth_socket for the root user, meaning you can’t just type a password to log in. This method can be tricky if you forget your password or need to change it. Luckily, you can switch to a standard password method to make managing your root access easier.

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

Resetting your MySQL or MariaDB root password involves a few key steps, starting with stopping the database server. You’ll then restart it in a special mode that lets you bypass the usual login checks. This allows you to set a new password without needing the old one, making it a straightforward process to regain access.

You can do this 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.

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

Then run the commands below to create a new mysqld directory and make the mysql user its owner.

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 -u root

Next, run the following commands if you’re using MySQL 5.7.6 and later, or MariaDB 10.1.20 and later:

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.

Query OK, 0 rows affected (0.00 sec)

Now that the root password has been reset, you can stop the server normally by running the commands below.

mysqladmin -u root -p shutdown

You will be prompted for the new password you just created. Type it to continue with the shutdown.

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.