Few ways to reset MySQL password

Several ways to reset MySQL password

This post provides instructions that helps to reset MySQL root user account password, regardless of what reason you have for resetting it.

Method 1:
1) Stop MySQL daemon if it is currently running by killing all mysqld and mysqld_safe processes:

pkill mysql pkill mysql-safe

2) Create a file /root/reset-mysql-password and fit it with following contents:

UPDATE mysql.user SET Password=PASSWORD('newpA$$w0rd') WHERE User='root'; FLUSH PRIVILEGES;

3) To reset the password, run MySQL daemon in safe mode and pass the SQL statements from above-created file:

mysqld_safe --init-file=/root/reset-mysql-password &

4) Password should be changed. Next, delete created file, stop MySQL and start it normally:

rm /root/reset-mysql-password pkill mysql-safe /etc/init.d/mysql start

5) The password could be checked with following command:

mysql -u root -p

Method 2:
1) Again, stop MySQL daemon if it is currently running by killing all mysqld and mysqld_safe processes:

pkill mysql pkill mysql-safe

2) To reset the password run MySQL daemon in safe mode with following options:

mysqld_safe --skip-grant-tables --skip-networking &

In –skip-grant-tables mode, anyone can log into the server and do as they please. When starting with this flag, it is preferable to use –skip-networking flag for security reasons.

3) Next, login to MySQL as root with no password and use the mysql database:

mysql -u root mysql

4) The password could be changed with UPDATE statement:

mysql>UPDATE user SET Password=PASSWORD('newpA$$') WHERE User='root'; mysql> FLUSH PRIVILEGES;

If error occurs (possibly the root username was deleted), GRANT statement could be used to change the password:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'newpA$$'; mysql> FLUSH PRIVILEGES;

5) After password is reset, MySQL safe daemon should be stopped and started normally:

pkill mysql-safe /etc/init.d/mysql start

6) The password could be checked with following command:

mysql -u root -p Related Posts: