How to Reset the MySQL Password?

Resetting Your MySQL/MariaDB Root Password: Here’s How

If you forget your root password for MySQL or MariaDB, it can be quite frustrating. But don’t worry, you can quickly resolve this issue with a few simple commands. Here’s how you can reset the root password to regain full access.

Prerequisites

Before you start, make sure you can establish an SSH connection to your server. This is necessary to perform the following steps.

Step 1: Establish Connection

SSH Connection: Connect to your server via SSH and open the root command line.
Test MySQL Server: Check if you might still have access to the MySQL server by entering the command mysql. If you don’t get an authentication error, you are already logged in and can skip to the next section.

Step 2: Start MySQL Without Privilege Tables

To reset the password, MySQL needs to be started in a mode where the privilege checking is disabled. This should only be done for the duration of the password reset, as in this mode anyone can access all databases.

Stop MySQL Server: Enter the following command to stop the MySQL server:

   systemctl stop mysql

Start MySQL in Unsafe Mode: Start the MySQL server in unsafe mode:

   mysqld_safe --skip-grant-tables &

You should now see an output indicating that MySQL has started in unsafe mode.

Step 3: Reset Password

Now you can change the password for the root user:

Open MySQL Console: Open the MySQL console:

   mysql

Change Password: Set a new password for the root user:

   update mysql.user set authentication_string=password('new-password') where user = 'root';
   flush privileges;
   quit;

Step 4: Restart MySQL Server

After resetting the password, the MySQL server needs to be restarted in safe mode again:

Stop Unsafe MySQL Server:

   pkill -f mysql

Start Normal MySQL Server:

   systemctl start mysql

Step 5: Test New Credentials

Now test if the new password works:

Command Line: Log in with the new password:

   mysql -uroot -p

Enter the new password when prompted.

phpMyAdmin: Alternatively, you can also test access via phpMyAdmin.

If everything worked, you should have full access to your MySQL or MariaDB server again.

Further Resources

Good luck managing your databases!