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.

Tom V
  • 15,670
  • 7
  • 63
  • 86
SecEngineer
  • 23
  • 2

1 Answers1

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?)

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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 Aug 01 '16 at 20:43
  • If you are running with --skip..., then don't use the "-p" on mysql. – Rick James Aug 01 '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 http://dba.stackexchange.com/questions/22053/reset-mysql-root-password-in-lampp-server-on-ubuntu/22061#22061 and my post http://dba.stackexchange.com/questions/33468/how-to-add-root-mysql-user-back-on-mamp/33489#33489 – RolandoMySQLDBA Aug 01 '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! – SecEngineer Aug 02 '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. – SecEngineer Aug 02 '16 at 13:03