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