unable to reset root password of mysql

Question

I need to reset the root password of my local mysql installation but it woudln't let me. I've tried this:

$ sudo /etc/init.d/mysql stop
 * Stopping MySQL database server mysqld                                                                                                              [ OK ] 
[1]-  Exit 1                  sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking
[2]+  Exit 1                  sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking
reg@regDesktopHome:~$ sudo mysqld --skip-grant-tables &
[1] 13651
reg@regDesktopHome:~$ 140627 19:02:02 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.

reg@regDesktopHome:~$ mysql -u root mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
reg@regDesktopHome:~$ sudo mysql -u root mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[1]+  Exit 1                  sudo mysqld --skip-grant-tables

How can I get the password reset? edit 1 I got this:

$ ps ax| grep mysql
16515 ?        Ssl    0:00 /usr/sbin/mysqld
16551 pts/23   S+     0:00 grep --color=auto mysql
reg@regDesktopHome:~/semios/v3upgrade$ sudo kill -9 16515
reg@regDesktopHome:~/semios/v3upgrade$ ps ax| grep mysql
16678 ?        Ssl    0:00 /usr/sbin/mysqld
16715 pts/23   S+     0:00 grep --color=auto mysql
reg@regDesktopHome:~/semios/v3upgrade$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

looks like a process is automatically restarting mysql as soon as I kill it...

score 44 · Accepted Answer · edited Feb 18 '17 at 15:29

44

First please try using

mysql -u root -p

and enter your password (if you remember) at the prompt to login as the sql-root user (note the switch -p is for password).

If you really have to reset your root password for mysql, here's an easy way - reconfigure the package with dpkg-reconfigure.

Easy steps to reset mySQL root password:

Check the version of your mysql-server;
```
apt-cache policy mysql-server
```
and see for the line which shows the installed version among other information. e.g. for my install it's:
```
Installed: 5.5.37-0ubuntu0.12.04.1
```
(From this I know that I have mysql-server-5.5 installed in my system.)
Start the reconfiguration with:
```
sudo dpkg-reconfigure mysql-server-*.*
```
where mysql-server-*.* should be replaced by the version that you have. (for me it'd be mysql-server-5.5). This will stop the database daemon. A prompt will then appear where you'd have to enter your new password and confirm the reconfiguration.

The daemon will be automatically started after the reconfig completes.
You can then log in with:
```
mysql -u root -p
```
and start your database admin tasks.

References:

https://help.ubuntu.com/community/MysqlPasswordReset [Which would soon be cleaned up as indicated in the page.]
Ubuntu Server Guide related to your specific version.

edited Feb 18 '17 at 15:29

muru

197,895
55
485
740

answered Jun 28 '14 at 05:31

rusty

16,327

3

Can confirm that dpkg-reconfigure mariadb-server-10.1 worked for MariaDB when all other solutions didn't. Thank you for this. – liviucmg Oct 03 '14 at 21:49
Yes this worked - better to understand what is going on - Thanks rusty – Gadgetroid Nov 05 '14 at 09:56
1

Didn't work for me on 14.04LTS. What worked was: dpkg-reconfigure mysql-server-5.5 – Meetai.com Dec 29 '14 at 01:22
22

dpkg-reconfigure didn't prompt for password for me.. is there a way to force it? – Unirgy Dec 17 '15 at 18:02
16

Did not work for me. I used the command as above, and got "Checking if update is needed. This installation of MySQL is already upgraded to 5.7.13, use --force if you still need to run mysql_upgrade". Using --force gave the same response. – Anthony Scaife Aug 14 '16 at 13:59
6

Apparently this does not work anymore. – dpi Jan 07 '17 at 18:05
@AnthonyScaife did you get it? I have this same output. – Paulo Henrique Feb 21 '17 at 22:05
1

@Paulo Henrique No. Given the situation, we decided the best use of our time was a full server reinstall. – Anthony Scaife Feb 23 '17 at 03:21
@precise how do i can reconfigure with --force, because if i run your command to reconfigure, i am getting always -- This installation of MySQL is already upgraded to 5.7.19, use --force if you still need to run mysql_upgrade – manny Aug 18 '17 at 09:25

score 22 · Answer 2 · edited Nov 24 '16 at 12:54

22

Reference taken from this blog:

Step 1: Stop MySQL Service.

sudo service mysql stop

Step 2: Kill all running mysqld.

sudo killall -9 mysqld

Step 3: Starting mysqld in Safe mode.

sudo mysqld_safe --skip-grant-tables --skip-networking &

Step 4: Start mysql client

mysql -u root

Step 5: After successful login, please execute this command to change any password.

FLUSH PRIVILEGES;

Step 6: You can update mysql root password .

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

for mysql > 5.7 use this instead of above:

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

Step 7: Please execute this command.

FLUSH PRIVILEGES;

Step 8: Exit mysql console

exit

Step 9: Kill mysqld_safe and start mysql

sudo killall mysqld_safe && sudo service mysql start

edited Nov 24 '16 at 12:54

Mehraban

1,285
2
19
26

answered Sep 08 '15 at 05:33

Anvesh

591
5
4

1

should be sudo service mysql stop, not sudo stop mysql – knocte Jul 04 '16 at 03:51
1

also, at the end it's missing the steps sudo killall mysqld_safe && sudo service mysql start and then try to log in again – knocte Jul 04 '16 at 03:58
3

For MYSQL 5.7 the change password syntax has changed to ALTER USER root IDENTIFIED BY 'NEW_PASSWORD_HERE' – Tarek Fadel Jul 06 '16 at 07:55
9

NOTE! There is a new beast in MySQL 5.7 called auth_socket. If you install mysql without a root password, the beast will attack you and you'll loose 50 hitpoints. In step 6 above you need to ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASS';. You can read about it here. Theoretically (I didn't try), you should be able to log in to mysql without a password if you "sudo su -" to become root. – olafure Oct 13 '16 at 13:14
6

And in case ALTER USER ... doesn't work, use UPDATE mysql.user SET authentication_string = PASSWORD('NEWPASS'), plugin = 'mysql_native_password' WHERE User = 'root' AND Host = 'localhost'; – olafure Oct 13 '16 at 14:04

score 9 · Answer 3 · edited Aug 02 '17 at 21:37

9

Under Ubuntu 16.04 and mysql-server-5.7, the correct answer is the last comment of olafure, dpkg-reconfigure mysql-server-5.7 no longer works.

sudo service mysql stop
sudo killall mysqld
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root

Now in mysql console >mysql

USE mysql;
UPDATE user SET authentication_string=PASSWORD('newpass') WHERE user='root';
FLUSH PRIVILEGES;
\q

Restart the good mysql process

sudo killall mysqld
sudo service mysql start

Check your new password

mysql -u root -p
Enter password: newpass
mysql>

edited Aug 02 '17 at 21:37

Evan Carroll

7,526

answered Nov 22 '16 at 11:05

Pierre-Damien

230

6

At mysqld_safe execution, I get the error: Directory '/var/run/mysqld' for UNIX socket file don't exists.. So I ran sudo mkdir /var/run/mysqld; sudo chown mysql /var/run/mysqld and the rest of the above answer works. Is this a bug on Ubuntu 16? I've never experienced all these issues updating passwords before. – Chester Apr 02 '17 at 21:07
@Chester thank you very much!! This was the only way that finally worked now as all other solutions throw an error. (Ubuntu-Server 16.04 here) This should be added to the answer – derHugo May 30 '17 at 06:48

score 3 · Answer 4 · answered Jan 20 '17 at 19:41

3

Starting with MySQL 5.7, during initial installation, if you leave password empty, then for that user, authentication will be based on auth_socket plugin.

The correct way to change password will be:

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

https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/

answered Jan 20 '17 at 19:41

Barun

725
1
6
10

This was my exact case (empty password during installation in mysql 5.7) and this was the only solution for my case. – cherouvim Apr 19 '17 at 07:06
I get the message: ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement – user2513149 Aug 25 '18 at 05:31
@user2513149 Please restart your MySQL server without --skip-grant-tables option and see this solution works. – Barun Aug 25 '18 at 05:37
@Barun, no, without --skip-grant-tables option I can't even log into MySQL as root. It says ERROR 1698 (28000): Access denied for user 'root'@'localhost' – user2513149 Aug 25 '18 at 05:58
Solved! I had to login to regular MySQL console (not mysqld_safe) as root without password. I have Ubuntu 18.04 and mysql-server-5.7. This answer helped me https://askubuntu.com/a/767252/585252 (see the bottom part) – user2513149 Aug 25 '18 at 06:56

score 1 · Answer 5 · answered Jan 19 '17 at 21:40

1

Nothing from the rest of the answers seemed to work for me. This is my solution:

sudo service mysql stop
sudo mysqld_safe &
sudo mysql -u root

In there:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
FLUSH PRIVILEGES;
QUIT;

Then:

sudo killall mysqld
sudo service mysql start
sudo mysql -u root -p

answered Jan 19 '17 at 21:40

Wtower

467
9
16

1

The rest doesn't work for me neither ... nor does your solution unfortunately: Still get this Error
2017-05-30T06:33:22.291126Z mysqld_safe Logging to syslog. 2017-05-30T06:33:22.294375Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2017-05-30T06:33:22.296874Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
– derHugo May 30 '17 at 06:34
1

This is the only advice that works for me out of the six or so suggestions I tried, but I added @Chester 's bit above to avoid the "UNIX socket file don't exists" error: sudo mkdir /var/run/mysqld; sudo chown mysql /var/run/mysqld This is on mysql 5.7 and ubuntu 18.04. – twowheeler Oct 09 '19 at 00:01

score 0 · Answer 6 · edited Dec 10 '14 at 05:22

0

Create a file with this:

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

Stop the mysql server and run this:

mysqld_safe --init-file=/home/me/mysql-init &

Check here for more details: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

edited Dec 10 '14 at 05:22

user.dz

48,105

answered Dec 10 '14 at 02:47

elier

101

unable to reset root password of mysql

6 Answers6

Easy steps to reset mySQL root password:

Linked

Related