Use local network for DB connection

Question

We have a dedicated server with proxmox on OVH. The idea is to connect the containers locally but also trough internet. So far I have 2 containers.

I added a bridge network for local IP and that is working since I am able to ping the containers from each other.
Also added bind-address=192.168.1.3 to my.cnf.

1 container is running apache + php 7.2 (192.168.1.3)

The other container is running MySQL. (192.168.1.2)

Problem

My MySQL keeps saying SQLSTATE[HY000] [2002] Connection timed out

Here is my php code:

<?php

/**
 * Configuration for database connection
 *
 */

$host       = "192.168.1.2";
$username   = "root";
$password   = "root";
$dbname     = "test";
$dsn        = "mysql:host=$host;dbname=$dbname";
$options    = array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
              );

try 
{
    $connection = new PDO("mysql:host=$host", $username, $password, $options);
    $sql = file_get_contents("data/init.sql");
    $connection->exec($sql);

    echo "Database and table users created successfully.";
}

catch(PDOException $error)
{
    echo $sql . "<br>" . $error->getMessage();
}

From my understanding the code is correct so it must be something with my mysql configuration.
I'm sure that is something really simple but I'm losing to much time with this.

GSinghLDN · Answer 1 · 2018-03-14T16:36:15.950

1

Try to telnet 192.168.1.2 3306 from the Apache, PHP container. Can you connect?

Ensure the listening port for MySQL is 3306, if other then adjust the PHP code accordingly. Also ensure that iptables is not blocking any incoming connections. Also ensure you have correct permissions for the root and any other users you need to have permissions from other hosts.

Also, please check when making any config changes to MySQL, that you restart the service.

edited Mar 14 '18 at 16:36

answered Mar 14 '18 at 16:27

GSinghLDN

161
8

1

Good test, but I think the they said MySQL is running on `192.168.1.2` – Bill Karwin Mar 14 '18 at 16:28
Yep .. connection timed out. But is weird since I allowed this specific IP to connect to 3306 same with icmp for ping which is working – Diogo Jesus Mar 14 '18 at 16:32
Is there anything else that i'm missing on mysql settings? – Diogo Jesus Mar 14 '18 at 16:33
can you try `nmap -sV -p 3306 192.168.1.2`? Seems that MySQL is not listening on port `3306` – GSinghLDN Mar 14 '18 at 16:33
``` root@lampserver:/# nmap -sV -p 3306 192.168.1.2 Starting Nmap 7.40 ( https://nmap.org ) at 2018-03-14 16:37 UTC Nmap scan report for 192.168.1.2 Host is up (0.000055s latency). PORT STATE SERVICE VERSION 3306/tcp filtered mysql MAC Address: 8E:F7:5A:62:FA:FA (Unknown) Service detection performed. Please report any incorrect results at https://nmap.org/submit/ . Nmap done: 1 IP address (1 host up) scanned in 1.44 seconds ``` – Diogo Jesus Mar 14 '18 at 16:38
`3306/tcp filtered` - presumably, the host is behind some sort of firewall. Here, the packet is simply dropped and you receive no response (not even a RST). Its definitely a MySQL config issue or a firewall such as `iptables`, more likely the former. Please ensure that MySQL allows remote connections and that you restart the service after all config changes. Good luck – GSinghLDN Mar 14 '18 at 16:43
Thanks, the firewall is open both TCP and UDP. On mySQL side I added bind-address=192.168.1.3 aswell idk if i'm missing more settings on mysql config – Diogo Jesus Mar 14 '18 at 16:45

score 0 · Accepted Answer · answered Mar 15 '18 at 09:00

After searching around for a while. Found out that I had to create a new user and grant permissions to it

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;

Also on my server config /etc/mysql/mariadb.conf.d/50-server.conf I had to comment the

bind-address  = 192.168.1.3

into

#bind-address  = 192.168.1.3

Then restart mysql server and change my mysql details on my php code with username 'monty' and password 'some_pass'

Use local network for DB connection

Problem

2 Answers2