1

I have an app built on app.mydomain.com (Server 1) and a support ticketing system at support.mydomain.com (server 2). How can I establish a connection between both databases in my laravel app? Both are using Laravel Forge and Digital Ocean.

I read this post on SO which looked great but I am getting a "connection timed out error". I believe it has to do with Forge needing the SSH key file (id_rsa.pub) when connecting to a database? Source here

I tried adding this to database.php:

//Server/Site 1
'mysql' => array(
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'database'  => 'support',
    'username'  => 'user',
    'password'  => 'mysecretpassword',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
),

//Server/Site 2
'mysql2' => array(
    'driver' => 'mysql',
    'host' => '123.456.789.101',
    'port' => '3306',
    'database' => 'app',
    'username' => 'forge',
    'password' => 'mysecretpassword',
    'charset' => 'utf8',
    'collation' => 'utf8_general_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
),

And then adding this to my model:

protected $connection = 'mysql2';
Community
  • 1
  • 1
Derek
  • 4,747
  • 7
  • 44
  • 79

1 Answers1

9

When you provision your server with Forge, MySQL configuration restricts external connections for security reasons. Only application at the same server can access your database.

New answer

Since you're using forge it's even easier than I suggested before. If you provisioned both your servers with forge, go to forge -> networking -> server network and select can connect to checkbox and hit update.

forge server network configuration

Afterwards you need to update DB_HOST= of mysql2 connection with private network IP obtained from digitalocean.

Old answer - manual setup

Now, there are couple of things to be done in order to allow external connections in secure way:

  1. At site2 - edit /etc/mysql/my.cnf and udpate bind-address with your server IP

Since you are using digital ocean, if your servers are in the same datacenter you should be able to use private network ip address of your droplet. This is more secure solution, any connection between servers will not leave datacenter infrastructure. Remember to restart mysql.

  1. At site2 - add new firewall rule

Go to Forge -> Networking -> New Firewall Rule and add a new rule for port 3306 and IP address of your site1, again a private IP if possible.

  1. At site1 - create mysql user for remote connections

Connect with ssh to site1 open mysql console and add new user

CREATE USER 'some-username'@'site1_ip' IDENTIFIED BY 'some_password';
GRANT ALL PRIVILEGES on your_database.* TO 'some-username'@'site1_ip';
FLUSH PRIVILEGES;

For more info refer to this tutorial, it solves similar issue to yours https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql

Hope this helps!

lchachurski
  • 1,770
  • 16
  • 21
  • 1
    Your answer pointed me in the right direction and was very helpful. Turns out in Forge under Server > Network there is a checkbox that allows you to connect to other servers in the same network. I checked that and refreshed and all seems well now :) . thank you so much! – Derek Mar 28 '17 at 17:29
  • 1
    I did pretty much the same thing yesterday, you're welcome :) – lchachurski Mar 29 '17 at 09:48
  • So helpful!, thanks a lot for sharing this information with the community,everyday I learn more about Laravel Forge and I just love. – Luis Mata B. Oct 22 '18 at 23:35
  • Thanks, really appreciate it. Forge itself is a wicked solution, saves tons of time for me as well. – lchachurski Oct 25 '18 at 08:08