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

3
votes

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.

2
mysqllinux
same problem i face if i open phpmyadmin then workbench. I dont know whats the issue? – Samiul

2 Answers

1
votes

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.

0
votes

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.