Reset root password

from the Artful MySQL Tips List

If you administer a server and have forgotten the MySQL password of the root user ...

On Windows, you can execute the sequnece described at https://dev.mysql.com/doc/mysql-windows-excerpt/8.0/en/resetting-permissions-windows.html, or
on any OS, you can execute the following sequence. In this case, on 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:

Linux other than Ubuntu: /etc/init.d/mysql stop

In Ubuntu, at least since 16.04, starting the service creates the sock file and stopping the service silently deletes it, so step 1 in Ubuntu needs to be ...

# if mysql isn't running, start the service
sudo service mysql start
# navigate to sock folder
cd /var/run
# back up sock file
sudo cp -rp ./mysqld ./mysqld.bak
#  stop mysql server
sudo service mysql stop
# restore sock file
sudo mv ./mysqld.bak ./mysqld

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. In Windows it is sometimes necessary to uncomment this line in %windir%system32driversetchosts:

127.0.0.1 localhost

Beware: one of the many hacks built into Windows is this must be done by running NotePad as an administrator; no other text editor will do.

3. Simplest: start the server from the commandline specifying just --datadir and --skip_grant_tables, i.e., in Windows ...

mysqld --datadir="..." --skip-grant-tables

... filling in the ellipsis with the full path to your MySQL data directory in quotes. If there's something else in my.ini that the server needs, the above will fail, and you'll need to use your config file, but first be sure to comment out authentication settings in it. Then 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

In Windows with MySQL 8 add --shared-memory to the above command, for the reason why see https://bugs.mysql.com/bug.php?id=90554.

*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';
-- 5.5.8 THROUGH 5.7.2:
update mysql.user 
set authentication_string=PASSWORD('new_pwd'),password_expired='N'
where user='root';
-- SINCE 5.7.7:
-- IF mysql.user.plugin for root='auth_socket',
-- or 'unix_socket' in mariadb
-- then root needs no password
-- (this is no password authentication)
-- 5.7.3 THROUGH 5.7.9
-- (see https://bugs.mysql.com/bug.php?id=79027)
flush privileges;
update mysql.user 
set authentication_string=PASSWORD('new_pwd'),password_expired='N'
where user='root';
-- SINCE 5.7.10 IF PLUGIN='authentication_string'
-- or "caching_sha2_password"
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 service in services.msc, or run ...

net start mysql

(or whatever is the name of the service)

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

Last updated 1 Sep 2024

Return to the Artful MySQL Tips page