Allowing remote application to make MySQL connection

Hi,

I have a Linode 1024 running Ubuntu 16.04 LTS. LAMP is successfully installed and we already have a production website working well.

I'm trying to make an existing MYSQL database (target_db) on this server available for remote connection by a separate, remote (trusted) PHP application.

I've created a new database user (targetuser) which has the correct privileges for targetdb.

Using Sequel Pro, I can already connect to targetdatabase using targetuser. However, this is using the 'SSH' option and key-pair authentication. I need to be able to connect using a standard connection (no SSH key-pair).

So far, I have taken the following steps:

• Temporarily changed the UFW firewall settings to "sudo ufw default allow incoming". (I'll tighten this up again later.)

• I also have the following explicit UFW rules (where 81.123.67.94 is my laptop's current IP address)

22 ALLOW Anywhere

443 ALLOW Anywhere

80/tcp ALLOW Anywhere

3306 ALLOW 81.123.67.94

22 (v6) ALLOW Anywhere (v6)

443 (v6) ALLOW Anywhere (v6)

80/tcp (v6) ALLOW Anywhere (v6)

• I've also edited /etc/alternatives/my.cnf (which is symlinked to /etc/mysql/mysql.cnf) so it looks like this:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
[mysqld]
bind-address    = 139.161.234.123

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

…and I restarted MySQL successfully.

When I try to connect via Sequel Pro, I get:

> Unable to connect to host 139.161.234.123, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Can't connect to MySQL server on '139.161.234.123' (61)

I then ran netstat -l which gave me the following output:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 localhost:smtp          *:*                     LISTEN
tcp        0      0 localhost:mysql         *:*                     LISTEN
tcp        0      0 localhost:submission    *:*                     LISTEN
tcp        0      0 *:ssh                   *:*                     LISTEN
tcp6       0      0 [::]:https              [::]:*                  LISTEN
tcp6       0      0 [::]:http               [::]:*                  LISTEN
getnameinfo failed
getnameinfo failed
getnameinfo failed
getnameinfo failed
getnameinfo failed
getnameinfo failed
Active UNIX domain sockets (only servers)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ACC ]     STREAM     LISTENING     2127878  /run/systemd/private
unix  2      [ ACC ]     STREAM     LISTENING     14105    /var/run/sendmail/mta/smcontrol
unix  2      [ ACC ]     STREAM     LISTENING     8006     /run/systemd/fsck.progress
unix  2      [ ACC ]     SEQPACKET  LISTENING     8009     /run/udev/control
unix  2      [ ACC ]     STREAM     LISTENING     2837069  /var/run/mysqld/mysqld.sock
unix  2      [ ACC ]     STREAM     LISTENING     8017     /run/systemd/journal/stdout
unix  2      [ ACC ]     STREAM     LISTENING     12683    /var/run/dbus/system_bus_socket
unix  2      [ ACC ]     STREAM     LISTENING     12686    /run/uuidd/request
unix  2      [ ACC ]     STREAM     LISTENING     14228    /var/run/fail2ban/fail2ban.sock
unix  2      [ ACC ]     STREAM     LISTENING     2836458  /run/user/1000/systemd/private

To my mind, because I have i) UFW/firewall set to default allow incoming traffic; and ii) I've configured the bind-address option in MySQL to the server's public IP, then the connection should work.

However, it looks from the netstat output as if mysql is still listening on localhost rather than on the public IP.

tcp        0      0 localhost:mysql         *:*                     LISTEN

Any help would be much appreciated!

PS I'll also just list some settings I have in /etc/ssh/sshd_config, just in case they're relevant:

PermitRootLogin no
PasswordAuthentication no
AddressFamily inet # so that SSH daemon listens only on IPv4.

Many thanks,

Laurence