Reset MySQL Root Password


When We need to reset the password. This can be done in two ways

Method 1

1) Add following parameter under mysqld section in /etc/my.cnf or any other custom parameter file


2) Restart the mysql server

3) Now you should be able to login to server without password

-bash-4.1$ mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version:

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.


4) Next we need to reset the password

mysql> update mysql.user set password=password(‘root’) where user=’root’;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

5) Restart the mysql server and try connecting with password

-bash-4.1$ mysql -u root -passsword
Your MySQL connection id is 1

This approach is widely used but has serious security concerns. This approach allows anyone to connect to mysql root user without password.
e.g I am connecting from remote machine when mysql was started with ‘skip-grant-tables’ option

-bash-4.1$ mysql -u root -h

mysql> select hostname();
ERROR 1305 (42000): FUNCTION hostname does not exist
mysql> select @@hostname;
| @@hostname |
| mysqldev01 |
1 row in set (0.00 sec)

One option is to use bind-address= in my.cnf which will disable remote connections. But again this is not fool proof.

Method 2:

This is one more way which is safer and recommended way of resetting passwords

1) Create a text file with following line say tmp_mysql.txt. Using new password to ensure that this file is read and correctly executed

update mysql.user set password=password(‘securepass’) where user=’root’;
flush privileges;

2) Edit /etc/my.cnf file and add following parameter under mysqld


3) Restart mysql server process and you will be able to connect using specified password

bash-4.1$ mysql -u root -paskdba
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
-bash-4.1$ mysql -u root -psecurepass

I tried with old password first to ensure that it doesn’t work. We are able to login to successfully login using “securepass” password


Method 3- Reset MySQL Forgotten Root PASSWORD

Start session ssh (using root if possible).
Edit my.cnf file using.

sudo vi /etc/my.cnf
Add line to mysqld block.*

Save and exit.
Restart MySQL service.

You can just start the server with skip-grant-tables from the command line:

OR Directly start with mysqld_safe –skip-grant-tables &

service mysqld restart
Check service status.

service mysql status
Connect to mysql.

Using main database.

use mysql;
Redefine user root password.

UPDATE user SET Password = PASSWORD(‘myNuevoPassword’) WHERE User = ‘root’;
Edit file my.cnf.

sudo vi /etc/my.cnf
Erase line.

Save and exit.
Restart MySQL service.

service mysqld restart
Check service status.

service mysql status
Connect to database.

mysql -u root -p
Type new password when prompt.
This action is too dangerous, it allows anyone to connect to all databases with no restriction without a user and password. It must be used carefully and MOST be reverted quickly to avoid risks.