How to set or reset MySQL or MariaDB root password

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.

1. Stop the MySQL service.

   $ sudo systemctl stop mysql
   [sudo] password for user:

   Related: How to manage MySQL or MariaDB service in Linux

2. Create folder for mysqld socket if it does not exist.

   $ sudo mkdir -p /var/run/mysqld

3. Set appropriate ownership for mysqld's run folder.

   $ sudo chown mysql:mysql /var/run/mysqld

4. Start MySQL without loading the grant tables.

   $ 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.

5. Connect to MySQL server.

   $ 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> 

6. Reload grant tables for MySQL server.

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.01 sec)

7. Set new password for root user.

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_password';
   Query OK, 0 rows affected (0.00 sec)

8. Exit MySQL shell.

   mysql> exit
   Bye

9. Stop mysqld process.

   $ sudo killall mysqld

10. Start MySQL server using systemd.

    $ sudo systemctl start mysql

11. Try connecting using new password.

    $ 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>