Resetting the MySQL and MariaDB Root Password

2019-12-02 By Aaron 30 Views mysql mariadb root password

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 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 root password for MySQL & MariaDB respectively.


MySQL


  1. Stop MySQL service using below command.

    # systemctl stop mysqld
    
  2. Restart MySQL server in safe mode by executing 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 root user.

    # mysql -u root
    

    The below screenshot shows the entire process. mysql1

  2. In the MySQL prompt, run 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 above query with your own password.

    Then execute 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 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 below command to start 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 MariaDB server as root user.

    # mysql
    
  4. In the MySQL prompt, run 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 above query with your own password.

    Then execute 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 previous step.

    # mysqladmin -u root -p shutdown
    

    mariadb4

  6. Start MariaDB service.

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

    # mysql -u root -p
    

    mariadb5