SysAdmin.MD

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

Tips mysql passwords