Reset 'root' password

from the Artful MySQL Tips List


If you administer a server and have forgotten the MySQL password of the root user, 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 commndline ("DOS") window to that folder to execute these commands.

1. Stop MySQL:

*Nix: /etc/init.d/mysql stop

Win: Stop it in services.msc, or run ...

net stop mysql

Mac with MySQL 5.7:

sudo /usr/local/mysql/support-files/mysql.server stop

2. Sometimes in Windows it is necessary to uncomment this line in %windir%system32driversetchosts:

127.0.0.1 localhost

3. 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

*Nix or Mac:

sudo mysqld_safe --skip-grant-tables

4. In another commandline window, start the mysql client:

mysql

5. Verify that the name of the admin user is 'root'; you cannot create users when running with skip-grant-tables):

select user,host,authentication_string from mysql.user;

6. Change root user's password
Substitute the desired root password for 'new_pwd':

-- BEFORE 5.5.7:
update mysql.user 
set password=PASSWORD('new_pwd') 
where user='root';
-- BEFORE 5.7.7:
update mysql.user 
set authentication_string=PASSWORD('new_pwd'),password_expired='N'
where user='root';
-- SINCE 5.7.7:
alter user 'root' identified by 'new_pwd';
-- AND IN ALL CASES:
flush privileges;
exit;

7. Stop mysqld in Task Manager or (*Nix, Mac) in another command window...

mysqladmin shutdown

8. Restart MySQL:
*Nix:

/etc/init.d/mysql start

Win: restart the services in services.msc, or run ...

net start mysql

*Nix, Mac:

sudo /usr/local/mysql/support-files/mysql.server restart

For more detail see https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html or https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html.

Return to the Artful MySQL Tips page