Reset 'root' password

from the Artful MySQL Tips List

If you administer a server and have forgotten your MySQL password, do this sequence.

Note that in Windows, if you've not added the MySQL's bin folder to the system path, you will have to navigate in the DOS window to that folder to execute these commands.

If your MySQL version is earlier than 5.5.7, substitute password for authentication_string in what follows:

1. Stop MySQL:
*Nix: /etc/init.d/mysql stop
Win: Stop it in services.msc, or run ...
net stop mysql
2. Start the MySQL server specifying your defaults-file (if you don't know that filename, see "What config file is the MySQL server using?"), and bypassing authentication ...
mysqld --defaults-file="..." --skip-grant-tables
3. In another command window, start the mysql client:
mysql
4. Find the name of the admin user (probably 'root'; you cannot create users when running with skip-grant-tables), update that user's password:
select user,host,authentication_string from mysql.user;
-- reset authentication_string (password before 5.5.7) for the admin user ...
update mysql.user set authentication_string=PASSWORD('new_pass') where user='root'; flush privileges; exit;
(Substitute your password for 'new_pass'.)

5. Stop mysqld in Task Manager or in another command window...
mysqladmin shutdown
4. Restart MySQL:
*Nix:
/etc/init.d/mysql start
Win: restart the services in services.msc, or run ...
net start mysql
For more detail see http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

Return to the Artful MySQL Tips page