• Uncategorized

About mysql : ERROR-2002-HY000-Cant-connect-to-local-MySQL-server-through-socket-varrunmysqldmysqldsock-2

Question Detail

I installed LAMP on Ubuntu 12.04 LTS (Precise Pangolin) and then set root password on phpMyAdmin. I forgot the password and now I am unable to login. When I try to change password through terminal I get:

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

How can I fix this? I am unable to open LAMP, uninstall it or reinstall it.

Question Answer

I once had this problem and solved it by installing mysql-server, so make sure that you have installed the mysql-server, not the mysql-client or something else.

That error means the file /var/run/mysqld/mysqld.sock doesn’t exists, if you didn’t install mysql-server, then the file would not exist. So in that case, install it with

sudo apt-get install mysql-server

But if the mysql-server is already installed and is running, then you need to check the config files.

The config files are:

/etc/my.cnf
/etc/mysql/my.cnf
/var/lib/mysql/my.cnf

In /etc/my.cnf, the socket file config may be /tmp/mysql.sock and in /etc/mysql/my.cnf the socket file config may be /var/run/mysqld/mysqld.sock. So, remove or rename /etc/mysql/my.cnf, let mysql use /etc/my.cnf, then the problem may solved.

Try this:

mysql -h 127.0.0.1 -P 3306 -u root -p <database>

Also (to see if it’s running):

telnet 127.0.0.1 3306 

Probably it is just a misconfiguration in the my.cnf file, in /etc/somewhere (depending on the Linux distribution).

I am seeing all these answers, but none offer the option to reset the password and no accepted answer. The actual question being he forgot his password, so he needs to reset, not see if it’s running or not (installed or not) as most of these answers imply.


To reset the password

Follow these steps (can be helpful if you really forget your password and you can try it anytime, even if you’re not in the situation at the moment):

  1. Stop mysql

    sudo /etc/init.d/mysql stop
    

    Or for other distribution versions:

    sudo /etc/init.d/mysqld stop
    
  2. Start MySQL in safe mode

    sudo mysqld_safe --skip-grant-tables &
    
  3. Log into MySQL using root

    mysql -u root
    
  4. Select the MySQL database to use

    use mysql;
    
  5. Reset the password

    -- MySQL version < 5.7
    update user set password=PASSWORD("mynewpassword") where User='root';
    
    -- MySQL 5.7, mysql.user table "password" field -> "authentication_string"
    
    update user set authentication_string=password('mynewpassword') where user='root';
    
  6. Flush the privileges

    flush privileges;
    
  7. Restart the server

    quit
    
  8. Stop and start the server again

    Ubuntu and Debian:

    sudo /etc/init.d/mysql stop
    ...
    sudo /etc/init.d/mysql start
    

On CentOS, Fedora, and RHEL:

    sudo /etc/init.d/mysqld stop
    ...
    sudo /etc/init.d/mysqld start
  1. Login with a new password

    mysql -u root -p
    
  2. Type the new password and enjoy your server again like nothing happened

This was taken from Reset a MySQL root password.

I tried the following steps:

  1. Log in as super user or use sudo
  2. Open /etc/mysql/my.cnf using gedit
  3. Find bind-address, and change its value to the database server host machine’s IP address. For me, it was localhost or 127.0.0.1
  4. Save and close the file.
  5. Come back to terminal and execute sudo service mysql start

And it worked for me.

In my case it was that the disk was full and mysqld couldn’t start anymore.

Try to restart mysql service.

> service mysql restart

or

> service mysql stop

> service mysql start

If it doesn’t recognize stop command then it’s definitely the disk space. You should make some space in the partition mysql is allocated or make the disk larger.

Check the disk space with

> df -h

I fixed this problem by executing the following command:

mysql.server start

And if you are on a mac and used brew to install mysql, simply use:

brew services start mysql

I had a similar problem. mysql wouldn’t start:

sudo service mysql start
start: Job failed to start

If I disabled apparmor:

sudo aa-complain /etc/apparmor.d/*

the problem went away. The issue was that mysqld was trying to access /run/mysqld/mysqld.sock but the apparmor profile only gave permission to /var/run/mysqld/mysqld.sock (/var/run is symlinked to /run, so these are actually the same). Not sure why mysqld isn’t using the var path since that’s what’s set in all the configuration files, but you can fix the problem by adding the following to /etc/apparmor.d/usr.sbin.mysqld

/run/mysqld/mysqld.pid rw,
/run/mysqld/mysqld.sock rw,

I solved this by killing the mysql process:

ps -ef | grep mysql
kill [the id]

And then I started the server again with:

sudo /etc/init.d/mysql restart

But start works as well:

sudo /etc/init.d/mysql start

Then I logged in as admin, and I was done.

Somehow the MySQL server process did not create the socket, or the client is looking for the socket in the wrong place.

My first suggestion would be to check if the MySQL server is running. Second suggestion might be, is the MySQL server running on another host? If so, add the -h <hostname> flag to your MySQL client in the terminal.

If MySQL is indeed running, and running locally, check your my.cnf file. There should be a line like

socket = /var/run/mysqld/mysqld.sock

See if that matches the socket location that you mentioned in your post.

From experience, I would say the most likely scenario is your MySQL server either is not running at all or is not running on the same host as where you run your MySQL client from the terminal.

I just experienced the same issue after I had to restart my production server. I am running Debian 8.1 (Jessie) on a DigitalOcean droplet.

This is what I did to resolve my issue:

  1. Check if the file /var/run/mysqld/mysqld.sock exists. If it doesn’t, manually create it by entering touch /var/run/mysqld/mysqld.sock (which is what I had to do).

  2. So the MySQL process can use this file. Change ownership of said file by entering chown mysql /var/run/mysqld/mysqld.sock.

  3. Once ‘2’ has been done, restart the MySQL service by entering service mysql restart or /etc/init.d/mysql restart.

After going through the above steps, my issue was solved. I rarely have this issue, and there is probably a better way, so by all means provide constructive feedback if need be :).

Check the "bind-adress" parameter in my.cnf.

Else try with the command:

mysql -h 127.0.0.1 -P 3306 -u root -p
  • -h for host 127.0.0.1, that is, localhost

  • -P (notice -P as uppercase) for port 3306, that is, the default port for MySQL

Your mysql-server might not be running. Ensure it runs by typing mysql.server start into the terminal.

I think whenever you get the error

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

I will recommend first to check whether your mysql daemon is running… Most of the time it will not running by default. You can check it by /etc/init.d/mysqld status.

If it’s not running then start it first:

.../etc/init.d/mysqld start.

I bet it will 110% work.

If you’re using Amazon EC2, and you’re having this problem on the instance, then you only need to do:

sudo yum install mysql-server
sudo service mysqld restart

Amazon EC2 doesn’t have a server installed (only the client is installed), so in case of that you need to install that on your instance, and after that try

 mysql -u root -p

to check if that worked.

Instead of using localhost:

mysql -u myuser -pmypassword -h localhost mydatabase

Use 127.0.0.1

mysql -u myuser -pmypassword -h 127.0.0.1 mydatabase

(also note, no space between -p and mypassword)

Enjoy 🙂

Here’s what worked for me:

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
service mysql restart

This creates a link.

Make sure you have backups of important databases and then try uninstall MySQL related stuff:

apt-get remove --purge mysql\*

Then install it again:

apt-get install mysql-server mysql-client

This worked for me and data was kept.

If PHP MySQL shows errors you might have to reinstall PHP MySQL:

apt-get install php5-fpm php5-mysql

I also facing same problem it will be occur if your mysql server is not running by default it will again stop after some sec so you again run ($ sudo service mysql start ) command you can change if know.

for that use command

$ sudo service mysql start   

(put user password if required because we use sudo )
and then run

$ sudo mysql -u root -p          (put user password if required )

now you got your database

I got this problem too, but I just did:

sudo service mysql restart 

It worked for me.

It seems your MYSQL is stopped. use below command to start MySQL again

sudo service mysql start

I FOUND THE SOLUTION

Before firing the command : mysql_secure_installation

  • Step 1: sudo systemctl stop mariadb
  • Step 2: sudo systemctl start mariadb
  • Step 3: mysql_secure_installation

Then it will ask root password and you can simply press Enter and set your new root password.

If your installation was recent, you should to confirm if your installation is the installation SERVER… as mysql-server-5.5.. Maybe you installed only “mysql” .. this is only client instead of the server.

If you have XAMPP installed on your Linux machine, try to copy your my.cnf file from /opt/lampp/etc/my.cnf to /etc/my.cnf.

Then, run the mysql -u root again… You should now have the correct socket and be able to run the MySQL client.

Fist,try to restart it with

service mysql stop

service mysql start

If above not resolve the issue, now let’s go…

Uninstall completely MySQL

sudo apt-get remove --purge mysql\*

reinstall it

sudo apt install mysql-server mysql-client

test if it run

sudo mysql

Install php drivers

sudo apt install php7.4 php7.4-fpm php7.4-mysql php7.4-cgi php7.4-cli php7.4-common

You can replace php7.4 by php7.x or php8.0.12 or later
Very nice !

❗️Be careful, you lose your data if they are not saved. Please backup your data if possible yet.

In my case, the default port 3306 was being used by some other process and thus it was not starting. After I stopped the other service and did sudo service mysql start, it worked fine. BTW, you can use something like sudo lsof -Pn -iTCP:3306 to see who may be using the port.

In my case it worked by doing some R&D:

I am able to connect to MySQL using

root-debian#mysql -h 127.0.0.1 -u root -p

But it’s not working with mysql -u root -p.

I did not find any bind-address in my.cnf. So I outcommented the parameter socket=/var/lib/mysql/mysqld.sock in my.cnf which was causing me a problem with login.

After restarting the service it went fine:

[email protected]:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.19 MySQL Community Server (GPL)

In my case, It seems like I wasnt really able to kill the mysql process, when I run

sudo service mysql stop
ps -ef | grep mysql

The mysql process was always there, it looks like it was blocking the socket file and new mysql process wasnt able to create it itself.

so this helped

cd /var/run
sudo cp mysqld/ mysqld.bc -rf
sudo chown mysql:mysql mysqld.bc/
sudo service mysql stop
sudo cp mysqld.bc/ mysqld -rf
sudo chown mysql:mysql mysqld -R
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Now Im able to log in database using

mysql -u root

Then to update root password:

UPDATE user SET authentication_string=password('YOURPASSWORDHERE') WHERE user='root';
FLUSH PRIVILEGES;

PS: I had trouble updating root passwod, seems like problem with “auth_socket” plugin, so I had to create new user with full privileges

insert into user set `Host` = "localhost", `User` = "super", `plugin` = "mysql_native_password", `authentication_string` = NULL, `password_expired` = "N", `password_lifetime` = NULL, `account_locked` = "N", `Select_priv` = "Y",
`Insert_priv` = "Y", `Update_priv` = "Y", `Delete_priv` = "Y", `Create_priv` = "Y", `Drop_priv` = "Y", `Reload_priv` = "Y", `Shutdown_priv` = "Y", `Process_priv` = "Y", `File_priv` = "Y",
`Grant_priv` = "Y",  `References_priv` = "Y", `Index_priv` = "Y", `Alter_priv` = "Y", `Show_db_priv` = "Y", `Super_priv` = "Y", `Create_tmp_table_priv` = "Y", `Lock_tables_priv` = "Y",
`Execute_priv` = "Y", `Repl_slave_priv` = "Y",  `Repl_client_priv` = "Y",  `Create_view_priv` = "Y", `Show_view_priv` = "Y", `Create_routine_priv` = "Y", `Alter_routine_priv` = "Y",
`Create_user_priv` = "Y",  `Event_priv` = "Y", `Trigger_priv` = "Y", `Create_tablespace_priv` = "Y";

This creates user “super” with no password and then you can connect with mysql -u super

On Debian server Jessie, my working solution was to simply do

service mysql restart
service mysql reload

as root user

i solved this problem with restart mysql

/etc/init.d/mysql stop

and

/etc/init.d/mysql start

that’s it.

By experience I say that you need to check if the server is running first and then try configuring MySQL. The last solution is to re-install MySQL.

You may also like...

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.