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

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.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c2b0adadb682">[email protected]</a>'localhost' = PASSWORD('my_new_password');

Method 2

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.

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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating