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

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.

CodePudding user response：

Type the IPv4 local of the VM database. (You can try to ping one VM to each other to be sure that the connection is workign between the vms)

CodePudding user response：

I've found the cause. Was missing the square brackets at the beginning and end of DB_HOST e.g:

DB_HOST=[my ipv6]

CodePudding user response：

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

Two servers can reach via ping or ssh.
MySQL can listen on IP of the dedicated VM or server.
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 [email protected]
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.

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.