Hi,

Nice post,

 The proper way to change passwords is using the "SET PASSWORD" syntax, like:

SET PASSWORD FOR 'someuser'@'somehost' = PASSWORD('newpassword');

 Directly manipulating the mysql.users table is less preferred, since you may accidently inject typos.

See: http://dev.mysql.com/doc/refman/5.0/en/set-password.html

Regards,

Shlomi

Thx 4 the feedback. I forgot to mention that the most common reason for this error is an empty password. You have to specify the root password while connecting to the Mysql database. mysqladmin -u root -pROOT-PASSWORD

Sam

Hi.

Your method for updating passwords is not the easiest nor the recommended one.

To change the password for the current user, simply use

SET PASSWORD=password('new_password');  # no FLUSH PRIVILEGES needed

To set the password for another user (as root), use

SET PASSWORD for user_name = password('new_password')

Best regards

Giuseppe

A safer way to do this would be to add the "init-file=/tmp/grant.sql" to the [mysqld] section of your my.cnf file. In /tmp/grant.sql you would have...

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

or whatever SQL that would fix the root user.

Restart MySQL and the password is reset. You can then remove /tmp/grant.sql and remove the init-file line from your configuration.

 Using the skip-grant-tables exposed your entire database to anyone or any service that has access to your mysql instance. If you make sure that the grant.sql file only is readable by the mysql daemon user, you will have less exposure.

 One more thing... instead of going to the mysql db to change the password for a user, you can just type the following at the mysql prompt:

SET PASSWORD FOR name@host = PASSWORD('xxxx');

I believe FLUSH PRIVILEGES is optional here, but I always run it out of habit.

Hi,

Sorry for double posting.

With regard to recovering root password, please see my post: Dangers of skip-grant-tables

 The post purposes that using an init-file is faster (requires but one restart instead of two) and safer.

Shlomi

Thank you very much for this!

 One thing to change if you do not have mysql setup to run as root is the "# mysqld_safe --skip-grant-tables &"  should have a -user=mysql in it.

 

 For me, my mistake was changing the security rights of the root user away from localhost.  So I did this trick listed to get mysqld running, then used phpMyAdmin to fix it.

Thanks for this article, its really works for me.

 Thanks again.

Really good!! Thanks

Just to know: in debian you can use the debian-sys-maint user to recover the password.

1. mysqladmin -u root -p'oldpassword' password newpassword

As taken from: http://www.linuxtutorial.co.uk/mysql-change-root-password/
 

Which...only works if you have the original root password.  This whole article is about resetting a lost root password. 

Neither this solution nor other solutions from various blogs solved the same issue with my machine. God khnows why it didnt work when everything seemed to be in order.

 

Hence, as a last resort, I used a very crude way. I backed up all my databases by converting each of them to .sql files. Then I uninstalled both mysql and mysql-server (uninstalling mysql automatically removes mysql-server as dependency). Then deleted everything in /var/lib/mysql library as it is the folder where all the databases are stored. What it did was it deleted the user table along with all other databases.

Then I installed mysql-server again. Now there was no password defined for root user. Then I set password for the root by executing command “ /usr/bin/mysqladmin -u root password MyNewPass “. Now the issue was solved and I was able to login as root.

Note : This is a last resort when all the other solutions are failed.

In this case also, You need to give root password while taking backup.

If mysqldump command is run, it'll prompt for root password inorder to finish the backup.

So, Yours is not the correct solution.

 

This worked, thanks Giuseppe Maxia

Thanks a lot. :) 

Thanks a lot. Just 2 line changes for 5.7.11 version.. 

instead of update run, 

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW-ROOT-PASSWORD'; 

And run 'flush privileges;' before the above ALTER command. 

Thank YOU sir, I was about to give up and just purge the whole thing :)

Mysql password reset worked! Kudos! 

Thanks alot the  'mysqladmin -u root password newpass' worked

Amazing note!!! worked like a champ... thanks very much!!!

thanks for this post. I have been having these problems for several days and I would appreciate it if you could help me out

I downloaded MySQL and didn't jot down the temporary password that pops on the screen. Since then, I have not been able to reset my root password. No matter where I search, I can't find a solution. Any help?

Thanks. it helped.

I'm having trouble with this. When I stop the mysql service the mysqld directory disappears in the /var/run folder. Then when I try step 2, I receive an error that says /var/run/mysqld directory doesn't exist and the --skip-grant-tables does nothing to allow me to login as root. Am I missing something?

Thanks bro. There're many tutorials about it, but this is what I want and understand

Thanks for your article, This is a very useful post for us.

Awesome post. It helped me a lot.

Thanks for the post.  It helped me from a forgotten install :)

Step 1. Login as root and restart the MySQL deamon.

Step 2. Login to MySQL as: 

mysql --user=root mysql

Step 3. Show current User/Host/HEX(authentication_string) values.

SELECT User, Host, HEX(authentication_string) FROM mysql.user;

Step 4. Use this command to change the password for the user. (You can leave it blank and run the command from step 3 to verify the previous password was removed, then change it).

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Th3_P@5$w0rd';

Step 5. Verify that the HEX string has changed using the command from step #3.

Step 6. Exit MySQL and verify that the new password works.

mysql -u root -p

You literally just save my life

Hi,

thank you for your post...but i have a problem and your can solve so can u just tell me what to do?

My problem this the first time am using Mysql on my computer...i use it mostly in school.

 So,when i open the cmd for Mysql it show me, Enter password...that it but the thing is i dont have any password so when i press enter it closed by itself. so can you just help me please. Really need this for my upcoming assignment.

Thank you.

Recent version has changed the field `password` to `authentication_string` in mysql.user table.

mysql> use mysql;mysql> update user set authentication_string=PASSWORD("NEW-ROOT-PASSWORD") where User='root';mysql> flush privileges;mysql> quit

Hi,

The password for mysql would have been selected by the administrator who installed mysql on that PC (your teacher?) - if you enter a false PW (in your case nothing!), then the black window closes - so speak to your teacher regarding the password.

 

The part for recover passwors did not work for me - using a fresh install of Ubuntu 16.04 server x64 and mariaDB.

This article from stackowerflow worked fine: https://stackoverflow.com/questions/41984956/cant-reset-root-password-with-skip-grant-tables-on-ubuntu-16/41987711

service mysql stop does not work for mysqld_safe. You need to shutdown like this: mysqladmin -u root -p shutdown

After a decade this article still rocks. I just used this to reset MariaDB root password. Only thing I did diffrently was to "killall mysqld" after "quit" command.

-Sam

When trying to change the password, some MySql versions shall emit a cryptic "ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement" error.

In this case you must execute a:

FLUSH PRIVILEGES;

first. Only then, you may continue with:SET PASSWORD FOR 'root'@'localhost' = PASSWORD("My_new_password_here");

 

Before you go and do a lot of uncessary work, be sure you don't have to use sudo, as in sudo mysql -u root -p. It turned out that my root password was just fine...

I tried all the above alternatives, but only this works : 

sudo mysql -u root -p

I tried all the above steps still im getting the same error, please help me out

 

vishnu@vishdino:~$ mysqladmin -u root -p  password vishnu0. Enter password: mysqladmin: connect to server at 'localhost' failederror: 'Access denied for user 'root'@'localhost' (using password: YES)'

I had an issue with this. Nothing worked including the sequence of

sudo /etc/ini.t/mysql stop

mysqld_safe --skip-grant-tables &

It would not start no matter what I did.  Apparently during the install process I gave a password to root and forgot it or misspelled it. I had run

sudo mysql_secure_installation

 

and got rid of the insecurities and had also changed the authentication method from auth_socket to

nysql_native_password.  I had seen tha as soon as I changed the authentication method, the password files went from blank to an encrypted string.

Then I logged out and could never log in again.

Here is the solution.

1. Install mc (sudo apt-get -y install mc)

2. Stop the mysqld process (sudo /etc/init.d/mysql stop)

3. Become root (sudo -s)

4. cd to /var/lib/mysql/mysql

5. Run mc (mc)  to get a listing of the files

6. Use arrow keys down to user.MYD

7. Press F3 to see inside the file.

8. Press F4 to view it as hex.

9. Arrow over to just past localhost.root to where the encrypted string representing the password starts.It will begin with an asterix (hex 2A)

10. Now press F2 to edit the hex.

11. Press '0' (zero) enough times to replace the encrypted string with nulls (00). Leave the final (01) just before the right square bracket (01 5D)

12. Press F6 to save and F10 to exit.

Fixed!  You can now login as root with no password.

Go into mysql:user and add yourself as a priviledged user as a fall back.

GRANT ALL PRIVILEDGES ON *.* TO 'yourname'@'LOCALHOST' INDENTIFIED BY 'yourpassword' WITH GRANT OPTION;

 

You are welcome.

Me too. At this late date, I am running version 5.7 and they have tightened up on security so that the sledgehammer seems to be the tool of choice. Moral: store those root passwords somewhere secure.

Thank you !

 MariaDB has complicated root reset and propably requires something like this after you login with skip-grant-tables:UPDATE mysql.user SET authentication_string = PASSWORD('NewPassword') WHERE User = 'root' AND Host = 'localhost';or...update mysql.user set password=password('NewPassword') where user='root';You also have to FLUSH PRIVILEGES; after every command these days.

SamTzu