FAQ: Change forgotten or lost MySQL root password

mysql logoQuestion: 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!!!

SHARE:
nv-author-image

Stefan Durand

My name is Stefan, I'm the admin of LinuxScrew. I am a full-time Linux/Unix sysadmin, a hobby Python programmer, and a part-time blogger. I post useful guides, tips, and tutorials on common Linux and Programming issues. Feel free to reach out in the comment section.

Leave a Reply

Your email address will not be published. Required fields are marked *