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

回答1:

hava a look at this from MySQL Reference manual:

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

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

回答2:

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

回答3:

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 text

回答4:

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.

回答5:

Please follow the below steps.

step1. stop mysql

step2. sudo mysqld_safe --skip-grant-tables

step3.mysql -u root

step4.use mysql;

step5.show tables;

step6.describe user;

step7.update user set authentication_string=password('1111') where user='root';

login with password 1111

回答6:

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

回答7:

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

回答8:

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

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

Source

回答9:

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

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

回答10:

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...

回答11:

sudo dpkg-reconfigure mysql-server-5.5 

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

回答12:

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.

回答13:

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

use

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

回答14:

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