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

Question

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?

Is mysql daemon running? Have yot got a firewall? Can you telnet to mysql the default port is 306 so - telnet localhost 3306 — mmmmmm, Nov 04 '09 at 12:42
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? — RMcLeod, Nov 04 '09 at 14:18
just to make sure, you are trying to connect to the db server locally? — John Kane, Jun 10 '11 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. — , Aug 22 '13 at 06:22
You might want to confirm that mysqld is running on the IP:port you're thinking of by running netstat -an | grep 3306 — Fabien Haddadi, Dec 11 '17 at 11:04

score 246 · Answer 1 · edited Dec 30 '21 at 23:51

246

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.

edited Dec 30 '21 at 23:51

Peter Mortensen

30,738
21
105
131

answered Apr 23 '13 at 08:21

Justin Vincent

2,694
2
13
11

8

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! – Ryan Jan 20 '14 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. – Vlad Schnakovszki Dec 25 '14 at 12:33
5

There is no `bind-address = 127.0.0.1` in `/etc/mysql/my.cnf` – RegarBoy Nov 25 '15 at 04:18
7

@developer, i found mine here: /etc/mysql/mysql.conf.d/mysqld.cnf – once Sep 07 '16 at 08:14
I changed the bind-address =
, but can't restart mysql.
– Avi Kehat Feb 22 '17 at 06: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 – flaky Feb 22 '17 at 19:01
2

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" . – Fabien Haddadi Dec 11 '17 at 11:09
I am using `windows 7` , what should i do? – Deepak Keynes Feb 06 '18 at 06:11
@justin, you saved my day. thanks. it works. though I had to restart mysql after this. But it worked. thanks. – Bhupinder Jul 03 '18 at 04: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! – Steven Tsao Jul 09 '18 at 03:46
for my case, I had to bind 0.0.0.0 – Ahmad Mousavi Sep 22 '19 at 12:25
1

@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`. – S3DEV Sep 24 '20 at 19:49

score 19 · Answer 2 · edited Dec 30 '21 at 23:42

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.

edited Dec 30 '21 at 23:42

Peter Mortensen

30,738
21
105
131

answered Dec 14 '12 at 19:59

grex_e

199
2
2

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. – Duke Sep 12 '13 at 00:08

score 10 · Answer 3 · edited Dec 30 '21 at 23:54

10

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

service iptables stop

edited Dec 30 '21 at 23:54

Peter Mortensen

30,738
21
105
131

answered Nov 08 '13 at 11:42

Bunyk

7,635
8
47
79

@ram Probably you are using some other firewall. Or your problem is not a firewall at all. Check documentation for your linux distro. – Bunyk Jan 22 '15 at 13:58
1

@ram try `service firewalld stop` – Maxim Mazurok Dec 14 '15 at 17:37
1

MySQL typically runs on port 3306, so add that to your firewall – kurdtpage Feb 21 '18 at 19:27
Thank you so much! I had to disable my firewall to get to work as well: `sudo ufw disable` – charlchad Oct 26 '20 at 20:55

score 10 · Answer 4 · edited Dec 31 '21 at 00:14

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

edited Dec 31 '21 at 00:14

Peter Mortensen

30,738
21
105
131

answered Apr 01 '16 at 19:30

Henry

333
1
3
10

in the terminal on mac and linux. I believe it is the same in windows CMD but I am not certain. – Henry Feb 07 '18 at 20:45
I had to start MySQL server using `sudo service mysqld start` in my AWS EC2 instance with MySQL Community Server. – harshainfo Oct 31 '19 at 01:11

score 6 · Answer 5 · edited Dec 31 '21 at 00:13

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.

edited Dec 31 '21 at 00:13

Peter Mortensen

30,738
21
105
131

answered May 24 '15 at 06:40

S.Roshanth

1,499
3
25
36

2

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. – Dejazmach Nov 24 '17 at 15:26

score 6 · Answer 6 · edited Dec 31 '21 at 00:16

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

edited Dec 31 '21 at 00:16

Peter Mortensen

30,738
21
105
131

answered May 24 '16 at 10:55

Yu Jiaao

4,444
5
44
57

where can i search the `my.cnf` . its not there in wamp server, in my laptop – Deepak Keynes Feb 06 '18 at 06:05
1

On windows, it calls ‘my.ini’ usually – Yu Jiaao Feb 06 '18 at 06:29

score 1 · Answer 7 · edited Dec 31 '21 at 00:37

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

edited Dec 31 '21 at 00:37

Peter Mortensen

30,738
21
105
131

answered Sep 18 '21 at 18:19

muinh

535
6
14

Banty · Answer 8 · 2022-07-10T21:08:51.030

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>

score 0 · Answer 9 · edited Dec 30 '21 at 23:26

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.

edited Dec 30 '21 at 23:26

Peter Mortensen

30,738
21
105
131

answered Nov 04 '09 at 12:41

nkr1pt

4,691
5
35
55

1

I have add the line "bind-address = 127.0.0.1" to the file my.cnf. But it doesn't work. – Charlie Epps Nov 04 '09 at 12:45
3

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. – webkraller Sep 16 '11 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. – Nir Alfasi Sep 06 '12 at 18:12
The bind-address should be 0.0.0.0 unless you don't want anyone *else* to connect. – user207421 Feb 09 '16 at 02:13

score 0 · Answer 10 · edited Dec 31 '21 at 00:02

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

edited Dec 31 '21 at 00:02

Peter Mortensen

30,738
21
105
131

answered Dec 19 '13 at 07:10

Aniruddha K.M

7,361
3
43
52

On Windows, presumably? – Peter Mortensen Dec 30 '21 at 23:57
yes @PeterMortensen – Aniruddha K.M Feb 27 '23 at 05:36

score 0 · Answer 11 · edited Dec 31 '21 at 00:05

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.

edited Dec 31 '21 at 00:05

Peter Mortensen

30,738
21
105
131

answered Dec 26 '13 at 12:27

Adhie_x

11
1

What is *"Antivirus Firewall"*? An actual product name? – Peter Mortensen Dec 31 '21 at 00:06
The link is (effectively) broken (redirects to the generic *https://www.sitepoint.com/community/*). – Peter Mortensen Dec 31 '21 at 00:07

score 0 · Answer 12 · edited Dec 31 '21 at 00:25

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.

C2RLOS · Answer 13 · 2022-01-07T11:14:48.923

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

What do you mean by *"Try don’t shut down"*? Please respond by [editing (changing) your answer](https://stackoverflow.com/posts/59494491/edit), not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). — Peter Mortensen, Dec 31 '21 at 00:23

score 0 · Answer 14 · edited Dec 30 '21 at 23:29

0

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

edited Dec 30 '21 at 23:29

Peter Mortensen

30,738
21
105
131

answered Jun 10 '11 at 08:44

KDAS

9
1

score 0 · Answer 15 · answered Nov 03 '20 at 05:59

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

ahmnouira · Answer 16 · 2022-04-19T04:36:28.867

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

Niyaz · Answer 17 · 2023-01-28T07:16:29.150

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/*

score 0 · Answer 18 · answered Aug 22 '22 at 15:29

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.

answered Aug 22 '22 at 15:29

katsu

604
6
7

score -1 · Answer 19 · answered Jul 10 '22 at 09:26

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

score -3 · Answer 20 · edited Dec 30 '21 at 23:57

-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

edited Dec 30 '21 at 23:57

Peter Mortensen

30,738
21
105
131

answered Nov 12 '13 at 04:00

jasim

268
1
5
16

score -3 · Answer 21 · edited Feb 09 '16 at 02:13

-3

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

edited Feb 09 '16 at 02:13

user207421

305,947
44
307
483

answered Jul 24 '14 at 22:53

Gursimran Pannu

1

score -4 · Answer 22 · edited Dec 31 '21 at 00:29

-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

edited Dec 31 '21 at 00:29

Peter Mortensen

30,738
21
105
131

answered Mar 31 '20 at 04:15

Safiqur Rhaman

38
4

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

22 Answers22

Linked