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

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