Forgetting the root password to a MySQL or MariaDB database is not the end of the world, as there are a few methods to reset the root password and regain access to the database. What's required is local and administrative access to the system.
The common way is to use the --skip-grant-tables method. The process requires stopping the current MySQL or MariaDB server service and starting it in safe mode. You can then connect to the server using the mysql client and then change the password.
All these can be done from the terminal. It should work for Linux and Unix-based systems such as macOS, and the principle can also be applied to Windows.
$ sudo systemctl stop mysql [sudo] password for user:
$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld
$ sudo mysqld --skip-grant-tables & [1] 4225
Skipping the folder creation step above will cause the following error at this step;
2023-02-06T05:04:44.347495Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2023-02-06T05:04:44.348821Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.32-0ubuntu0.22.10.2 (Ubuntu) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_password'; Query OK, 0 rows affected (0.00 sec)
mysql> exit Bye
$ sudo killall mysqld
$ sudo systemctl start mysql
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.32-0ubuntu0.22.10.2 (Ubuntu) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Comment anonymously. Login not required.