Resetting the MySQL and MariaDB Root Password

2019-12-02 By Aaron 9206 Views mysql mariadb root password
0 reviews

Knowing the MySQL/MariaDB root password of your server can come in handy in several cases such as accessing the database server console to manually create databases, create users and associate them with the databases, grant privileges to database users on specific databases, etc.

If you are unsure of the MySQL/MariaDB root user password, then the below steps will guide you on how to reset the root password safely. Resetting/changing the database server root password occasionally is also an important security factor, which helps protect against unauthorized intrusion attempts.


The below steps can be followed to reset the root password for MySQL & MariaDB respectively.


MySQL


  1. Stop MySQL service using the below command.

    # systemctl stop mysqld
    
  2. Restart MySQL server in safe mode by executing the below commands.

    # systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
    # systemctl start mysqld
    

NOTE : --skip-grant-tables option allows you to connect to the service without a password and with all privileges.

  1. Log in to MySQL server as the root user.

    # mysql -u root
    

    The below screenshot shows the entire process. mysql1

  2. In the MySQL prompt, run the following command to set the new password for the root user.

     # UPDATE mysql.user SET authentication_string = PASSWORD('NewPassword') WHERE User = 'root';
    

    mysql2

    NOTE : Replace NewPassword in the above query with your own password.

    Then execute the following queries.

    # FLUSH PRIVILEGES;
    # quit
    

    mysql3

  3. Stop the MySQL service.

    # systemctl stop mysqld
    
  4. Unset the MySQL environment option.

    # systemctl unset-environment MYSQLD_OPTS
    
  5. Start MySQL normally.

    # systemctl start mysqld
    
  6. Confirm the working of the new password by executing the below command, which will prompt you to enter the new password.

    # mysql -u root -p
    

    mysql4


MariaDB


  1. Stop MariaDB service by executing the below command.

    # systemctl stop mariadb
    
  2. Run the below command to start the MariaDB service in safe mode.

    # mysqld_safe --skip-grant-tables &
    

    NOTE : --skip-grant-tables option allows you to connect to the service without a password and with all privileges.

    The below output will be displayed once you execute the above command. Press enter to exit back to the command console.

    mariadb1

  3. Log in to the MariaDB server as the root user.

    # mysql
    
  4. In the MySQL prompt, run the following command to set the new password for the root user.

    # UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';         
    

    mariadb2

    NOTE : Replace newpassword in the above query with your own password.

    Then execute the following queries.

    # FLUSH PRIVILEGES;
    # exit
    

    mariadb3

  5. Stop the MariaDB service and exit the safe mode of MariaDB by executing the below command. You would be prompted to enter the password you set in the previous step.

    # mysqladmin -u root -p shutdown
    

    mariadb4

  6. Start MariaDB service.

    # systemctl start mariadb
    
  7. Confirm the working of the new password by executing the below command, which will prompt you to enter the new password.

    # mysql -u root -p
    

    mariadb5


Related Tutorials

What do you think about this article?

Rate this article
LayerStack Promotion
Need assistance?

Try this guide to receive free bundled services at signup on a new free account.

Sign Up

Your Feedback Is Important

We hope you’ll give the new products and updates a try. If you have an idea for improving our products or want to vote on other user ideas so they get prioritized, please submit your feedback on our Community platform. And if you have any questions, please feel free to ask in the Community or contact our Technical Support team.