Question: I forgot root password for MySQL DBE. How to reset or recover it? PLEASE HELP!
Answer: Below is simple algorithm to reset MySQL root password in Linux, FreeBSD, OpenBSD and other Unix like operating systems:
1. Stop MySQL server process by one of the following commands:
# /etc/init.d/mysqld stop
# killall -9 mysqld
# kill `cat /mysql-data-directory/host_name.pid`
# mysqladmin shutdown
To check if mysqld is killed run "ps ax | grep mysqld" that should show no mysqld instances.
2. Start MySQL server without password protection:
$ mysqld --skip-grant-tables --user=root &
or
$ mysqld --skip-grant-tables &
3. Login to MySQL console by "mysql -u root" and set up new password with the following mysql commands:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;
4. Now you should be able to connect MySQL with new password.
P.S. There is alternative 2 and 3 steps:
2. Create text file /tmp/init.mysql with the following contents:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
3. Start MySQL with command:
# mysqld_safe --init-file=~/mysql-init &
Hope it helps!!!
mysql> UPDATE mysql.user SET Password=PASSWORD(‘newpassword’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;
—————————————————
I was getting errors until I changed the ‘ in newpassword brackets to “s