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.

Improve this question
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
    Commented Aug 1, 2016 at 19:42
  • 1
    Hello @Hector thank you for your response. My bind-address in my.cnf is 127.0.0.1.
    – SecEngineer
    Commented Aug 1, 2016 at 20:05
  • ps aux | grep mysql shows /usr/sbin/mysqld
    – SecEngineer
    Commented Aug 1, 2016 at 20:05
  • type mysql -u root -p what do you see now?
    – Hector
    Commented Aug 1, 2016 at 20:30
  • I see: Enter password:
    – SecEngineer
    Commented Aug 1, 2016 at 20:32

1 Answer 1

Reset to default
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?)

Improve this answer
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?
    – SecEngineer
    Commented Aug 1, 2016 at 20:43
  • If you are running with --skip..., then don't use the "-p" on mysql.
    – Rick James
    Commented Aug 1, 2016 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/…
    – RolandoMySQLDBA
    Commented Aug 1, 2016 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!
    – SecEngineer
    Commented Aug 2, 2016 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.
    – SecEngineer
    Commented Aug 2, 2016 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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