How to Reset MySQL Root Password on Ubuntu

MySQL and MariaDB using root passwords

Switching your database root user to use a standard password instead of the default socket plugin lets you reset MySQL root password credentials more easily. This change applies to MySQL 5.7 and newer versions, as well as MariaDB 10.1 and newer. Run the command to set a strong password so external tools can connect without permission errors.

MySQL:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'type_strong_password_here';

MariaDB:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User='root';

Now reset the forgotten password using the steps below.

How to reset MySQL or MariaDB root password

Reset your MySQL root password by stopping the database server and starting it in a safe mode that skips normal login checks. This lets you access the database prompt without a password so you can create a new login. Use this process on Ubuntu when you lose access to MySQL or MariaDB versions 5.7 and later.

This can be done for MySQL or MariaDB.

sudo systemctl stop mysql
sudo systemctl stop mariadb

After that, run the commands below to start MySQL in safe mode, bypassing standard authentication without loading the grant tables (the internal system tables that store user privileges):

sudo mysqld_safe --skip-grant-tables &

The --skip-grant-tables option lets anyone connect to the database server without a password. This grants full privileges, but only when connecting directly from the local server terminal.

You may need to run these commands for MySQL servers to resolve certain errors.

These commands create a new `mysqld` directory. The `mysql` user then receives ownership of this directory. This prepares the MySQL or MariaDB system for the root password reset, ensuring it is ready for the next step.

sudo mkdir /var/run/mysqld/
sudo chown mysql /var/run/mysqld

While on the local server, run the commands below to log on as root to the database server.

MySQL 5.7.6+ and MariaDB 10.1.20+ require specific commands for resetting the root password. These commands handle password changes correctly for these newer database versions.

UPDATE mysql.user SET authentication_string = PASSWORD('type_new_password_here')
 WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

If the SQL commands above don't work, try the one below.

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'type_new_password_here';
FLUSH PRIVILEGES;

If you're running earlier database server versions, run the commands below to reset the password.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('type_new_password_here');
FLUSH PRIVILEGES;

You should see the following output without errors if everything went well above.

Execute the specific commands to safely stop the database server.

The MySQL or MariaDB database will ask for the new root password you just created. Typing this correct password continues the database shutdown process.

Next, start up either server using the commands below.

sudo systemctl start mysql
sudo systemctl start mariadb

Try to log in with the newly created password now.

sudo mysql -u root -p

If it works, you are all set.

Conclusion:

• Resetting the root password for MySQL or MariaDB is a straightforward process that can be completed without reinstalling the database server.
• Always take note of your MySQL or MariaDB version as the steps may vary slightly based on the version you are using.
• The default authentication method for root users is auth_socket, and switching to mysql_native_password allows password authentication.
• Always ensure that you execute commands with caution, especially when working in safe mode with --skip-grant-tables.
• Regularly updating your passwords and managing user privileges can contribute to the security of your database.
• If you encounter issues, consult the official documentation or community forums for guidance.