Reset of MySQL password can be done in several ways depending of the:

  • OS
  • are you connected
  • user rights

In this post:

Access denied for user 'root'@'localhost' (using password: YES) after new installation on Ubuntu/Linux Mint

On fresh installation on Linux Mint 19 and Ubuntu 18.04 you can see that MySQL server is installed by default. If you try to log in you will get an error like:

Access denied for user 'root'@'localhost' (using password: YES)

In order to log in your MySQL server and create new user or get access to your DB you can do:

  • Login in MySQL with your OS account password by:
sudo mysql -u root
sudo mysql -u root -p

After entering your user OS password you could be asked for MySQL root password - enter nothing and finally you will enter in MySQL server:

sudo mysql -u root -p
[sudo] password for myuser: 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)

After successful login you can select any of the other sections or just remove the root password by:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

and restart mysql server by(optional):

sudo systemctl stop mysql

MySQL 8 set new root password

If you can log in to your MySQL server and you wanto to change your password by query you can do it by:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

or removing the root password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

MySQL problems related to root authentication

Not able to connect with root and no password

If you remove your root password or set it to null then you may experience problems when you try to connect with root. It's advisable to create another DBA user while playing with these settings.

One of the reasons to not be able to connect would be: auth_socket plugin. This is a new change since 5.7:

If we want to configure a password, we need to change the plugin and set the password at the same time, in the same command. First changing the plugin and then setting the password won’t work, and it will fall back to auth_socket again. So, run:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

For more information you can check the link in references

1699 SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it.

In case that you are trying to change your root password(or other user password without success you can check previous section.

1287 'SET PASSWORD FOR = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR = '' instead

If you see this warning your can solve it just by removing:

SET PASSWORD FOR root@localhost=PASSWORD('1234');

SET PASSWORD FOR root@localhost='';

It seems that most DB tools are not updated with last changes and still produce old format queries

MySQL remove root password

If you want to remove the password from your root account in MySQL you can:

  • login in MySQL or by using a tool
mysql -u root -p

and run this command:

SET PASSWORD FOR root@localhost=PASSWORD('');

MySQL set new root password

As the previous section if you want to change your root password you can do it by running:

after MySQL 5.7.6:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPass';

prior MySQL 5.7.5:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newPass');

This way you will reset only the root password. If your forgot your root password than you can check next section.

Windows Resetting the Root Password

For this solution you will need administrative rights:

  • Stop the MySQL server(service)
  • Create new text file - C:\mysqlResetRoot.txt
  • Add this line(for earlier version use - see prevoius section):
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPass';

  • Open Command Prompt

    • start menu, type cmd and enter
    • WINDOWS+R and type cmd

  • Go to your MySQL installation folder:

cd "C:\Program Files\MySQL\MySQL Server 5.7\bin
  • Start MySQL with this command:
mysqld --init-file=C:\\mysqlResetRoot.txt

Ubuntu Resetting the Root Password

For Ubuntu and Linux Mint you can do these steps:

  • Open terminal
  • Login with the user running the MySQL service
  • Stop the MySQL server by
sudo systemctl stop mysql

for older versions of Ubuntu you can use:

sudo /etc/init.d/mysql stop

or

service mysqld stop
  • Create new text file - /home/user/mysqlResetRoot.txt
  • Add this line(for earlier version use - see prevoius section):
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPass';
  • Start MySQL with this command:
mysqld --init-file=/home/me/mysqlResetRoot.txt &

Reset the user by query

Another option to reset the password of your root account is by update statement. This is a simple update - set which will set new password:.

UPDATE mysql.user
    SET authentication_string = PASSWORD('newPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';

Finally reload the table grants by:

FLUSH PRIVILEGES;

Reset password from MySQL Workbench

  • Open MySQL Workbench
  • Connect to your database
  • Sidebar
  • Management
  • User and Privileges
  • Select the user - root
  • Type a new password to reset it
  • Apply

Reference