Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) - error

[John Losty]

I installed MySQL on Ubuntu and now I would like to reset the root password.
I follow this instructions:
ubuntu documentation

this step works:

sudo /etc/init.d/mysql stop

This also:

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

On this step I get the following error:

 mysql -u root

Error:

mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[1]+  Exit 1                  sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

How can I solve this ?

[Ron McLeod]

You need to find where the socket has been opened -- it appears to not be at the default for your installation - /var/run/mysqld/mysqld.sock.

There are a couple ways to do that:

check your my.cnf file and look for a line like: socket = /home/hss/db/mysql.sock

run netstat --listen and locate the line for mysql -- my platform shows: unix 2 [ ACC ] STREAM LISTENING 10427 /home/hss/db/mysql.sock

Then, try your command again, specifying the socket location: mysql -u root -S /home/hss/db/mysql.sock

Also, you will probably have to use the -p option so that you will be prompted for a password.

[John Losty]

I don't know exactly where is this file but I assume that is this?
What should I do when there is no line like socket = /home/hss/db/mysql.sock

sudo gedit /etc/mysql/my.cnf

my.cnf:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[John Losty]

I think MySQL is not running so therefore I cannot find it inside netstat, or??

[John Losty]

I ran mysql
and this is what i found with netstat --listen:

unix  2      [ ACC ]     STREAM     LISTENING     108426   /var/run/mysqld/mysqld.sock

[Ron McLeod]

Try using ps to see the config file and socket location was  provided to mysqld (I reformatted the output):

# ps -ef | grep mysqld

mysql     2312  1815  0 Oct31 ?        00:05:33
    /usr/sbin/mysqld
        --defaults-file=/home/hss/current/CONF/my.cnf
        --basedir=/usr --datadir=/home/hss/db
        --plugin-dir=/usr/lib/mysql/plugin
        --user=mysql
        --log-error=/home/hss/db/mysqld.log
        --pid-file=LS-SSIeNB.pid
        --socket=/home/hss/db/mysql.sock
        --port=3306
        --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1


Edit: this step is necessary anymore since you found the socket location

[Ron McLeod]

John Losty wrote:I ran mysql
and this is what i found with netstat --listen:

unix  2      [ ACC ]     STREAM     LISTENING     108426   /var/run/mysqld/mysqld.sock

Ok - are you able to connect by specifying the socket now?

[John Losty]

This is how I ran commands:

sudo /etc/init.d/mysql stop

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

This time

mysql -u root -S /var/run/mysqld/mysqld.sock

And error:

mysql -u root -S /var/run/mysqld/mysqld.sock
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[1]+  Exit 1                  sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

[John Losty]

The problem is I think that this line

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

doesn't even starts the server. According to htop the process is not running.

[Ron McLeod]

Does Ubuntu use init.d scripts to start/stop platform services?  If it does, then you should probably be starting the mysql server with: sudo /etc/init.d/mysql start

[John Losty]

I tried this:

seba@seba-H81-D3:~$ sudo /etc/init.d/mysql stop
[ ok ] Stopping mysql (via systemctl): mysql.service.
seba@seba-H81-D3:~$ sudo /etc/init.d/mysql start --skip-grant-tables --skip-networking 
[ ok ] Starting mysql (via systemctl): mysql.service.
seba@seba-H81-D3:~$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[Ron McLeod]

As I mentioned earlier, you will probably have to use the -p option so that you will be prompted for a password:

mysql -u root -p   or   mysql -u root -p your-password-here

[John Losty]

I don't have the root password, I want to change it, see my first post

[John Losty]

seba@seba-H81-D3:~$ sudo /etc/init.d/mysql stop
[sudo] password for seba: 
[ ok ] Stopping mysql (via systemctl): mysql.service.
seba@seba-H81-D3:~$ sudo /etc/init.d/mysql start --skip-grant-tables --skip-networking 
[ ok ] Starting mysql (via systemctl): mysql.service.
seba@seba-H81-D3:~$ mysql -u root -p
Enter password: 

[John Losty]

It want's password from me but I don't have it, that is why I want it to reset it.

[Ron McLeod]

If you are setting the password for the first time, you should be using the mysqladmin command:

mysqladmin -u root password your-new-password

[John Losty]

No I already had a password. Maybe I should just unnistal the program and install it again and set the password that way?

[John Losty]

I unnistaled MySQL and installed it again.
I tried to set the root password for the first time this way, but I cant I get the following error:

mysqladmin -u root password "seba";
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

[John Losty]

This is how I installed MySQL:

sudo apt-get update
sudo apt-get install mysql-server

[Ron McLeod]

Maybe your uninstall did not clean up everything.  This is what I see with a fresh install on a Centos 6 distrubution:

[root@pmgr ~]# yum -y install mysql-server

...

Installed:
  mysql-server.x86_64 0:5.1.73-7.el6

Dependency Installed:
  mysql.x86_64 0:5.1.73-7.el6                                perl-DBD-MySQL.x86_64 0:4.013-3.el6

Dependency Updated:
  mysql-libs.x86_64 0:5.1.73-7.el6

Complete!

[root@pmgr ~]# service mysqld start
Initializing MySQL database:  WARNING: The host 'pmgr' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h pmgr password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[root@pmgr ~]# mysqladmin -u root password secret

[root@pmgr ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[root@pmgr ~]# mysql -u root -p
Enter password: ++++++
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@pmgr ~]#

[John Losty]

I fixed it already