Can't Reset Root Password With --skip-grant-tables On Ubuntu 16

Answer :

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

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.

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