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.
Advertisement
Answer
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 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.
- 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.