Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Question

I am getting error:

"Lost connection to MySQL server at 'reading initial communication packet, system error: 0"

while I am going to connect my db.

If I am using localhost everything is working fine. But when I am using my live IP address like below, it's getting error:

mysql_connect("202.131.xxx.106:xxxx", "xxxx", "xxxxx") or die(mysql_error());

This error will appear if you try to connect to a postgres database using a mysql tool. — Juuso Nykänen, Apr 29 '22 at 12:21

score 127 · Accepted Answer · edited Dec 24 '22 at 21:06

127

Someone here suggests that it might be a firewall problem:

I have just had this problem and found it was my firewall. I use PCTools Firewall Plus and it wasn't allowing full access to MySQL. Once I changed that it was fine.

Could that be it?

Also, someone here suggests that it might be because the MySQL server is bound to the loop-back IP (127.0.0.1 / localhost) which effectively cuts you off from connecting from "outside".

If this is the case, you need to upload the script to the webserver (which is probably also running the MySQL server) and keep your server host as 'localhost'

edited Dec 24 '22 at 21:06

starball

20,030
7
43
238

answered Apr 22 '11 at 14:07

Thomas Daugaard

1,569
1
15
14

10

what do you mean localhost? I am also facing the same problem and using localhost only also. Application is on the same machine as the database. What did you mean localhost? – oneofakind Apr 06 '15 at 01:56
7

@oneofakind He means to connect to 'localhost" instead of '127.0.0.1' – Alexander Holsgrove Oct 30 '15 at 11:04
2

I had the same problem on OSX with mamp pro. I fixed it by disabling/re-enabling the "allow network access to mysql" checkbox on the mamp pro mysql tab. – Ousmane Dec 27 '17 at 15:33
Another reason for this error would be using a MySQL client to connect to a non MySQL db (like PostgreSQL). – roychri Dec 21 '22 at 19:15

score 49 · Answer 2 · edited Dec 19 '22 at 16:47

49

Allow remote connect to MySQL.
Edit file:

>sudo nano /etc/mysql/my.cnf

Comment line:

#bind-address        = 127.0.0.1

Restart MySQL:

>sudo service mysql restart

Create user for remote connection.

>mysql -uroot -p

CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev_password';
CREATE USER 'developer'@'%' IDENTIFIED BY 'dev_password';

GRANT ALL ON *.* TO 'developer'@'localhost';
GRANT ALL ON *.* TO 'developer'@'%';

In my case I need to connect remotely from Windows to VirtualBox machine with Ubuntu. So I need to allow port 3306 in iptables:
```
>iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT
```

edited Dec 19 '22 at 16:47

7ochem

2,183
1
34
42

answered Jun 28 '16 at 08:30

sNICkerssss

6,312
1
24
16

1

For me I forget verify bind-address on my.cnf. Thanks. – abkrim Sep 20 '16 at 08:17
1

This totally helped. Thank you! – osehgol Dec 21 '17 at 16:07
1

Worked perfectly, but i was using workbench on windows so didn't do step 3. but it worked. Thanks! – RohitAneja May 07 '18 at 07:17
Although last command did not work for me, creating new user with your way solved my problem. I can access mysql on raspberry pi from my laptop. Thanks! – Eyyüp Alkış Mar 21 '20 at 20:30
1

remember to `FLUSH PRIVILEGES` after created a new user. – Yew Hong Tat Apr 09 '20 at 07:03
Thank you, this worked for me, but as pointed out, you need an FLUSH PRIVILEGES; after the GRANT... part – omega1 Feb 15 '21 at 11:55

score 49 · Answer 3 · edited Dec 19 '22 at 16:46

49

Open mysql configuration file named my.cnf and try to find "bind-address", here replace the setting (127.0.0.1 OR localhost) with your live server ip (the ip you are using in mysql_connect function)

This will solve the problem definitely.

edited Dec 19 '22 at 16:46

7ochem

2,183
1
34
42

answered Jul 25 '11 at 14:32

intekhab rizvi

499
4
2

2

Make sure you use bind-address = 127.0.0.1 or localhost when using ssh tunnel. Had this problem with bitnami, where bind-address was machine ip. – z2z Jan 03 '19 at 11:25
1

The same problem and solution for Debian 10 + MySQL standalone dedicated server. – Vilq Jan 27 '20 at 17:04
It should be mentioned that it might be generally preferrable to bind the mysql service to `127.0.0.1` where applicable so direct access from other hosts is not possible. This prevents remote brute force attacks and does not expose possible security issues to the network. If your application is located on the same machine (which is a very common hosting setup), use `127.0.0.1` resp. `localhost` as mysql host and it will work the same way as binding the service to your external interface and using that as mysql host. If your application is located elsewhere use an internal network if possible. – David Apr 20 '20 at 19:16
Trust in this guy, I have a team of people here that were doubtful, but this does *definitely solve the problem definitely*. – John Hunt Feb 10 '22 at 17:16

score 17 · Answer 4 · edited Oct 03 '12 at 19:57

17

Had this problem when setting up a new slave server. Found it was the slave server IP address was missing from the master server /etc/hosts.allow file. Added the IP address and it let me connect to the master server.

Note that I use hosts.allow and hosts.deny to control access.

edited Oct 03 '12 at 19:57

Marko

20,385
13
48
64

answered Aug 21 '12 at 20:08

Mark

171
1
2

my problem with connector/c++. now, if i could only find what setting needs to be in `allow` when `deny` has `ALL: ALL`... – Jul 08 '13 at 23:30
this really saved my live, I was having issues connecting to mysql from workbench, I needed to add "ALL: 127.0.0.1" to /etc/hosts.allow file and it started to work – Tomáš Tibenský May 30 '14 at 06:58
In my case, /etc/hosts had a bad (old) entry for the host IP. When issued a new IP, it came under the old one and was seemingly ignored. Deleting the old IP healed the error. – David Ramirez Nov 12 '15 at 22:44

score 9 · Answer 5 · answered Mar 07 '14 at 20:54

9

I had this problem and it ended up being the prior sys admin changed the port MySQL was running on. MySQL Workbench was trying to connect to the default 3306 but the server was running on 20300.

answered Mar 07 '14 at 20:54

user3347295

91
1
1

2

Good thing for this answer. Turns out, i did this to myself. – cautionbug Dec 13 '14 at 03:12

ClearCrescendo · Answer 6 · 2016-01-13T02:15:34.667

7

The error means that it didn't receive a response from the port it expected to find the server on. The causes range from contacting the wrong machine (For one of a number of reasons) to the server not being on the expected port.

Check which port your server is bound to in /etc/mysql/my.cnf. Does that correspond to what is in your connect statement. If they match then try connecting with mysql from the server itself and from the command line of the machine where you are running the client. If it works form one place and not another then you may have a firewall / router configuration issue.

edited Jan 13 '16 at 02:15

answered Jul 06 '14 at 23:02

ClearCrescendo

1,145
11
22

I don't think this is correct, if there's nothing listening on that port then you'll get the response `ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)`, not the `Lost connection to MySQL server` message. – Ken Williams Oct 12 '19 at 02:12

score 6 · Answer 7 · answered Jan 17 '19 at 15:16

6

One more reason...

I ran into an Ubuntu server where everything was customized and could not connect because of that same error.

This setting was inside /etc/ssh/sshd_config

PermitTunnel no

After turning into

PermitTunnel yes

I was able to connect remotely to my MySQL DB

answered Jan 17 '19 at 15:16

lewis4u

14,256
18
107
148

I don't see this line in my file. PermitPassword is there. – Nithur Sep 16 '22 at 08:27
Default is `PermitTunnel yes` and it's not necessary to add the line... but in my case it was `no` so I just changed the no to yes... I could've also delete the whole line... – lewis4u Sep 16 '22 at 08:35
ok, I am now facing system error 104 while connecting to a remote MySQL server and couldn't find any useful solutions. – Nithur Sep 16 '22 at 08:46
that has to do something with port... – lewis4u Sep 16 '22 at 08:48
can you please explain it a bit?? – Nithur Sep 16 '22 at 08:59
Maybe this would help https://stackoverflow.com/questions/3578147/mysql-error-2013-lost-connection-to-mysql-server-at-reading-initial-communic – lewis4u Sep 16 '22 at 09:04

score 5 · Answer 8 · answered Jul 31 '13 at 19:01

5

The problem on my case was MySQL being bind only to the lo on linux. in order to solve the problem i have edited the my.cnf (found at /etc/mysql/my.cnf) removing the line bind-address=127.0.0.1

this allows mysql to bind to any network interface

answered Jul 31 '13 at 19:01

Renato Mendes

68
1
7

score 5 · Answer 9 · answered Jan 23 '15 at 23:53

5

I just set up mysql on a windows box. I got the OP's error when trying to connect with the Navicat MySql client on the same box. I had to specify 127.0.0.1 as the host, and that got it.

localhost, or the servers actual ip address both did not work.

answered Jan 23 '15 at 23:53

Paul B

51
1
1

score 5 · Answer 10 · answered Jun 07 '15 at 23:07

This error occurred to me while trying to connect to the Google Cloud SQL using MySQL Workbench 6.3.

After a little research I found that my IP address has been changed by the internet provider and he was not allowed in the Cloud SQL.

I authorized it and went back to work.

score 4 · Answer 11 · edited Mar 26 '19 at 10:36

I ran into this exact same error when connecting from MySQL workbench. Here's how I fixed it. My /etc/my.cnf configuration file had the bind-address value set to the server's IP address. This had to be done to setup replication. Anyway, I solved it by doing two things:

create a user that can be used to connect from the bind address in the my.cnf file

e.g.

CREATE USER 'username'@'bind-address' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON schemaname.* TO 'username'@'bind-address';
FLUSH PRIVILEGES;

change the MySQL hostname value in the connection details in MySQL workbench to match the bind-address

Thank you very much, we had also MySQL replication and the bind-address was a local one: 10.0.0.x. I was struggling for days for this solution. — iFadi, Feb 05 '20 at 09:42

score 4 · Answer 12 · answered Jun 22 '18 at 18:18

I faced the same problem. I checked and tried to set AllowTcpForwarding Yes but it was missing in my sshd_config so no help.I didn't change sshd_config or my.cnf. Make sure the ssh hostname is NOT the same with the mysql hostname(use localhost).

In workbench, choose + to add new connection and set the following:

connection method: standard TCP/IP over SSH
SSH Hostname: 192.168.0.50:22 (replace remote SSH server IP and port(optional))
SSH Username: sshuser
You can set password or add at the prompt
MYSQL Hostname: localhost or 127.0.0.1
MYSQL Server port:3306
You can set password or add at the prompt

Test connection. It should be successful then hit OK.Viola!

Thank you so much, the mysql host name was my hang up. It makes perfect sense now though. — Nick, Aug 24 '22 at 21:47

score 3 · Answer 13 · answered Oct 23 '13 at 22:39

3

The problem for me was that DNS queries were blocked by the FW within the subnet. The solution was to disable DNS lookups within MySQL.

answered Oct 23 '13 at 22:39

Bill Grady

41
1

I suspect that DNS is to blame in my case, as well. – Zenexer Apr 09 '15 at 19:22

score 3 · Answer 14 · answered Mar 13 '17 at 12:37

The problem was quite stupid for me.

I used to get the same issue on AWS EC2 Ubuntu machine (MariaDB is installed locally for the time being), so I tried to make SSH tunneling, and had the same issue. So I tried to ssh tunnel over terminal:

ssh -L13306:127.0.0.1:3306 root@ip.address -i my/private/key.pem

And it told me this:

Please login as the user "ubuntu" rather than the user "root".

I changed ssh user from root to ubuntu, just like my ssh config, and it connected just fine.

So check your SSH connecting user.

I oversaw this, so this too half an hour of my time, so I hope this will be useful for you.

score 3 · Answer 15 · answered Nov 11 '17 at 07:06

3

For me the config file was found "/etc/mysql/mysql.conf.d/mysqld.cnf" commenting out bind address did the trick.

As we can see here: Instead of skip-networking the default is now to listen only on localhost which is more compatible and is not less secure.

answered Nov 11 '17 at 07:06

scott

1,531
2
16
29

For my biggest surprise, this one worked under Debian 10. I gave a chance for it and WORKS! Edited mysql config `sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf`and instead of `bind-address = 111.112.113.114` I used `bind-address = 127.0.0.1`. The SSH tunnel settings are set as described on https://hostpresto.com/community/tutorials/how-to-connect-to-a-remote-mysql-server-via-an-ssh-tunnel/ Good job! Thanks! – klor Jul 17 '19 at 18:07

score 3 · Answer 16 · answered Mar 14 '19 at 12:10

3

I am trying to connect my db docker container on Ubuntu 18.04, same problem.

First check your device by run nmcli dev to check if device docker0 is connected.

If it is not connected, try to restart docker service:

sudo service docker restart

answered Mar 14 '19 at 12:10

Belter

3,573
5
42
58

score 3 · Answer 17 · answered Sep 30 '20 at 12:38

I tried make a telnet over remote server on port 3306. The error message is clear

Host 'x.x.x.x' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.

As root at server mysqladmin flush-hosts worked at all!

score 3 · Answer 18 · edited Jan 30 '21 at 12:09

3

I had the same error when using localhost. I restarted the MySQL service and it worked fine.

edited Jan 30 '21 at 12:09

geertjanvdk

3,440
24
26

answered Jan 30 '21 at 09:47

The Algerianman

41
6

1

Apparently in my case, it took a lot of time for the image to initialize the database, and that's why I couldn't connect for quite a while. – Param Siddharth Aug 22 '21 at 12:10

score 2 · Answer 19 · answered Jan 14 '15 at 13:36

2

in my case, I had ALL: ALL in hosts.deny. Changing this to ALL: PARANOID solved my problem when connecting over ssh

answered Jan 14 '15 at 13:36

domoarigato

2,802
4
24
41

score 2 · Answer 20 · answered Mar 14 '17 at 16:35

2

Ran into this same issue, Bind Address back and forth to no avail. Solution for me was flushing privileges.

mysql> FLUSH PRIVILEGES;

answered Mar 14 '17 at 16:35

Marko Bajlovic

323
5
12

score 2 · Answer 21 · edited Apr 07 '20 at 22:09

2

Firewalld blocks the IP address. so to give access, use these commands:

firewall-cmd --permanent --zone=trusted --add-source=YOUR_IP/32

firewall-cmd --permanent --zone=trusted --add-port=3306/tcp

firewall-cmd --reload

edited Apr 07 '20 at 22:09

AbsoluteƵERØ

7,816
2
24
35

answered Aug 27 '17 at 10:13

Rodniko

4,926
20
69
93

Jahanzeb Khan · Answer 22 · 2017-08-20T02:46:13.593

1

For me setting bind-address = 0.0.0.0 in mysql/my.cnf worked. It basically listens to all addresses (but still one port) then.

And don't forget restart your server: systemctl restart mysql

edited Aug 20 '17 at 02:46

answered Jan 04 '15 at 20:19

Jahanzeb Khan

440
4
17

6

All addresses/interfaces, but only a single port. – Zenexer Apr 09 '15 at 19:23

score 1 · Answer 23 · edited Dec 10 '20 at 22:02

1

I have done below 3 steps then working for me.

bind-address = "YOUR MACHINE IP" in my.cnf file at /etc/my.cnf
Restart service by command : service mysql restart
GRANT ALL PRIVILEGES ON yourDB.* TO 'username'@'YOUR_APPLICATION_IP' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;

edited Dec 10 '20 at 22:02

JoSSte

2,953
6
34
54

answered Aug 13 '17 at 10:14

Kamal Oberoi

165
1
1
9

GunSky7 · Answer 24 · 2017-09-29T02:19:35.823

1

I just had the same problem, but in my case I solved it with

service mysqld start

edited Sep 29 '17 at 02:19

answered Sep 29 '17 at 02:14

GunSky7

25
8

score 1 · Answer 25 · answered Jan 25 '20 at 14:46

In my case it was the university wifi blocking port 3306. I was able to connect by using a mobile hotspot.

Change to a mobile hotspot or another network, and if it works there, then you know that original network is blocking port 3306. If you get the same error on more than 1 network, then you know it's specific to your machine.

Юрий Светлов · Answer 26 · 2020-10-17T02:17:38.980

1

I had port 3306 in Docker container but in Dockerfile it was 33060. I edited the port in Docker container to 33060
Must have been added to the Dockerfile

ENV MYSQL_ROOT_HOST 172.17.0.1

edited Oct 17 '20 at 02:17

answered Oct 17 '20 at 01:53

Юрий Светлов

1,622
2
23
27

score 1 · Answer 27 · answered Feb 20 '21 at 16:54

I had the same issue installing MySQL docker image then trying to connect from WSL2 MySQL client.

As it was stated in the accepted answer that it should be a firewall issue, in my case this error was caused due to not allowing docker for windows to communicate to private network.

I changed the settings on "Firewall & network protection", "allow an app through firewall", "change settings" (need administrator rights) and allowed "Docker desktop backend" to connect to private network.

score 1 · Answer 28 · answered Sep 22 '21 at 17:19

I had the same error on my Mac with a local MySQL installation. The problem was that the number files that MySQL was opening was too high for MacOS.

To see if you have the same problem you can run this command and look for File Descriptor errors:

tail -200 /usr/local/var/mysql/$(whoami).err | grep "Warning"

I added this line to my.cnf file and the problem was fixed:

table_open_cache = 200

score 1 · Answer 29 · answered Mar 28 '22 at 06:07

Had the same problem, what worked for me was:

Go to Windows Firewall where you allow applications.
mysql probably won't be in the list, so you need to add it, its path is typically C:/Program Files (x86)/MySQL/bin/mysql
Mark both private and public networks, apply.

score 0 · Answer 30 · edited Jul 30 '16 at 23:25

0

When connecting to Mysql remotely, I got the error. I had this warning in /var/log/mysqld.log:

[Warning] IP address 'X.X.X.X' could not be resolved: Temporary failure in name resolution

I just added this line to /etc/hosts file:

X.X.X.X some_name

Problem solved! Not using skip-name-resolve caused some errors in my local app when connecting to MySQL.

edited Jul 30 '16 at 23:25

Laurel

5,965
14
31
57

answered May 21 '16 at 08:00

Amin Sh

2,684
2
27
42

score 0 · Answer 31 · answered Sep 07 '16 at 12:50

0

I had identical problem. To fix it I just changed host from localhost:3306 to just localhost. So error may acour when You sepcify unproper port for connection. It's better to leave it default.

answered Sep 07 '16 at 12:50

Barto

469
1
6
15

2

This is likely because mysql doesn't accept the port as part of the hostname, instead you need to use the argument `-P 3306` – Clay H Jun 27 '17 at 13:48

score 0 · Answer 32 · answered Feb 11 '19 at 11:06

If bind-address is not present in your configuration file and mysql is hosted on AWS instance, please check your security group. In ideal conditions, the inbound rules should accept all connection from port 3306 and outbound rule should respond back to all valid IPs.

score 0 · Answer 33 · edited Aug 02 '19 at 07:33

I had a similar error (connecting to MYSQL on aws via MYSql Workbench). I used to connect fine before and all of a sudden it stopped working and just wouldn't work again). My connection was via SSH protected by keyfile.

Turns out I was timing out. So I increased the SQL connection timeout to 30 secs (from default 10) and was good to go again. things to try (if you're in a similar setup)

Can you ssh directly from terminal to the server (detects issues with key file permissions etc)?
Can you then through terminal connect to MySQL with the same user/pwd using something like mysql -u [username] -p [database]? This will check for user rights issues etc.
if both of those work then your parameters are not the problem and maybe same timeout issue like me (except it never said timeout error, but rather asked to check for permissions etc)

score 0 · Answer 34 · answered Oct 22 '19 at 16:07

Limited disk space can cause to this error.

Check your disk space

$ df -h

Try to increase the space if there are 100% used disks.

In my case: I have Vagrant (8.0.1) box (Ubuntu 16.04) My mysql disk capacity was 10GB, I increased it to 20GB

$ sudo lvextend -L20G -r /dev/mapper/homestead--vg-mysql--master

Then restart mysql

$ sudo service mysql restart

score 0 · Answer 35 · answered Jul 30 '20 at 01:39

0

If you face this erorr connecting from remote, go to remote mysql option in cpanel and then add % in Host (% wildcard is allowed) .

answered Jul 30 '20 at 01:39

Nur Uddin

1,798
1
28
38

score 0 · Answer 36 · answered Aug 18 '20 at 13:17

I had this problem connecting to my MySQL server via a proxy server. In my case it was working fine the week prior, and I didn't make any changes to my connection or server settings.

On a hunch, I decided to SSH into the proxy server to see if it was still working, and as soon as I did I was prompted to update my UNIX password, because it had expired. Resetting the password allowed me to connect again via the proxy.

score 0 · Answer 37 · answered Nov 09 '21 at 22:15

I gets this error when I use docker in M1: the problem for me is that the docker container exits once it is launched. When I use docker network ls and it does not show the container in the network. Thus, rebuilding a container that is active solves my problem.

score 0 · Answer 38 · answered Sep 06 '22 at 20:37

I met this error in the past, and the reason was a bit trivial, the main reason is that I had used Postger before I used mysql, and the postage port was different from the port of mysql. I hope that they can take advantage of the error that happened to me before using any other solution, I must check the port

'PORT': '3306',

  DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.mysql',
            'NAME': <Database_name>,
            'USER':'root',
            'PASSWORD':'',
            'HOST':'localhost',
            'PORT': '3306',
        }
    }

score 0 · Answer 39 · answered Dec 02 '22 at 06:05

0

What worked for me was the following:

Opened my my.cnf file and added

innodb_file_per_table = OFF

And, here are the advantages and disadvantages of disabling this file-per-table configuration.

answered Dec 02 '22 at 06:05

Salo Charabati

147
1
2
12

Please add the relevant aspects of the external link directly in this answer. – Ingo Steinke Dec 05 '22 at 09:59

score 0 · Answer 40 · answered Mar 21 '23 at 15:36

0

It was a temporary issue in my case. I use a external cloud provider that may have closed the door for a few minutes. It could well have been a quick internet connection issue. It is worth repeating after 5-10 min.

answered Mar 21 '23 at 15:36

Al Martins

431
5
13

score 0 · Answer 41 · answered May 28 '23 at 05:23

I was using mydumper to connect and my problem was I specified --ssl only my local mysql server isn't set up for that. Removing it fixed the problem.

My specific error (from the log) was mydumper Lost connection to MySQL server at 'reading initial communication packet', system error: 54.

Connecting via mysql command line with the same user/pass/host/tcp-protocol worked fine. (used root user)

score -1 · Answer 42 · answered Jun 06 '17 at 05:31

-1

Database directory read-write permission also a problem i found. Just make sure your application is able to rw files on db location. Try chmod 777 for testing.

answered Jun 06 '17 at 05:31

Abbas

552
5
8

Lost connection to MySQL server at 'reading initial communication packet', system error: 0

42 Answers42

Linked

Related