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. 

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

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.