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

Mac with MySQL 5.7:

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

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

*Nix or Mac:

sudo mysqld_safe --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'),password_expired='N', where user='root';
flush privileges;
exit;

(Substitute your password for 'new_pass'.)

5. Stop mysqld in Task Manager or (*Nix, Mac) 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

*Nix, Mac:

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

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

Return to the Artful MySQL Tips page