Reset MySQL Root Password (Use Second Method)

Reset MySQL Root Password (Use Second Method)
———————————————-

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(‘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 -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

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: