In this tutorial, we will guide you through resetting the root password for MySQL 8.0 and MariaDB 10.4. If you are having issues with the root user or are unable to access database due to an authentication error, it’s probably time to reset the password. Below are the steps to reset the root password safely.

When to Reset the Root Password

If you encounter an error message indicating that the root password isn’t working or the MySQL root user has issues, it’s time to reset the password.

Example error message:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Steps to Reset the Root Password

1. Stop the MySQL/MariaDB Service

Before making any changes, stop the MySQL or MariaDB service to ensure no processes interfere with the reset operation.

Command:

systemctl stop mysqld / systemctl stop mariadb
service mysqld stop / service mariadb stop

2. Start MySQL with the –skip-grant-tables Option

Start the database server in a mode that bypasses the privilege system, allowing you to reset the root password without authentication.

Command:

mysqld --skip-grant-tables --user=root &

If the root user command doesn’t work, you can try this alternative:

mysqld --skip-grant-tables --user=mysql &

Once executed, press the Enter key to return to the shell.

3. Access the MySQL Command-Line Interface (CLI)

Log in to the MySQL CLI and refresh the system privileges to ensure changes take effect.

Command:

mysql
FLUSH PRIVILEGES;

4. Reset the Root Password

Update the root password with a secure value of your choice.

Command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root_passowrd';

Replace root_password with your desired password.

Exit the MySQL CLI by typing:

quit

5. Kill All MySQL Processes

Terminate any running MySQL processes to clear any lingering sessions.

Command:

killall -u mysql

To ensure no unwanted environment variables are set, use:

systemctl unset-environment MYSQLD_OPTS

6. Restart MySQL/MariaDB

Restart the MySQL or MariaDB service to apply the changes.

Command:

systemctl stop mysqld.service && systemctl restart mysqld.service
service mysqld stop && service mysqld restart

7. Verify the Reset

Log in as the root user with the new password to confirm the reset was successful.

For Control Panels (e.g., CWP)

If you’re using a control panel like CWP, make sure to update the root password in the corresponding configuration files, such as /root/.my.cnf.

Commands to update CWP files:

/root/.my.cnf
/usr/local/cwpsrv/htdocs/resources/admin/include/db_conn.php

Conclusion

By following these steps, you can reset the root password for MySQL 8.0 and MariaDB 10.4/10.5 with minimal disruption. Always remember to use a strong password and secure your database configuration files after the reset.