Categories
Work

Setting up a remote MySQL database with Laravel 5

Using a tutorial on DigitalOcean I successfully migrated a project that was running on a single droplet onto two droplets, a database server (running MySQL 5.7) and a web server ( running PHP 7.2). I’m happy with the outcome but there were a few hurdles I thought I would make a note on.

First, Laravel 5 uses The PHP data objects extension (PDO) to connect to MySQL. In the aforementioned tutorial, MySQL was set up to accept only secure connections. This can be handled in Laravel by adding an options attribute to the database configuration containing some PDO constants. These constants point to the keys that I copied over from the database server after running sudo mysql_ssl_rsa_setup --uid=mysql.

'mysql' => [
    ...
    'options'   => [
        PDO::MYSQL_ATTR_SSL_KEY  => '/path/to/client-key.pem',
        PDO::MYSQL_ATTR_SSL_CERT => '/path/to/client-cert.pem',
        PDO::MYSQL_ATTR_SSL_CA   => '/path/to/ca.pem',
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
    ],
    ...
]

I had to utilize PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false because the database server was refusing a connection. This constant was introduced to overcome a known bug in PHP.

Second, I was experiencing major lag when connecting to the database server. I thought utilizing private IP addresses, instead of public IP addresses, might fix the problem but I did not notice any improvement. Ultimately, I discovered DNS host name lookups was causing my problem.

If you have a very slow DNS and many hosts, you might be able to improve performance either by disabling DNS lookups with --skip-name-resolveMySQL

Since I’m using IP addresses, it made sense to go ahead and disable it in mysql.conf.d as depicted below.

[mysqld]
skip-name-resolve

After learning those additional steps, I have a working remote MySQL set up. On to the next task!

Leave a Reply

Your email address will not be published. Required fields are marked *