How to Connect to Database on Another Server

how to connect to database on another server

its simple all thise above techniques are quite complicated

suppose you have database on server B and website on server A(say it has IP 192.234.12.1)

on cpanel whitelist the IP of server B

and create a new user having sufficient privileges in database (say this user is test)

then create this user as test@192.234.12.1

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

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.

2. 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.

Connection to a db from localhost to another server with php

Yes it is possible.You must have the username and password of that domain in which you want to connect.

mysqli_connect("www.domain.com","username","password","database_name")or die("Error " . mysqli_error());

php mysql Connect Database from another server

<?php

mysql_connect("mysite.com:3306", "admin", "1admin") or die(mysql_error());
echo "Connected to MySQL<br />";

?>

Just run it and see if it works.

AWS EC2 Connect Database from another server

Here is a detail of the various options that you have, based on your scenario you are trying to rehost your database from its current host to another.

As you're using MySQL you can make use of the managed RDS service, Aurora MySQL or an EC2 host either within the same region or a different region.

If the VPC is different (this would be the case in a different region) you can establish a peering connection between the VPC. You will need to make sure that the security groups allow inbound access from the source of the other VPCs CIDR. You should also ideally keep the database as a private instance to avoid any malicious public ingress.

If the database is instead migrated to either another cloud or on-premise you would need to establish a connection between the 2 networks. AWS provides VPN solutions for both site to site VPN and client VPN so this is worth exploring. Additionally there are third party VPN solutions on the AWS marketplace such as OpenVPN.

Finally ensure you are aware that there will be an increased latency between regions, for an overview of this latency take a look at Cloudpings AWS latency page.

Connect to mysql on a different server

Since your database server is different from your php/apache server you need to specify the hostname as 172.x1.x1.x2 in mysql-php connection string.

Also make sure that mysql user root have remote connection permission. Other wise mysql-server will not allow your root user to login remotely. i.e. from your server1.

You can make sure that from mysql.user table.

mysql> select Host,User from user where User = "root";
+------------+------+
| Host       | User |
+------------+------+
| 127.0.0.1  | root |
| ::1        | root |
| localhost  | root |
| sgeorge-mn | root |
| %          | root |
+------------+------+
4 rows in set (0.01 sec)

% means any host.

To create a user with remote connection permission, use following mysql query:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';

Try to connect localhost laravel project to another database from another server

The root cause: You forgot to change the host, port to host another.

Please update the code same below:

'mysql2' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST_SECOND'),
        'port' => env('DB_PORT_SECOND'),
        'database' => env('DB_DATABASE_SECOND'),
        'username' => env('DB_USERNAME_SECOND'),
        'password' => env('DB_PASSWORD_SECOND'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],  

---

Related Topics