John Davidson

Message:

I need to do a remote mysql connection from a server to another, using a standard PDO connection:

$db = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8mb4', DATABASE_USER, DATABASE_PASSWORD, [

    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

]);

The problem is that this server has many IPs attached, both IPv4 and IPv6, and to avoid this error:

Host '2001:...' is not allowed to connect to this MySQL server in ...

I'd need to create many users in the target host, one for each IP... (or allow connections from any IP, which is unsafe)

Is it possible to specify which IP to use?
For example with curl I can do that with:

curl_setopt($ch, CURLOPT_INTERFACE, '1.2.3.4');

But I didn't find any similar option for PDO.

---

My Answer:
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);

However, if you need to specify the outbound IP address for the connection, you can do so by using the `PDO::MYSQL_ATTR_LOCAL_INFILE` attribute. Here's an example:

php
$db = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8mb4', DATABASE_USER, DATABASE_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION connect_timeout=10; SET SESSION wait_timeout=28800; SET SESSION interactive_timeout=28800;",
]);


In this example, `PDO::MYSQL_ATTR_LOCAL_INFILE` is set to `true` to enable the use of the `LOCAL INFILE` command for loading data from a file on the client side. Additionally, `PDO::MYSQL_ATTR_INIT_COMMAND` is used to set specific session variables for the connection.

You can also specify the outbound IP address in the `host` parameter of the connection string, like so:

php
$db = new PDO('mysql:host=OUTBOUND_IP;dbname=' . DATABASE_NAME . ';charset=utf8mb4', DATABASE_USER, DATABASE_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);


Replace `OUTBOUND_IP` with the actual IP address you want to use for the outbound connection.