Reset MySQL Root Password

There are cases when you forget mysql root password or you are asked to start supporting a new database and you have not been handed over passwords.Oracle DBA’s are used to login as sqlplus “/ as sysdba” from host with user which is part of dba group. This can be used to connect or changing password by recreating password file (orapwd)

In case of mysql , this is not possible. If you have enabled password authentication (which is the right way 🙂 ) , you will get following error

-bash-4.1$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Lets try with some dummy password

-bash-4.1$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

We need to reset the password now. 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

[mysqld]
skip-grant-tables

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: 5.1.67.0

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
owners.

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

mysql>

4) Next we need to reset the password

mysql> update mysql.user set password=password('askdba') 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 -paskdba
Your MySQL connection id is 1
mysql>

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 mysqldev01.askdba.org 

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

One option is to use bind-address=127.0.0.1 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

[mysqld]

init-file=/home/askdba/tmp_mysql.txt

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
mysql>

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