MySQL root password change (ok)

https://stackoverflow.com/questions/7534056/mysql-root-password-change

Asked 9 years, 4 months agoActive 19 days agoViewed 252k times11032

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 advancemysqlrootmysqladminShareImprove this questionFollowasked Sep 23 '11 at 19:47nick1,48822 gold badges1010 silver badges1414 bronze badges

• 2please post your actual error – Vishwanath Dalvi Sep 23 '11 at 19:57

• 17If your not locked out SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somepassword'); is safest. – sabgenton Sep 13 '13 at 17:23

• I followed the steps in the video to change password in one of my servers youtu.be/gFo5DV_pSg8 – Prem Dec 31 '18 at 19:21

• 1Possible duplicate of ERROR 1698 (28000): Access denied for user 'root'@'localhost' – Bamboomy May 8 '19 at 18:47

• 1Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu) ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD("1")' at line 1 SET PASSWORD FOR 'root'@'localhost' = PASSWORD("somepassword"); – discipleartem Jun 30 '20 at 13:18

add a comment

24 Answers

ActiveOldestVotes152

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

ShareImprove this answerFollowedited Oct 11 '20 at 17:02T.J. Crowder849k154154 gold badges15461546 silver badges15901590 bronze badgesanswered Feb 16 '14 at 7:07kta16.4k77 gold badges5858 silver badges4343 bronze badges

• 8This is working for me in 5.7 while the accepted answer is not. – Stoopkid Oct 2 '17 at 19:01

• 3This is the correct solution if your MySQL root password is set to blank. – fschuindt Feb 6 '18 at 13:30

• 4> 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. – Robert Anthony S. Tribiana Aug 19 '18 at 3:16

• 2This gives a OK to me, but don't actually do anything – Freedo Aug 1 '19 at 8:13

• 1It needs an extra command FLUSH PRIVILEGES to make effective the change without restarting the database server. – NetVicious Dec 12 '19 at 16:03

show 5 more comments67

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 SystemsShareImprove this answerFollowedited Jun 5 '20 at 14:53Dung14.3k66 gold badges5050 silver badges4848 bronze badgesanswered Aug 7 '13 at 9:51VSB7,39088 gold badges5858 silver badges115115 bronze badges

• I got an error "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column..." when updating the user's password. See stackoverflow.com/questions/11448068/… for how to work around this error. – Joe Borysko Dec 1 '16 at 23:18

• 3ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('MyNewPass') WHERE User='root'' at line 1 – discipleartem Jun 30 '20 at 13:12

add a comment36

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

You can find Resetting the Root Password in the MySQL documentation.ShareImprove this answerFollowedited Dec 29 '18 at 11:44Matthijs2,13644 gold badges1919 silver badges3232 bronze badgesanswered Dec 29 '18 at 9:12iamfaith36933 silver badges22 bronze badges

• This query is worked for me but new password is not working (Access denied). It's only start to work after I recreated root user askubuntu.com/a/784347/160368. – hlcs Apr 5 '20 at 22:01

• 1works for me on MySQL 8.0.20 and after initializing data folder using this command mysqld --initialize --console – A. Henteti Jun 6 '20 at 19:07

add a comment29

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)ShareImprove this answerFollowedited Feb 4 '20 at 8:46answered Apr 20 '17 at 4:33Robert Anthony S. Tribiana8801010 silver badges1515 bronze badges

• This answer is one of the few who says that mysql.user table should be updated. Notice that if You are using any other database (set by the use command), You will get an error that the table user does not exist. Thank You, sir! – Aleksandar Nov 5 '18 at 12:29

• 1works for me . I am using 5.7.x , there's no mysql.user table. other answers are out-dated – Siwei Shen 申思维 Dec 26 '18 at 9:38

• Happy to help!! – Robert Anthony S. Tribiana Aug 23 '19 at 3:25

• I roll it back because it is my intention to use > because of the semicolon per line which is intended for the terminal – Robert Anthony S. Tribiana Feb 4 '20 at 8:44

• 0 rows affected, rows matched: 3 changed: 0 ... ? – trainoasis Mar 5 '20 at 9:37

show 1 more comment25

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 1111ShareImprove this answerFollowedited Dec 24 '20 at 11:27maulik bafalipara18122 silver badges1010 bronze badgesanswered Feb 27 '17 at 11:16Lokesh G62277 silver badges1818 bronze badges

• 4flush privileges is important as a last step. – Khom Nazid Apr 18 '19 at 19:58

• It's really done with the ass .I did all the tutorials to reset the password. Everything works except when I log in with the new password, it doesn't work. – Shidomaru NeveRage Jun 15 '20 at 21:14

• if mysqld is not starting and message like this mysqld daemon with databases from /var/lib/mysql mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended do this: mkdir /var/run/mysqld chomod 777 /var/run/mysqld and start all from the begging – jokermt235 Aug 14 '20 at 23:12

add a comment22

Found it! I forgot to hash the password when I changed it. I used this query to solve my problem:

update user set password=PASSWORD('NEW PASSWORD') where user='root';

I forgot the PASSWORD('NEW PASSWORD') and just put in the new password in plain textShareImprove this answerFollowedited Sep 27 '11 at 12:48ghostJago3,12144 gold badges3131 silver badges4747 bronze badgesanswered Sep 25 '11 at 2:14nick1,48822 gold badges1010 silver badges1414 bronze badges

• make sure you come back and accept your answer to allow others to see the solution – ghostJago Sep 27 '11 at 12:31

• 1dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html shows how to do this with --init-file= if your looked out. – sabgenton Sep 13 '13 at 17:25

• 3Any idea why when I use the same command, the following error is returned? Error 1054 (42S22): unknown column 'Password' in 'field list' – Adrian M. Jan 7 '16 at 7:23

• 2You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('NEW PASSWORD') where user='root'' at line 1 – discipleartem Jun 30 '20 at 13:06

• I too get this error. syntax error. Please add the solution. – indianwebdevil Dec 22 '20 at 15:18

add a comment21

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...ShareImprove this answerFollowedited Oct 25 '20 at 16:45answered May 10 '19 at 13:33Bamboomy2,09433 gold badges1818 silver badges2929 bronze badges

• 1This is the best answer so far, at least for mysql server 5.7. – Zoltán Hajdú Feb 15 '20 at 15:22

• 1The BEST answer. Thank you. – Luciano Fantuzzi May 20 '20 at 21:59

• 2mysql_secure_installation worked for me after a fresh installation. – Troy Dec 31 '20 at 18:11

add a comment11

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';

ShareImprove this answerFollowedited Oct 2 '19 at 14:57Gryu1,53222 gold badges1010 silver badges2424 bronze badgesanswered Jul 7 '19 at 6:14Carlito11111 silver badge44 bronze badgesadd a comment9

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.htmlShareImprove this answerFollowanswered Dec 9 '19 at 13:22LV9870955 silver badges1717 bronze badges

• ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'MyNewPass'' at line 1 – trainoasis Mar 5 '20 at 9:38

• Just copied the exact over in sql via CLI :o – trainoasis Mar 5 '20 at 9:58

• Of course, sshed to my server + mysql -u root -p to get to the mysql cli – trainoasis Mar 5 '20 at 13:31

add a comment6

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'.ShareImprove this answerFollowanswered Feb 19 '15 at 7:51tk_12.5k66 gold badges7171 silver badges8080 bronze badges

• I've got a message : no database selected – Mimouni Aug 19 '19 at 14:14

• @Mimouni try this : stackoverflow.com/questions/21944936/… – tk_ Aug 19 '19 at 14:16

add a comment5

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');

ShareImprove this answerFollowanswered Nov 4 '18 at 14:20Eagle_Eye95211 gold badge1212 silver badges2222 bronze badgesadd a comment4

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.ShareImprove this answerFollowanswered May 29 '19 at 22:48Suhas42233 silver badges1010 bronze badges

• ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) and then [Warning] [MY-010319] [Server] Found invalid password for user: 'root@localhost'; Ignoring user – Eugene Gr. Philippov Dec 24 '20 at 16:26

add a comment2

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

ShareImprove this answerFollowanswered Sep 23 '11 at 19:53Marian Theisen5,5642222 silver badges3636 bronze badgesadd a comment2

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

use

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

ShareImprove this answerFollowanswered May 14 '18 at 11:34Kamal Reddy2,47044 gold badges2323 silver badges3636 bronze badgesadd a comment2

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

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

SourceShareImprove this answerFollowanswered Dec 1 '18 at 6:00Darush9,03755 gold badges4848 silver badges5252 bronze badgesadd a comment1

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'ShareImprove this answerFollowedited Mar 11 '19 at 16:02answered Mar 11 '19 at 11:35chainstair38111 silver badge1414 bronze badges

• 1Please add some explanation to your code - how exactly does this solve a seven years old question? – Nico Haase Mar 11 '19 at 15:22

• It looks like the syntax has been changed over the seven years. It's not wrapping password with PASSWORD() dev.mysql.com/doc/refman/8.0/en/set-password.html – kangkyu Apr 16 '19 at 16:48

• Password hash should be a 41-digit hexadecimal number – Mimouni Aug 19 '19 at 14:21

add a comment1

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.ShareImprove this answerFollowanswered Jun 11 '19 at 7:57Konstantin Romanovsky1922 bronze badges

• It could be used to reset mysql root user password without knowing the old one. But make sure no one has access to your syslog, as this password is logged: Aug 07 16:21:55 ubuntupc sudo[93250]: user : TTY=pts/0 ; PWD=/home/user ; USER=root ; COMMAND=/usr/bin/mysqladmin -u root password 'my password' – Gryu Aug 7 '20 at 13:26

add a comment1

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';

ShareImprove this answerFollowanswered Dec 29 '20 at 19:48Supreet Singh18822 silver badges44 bronze badgesadd a comment0

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...ShareImprove this answerFollowanswered Jul 15 '15 at 8:18MohitGhodasara1,0521616 silver badges2626 bronze badgesadd a comment0

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.ShareImprove this answerFollowedited May 23 '17 at 12:02Community♦111 silver badgeanswered Apr 20 '17 at 5:13Shaofei Cheng82799 silver badges1313 bronze badgesadd a comment0

Or just use interactive configuration:

sudo mysql_secure_installation

ShareImprove this answerFollowanswered Sep 16 '20 at 12:55shilovk7,1931515 gold badges5151 silver badges6363 bronze badgesadd a comment0

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

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

ShareImprove this answerFollowanswered Dec 31 '20 at 13:37Hokascha1,2191414 silver badges3535 bronze badgesadd a comment-1

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.ShareImprove this answerFollowedited Apr 18 '19 at 17:29answered Sep 26 '16 at 9:02Mouneer9,59222 gold badges3131 silver badges4343 bronze badges

• In what OS? This makes no sense. – Khom Nazid Apr 17 '19 at 14:10

add a comment-1

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.

ShareImprove this answerFollow