Remote Mysql fail to connect - Tried everything I can think of.

June 11, 2019 629 views
MySQL DigitalOcean Cloud Firewalls Firewall Databases DNS Ubuntu 18.04

So I am going to walk you through everything I have done since I created this. Since this is a test before I run it for production.

ssh in, change root password
adduser (new user added)
usermod -aG sudo (new user)
ufw limit ssh/tcp
ufw limit (new ssh ip/tcp)
ufw allow 3306
edit /etc/ssh/sshd_config (change ssh port and disallow root login)
systemctl restart ssh
(disconnect and reconnect via port 22 to make sure ssh isn’t listening. Try new port and make sure root doesn’t allow login.)
Sign in via new credentials.
ufw enable
ufw status (verify 3306 is open)
apt update
apt upgrade
apt install mysql-server
mysql (create database)
mysql (create user -> create user “user”@“%” identified by “password”; )
mysql (grant all on database.* to “user”@“%”;)
mysql flush privileges;
edit /etc/mysql/mysql.conf.d/mysqld.cnf
set bind-address to database server ip (save & exit)
systemctl restart mysql
netstat -ln | grep 3306 (verify mysql is listening)
attempt to connect using https://www.rainbowspuppiessunshine.com/tools/dbtest/index.php connection tool.

I get an error (111 failed to connect)

I have also tried securing the connection via a few guides and still no luck. I tried an alternate connection tool and it said refused to connect.

so I disabled the firewall (since this is just a test server)

ufw disable
ufw status
(verified it was disabled)
tried the connection again, still nothing.
refused to connect and failed to connect

there is no cloud firewall unless it’s allocated by digital ocean.

I have tried this various times and on various droplets. If I use the mysql one clicks I can’t even change the bind address without getting a fail error when mysql reloads.

I am out of ideas here. Any help or ideas would be appreciated.

4 Answers
quentinpidcock June 19, 2019
Accepted Answer

I was unable to resolve this issue. Removing the droplet and closing ticket. Anyone that is curious all steeps I have taken are listed above. It is replicate-able by simply following those steeps.

Hello,

Have you tried changing the MySQL bind-address to 0.0.0.0? Note you need to restart MySQL after the changes in your my.cnf.

Yeah, sorry I tried setting it to 0.0.0.0 and just commenting the bind-address out (as per documentation that will make it listen on all ports of all connections) Still same issue.

Here is the netstat info https://www.dropbox.com/s/ngmmqmkyobbtyl9/putty_2019-06-11_08-29-06.png?dl=0

(currently I have the bind-address commented out.)

https://www.dropbox.com/s/a5tjgqz20vzxhd5/putty_2019-06-11_08-31-01.png?dl=0

Looks like it’s a security error. Currently I am not using any certs but I didn’t tell it to require them either. I will will secure it and try to connect again then check the log and post results here.

  • Sorry, it is taking me a bit. I can’t for the life of me remember the command for adding the certs. I was thinking of mysqlsecureinstallation.

    • So I am also able to ssh into the db via local and remote machines using mysql -h <server ip> -u <user> -p <database name>

      Then I am prompted for the password and allowed in.

    • update:

      Every time I try to connect I get this in the syslog & nothing in the mysql error log.

      Jun 12 03:21:34 ubuntu-s-1vcpu-1gb-nyc3-01 kernel: [66801.699572] [UFW BLOCK] IN=eth0 OUT= MAC=2a:84:f6:3c:0a:88:c0:42:d0:39:2c:30:08:00 SRC=74.82.47.47 DST=165.22.36.26 LEN=32 TOS=0x00 PREC=0x00 TTL=57 ID=13926 DF PROTO=UDP SPT=21665 DPT=10001 LEN=12
      Jun 12 03:21:35 ubuntu-s-1vcpu-1gb-nyc3-01 kernel: [66802.446521] [UFW BLOCK] IN=eth0 OUT= MAC=2a:84:f6:3c:0a:88:c0:42:d0:39:2c:30:08:00 SRC=58.221.47.19 DST=165.22.36.26 LEN=40 TOS=0x00 PREC=0x00 TTL=112 ID=256 PROTO=TCP SPT=12047 DPT=60001 WINDOW=16384 RES=0x00 SYN URGP=0
      Jun 12 03:22:25 ubuntu-s-1vcpu-1gb-nyc3-01 kernel: [66852.094683] [UFW BLOCK] IN=eth0 OUT= MAC=2a:84:f6:3c:0a:88:64:c3:d6:0b:ef:f0:08:00 SRC=85.93.20.62 DST=165.22.36.26 LEN=40 TOS=0x00 PREC=0x00 TTL=243 ID=25848 PROTO=TCP SPT=56574 DPT=3394 WINDOW=1024 RES=0x00 SYN URGP=0
      I disabled ufw and now I get nothing in the logs and the connection still fails.

      • Hello,

        I’ve tested that and I can confirm that I can connect to the server on port 3306 via telnet and I’m getting a response from MySQL so this is not a firewall issue I believe.

        Are you connecting with the root user? If so this would not work as you’ve ran the mysqlsecureinstallation command.

        Can you also run this command via MySQL and make sure that the user that you are using is allowed to authenticate via the IP that you are connecting from?

        SELECT user,host FROM mysql.user;
        
        • Not trying to connect as root and yes, it has correct permissions. I changed it to % so now it works everywhere. I know % is less secure but I have no concern for security on this server as it is simply a test just to get it working at which point I will rebuild and use as needed.

        • Not sure how you were able to telnet the system. The only one I was able to telnet was my custom ssh port.

      • Hello,

        I’m just curious if you’ve managed to get this working at the end and if so what the problem was?

        Thanks!

        • Nope, still not working. I have about just given up on it. I have the same issue every time. I have tried the one click droplets, created the droplets myself and none of them work.

        • Thank you for your interest.

Have another answer? Share your knowledge.