Example Remote MySQL SSL Connections Using PHP: MySQLi and PDO

By on

Please understand that this was part of an exploration, and I never implemented this option. I was exploring remote access to MySQL. However, this may be helpful to others, and I wanted to share a bit of my experience and PHP code. I will most likely use autossh and SSH tunneling as the final solution. Here is a follow up post on how to use autossh and ssh tunneling for creating the connection.

Links That Were Helpful

Setup and understanding the integration with the remote MySQL server:

Speed comparisons between remote MySQL using SSH versus SSL. Understand that your performance will suffer:

Using ProxySQL for remote connections improves speed of MySQL using SSL.

MySQL Shell for Testing Connection Via Mac Terminal

Notes
I used OpenSSL to generate the certificates on the MySQL remote server.

ini_set('error_reporting', E_ALL);
ini_set('display_errors', '1');
error_reporting(E_ALL|E_STRICT);

// THE DATABASE USER SHOULDN'T BE THE ROOT USER, THIS IS NOT ALLOWED BY DEFAULT
$db_host = '';
$db_user = '';
$db_password = '';
$db_database = '';

//ALL PEM FILES ARE COPIED FROM THE REMOTE MYSQL SERVER AND CREATED WITH OPENSSL

// THE PDO VERSION
$pdo = new PDO('mysql:host=;dbname=', '< USER >', '< PASSWORD> ', array(
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
    PDO::MYSQL_ATTR_SSL_KEY    =>'/home/vagrant/client-ssl/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT=>'/home/vagrant/client-ssl/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA    =>'/home/vagrant/client-ssl/ca.pem'
));
$query = $pdo->prepare("SELECT * FROM wp_posts LIMIT 0, 10");
$query->execute();
$result = $query->fetchall(); // or you can just $result = $query as hakre proposed!
var_dump($result);

// THE MYSLQI VERSION
$db = mysqli_init();
if (!$db) {
    die('mysqli_init failed');
}

// THIS OPTION, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, HAS THE REMOTE SERVER VALIDATE THE CERTIFICATE
mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
// THESE ARE FROM THE MYSQL SERVER.
$db->ssl_set(
    '/home/vagrant/client-ssl/client-key.pem',
    '/home/vagrant/client-ssl/client-cert.pem',
    '/home/vagrant/client-ssl/ca.pem',
    null,
    null
);

if (!$db->options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
    die('Setting MYSQLI_INIT_COMMAND failed');
}
if (!$db->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5)) {
    die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}

// You may want to verify the certificate and not use the option: MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT
if (!$db->real_connect($db_host, $db_user, $db_password, $db_database, '3306', null, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT)) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

$result = mysqli_query($db, "SELECT * FROM wp_posts LIMIT 0, 10");
while ($row = mysqli_fetch_assoc($result)) {
    var_dump($row);
};