ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

T

23

172

I use the following command:

mysql -u root -h 127.0.0.1 -p

And the error message is:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

How can I fix it?

Torture answered 4/11, 2009 at 12:37 Comment(7)

Is mysql daemon running? Have yot got a firewall? Can you telnet to mysql the default port is 306 so - telnet localhost 3306 – Beene 4/11, 2009 at 12:42

the mysql daemon is running. I'm sure. – Torture 4/11, 2009 at 12:43

Is it just me or if you're connecting to the local mysql server you don't need to specify the host e.g. mysql -u root -p should work? – Mendez 4/11, 2009 at 14:18

just to make sure, you are trying to connect to the db server locally? – Kessiah 10/6, 2011 at 18:30

I fresh installed mysql on ubuntu-12.04.2. The Version is 14.14 Distrib 5.5.32, for debian-linux-gnu (x86_64) using readline 6.2, its default config does not contain 'skip-networking'. So you would not encounter the error with this version. – Rodi 22/8, 2013 at 6:22

You might want to confirm that mysqld is running on the IP:port you're thinking of by running netstat -an | grep 3306 – Wellrounded 11/12, 2017 at 11:4

Why should tag "mysql-error-2003" exist? – Gracielagracile 30/12, 2021 at 23:59

P

250

If you are using Ubuntu, you have to use the following steps to avoid this error (if there is no replication enabled):

run the command vim /etc/mysql/my.cnf
comment bind-address = 127.0.0.1 using the # symbol
restart your MySQL server once.

In Step 1, if you cannot find bind-address in the my.cnf file, look for it in /etc/mysql/mysql.conf.d/mysqld.cnf file.

Update in case of MySQL replication enabled

Try to connect MySQL server on the IP address for which MySQL server is bound in file my.cnf instead of localhost or 127.0.0.1.

Parrotfish answered 23/4, 2013 at 8:21 Comment(13)

Thank you!!! I had been trying to figure this out for a long time! I commented out that line, saved the file, and then ran service mysql restart. And now it works! – Cykana 20/1, 2014 at 17:59

In my case, I had the server's address instead of 127.0.0.1 for bind-address. If this is the case for you, you will need to comment that out as well. – Apparel 25/12, 2014 at 12:33

There is no bind-address = 127.0.0.1 in /etc/mysql/my.cnf – Affiant 25/11, 2015 at 4:18

@developer, i found mine here: /etc/mysql/mysql.conf.d/mysqld.cnf – Glia 7/9, 2016 at 8:14

I changed the bind-address = <main server ip>, but can't restart mysql. – Schoen 22/2, 2017 at 6:50

I'm late to the party. This did indeed help with the described issue (thanks for that), however it would be great if you could add a bit more explanatory text to your answer what exactly the issue is/ and how this fix helps. Thanks again – Kreiker 22/2, 2017 at 19:1

If your flavour of Debian is running MariaDB -- who knows, you may find the bind address within the /etc/mysql/mariadb.conf.d directory; you may also locate file that bears the bind address by running this command within /etc/mysql/ : grep -r "bind\-address" . – Wellrounded 11/12, 2017 at 11:9

I am using windows 7 , what should i do? – Integumentary 6/2, 2018 at 6:11

@justin, you saved my day. thanks. it works. though I had to restart mysql after this. But it worked. thanks. – Kerrikerrie 3/7, 2018 at 4:29

This solved my issue too. To verify that you need the change, run netstat -nat | grep:3306 to verify port 3306 is bound to 127.0.0.1 in the 4th column. After the instruction above, run the command again and the local address should be updated to :::3306. Thanks! – Fisc 9/7, 2018 at 3:46

for my case, I had to bind 0.0.0.0 – Oligarchy 22/9, 2019 at 12:25

@JustinVincent - Thank you! Another update for you. I'm using Debian 10 (on a DigitalOcean server) and it's here: /etc/mysql/mariadb.conf.d/50-server.cnf. – Blancablanch 24/9, 2020 at 19:49

Using Ubuntu and I found it in /etc/mysql/mariadb.conf.d/50-server.cnf as well – Bouton 14/9, 2023 at 21:7

S

19

Try localhost instead of 127.0.0.1 to connect or in your connection-config. It worked for me on a Debian 6.0 (Squeeze) Server.

Scoter answered 14/12, 2012 at 19:59 Comment(1)

FYI if you specify localhost, that tells Mysql to connect via the unix socket (/var/run/mysqld/mysqld.sock on ubuntu) rather than over a TCP socket. – Bowman 12/9, 2013 at 0:8

F

10

In my case (remote connection), it helped turning off the firewall on the server:

service iptables stop

Faveolate answered 8/11, 2013 at 11:42 Comment(4)

@ram Probably you are using some other firewall. Or your problem is not a firewall at all. Check documentation for your linux distro. – Faveolate 22/1, 2015 at 13:58

@ram try service firewalld stop – Kone 14/12, 2015 at 17:37

MySQL typically runs on port 3306, so add that to your firewall – Whitley 21/2, 2018 at 19:27

Thank you so much! I had to disable my firewall to get to work as well: sudo ufw disable – Rampageous 26/10, 2020 at 20:55

I

10

This happens when you forget to start the database before connecting to it:

mysql.server start

Then

mysql -u root -p -h 127.0.0.1

Inherit answered 1/4, 2016 at 19:30 Comment(2)

in the terminal on mac and linux. I believe it is the same in windows CMD but I am not certain. – Inherit 7/2, 2018 at 20:45

I had to start MySQL server using sudo service mysqld start in my AWS EC2 instance with MySQL Community Server. – Perlite 31/10, 2019 at 1:11

C

6

On Windows, this problem may occur because your MySQL server is not installed and running.

To do that, start a command prompt as administrator and enter the command:

"C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin\mysqld" --install

If you get "service successfully installed" message then you need to start the MySQL service. To do that: go to Services window (Task Manager → Services → Open Services). Search for MySQL and start it from the top navigation bar. Then if trying to open mysql.exe, it will work.

Covenant answered 24/5, 2015 at 6:40 Comment(1)

A little mistake, cd "C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin" and then mysqld --install. In addition the server number depends on the download. – Korwun 24/11, 2017 at 15:26

G

6

Look at the my.cnf file. If it contains a [client] section, and the port is other than the real listen port (default 3306), you must connect the server with an explicit parameter, -P 3306, e.g.

mysql -u root -h 127.0.0.1 -p -P 3306

Geof answered 24/5, 2016 at 10:55 Comment(2)

where can i search the my.cnf . its not there in wamp server, in my laptop – Integumentary 6/2, 2018 at 6:5

On windows, it calls ‘my.ini’ usually – Geof 6/2, 2018 at 6:29

I

2

If you are running Ubuntu via WSL (Windows Subsystem for Linux) and wish to connect to the MySQL instance on the host machine, you will need to use the host machine's IPv4 address e.g. 192.X.X.X, not 127.0.0.1 or localhost.

$ mysql -u <user> -h 127.0.0.1 -p -P 3306

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (111)

$ mysql -u <user> -h 192.X.X.X -p -P 3306

Welcome to the MySQL monitor...Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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

mysql>

To check your IPv4 address, go to Settings -> Network $ Internet -> Properties -> IPv4 address.

I got the same error configuring MySQL URI for apache airflow as:

mysql+mysqlconnector://<user>:<password>@localhost:3306/<database>

(mysql.connector.errors.DatabaseError) 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (111)

or

mysql+mysqlconnector://<user>:<password>@127.0.0.1:3306/<database>

(mysql.connector.errors.DatabaseError) 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (111)

Fixed the error configuring the URI as:

mysql+mysqlconnector://<user>:<password>@192.X.X.X:3306/<database>

Inestimable answered 10/7, 2022 at 20:0 Comment(0)

P

1

If you are here with the same error message and you use Docker - try to use the name of the database service as a host.

services:
  app:
    blablabla
  db:
    blablabla

I mean, instead of:

127.0.0.1 or localhost

use:

db

Pun answered 18/9, 2021 at 18:19 Comment(0)

R

1

Quickest way, especially if you just want to do a sanity check or grab some data, is to use the container shell by running the following command: docker exec -it <container_name> sh. Then, you can run mysql -p to access MySQL shell.

Notes: One way for finding a container name is by running docker ps

Rackety answered 30/1 at 5:28 Comment(0)

R

0

You need to change the bind-address parameter to 127.0.0.1 in the MySQL configuration file (my.ini or my.cnf) or use the one that is defined there.

If that doesn't work you should check that the MySQL service is actually running.

Resurrect answered 4/11, 2009 at 12:41 Comment(4)

I have add the line "bind-address = 127.0.0.1" to the file my.cnf. But it doesn't work. – Torture 4/11, 2009 at 12:45

If this line is present, mysql will listen ONLY to the address listed. To enable remote access, you should REMOVE this line. Be aware of the security implication this has. – Wrongdoer 16/9, 2011 at 20:49

adding bind-address =127.0.0.1 in my.cnf disables remote connection to the DB. So even if it would work (which it isn't) - that's not a good solution. – Provocation 6/9, 2012 at 18:12

The bind-address should be 0.0.0.0 unless you don't want anyone else to connect. – Halfbound 9/2, 2016 at 2:13

R

0

In case you are running on a non-default port, you may try using --port=<port num> provided --skip-networking is not enabled.

Riddell answered 10/6, 2011 at 8:44 Comment(0)

B

0

I was also facing the same issue.

The following helped me fix the problem

Go to Control Panel → Administrative Tools → Services. Inside this you will most certainly see the MySQL service: right click and say start (force start).

Balloon answered 19/12, 2013 at 7:10 Comment(2)

On Windows, presumably? – Gracielagracile 30/12, 2021 at 23:57

yes @PeterMortensen – Balloon 27/2, 2023 at 5:36

H

0

I just have this problem... running in Windows 7 and WAMP server ... after reading this.

I found that Antivirus Firewall had caused the problem.

Hodge answered 26/12, 2013 at 12:27 Comment(2)

What is "Antivirus Firewall"? An actual product name? – Gracielagracile 31/12, 2021 at 0:6

The link is (effectively) broken (redirects to the generic sitepoint.com/community). – Gracielagracile 31/12, 2021 at 0:7

H

0

For Docker users - When trying to connect to the local SQL database server using mysql -u root -h 127.0.0.1 -p and your database is running in a Docker container, make sure the MySQL service is up and running (verify using docker ps and also check that you are in the right port as well). If the container is down you'll get connection error.

The best practice is to set the IP addresses in /etc/hosts on your machine:

127.0.0.1 db.local

And running it by mysql -u root -h db.local -p.

If all this is ok and working (or in case it was working and sudenly it stopped), you should check if your container is not out of space. In this case MySQL isn't able to authorize and create connection. The symptom is that port is active, everything looks great, but if you try to connect, the connection is either cancelled, or refused.

Hawfinch answered 24/9, 2019 at 11:3 Comment(0)

C

0

Check if it is open port 3306 (more here):

nmap -p3306 127.0.0.1

If you receive something like:

Starting Nmap 7.92 ( https://nmap.org ) at 2022-01-07 11:55 CET
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000082s latency).

PORT     STATE  SERVICE
3306/tcp closed mysql

Nmap done: 1 IP address (1 host up) scanned in 0.08 seconds

then open port 3306:

sudo iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

Or sudo ufw allow 3306 if you use UFW.

Check: netstat -lnp | grep mysql you should get something like this:

cp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      2048/mysqld
tcp6       0      0 :::33060                :::*                    LISTEN      2048/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     514961   2048/mysqld          /var/run/mysqld/mysqld.sock
unix  2      [ ACC ]     STREAM     LISTENING     514987   2048/mysqld          /var/run/mysqld/mysqlx.sock

Churl answered 26/12, 2019 at 23:37 Comment(1)

What do you mean by "Try don’t shut down"? Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Gracielagracile 31/12, 2021 at 0:23

S

0

If you have tried all of the above and it still did not work. Check firewall.

I was trying to connect from a windows machine to a MySql Server 5.7.32 installed on a VirtualBox machine with CentOs7. I have tried everything and it was not working, even though i could ping the machine i couldn't connect to the MySql Server.

On CentOs7 the commands to check the firewall are:

Step 1: Check the status of your firewall service:

systemctl status firewallid.service

Step 2: Disable the firewall service

systemctl stop firewallid.service

Seamount answered 3/11, 2020 at 5:59 Comment(0)

P

0

I had this problem when I tried to connect to the MySQL server in the Docker container

I fixed by following the steps below.

Go inside the container:

docker exec -it mysql bash

Run this command:

echo "bind-address           = 0.0.0.0" >> /etc/mysql/conf.d/mysql.cnf

Exit from the container

exit

Restart the container

docker restart mysql

Go inside the container

docker exec -it mysql bash

Connect to the MySQL server, and enter your password

mysql -u root -p

Pueblo answered 19/4, 2022 at 4:8 Comment(0)

J

0

In my case, with Centos Linux and MYSQL 8.0.26. I fixed it by opening the port from the firewall. You can run the below command if [3306] is the MySQL port and [public] is the active zone (default configuration)

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

Also, I looked for bind-address=127.0.0.1 but did not configure anywhere in the configuration./etc/my.cnf /etc/my.cnf.d/*

Jeep answered 29/7, 2022 at 11:45 Comment(0)

P

0

This happens when connecting to RDS on AWS when the database has run out of storage capacity. You have to increase the amount of storage capacity available to fix this.

Peptize answered 22/8, 2022 at 15:29 Comment(0)

C

-1

Make sure that, your password doesn't contain the '@' character. As an example, If your password is Jane@123, then the following error will be displayed.

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '123@localhost' ([Errno -2] Name or service not known)")

Consecration answered 10/7, 2022 at 9:26 Comment(0)

S

-3

I just restarted my MySQL server and the problem was solved.

On Windows, net stop MySQL, and then net start MySQl.

On Ubuntu (Linux): sudo service start mysql

Streamer answered 12/11, 2013 at 4:0 Comment(0)

R

-3

I changed the installation directory on re-install, and it worked.

Retroversion answered 24/7, 2014 at 22:53 Comment(0)

R

-4

Please make sure your MySQL server is running on localhost.

On Linux

To check if MySQL server is running:

sudo service mysql status

To run MySQL server:

sudo service mysql start

On Windows

To check if MySQL server is running:

net start

If MySQL is not in list, you have to start/run MySQL.

To run MySQL server:

net start mysql

Robedechambre answered 31/3, 2020 at 4:15 Comment(0)

Recommended topics

Hot tags