Connect one LAMP app to mysql database of another LAMP app

February 12, 2018 591 views
MySQL LAMP Stack Ubuntu 16.04
eskimo10
By:
eskimo10

I have 2 droplets, both with a LAMP stack. Both also have PHPMyAdmin installed, run a PHP application, and have a self-signed SSH certificate that auto redirects to https.

What I would like to do is connect to the MySQL database that's on Droplet 1 from the app on Droplet 2. I know you can do this by running a mysql server Droplet 1, but both need to run a full PHP app in this case.

Here is what I've already done and tried:

  • Enabled private networking on both droplets (https://www.digitalocean.com/community/tutorials/how-to-enable-digitalocean-private-networking-on-existing-droplets)
  • On Droplet 1 I've added port 3306 to the firewall with sudo ufw allow 3306/tcp and sudo service ufw restart
  • Both have the same firewall settings applied that are configured in the DO interface. I've added 'All TCP', 'All ports' from source 'Droplet 2'
  • Outbound rules on the firewall settings are 'ICMP', 'All TCP' and 'All UDP' from 'All ports' and 'All IPv4' and 'All IPv6'
  • I've created a MySQL user through PMA on Droplet 1 with hostname %

My PHP app connection settings
$db['remotedb'] = array(
'dsn' => '',
'hostname' => 'PRIVATE IP OF DROPLET HERE', // in the format xx.xx.xx.xx so not with https:// in front of it
'port' => 3306,
'username' => 'user',
'password' => 'password',
'database' => 'database',
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => FALSE,
'dbdebug' => FALSE,
'cacheon' => FALSE,
'cachedir' => '',
'charset' => 'utf8',
'dbcollat' => 'utf8generalci',
'swappre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'savequeries' => TRUE
);

Currently the app op Droplet 2 can not connect to the database that runs on Droplet 1. What am I missing?

A few questions:

  1. Do I need to do anything in the /etc/mysql/my.cnf file with bind-address?
  2. When creating the user on Droplet one through PMA, did I use the right settings?
  3. Any other setting that I'm missing?
1 comment
  • eskimo10 February 13, 2018

    I just remembered that I also ran mysql_secure_installation. If I remember correctly that disables remote access, not sure.

Log In to Comment
1 Answer
xMudrii February 13, 2018
Accepted Answer

By default, MySQL is bound to the loopback interface, so it's not reachable from others, e.g. you're private network.

You can try binding it to the private address, or if that doesn't work (0.0.0.0). Be careful with 0.0.0.0 as it will allow traffic from any interface!

About the user, % should be good enough. If you want to additionally secure, you try to use private address instead of %. % allows you to connect using that user from any IP address. If you're going to use only another Droplet, you can try to change it to the appropriate IP.

If that doesn't work, try to get some logs, as that can help you to debug it further.

Reply
  • eskimo10 February 13, 2018

    Indeed I had to add bind-address = private IP to my my.cnf file. In that file there was no [mysqld]which I had to add as well. Thanks!

    • xMudrii February 15, 2018

      There're several files and directories under that /etc/mysql directory as of Ubuntu 16.04 and the appropriate MySQL version. That should work as well, but you should be able to locate the file somewhere in that directory/subdirectory.

Have another answer? Share your knowledge.

Related Questions