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)
$ 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 &  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) + 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.
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.
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
$ sudo service mysql start
Go to sock folder
$ cd /var/run
Back up the sock
$ sudo cp -rp ./mysqld ./mysqld.bak
$ sudo service mysql stop
Restore the sock
$ sudo mv ./mysqld.bak ./mysqld
$ sudo mysqld_safe --skip-grant-tables --skip-networking &
Init mysql shell
mysql -u root
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');
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.cnfon my machine), under
skip-grant-tables = 1
plugin-load-add = auth_socket.so
- Restart the MySQL Service;
Connect to MySQL:
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-tablesin the configuration file;
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>';
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
It works in my VM CentOS 7.