Search code examples
phpmysqlsqllaravellumen

Laravel (Lumen) connect to database on another server on same network

I've got a Laravel project on a server, and a Laravel Lumen 8 project on another server, they're both virtual machines and are clones of one another so have the same hardware and OS.

I have a domain, which for the purposes of this Stackoverflow we'll call foo.com, it goes through Cloudflare, both servers are ipv6 servers and when pinging each other via ssh they can see each other just fine.

The problem I have is with connecting to the MySQL (Maria DB) database from my Lumen project on the other server.

I've tried using:

  • The domain of the server where the DB exists
  • The ipv6 domain
  • The VM's local ipv4 address since both servers exist on the same network this is how they can see each other

I'm testing the connection using Tinker and running:

DB::connection()->getPdo();

And I'm unable to connect for some reason, these are the combinations and errors I've encountered and would like to know what I'm missing:

Attempt 1

DB_CONNECTION=mysql
DB_HOST=local ipv4
DB_PORT=3306
DB_DATABASE=my_db
DB_USERNAME=user
DB_PASSWORD=pass

PDOException with message 'SQLSTATE[HY000] [2002] No route to host

Attempt 2

DB_CONNECTION=mysql
DB_HOST=ipv6 address
DB_PORT=3306
DB_DATABASE=my_db
DB_USERNAME=user
DB_PASSWORD=pass

PDOException with message 'SQLSTATE[HY000] [2002] Invalid argument'

Attempt 3

DB_CONNECTION=mysql
DB_HOST=api.foo.com
DB_PORT=3306
DB_DATABASE=my_db
DB_USERNAME=user
DB_PASSWORD=pass

PDOException with message 'SQLSTATE[HY000] [2002] Connection timed out'

How can I get my Lumen project to connect to my Laravel database on another server, I'm pretty sure I've tried every combination here.

Solution

  • To connect MySQL Database on separate server, We have to check the followings:

    1. Two servers can reach via ping or ssh.
    2. MySQL can listen on IP of the dedicated VM or server.
    3. Laravel or Lumen can access the IP address of the server.

    In this answer, lets assume the network is 192.168.1.0/24. Lumen Server is Server A with 192.168.1.1. MySQL server is is Server B with 192.168.1.2.

    In My configuration, I check Two servers can reach via ping using:

    ping -c 4 192.168.1.2

    the server must respond like.

    PING 192.168.1.2 (192.168.1.2) 56(84) bytes of data.
64 bytes from 192.168.1.2: icmp_seq=1 ttl=64 time=0.399 ms
64 bytes from 192.168.1.2: icmp_seq=2 ttl=64 time=0.412 ms
64 bytes from 192.168.1.2: icmp_seq=3 ttl=64 time=0.352 ms
64 bytes from 192.168.1.2: icmp_seq=4 ttl=64 time=0.409 ms

--- 192.168.1.2 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3057ms
rtt min/avg/max/mdev = 0.352/0.393/0.412/0.024 ms

    For IPv6, use ping 6

    ping6 -c 4 abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd

    Result similar to IPv4,

    PING abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd(abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd) 56 data bytes
64 bytes from abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd: icmp_seq=1 ttl=61 time=0.421 ms
64 bytes from abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd: icmp_seq=2 ttl=61 time=0.333 ms
64 bytes from abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd: icmp_seq=3 ttl=61 time=0.388 ms
64 bytes from abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd: icmp_seq=4 ttl=61 time=0.324 ms

--- 2001:19f0:5:241:a315:602c:64ce:d6c1 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4096ms
rtt min/avg/max/mdev = 0.324/0.368/0.421/0.035 ms

    if the result is other than this, please make sure you have connectivity or the same network. Numbers will be changed according to real result. If failed, try ssh by using:

    ssh user@192.168.1.2
ssh user@[abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd]

    If your server B is not accessible both via ssh or ping, there is some problem with your server connectivity.

    1. If these twos can ping, you can make sure your MySQL Server is listening to IP address of the server. You can check using:

    #IPv4

    nmap -sT -O 192.168.1.2

    #IPv6

    nmap -sT -O -6 abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd

    The Result will show like:

    Starting Nmap 7.92 ( https://nmap.org ) at 2022-02-11 10:04 EST
Nmap scan report for 192.168.1.2
Host is up (0.00036s latency).
Not shown: 998 closed tcp ports (conn-refused)
PORT     STATE SERVICE
3306/tcp open  mysql
MAC Address: AA:BB:CC:DD:EE:FF (Unknown)
Device type: general purpose
Running: Linux 4.X|5.X
OS CPE: cpe:/o:linux:linux_kernel:4 cpe:/o:linux:linux_kernel:5
OS details: Linux 4.15 - 5.6
Network Distance: 1 hop

OS detection performed. Please report any incorrect results at https://nmap.org/submit/ .
Nmap done: 1 IP address (1 host up) scanned in 1.45 seconds

    Check the Session

    PORT     STATE SERVICE
3306/tcp open  mysql

    if it is listen, MySQL is listening to port. Otherwise, please change the following in mysql config. Mine is /etc/mysql/my.cnf.

    port          = 3306
#ipv4
bind-address  = 192.168.1.2
#ipv6
bind-address  = abcd:abcd:abcd:abcd:abcd:abcd:abcd:abcd

    CAUTION: editing Configs requires sudo or root permissions

    After changing and restarting server, please check with nmap again to make sure your DB Server starts listening. PLEASE MAKE SURE FIREWALL IS OFF FOR TESTING.

    To Disable Temporary, sudo service firewalld stop and try again. Please refer to your distro firewall service. Both Lumen and MySQL Servers should disable for testing reason. NOT RECOMMENDED FOR PRODUCTION SERVERS.

    If Step 1 and 2 are working well. try to access the server with Laravel Tinker, or mysql client.

    mysql -h 192.168.1.2 -u root -p password

    if it shows other than socks not found, like access denied, please refer to MySQL or Mariadb Manual.

    If it is successful, please set your .env file inside your Laravel/Lumen Project.

    #IPv4
DB_HOST=192.168.1.2
#IPv6
DB_HOST=[2001:19f0:5:241:a315:602c:64ce:d6c1]
DB_PORT=3306

    Hope this help. Worked in Mariadb.