MySQL connectivity error

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

1. I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
2. I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.

3. Finally I have the following entered in MySQL WorkBench.

   • SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
   • SSH Username: sshuser
   • SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
   • MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
   • MySQL Server Port: 3306 (also default)
   • Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.

By default mysql server accept connections only from localhost, to change this behaviour you should change in mysql config the bind-address variable in /etc/mysql/my.cnf:

 bind-address=ipAddressOfYourDatabaseServer

than restart mysql server.

 sudo service mysql restart

The explain: http://www.howtogeek.com/howto/mysql/switch-mysql-to-listen-on-tcp/

After this i recommend you, that change your iptables, that your 222 port should be reachable only from your other VPS.

 iptables -A INPUT -s ! yourOtherVPSipAddress -p tcp -dport 222 -j DROP

The other thing: the mysql_ funcrions are deprecated, as the other commenter wrote. If you do not want to make a lot of work with your existing code, you should use mysqli. See the php manual, only a minimal work with sintax if you use mysqli as function library.