CODEWITHSUNDEEP
mysqllinux
Mohammad Gharehyazie
Mohammad Gharehyazie

Reputation: 345

Yet another "Lost connection to mysql server at 'reading initial communication packet', system error: 0" issue

This seems like a duplicate post, but I have checked all the solutions posted in relevant posts and none of them worked for me. So allow me to state the problem more accurately.

I have a server, where MySQL is installed. I have a user X with password P.

If I connect to the server (ssh or something) and try to run MySQL locally (mysql --user=X --password==P) it logs in perfectly, and I have access to everything:

    mysql> show grants;
    ...
    +------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'X'@'%' IDENTIFIED BY |
    |PASSWORD 'somehash' WITH GRANT OPTION                 |
    +------------------------------------------------------+

Now if I go to the config file: (sudo vim /etc/mysql/my.cnf) I see the following:

bind-address = 0.0.0.0
port = 3306

Then I go to hosts allow file (sudo vim /etc/hosts.allow) I see:

mysqld: ALL

Then I go to hosts allow file (sudo vim /etc/hosts.deny): the file is empty (except for some comments).

Still, when I try to connect with MySQL workbench I have the problem. Here is how I do it. I go to add a new connection, I add the URL, username, pass, and port, and I click test connection and I get the message that is the title of this question. I tried with a random (non-existent) user pass combination and still I get the same response.

I tried commenting out bind-address too BTW. Also the server is generally accessible for other services like PostgreSQL and such.

Upvotes: 4

Views: 22345

Answers (3)

Fed C
Fed C

Reputation: 303

I had your same issue in MySql 15.1, and logging the /var/log/secure file I realised that each login produced errors like this:

sshd[2598444]: refused local port forward: originator  port 18204, target 127.0.0.1 port 3306

at this point i realised that in my /etc/ssh/sshd_config file the AllowTcpForwarding and GatewayPorts values were commented, so I set them as:

AllowTcpForwarding yes
GatewayPorts yes

At that point, i just ran:

sudo systemctl restart sshd

And the MySQL connection worked correctly.

Upvotes: 0

Haleemur Ali
Haleemur Ali

Reputation: 28313

not a solution to your server firewall issue but a workaround, as you are able to ssh into your database server:

You can try ssh remote port forwarding from your mysql server to your local machine, and then connect the mysql client to the local port. I use this method whenever I'm behind a firewall. As a bonus, data transmitted over this connection is also very secure.

For example, if you ssh'ed into the remote machine using 

ssh [email protected] -i ~/.ssh/hal.key

Then you could set up the port forwarding like this:

ssh -L 54321:127.0.0.1:3306 [email protected] -i ~/.ssh/hal.key -f -N -M -S ~/.ssh/tunnel_54321_remote_machine_mysql

Then you can connect to the database as if you were connecting to the database locally (using the commanline mysql client as example):

mysql -h 127.0.0.1 -P 54321 -u my_user -p my_database

This should then prompt for your password.

To close the tunnel:

ssh -S ~/.ssh/tunnel_54321_remote_machine_mysql [email protected] -i ~/.ssh/hal.key

I first learned about this method from the postgres docs.

Upvotes: 1

Trent Lloyd
Trent Lloyd

Reputation: 1892

This is more than likely a firewall issue.

Easiest way to debug that at first, is to try telnet to the server on port 3306 both locally, and from remote. MySQL will send the version string in plaintext that you can see inside telnet if you are being correctly connected.

If you do not get that string, then something such as a firewall is likely blocking the connection.

Upvotes: 0

Related Questions