How to Connect Your Laravel Application to Digital Ocean's Managed MySQL 8 database

If you're like me, you've spent way too much time trying to properly connect to a managed MySQL 8 database from Digital Ocean.

Everything looks great, you believe you configured everything correctly, but then you receive an SQLSTATE[HY000] [2002] Doctrine\DBAL\Driver\PDO\Exception error from your application.

Not a helpful error message, I've also seen it way too many times.

The last time I worked on my company's web app, I ran into this, and decided to document the solution, for the sake of future me.

If you're in my shoes, don't worry, I have you covered. Here is a step-by-step guide on how to fix it and set up a working connection from Laravel to Digital Ocean's managed MySQL 8 database.

---

Digital Ocean Configurations

Let's start by setting up the database.

Create a managed database in Digital Ocean. Make sure to choose MySQL version 8, preferably to the same region where your Laravel server is.

Once it's done, I recommend creating a new user instead of using the default one.

You can do it under the Users & Databases tab:

Make sure to keep the password encryption as is (MySQL 8+).

While you're here, you can also create a database for your project, it's up to you.

The next thing you need to do is allowing traffic from various trusted sources.

This means that only certain computers/servers can access your database.

You can set this up under the Settings tab's Trusted Sources section.

Make sure to add the Laravel server's IP (or if it's hosted at Digital Ocean, you can refer to it by name) and your local IP as well, so you can connect from your computer.

Testing the connection

If everything went fine, you can see the connection details in the Overview tab:

Make sure your new user and your database are selected, so you see the correct login details.

Also, check that you see the public host, as you're trying to connect from your computer.

Now, download the CA certificate file, and save it somewhere safe.

This file will be needed later when connecting from Laravel.

But for now, try connecting from your local MySQL client.

Make sure to select a MySQL 8 connection, and use the following configuration:

• host: prod-do-user-XXXXXX.b.db.ondigitalocean.com replace with your database's public hostname
• port: 25060 it's not the default, 3306, make sure to update it!
• username
• password make sure not to copy the extra space at the end of the password
• database

and finally, choose the option so you can add the CA certificate that you just downloaded.

This is how a configuration can look like in TablePlus:

You should be able to connect now. If there's still some issue, double-check if your IP is whitelisted in the Trusted Sources list in the Digital Ocean dashboard.

Connecting from Laravel

Server Setup

Before you configure anything, make sure that you have the MySQL extension enabled for your PHP.

You can check this by running php -i on your server, and if it's not enabled, install it.

You can do it like this on an Ubuntu server:

sudo apt install php8.0-mysql

Configuring the database connection]

First, let's check our app's database configuration config/database.php.

Add the following to the end of the MySQL driver's configuration:

'ssl_mode' => env('SSL_MODE'),
'options' => extension_loaded('pdo_mysql') ? array_filter([
     PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
) : []

so it looks like this:

Uploading the CA certificate

Next, upload the CA certificate to the server.

I recommend adding it in your project's storage folder, eg.
[PATH TO MY PROJECT]/storage/certs/ca-certificate.crt. But it can be anywhere.

What's important is that your Laravel application can access it. For that, you need to set some permissions on the file.

I recommend very restrictive permissions, such as this:

chmod 440 ca-certificate.crt

Also, make sure that the user who executes the PHP script (normally www-data) has access to this file:

chown www-data:www-data ca-certificate.crt

Setting Up the Environment Variables

The last step is to set up the environment variables in Laravel.

Simply edit your .env file, and in the database area replace the values:

DB_CONNECTION=mysql
DB_HOST=private-XXXXX.b.db.ondigitalocean.com
DB_PORT=25060
DB_DATABASE=XXXXX
DB_USERNAME=XXXXX
DB_PASSWORD=XXXXX
SSL_MODE=required
MYSQL_ATTR_SSL_CA=[PATH TO MY PROJECT]/storage/certs/ca-certificate.crt

• The connection, database, username, and password should be self-explanatory.
• If you're connecting from another Digital Ocean server in the same region, use the private VPC network host (you can find it in the Digital Ocean overview tab).
• The port should be always 25060, and you should include the SSL_MODE as required.
• And make sure to include the full absolute path to your CA certificate.

If everything's configured, make sure to clean Laravel's config cache, by running php artisan config:cache.

Testing and Troubleshooting

If you followed these steps, you should be able to connect to your database.

To test it quickly, use Laravel's Tinker:

php artisan tinker

>>> DB::connection()->getDatabaseName();

This should return with your database's name.

If for some reason you still can't connect, double-check these common errors:

• the Laravel application's IP is not whitelisted in Digital Ocean's trusted source list
• incorrect path for the CA certificate
• the CA certificate is not readable by the Laravel application (permission, ownership issues)
• extra whitespace after the password
• old Laravel config is cached, refresh it with php artisan config:cache

Comments

[Rémy 🤖]

I get the feeling that half those trouble could be avoided using the URL configuration of database? (In the
DB docs, "Configuration Using URLs" section).

DigitalOcean gives you this URL natively (also using the apps platform) so that's pretty convenient.

[Johnny Fekete]

Thanks Rémy! I didn't notice that option! Looks like a nice one-liner to add those connection details 📡

However, I still think the CA certificate is required to be uploaded and configured, right?

[Rémy 🤖]

For a bit more context, this practice of the DATABASE_URL has been popularized by Heroku and things like the The Twelve-Factor App which are definitely interesting principles to follow in order to make apps serverless-ish :)

Regarding the CA cert, the problem is indeed a bit different, I'd grease up your setup a bit:

• The CA cert can be explicitly committed to your code (it's not a secret), so I guess a ssl folder at the root of the project would be more fitting than putting into storage
• You can probably just ask for the file name as an environment variable, like DB_CERT=do would translate into something like __dir__ . "/../ssl/$certName.crt" (pardon my rusty PHP it's been time)

Hope this helps :)

[Johnny Fekete]

very much, thanks for the clarification.
Are you sure the certificate can committed? It's used for connecting to the database, and when there are multiple environments, keeping its location as an environment variable seems like a better option.

[Alex Bouma]

The CA certificate is per project (as Digital Ocean calls them), so all DB's you create within the same Digital Ocean project share the same CA certificate, or at least for the same database type. All my MySQL databases in the same project use the same CA certificate.

It's safe to commit since it's just a public certificate, no private data in there. Since it's just the public part there is no worry in anyone using it for something bad since that would require the private part of the CA certificate which you are not given it's just there to validate you are talking to the correct database server without Digital Ocean needing to use a publicly signed certificate for it that is already trusted by your host machine (which has a library of trusted CA certificates used to validate public certificates).

To make it easy on myself, I have this snippet in my config:

    'options' => extension_loaded('pdo_mysql')
        ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA') !== null
                ? resource_path(env('MYSQL_ATTR_SSL_CA'))
                : null,
        ])
        : [],

This allows me to set this in my .env:

MYSQL_ATTR_SSL_CA=certs/do_mysql_ca.pem

The actual path to the file being <project root>/resources/certs/do_mysql_ca.pem.

Added benefit of keeping it in git is that it's already shared with the rest of the team in case they need to connect to the database directly (although I keep the circle that can actually do that really small to prevent accidental "I was in the wrong database and deleted everything" calls).

[Johnny Fekete]

wow thanks Alex for the super detailed description! It's all clear and makes perfect sense.
I would've been worried with those accidental situations of "oops I deleted production DB" that's why it was unclear that the CA certificate can actually be committed to the repo.
But it makes sense, thanks again!

[Ali Haider]

Hey Johnny, thanks for the helpful article. But when ever i try to connect to do managed db i get SQLSTATE[HY000] [2002] Connection refused (SQL: insert into
tbh i have been banging my head over this and couldn't debug it.

App server is behind a load balancer,
Did everything same way you told in the article.
Double checked everything as well but still i am struck over here.

I created another db server on forge and was able to connect with that easily, but unable to establish connection with dd managed db.

[Andreas]

Hello.

I keep getting "failed loading cafile stream:"

I tried "DB::connection()->getDatabaseName();"
and it returns the correct database, but I cant migrate.

I also whitelisted the ip and cache:clear etc.

Im unsure what the problem is.