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.

Engram

Possible duplicate of ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) – Martel

when using safe mode could you make connection using root? Did you try without the --skip-networking? – Martel

@Martel Yes, I tried without --skip-networking and I can't connect to the mysql server in safe mode – Engram

Answer 1

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

Engram

I had to set the password that way: #37879948 – Demineralize

On last command got ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded on Kubuntu 18.04 – Latton

I believe this method is easier – superuser.com/a/1175035/320611 – Abecedarian

In mysql 5.7.24, the password column must be changed to authentication_string – Monjan

after doing this mysql server didn't start – Kilderkin

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

I had to reboot linux as I couldn't start the service again after doing this. All working now, thank you! – Firn

This doesn't work for Ubuntu 16.04 MySQL Community Server – Amelia

God bless you. Worked like a charm – Potsdam

Answer 2

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

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

2. Restart the MySQL Service;

3. Connect to MySQL: mysql -uroot;

4. Run:

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

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

5. Stop MySQL and comment skip-grant-tables in the configuration file;

6. Start MySQL again and this should now work: mysql -u root -ppass123.

Integrity

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 to create it before running these queries again. – Lefler

I cannot tell you how long I blundered around for before I found this answer. Thank you. – Incisor

mysql: [ERROR] unknown variable 'skip-grant-tables=1' – Stew

@Stew It should be enough to simply put: skip-grant-tables – Novak

THANK YOU THANK YOU THANK YOU!! This answer saved my bacon today. – Pellicle

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! – Serge

Thank you! Tried to solve this problem for more than one day. – Brackett

I totally fall in line with the previous acknowledgements, thanks so much! Finally I solved this issue after several hours. – Mortmain

After a dozen different attempts, this is the one that worked. – Maskanonge

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

Thanks a lot ! Ubuntu 20. Mysql 8.0.26 ... Tried so many things. This finally worked ! – Septet

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) – Discotheque

Answer 3

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

Delk

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

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

Moriarty

This method of configuring skip-grant-tables also works for Ubuntu 19 and MySQL 8. – Integrity

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