How to Connect Your Laravel Application to Digital Ocean's Managed MySQL 8 database
Tags: laravel, mysql, database, php
Published: 2021-09-07 15:17:45

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](https://bonboarding.com), 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.

![Choose MySQL version 8 at the Digital Ocean dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dgcfex8gde0ztp55nhhi.png)

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:
![Add a new user](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b66be3ah1k5o2gxfcoru.png)

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:
![Connection details at Digital Ocean Overview page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gk0hvkch6hzceniih5d5.png)

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:

![MySQL 8 connection with CA certificate](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wazg0t4g1zy4oxpvl31h.png)

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:*

```console
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:
```php
'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:
![Final MySQL config in Laravel](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fex9y23grombdnsn92rc.png)


### 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:
```console
chmod 440 ca-certificate.crt
```

Also, make sure that the user who executes the PHP script (normally *www-data*) has access to this file:
```console
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:
```env
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
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`