Can't reset root password with --skip-grant-tables on ubuntu 16

Question

I am trying to reset the root password following MysqlPasswordReset but when I try to start the server with --skip-grant-tables the server doesn't start

Ubuntu 16.04.1 LTS (GNU/Linux 4.4.0-59-generic x86_64)
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64)

Server is running

$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Stop server

$ sudo /etc/init.d/mysql stop
[ ok ] Stopping mysql (via systemctl): mysql.service.

Trying to start with --skip-grant-tables

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
[1] 9856

Connect with no password

$ mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[1]+  Exit 1                  sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

I also tried to start with mysql_safe (error.log is empty)

sudo mysqld_safe --skip-grant-tables
2017-02-01T16:33:31.382105Z mysqld_safe Logging to syslog.
2017-02-01T16:33:31.383942Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-02-01T16:33:31.386058Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-02-01T16:33:31.388009Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

Possible duplicate of [ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)](http://stackoverflow.com/questions/11657829/error-2002-hy000-cant-connect-to-local-mysql-server-through-socket-var-run) — davejal, Feb 01 '17 at 16:46
when using safe mode could you make connection using root? Did you try without the `--skip-networking`? — davejal, Feb 01 '17 at 16:50
@davejal Yes, I tried without --skip-networking and I can't connect to the mysql server in safe mode — pedronalbert, Feb 01 '17 at 17:10

score 167 · Accepted Answer · edited Jul 30 '18 at 13:16

167

I found that the mysql.sock is deleted when the mysql service is stoped and mysqld_safe can't create it (I couldn't find the reason), so my solution was back up the sock folder and restore before start mysqld_safe

Start server

$ sudo service mysql start

Go to sock folder

$ cd /var/run

Back up the sock

$ sudo cp -rp ./mysqld ./mysqld.bak

Stop server

$ sudo service mysql stop

Restore the sock

$ sudo mv ./mysqld.bak ./mysqld

Start mysqld_safe

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

Init mysql shell

mysql -u root

Change password

FLUSH PRIVILEGES;

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

edited Jul 30 '18 at 13:16

mrjake101

67
1
10

answered Feb 01 '17 at 19:09

pedronalbert

2,676
3
15
18

2

I had to set the password that way: http://stackoverflow.com/questions/37879448/mysql-fails-on-mysql-error-1524-hy000-plugin-auth-socket-is-not-loaded#answer-37879449 – Jerome Jaglale Feb 06 '17 at 21:22
20

On last command got `ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded` on Kubuntu 18.04 – Dr.X Sep 03 '18 at 12:00
1

I believe this method is easier – https://superuser.com/a/1175035/320611 – David Refoua Nov 03 '18 at 01:56
In mysql 5.7.24, the password column must be changed to authentication_string – Igor Uchôa Dec 06 '18 at 12:11
8

after doing this mysql server didn't start – cksahu Aug 27 '19 at 17:08
Thanks, I've been searching for a while for a solution to a newly installed mysql service on Debian 19.10. This is the first solution that actually worked. – MKUltra Jan 12 '20 at 23:03
I had to reboot linux as I couldn't start the service again after doing this. All working now, thank you! – lcompare May 04 '20 at 16:00
This doesn't work for Ubuntu 16.04 MySQL Community Server – ranka47 Dec 03 '20 at 03:05
God bless you. Worked like a charm – JsHitachi Jun 10 '21 at 04:38

dvlcube · Answer 2 · 2019-11-18T19:35:43.820

56

For Ubuntu 19 with MySQL 8.0.17-0ubuntu2, what ended up working for me was a combination of many answers:

In the MySQL's configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf on my machine), under [mysqld], add:

skip-grant-tables = 1 plugin-load-add = auth_socket.so
Restart the MySQL Service;
Connect to MySQL: mysql -uroot;
Run:

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

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

Stop MySQL and comment skip-grant-tables in the configuration file;
Start MySQL again and this should now work: mysql -u root -ppass123.

edited Nov 18 '19 at 19:35

answered Nov 18 '19 at 19:30

dvlcube

1,117
1
12
20

1

Note that if you see that the `UPDATE` statement did not alter any rows (e.g. `Query OK, 0 rows affected`) it means you don't have a root user. In that case follow the instructions in [this other answer](https://stackoverflow.com/questions/1709078/how-can-i-restore-the-mysql-root-user-s-full-privileges) to create it before running these queries again. – devius Nov 21 '19 at 10:48
3

I cannot tell you how long I blundered around for before I found this answer. Thank you. – Chris J Harris Apr 01 '20 at 06:23
`mysql: [ERROR] unknown variable 'skip-grant-tables=1'` – logicbloke Jul 13 '20 at 23:54
2

@logicbloke It should be enough to simply put: `skip-grant-tables` – Jesse Nickles Jul 18 '20 at 15:53
3

THANK YOU THANK YOU THANK YOU!! This answer saved my bacon today. – Bernardo Siu Sep 14 '20 at 19:21
The accepted answer didn't work for me (mysld_safe just kept exiting right away with no useful error) but this answer worked perfectly. Thanks! – Stewart Johnson Sep 23 '20 at 00:16
1

Thank you! Tried to solve this problem for more than one day. – Caio Ladislau Oct 05 '20 at 13:00
I totally fall in line with the previous acknowledgements, thanks so much! Finally I solved this issue after several hours. – Andreas L. Dec 06 '20 at 17:30
After a dozen different attempts, this is the one that worked. – GeneC Dec 28 '20 at 22:31
This is what helped me. Nothing else worked. Not even the official advice of running MySQL in safe mode with command line options and init-file. – Umar Farooq Khawaja Feb 10 '21 at 19:48
Thanks a lot ! Ubuntu 20. Mysql 8.0.26 ... Tried so many things. This finally worked ! – dark knight Jul 29 '21 at 12:08
I tried the most upvoted (backup the sock) solution and it did not work however this did perfectly. Server version: 8.0.32-0ubuntu0.20.04.2 (Ubuntu) – PTBW Mar 03 '23 at 09:06

score 5 · Answer 3 · edited May 16 '18 at 00:20

5

pedronalbert's answer above worked for me but the last step is now deprecated and throws the following warning:

Warning | 1287 | 'SET PASSWORD FOR = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR = '' instead

Use this command instead:

SET PASSWORD FOR root = '<plaintext_password>';

edited May 16 '18 at 00:20

Stephen Rauch

47,830
31
106
135

answered May 15 '18 at 23:57

auhsor

101
1
4

1

looks like they have removed an important functionality. with `--skip-grant-tables` the auth plugin is not loaded, and we cannot use `set password`, so there is no way to seems to revert the password. looks like we need to execute `password('password')` on a old mysql server, and copy paste the auth string, and update the table manually. – Mathieu J. Nov 18 '19 at 09:12

score 4 · Answer 4 · answered May 23 '18 at 09:34

4

I tried many ways including @pedronalbert 's but still not working.

The way I solved it is adding "skip-grant-tables" in /etc/my.cnf then start mysql service and connecting mysql with "mysql -u root" as https://www.codero.com/knowledge-base/content/33/296/en/how-to-reset-your-root-mysql-password.html

It works in my VM CentOS 7.

answered May 23 '18 at 09:34

gevge

399
2
6

This method of configuring `skip-grant-tables` also works for Ubuntu 19 and MySQL 8. – dvlcube Nov 18 '19 at 19:34
1

This answer doesn't go into enough detail and will most likely not work. You can't just add `skip-grant-tables` randomly like it says. Check the answer below for the actual complete process to use. – devius Nov 21 '19 at 10:48

Can't reset root password with --skip-grant-tables on ubuntu 16

4 Answers4

Linked