Allowing remote application to make MySQL connection
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 184.108.40.206 is my laptop's current IP address)
22 ALLOW Anywhere
443 ALLOW Anywhere
80/tcp ALLOW Anywhere
3306 ALLOW 220.127.116.11
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 = 18.104.22.168 !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 22.214.171.124, 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 '126.96.36.199' (61)
I then ran
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.
I thought the only mysql configuration file in use was /etc/alternatives/my.cnf (symlinked to /etc/mysql/mysql.cnf).
However, I worked out that there were more in use, located in the /etc/mysql/conf.d/ and /etc/mysql/mysql.conf.d/ directories. One of the deeper mysqld.cnf files had the following line:
bind-address = 127.0.0.1
…which was locking mysql down to localhost only. Once I commented out that line, the connection worked.
So, in case it helps anyone in future, the lesson is to keep in mind is that multiple mysql configuration files may be in use simultaneously.
Thank you so much for posting this fix @laurencegreenhill! I had pretty much given up on getting this to work--you made my day.