Update/Reset Mysql Root Password

Today we will learn, How to change/reset/update MySQL root password.

First, the most important thing is "Are you able to access the data using root credentials."

If No, Please follow the below steps:

  • Log-in to your server using SSH.
  • Stop Mysql Service:

For CentOS and Fedora, Run below command:

1service mysqld stop

For Ubuntu, Run below command:

1service mysql stop
  • Restart the MySQL server with the —skip-grant-tables option.

To do this, type the following command:

1mysqld_safe --skip-grant-tables &

Note: Make sure you type the ampersand (&) at the end of the command. This runs the command in the background and allows you to type the commands in the following steps.

  • Now, Log into MySQL using the following command:
1mysql

===============================================================================

If you know, the current MYSQL root password. Please follow the below steps:

  • Connect to MySQL: Run the following command to connect and enter the password:
1mysql -u root -p
  • Check user table, if table has Password column or authentication_string column.
1Select * from user;
1+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+
2| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired |
3+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+
4| localhost | root | *18B0EFA631786792E382200C640D11088DDF1B98 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                |
5| 127.0.0.1 | root | *18B0EFA631786792E382200C640D11088DDF1B98 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                |
6| ::1       | root | *18B0EFA631786792E382200C640D11088DDF1B98 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                |
7+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+
  • Reset/Update the MySQL root password:

    If password column is present and has values, Run the following command:

1update user set password=PASSWORD("mynewpassword") where User='root';

Or

if password column is not present AND "authentication_string" column has values, Run below command to reset the password:

1update user set authentication_string=PASSWORD("mynewpassword") where User='root';
  • Lastly flush the privileges and exit from mysql.
1flush privileges;
2
3quit

I :heart: Linux! :smile: Enjoy