How to set or reset MySQL or MariaDB root password

Forgetting the root password in MySQL or MariaDB can lock you out of critical administrative functions. However, with local administrative access, you can reset or set a new root password using methods that bypass the regular authentication system. One of the most reliable methods to do this is by starting the database server with the --skip-grant-tables option, which temporarily disables the need for authentication.

This method involves stopping the MySQL or MariaDB service, starting it in safe mode (without loading user privileges), and then changing the root password from within the database. This process is suitable for Linux, macOS, and Unix systems, with slight variations for Windows environments.

By following these steps, you can reset the root password for MySQL or MariaDB and regain full access to the database. Once the password is reset, it is crucial to restart the database securely to prevent unauthorized access.

1. Stop the MySQL or MariaDB service.

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

   This step ensures that the database service is halted before we start it in a bypass mode.

2. Create the folder for the mysqld socket if it doesn’t exist.

   $ sudo mkdir -p /var/run/mysqld

3. Set the correct ownership for the mysqld run folder.

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

   Setting the correct ownership allows the mysqld process to run without errors related to socket creation.

4. Start the MySQL service without loading grant tables to bypass authentication.

   $ sudo mysqld --skip-grant-tables &
   [1] 4225

   If the directory creation step is skipped, you might encounter this error:

   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 doesn't exist. 

5. Log in to the MySQL or MariaDB server using the mysql client.

   $ 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)
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

6. Reload the grant tables to restore normal user privilege operations.

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

7. Set a new password for the root user.

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

   Replace 'your_password' with a secure new password. For MariaDB versions, the command may be slightly different:

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

8. Exit the MySQL shell.

   mysql> exit
   Bye

9. Stop the mysqld process running without authentication.

   $ sudo killall mysqld

10. Restart the MySQL or MariaDB service securely using systemd.

    $ sudo systemctl restart mysql

11. Test the new root password by logging in.

    $ 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)
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>

    Successful login confirms that the root password reset was successful.