To reset MySQL password You need:

  1. Stop mysqld daemon
  2. Run mysqld from root with --skip-grant-tables mysql turn off safe mode
  3. Connect from another terminal using mysql CLI tool
  4. Perform actual pasword update with update user
  5. Stop mysqld from the first terminal by Ctrl+C and start the daemon again
  6. Test password working with mysql -u root -p command

Tested on 5.7.31-0ubuntu0.18.04.1 and 8.0.*

First stop MySQL daemon:

sudo service mysql stop

NOTE: To make sure mysqld is not running use ps aux | grep mysqld - should not include a process

Run mysqld daemon with --skip-grant-tables:

sudo mysqld --skip-grant-tables

By during this we turned off safe mode in MySQL.

This should block terminal, so open new terminal and check mysql version:

mysql --version

It will show for example:

mysql Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

☝Remember it, next action will very depending on 5.7 / 8.0.

Now run unprotected CLI:

mysql

You should see:

Reset mysql password CLI view

Perform password reset:

If you have MySQL version 5.7

use mysql;
update user set authentication_string=PASSWORD("NewPassword") where User='root';
update user set plugin="mysql_native_password" where User='root';

flush privileges;
quit

If you have MySQL version 8.x.x

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

Now run mysql with root user:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'NewPassword';
FLUSH PRIVILEGES;
exit;

Now stop mysqld which is running in our first terminal:

sudo killall mysqld

And start daemon in normal mode:

sudo service mysql start

To test that new password works:

sudo mysql -uroot -p

And enter NewPassword.

reset mysql root password preview

#mysql