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
[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(‘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
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 |
+———————————+
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
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
================================================
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.*
skip-grant-tables
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.
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.
skip-grant-tables
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.
