[mysql] MySQL root password change

I have been trying to reset my MySQL root password. I have run the mysqld_safe --skip-grant-tables, updated the root password, and checked the user table to make sure it is there. Once restarting the mysql daemon I tried logging in with the new root password that I just set and still get Access denied for user 'root' errors. I have also tried completely removing and reinstalling mysql (including removing the my.cnf file) and still no luck. Does anyone have any suggestions on what I can do next?

Thanks in advance

This is for mac users.

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work You have to do:

Make sure you have Stopped MySQL first (above). Run the server in safe mode with privilege bypass:

sudo mysqld_safe --skip-grant-tables

replace this mysqld_safe with your MySQL path like in my case it was

sudo /usr/local/mysql/bin/mysqld_safe –skip-grant-tables

then you have to perform the following steps.

mysql -u root

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

FLUSH PRIVILEGES;

exit;

Then

mysql -u root

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

For me, only these steps could help me setting the root password on version 8.0.19:

mysql
SELECT user,authentication_string FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pass_here';
FLUSH PRIVILEGES;
SELECT user,authentication_string FROM mysql.user;

If you can see changes for the root user, then it works. Source: https://www.digitalocean.com/community/questions/can-t-set-root-password-mysql-server

a common error i run into from time to time, is that i forget the -p option, so are you sure you used:

mysql -u root -p

On MySQL 8 you need to specify the password hashing method:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new-password';

In MySQL 5.7, the password is replaced with 'authentication_string'.

use

update user set authentication_string=password('myfavpassword') where user='root';

On Ubuntu,

sudo dpkg-reconfigure mysql-server-5.5 

Replace 5.5 with your current version and you will be asked for the new root password.

This is the updated answer for WAMP v3.0.6 and up

> UPDATE mysql.user 
> SET authentication_string=PASSWORD('MyNewPass') 
> WHERE user='root';

> FLUSH PRIVILEGES;

In MySQL version 5.7.x there is no more password field in the mysql table. It was replaced with authentication_string. (This is for the terminal/CLI)

UPDATE mysql.user SET authentication_string=PASSWORD('MyNewPass') WHERE user='root';

FLUSH PRIVILEGES;

(This if for PHPMyAdmin or any Mysql GUI)

1. On Mac open system preferences> MySQL.
2. In the configuration section of MySQL check for initialize database.
3. change the password in the prompt.

For MacOS users, if you forget your root password, @thusharaK's answer(https://stackoverflow.com/a/28601069/5628856) is good, but there are a little more tricks:

If you are using system preference to start mysql serverside, simply

sudo mysqld_safe --skip-grant-tables

might not work for you.

You have to make sure the command line arguments is same with system start config.

The following command works for me:

/usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid --keyring-file-data=/usr/local/mysql/keyring/keyring --early-plugin-load=keyring_file=keyring_file.so --skip-grant-tables

You can use

ps aux | grep mysql

to check your own.

I searched around as well and probably some answers do fit for some situations,

my situation is Mysql 5.7 on a Ubuntu 18.04.2 LTS system:

(get root privileges)

$ sudo bash

(set up password for root db user + implement security in steps)

# mysql_secure_installation

(give access to the root user via password in stead of socket)

(+ edit: apparently you need to set the password again?)

(don't set it to 'mySecretPassword'!!!)

# mysql -u root

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> set password for 'root'@'localhost' = PASSWORD('mySecretPassword'); 
mysql> FLUSH PRIVILEGES;
mysql> exit;

# service mysql restart

Many thanks to zetacu (and erich) for this excellent answer (after searching a couple of hours...)

Enjoy :-D

S.

Edit (2020):

This method doesn't work anymore, see this question for future reference...

have a look at this from MySQL Reference manual:

First login mysql:

# mysql -u root -p

Then at mysql prompt run:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

Then

FLUSH PRIVILEGES;

Look at this page for more information: Resetting the Root Password: Unix Systems

Or just use interactive configuration:

sudo mysql_secure_installation

On MySQL 8.0.4+

To update current root user:

select current_user();
set password = 'new_password';

To update other user:

set password for 'otherUser'@'localhost' = 'new_password';

To set password policy before updating password:

set global validate_password.policy = 0;
set password = 'new_password';
set password for 'otherUser'@'localhost' = 'new_password';

Other / better way to update root password:

mysql_secure_installation

Want to stick with 5.x authentication so you can still use legacy apps?

On my.cnf

default_authentication_plugin = mysql_native_password

To update root:

set global validate_password.policy = 0;
alter user 'root'@'localhost' identified with mysql_native_password by 'new_password';

Using the mysqladmin command-line utility to alter the MySQL password:

mysqladmin --user=root --password=oldpassword password "newpassword"

Source

For MySQL 5.7.6 and later:

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

For MySQL 5.7.5 and earlier:

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

You have to reset the password! steps for mac osx(tested and working) and ubuntu

Stop MySQL

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

Start it in safe mode:

$ sudo mysqld_safe --skip-grant-tables

(above line is the whole command)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

$ mysql -u root

mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

Start MySQL

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

your new password is 'password'.

stop all wamp services , exit from wamp.

open notepad then type> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('');

then save it to c: drive with any name .. like this "c:/example.txt"

now goto your "wamp" falder wamp > bin > mysql > mysql(your version) > bin in my case path is "C:\wamp\bin\mysql\mysql5.6.17\bin"

now copy your path then run cmd with (ctrl + r) then type "cmd" (enter)

type cd then right click on cmd and paste path (enter) now type (mysqld --init-file=C:\example.txt) without braces then (enter)

then restart PC or open taskmgr and kill mysqld.exe

start wamp and your password will be removed...

For the current latest mysql version (8.0.16), none of these answers worked for me.

After looking at several different answers and combining them together, this is what I ended up using that worked:

update user set authentication_string='test' where user='root';

Hope this helps.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
FLUSH PRIVILEGES;

This worked for me -

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

https://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/resetting-permissions-windows.html

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

You can find Resetting the Root Password in the MySQL documentation.

Please follow the below steps.

1. sudo service mysql stop
2. sudo mysqld_safe --skip-grant-tables
3. sudo service mysql start
4. sudo mysql -u root
5. use mysql;
6. show tables;
7. describe user;
8. update user set authentication_string=password('1111') where user='root';

login with password 1111

So many coments, but i was helped this method:

sudo mysqladmin -u root password 'my password'

In my case after instalation i had get mysql service without a password for root user, and i was need set the password for my security.

Tried the answer from @kta but didn't work for me.

I am using MySQL 8.0

This worked for me:

mysql> SET PASSWORD FOR 'root'@'localhost' = 'yourpassword'