2

Disk space was full on my server and I needed to do a mysqldump. I tried logging into MySQL, but temporarily forgot the password. I then tried using the --skip-grant-tables & , but I received an error and it did not work. I tried several times.

I finally got hold of the correct password, but I encountered an issue that:

Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock'  

Somehow I was able to get rid of that error through lots of trial and error, but now I can still not login.

Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost'(using   password: YES)

I need to reset the password for MySQL without losing data.

5
  • @SecEnginner Try connecting to 127.0.0.1 also, make sure your services are running. Once you confirm those, please let us know if you are still having issues and we can start trying a few things.
    – Hector
    Aug 1 '16 at 19:42
  • 1
    Hello @Hector thank you for your response. My bind-address in my.cnf is 127.0.0.1. Aug 1 '16 at 20:05
  • ps aux | grep mysql shows /usr/sbin/mysqld Aug 1 '16 at 20:05
  • type mysql -u root -p what do you see now?
    – Hector
    Aug 1 '16 at 20:30
  • I see: Enter password: Aug 1 '16 at 20:32
1

Looks like your socket file is missing

When that happens, you must connect to MySQL using TCP/IP as follows

mysql -uroot -p -h127.0.0.1 -P3306 --protocol=tcp

Specifying the port number and protocol, this will connect to MySQL with or without a socket file.

I have suggested this method many times when mysqld is running without a socket file

NOTE: Restarting MySQL will bring back the socket file. Usually, starting MySQL when mysqld is already running is what usually cause the socket file to disappear. (See Is there anyway that I can manually create a PID for the MySQL instance?)

5
  • Hello, I appreciate your response very much. As mentioned above, I am not getting the socket error anymore, but my last known password is not working. Is there a way I can safely reset the MySQL password, which will also allow me to see if the communication to MySQL is working properly? Aug 1 '16 at 20:43
  • If you are running with --skip..., then don't use the "-p" on mysql.
    – Rick James
    Aug 1 '16 at 20:54
  • Shlomi Noach had a great suggestion long ago to restart mysqld just once to embed new root password without --skip-grant-tables. See his post dba.stackexchange.com/questions/22053/… and my post dba.stackexchange.com/questions/33468/… Aug 1 '16 at 21:39
  • Hello, my MySQL is working now. I am not sure what exactly made it work this time when running with --skip, but I do know I did service mysql stop instead of /etc/init.d/mysql stop this time. Also I made sure to not include -p when using --skip. As Rolando stated, I believe this issued may have been caused by starting MySQL when mysqld is already running. Thank you all for your help! Aug 2 '16 at 13:01
  • To others who have run into this issue, please just be very careful when using --skip-grant-tables. I would not recommend using /etc/init.d/mysql stop for stopping MySQL. I am using Ubuntu FYI. Aug 2 '16 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.