2

I'm having trouble connecting to MySQL with from Ubuntu 16.04:

mysql -u root -p

which returns the error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

When I try starting the server with: 

sudo service mysql start
mysqld_safe --skip-grant-tables &

the output is:

# 2017-01-26T20:11:45.329764Z mysqld_safe Logging to syslog.
2017-01-26T20:11:45.333031Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-01-26T20:11:45.336684Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-01-26T20:11:45.358956Z mysqld_safe A mysqld process already exists

If I kill the process with:

ps aux | grep "msyql"
kill -9 pid

and then run:

mysqld_safe --skip-grant-tables &

I get:

2017-01-26T20:10:07.979813Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-01-26T20:10:07.984114Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-01-26T20:10:07.987546Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

It's an endless loop. So then what if I reinstalled from scratch?

First remove MySQL:

sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean

Then reinstall:

sudo apt-get update
sudo apt-get install mysql-server
sudo mysql_install_db   // (deprecated command)
sudo /usr/bin/mysql_secure_installation

As far as here:

sudo /usr/bin/mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
Error: Access denied for user 'root'@'localhost' (using password: YES)

or:

mysqladmin -u root password [newpass]
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Then it falls down.

I've tried following all of the answers here - ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Sometimes a temporary password is stored according to others, but it's not stored in /var/log/mysqld.log as suggested

Anybody able to help?

Community
  • 1
  • 1
snakespan
  • 1,183
  • 2
  • 15
  • 33

2 Answers2

4

If you are running mysqld_safe --skip-grant-tables & and getting the following error:

2017-01-26T20:10:07.984114Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-01-26T20:10:07.987546Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists

It might be because MySQL 5.7 and above create the /var/run/mysqld directory dynamically while running. When you stop it with service mysql stop to run mysqld_safe --skip-grant-tables , it's not available.

You can create it manually before running mysqld_safe:

service mysql stop
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
mysqld_safe --skip-grant-tables &

Then you can change the root password or whatever you were trying to do.

Leo Gallego
  • 372
  • 2
  • 11
-1

How you read this web page and tried the method(s) described there?

Resetting the Root Password: Unix and Unix-Like Systems

Shiping
  • 1,203
  • 2
  • 11
  • 21
  • Yes, I get as far as step 5 in the Unix like systems section, and I get this: mysqld_safe --init-file=/home/me/mysql-init & [1] 30542 # 2017-01-26T20:47:19.809930Z mysqld_safe Logging to syslog. 2017-01-26T20:47:19.813143Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2017-01-26T20:47:19.817689Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2017-01-26T20:47:19.820696Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists. – snakespan Jan 26 '17 at 20:48
  • This should probably be a comment. Link-only answers are discouraged as they become useless should the link go dead. Consider keeping the link for detailed explanation but adding a coarse list of instructions here. – CollinD Jan 26 '17 at 20:52
  • @CollinD i agree with you. the reason is i'm not sure if a link in comments is clickable and i just wanted to make it easier to go to the page. and that page is an official document and may be useful for others with similar problems. – Shiping Jan 26 '17 at 21:06
  • Links in comments are [totally clickable](https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html) – CollinD Jan 26 '17 at 21:09
  • @snakespan i had the similar problem a while ago and had it resolved based on the instruction in that page. but i can't remember exactly what i did except i saved the link. Seems you got a brand new installation and it puzzled me why you should have such problem. – Shiping Jan 26 '17 at 21:11
  • @Shiping I agree. I might have to start from scratch and clear everything down. Very rarely do you get these issues with fresh installations. – snakespan Jan 26 '17 at 21:14
  • @snakespan here is another post with similar problem, probably you already read it, but just in case. http://stackoverflow.com/questions/21944936/error-1045-28000-access-denied-for-user-rootlocalhost-using-password-y – Shiping Jan 26 '17 at 21:18
  • Yes I've also read that one. There were a few solutions that came close, but ultimately failed due to the: " Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) " issue or " Directory '/var/run/mysqld' for UNIX socket file don't exists." error messages when running mysqld_safe --skip-grant-tables & or mysqld_safe --init-file=/home/me/mysql-init & – snakespan Jan 26 '17 at 21:23
  • @snakespan sorry it may be too late, but you could force mysqld to listen to a port instead of socket, so you can connect via the port. not sure though if it'd solve your problem. – Shiping Jan 26 '17 at 21:34