MySQL Change Root Password

mysql change root password

MySQL is a popular, open-source database system used in many of today’s web-based projects. A database essentially allows users to store and review data based on what has been defined within it. If you are creating a web app and decide to use MySQL as your database system of choice, then at some point it may happen to you need to change or recover your root password.

This article explains how to perform a MySQL change root password for Linux users as well as how to recover or reset your existing password in case it is forgotten.

How To: MySQL Change Root Password

There are a couple of ways to change your MySQL root password. This section will outline both methods which can be used.

Method #1

The first method is for changing your root password given that you already know the existing password. This involves using a simple mysqladmin command.

  • If you are setting up your MySQL password for the first time this can be done by running the following command (ensure you replace “newpassword” with your actual password).
    mysqladmin -u root password newpassword
  • If you have an existing password, however need to update it, this can be achieved with the following command (ensure you replace “oldpassword” with your existing password and “newpassword” with your new password).
    mysqladmin -u root -p oldpassword newpassword

    Additionally, if you want to update the old password of a specific MySQL user, you can simply define the username of that user by replacing “root”, for example:

    mysqladmin -u userX -p oldpassword newpassword

Method #2

Alternatively, if you cannot remember your existing root password, use the following steps to bypass the password prompt and define a new MySQL password.

  1. Stop All MySQL processes with the following command.
    /etc/init.d/mysql stop
  2. Start the MySQL server with --skip-grant-tables & to avoid a password prompt.
    mysqld_safe --skip-grant-tables &
  3. Connect to MySQL without defining a password as follows:
    mysql -u root
  4. Define the new root password by running the following (ensure you replace “newpassword” with your actual new password).
    use mysql;
    update user set password=PASSWORD("newpassword") where User='root';
    flush privileges;
    quit
  5. Once you have defined your new root password, stop the MySQL processes.
    /etc/init.d/mysql stop

    Then, start MySQL again with the following command.

    /etc/init.d/mysql start

Verify The New Password is Working

Now that you have recovered or updated your MySQL password, you need to verify that it is working properly. To do so, try logging in to your MySQL service by using the following command mysql -u root -p. If you are testing a user other than “root” replace that portion of the command with the name of the actual user.

Once you press Enter, MySQL will prompt you for your password. Simply enter the password you just defined in the steps above and you should then see a message similar to the following.

mysql root login

That’s it! If you have successfully logged into MySQL then you can be sure that your new password is working as expected.