Reputation: 26421

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

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());

Upvotes: 171

Answers (30)

Curtis Yallop

Reputation: 7319

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)

Upvotes: 0

Al Martins

Reputation: 436

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.

Upvotes: 0

Thomas Daugaard

Reputation: 1579

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'

Upvotes: 128

sNICkerssss

Reputation: 6430

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
```

Upvotes: 51

intekhab rizvi

Reputation: 499

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.

Upvotes: 49

S_C

Reputation: 167

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.

Upvotes: 0

CodeView

Reputation: 500

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',
        }
    }

Upvotes: 0

UberStreuneR

Reputation: 57

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.

Upvotes: 1

htlbydgod

Reputation: 340

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.

Upvotes: 0

Huseyin Yagli

Reputation: 10285

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

Upvotes: 1

balq

Reputation: 21

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.

Upvotes: 1

HakimDev

Reputation: 51

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

Upvotes: 3

Kamal Oberoi

Reputation: 165

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;

Upvotes: 1

Юрий Светлов

Reputation: 1750

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

Upvotes: 1

Moesio

Reputation: 3178

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!

Upvotes: 3

The Unknown Dev

Reputation: 3118

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.

Upvotes: 0

Nur Uddin

Reputation: 1840

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

Upvotes: 0

Rodniko

Reputation: 5124

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

Upvotes: 2

R Diaz

Reputation: 32

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.

Upvotes: 1

Sadee

Reputation: 3180

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

Upvotes: 0

Burges K

Reputation: 11

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)

Upvotes: 1

Tishan

Reputation: 141

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

Upvotes: 4

Belter

Reputation: 3807

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

Upvotes: 3

Aman Bansal

Reputation: 91

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.

Upvotes: 0

lewis4u

Reputation: 15037

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

Upvotes: 7

Reagan Ochora

Reputation: 1742

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!

Upvotes: 5

scott

Reputation: 1478

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.

Upvotes: 3

GunSky7

Reputation: 25

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

service mysqld start

Upvotes: 1

Jahanzeb Khan

Reputation: 440

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

Upvotes: 1

Abbas

Reputation: 560

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.

Upvotes: -1

Lost connection to MySQL server at &#39;reading initial communication packet&#39;, system error: 0

Answers (30)

Related Questions

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