Reset root password

from the Artful MySQL Tips List


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

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. Sometimes in Windows it is necessary to uncomment this line in %windir%system32driversetchosts:
127.0.0.1 localhost
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 PLUGIN='auth_socket' 
-- (auth_socket is designed for no password)
alter user 'root' 
IDENTIFIED WITH mysql_native_password BY 'new_pwd';
-- 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'
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.

Last updated 9 Jul 2021


Return to the Artful MySQL Tips page