Question

How To Connect Managed MySQL with PHP PDO

Posted October 31, 2019 1.7k views
DigitalOcean Managed MySQL Database

Here is my code;


        public function __construct($username = "doadmin", $password = "blablabla", $host = "blablabla.db.ondigitalocean.com", $dbname = "defaultdb", $options=array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false)){

          $options = array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            PDO::MYSQL_ATTR_SSL_CA => 'ca-certificate.crt',
          );

            $this->isConnected = true;
            try {
                $this->datab = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8;port:25060", $username, $password, $options);
                $this->datab->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            }
            catch(PDOException $e) {
                $this->isConnected = false;
                throw new Exception($e->getMessage());
            }
        }

But when i try to connect it gives “Uncaught Exception: SQLSTATE[HY000] [2002] Connection timed out in”

whats wrong with it? i couldnt find any documentation how to connect with php. and tried to save my certificate.

how can I resolve this?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
3 answers

Hi! I was also facing this problem when I was trying to connect to the managed MySQL database on DO. From a combination of some of the answers above:

1) create a new MySQL user with standard authentication type directly in your MySQL database either using the MySQL Workbench GUI or from the command line on your attached droplet. Do not use the DO dashboard to create your user.

2) you need to specify port 25060 in your connection string. The code given above is not correct. It should be an “=” sign instead of the “:” in the connection string.

$dsn = "mysql:host=$host;dbname=$dtbs;charset=$char;port=25060";

So the full connection string will read something like this:

$host = 'hostname-given-by-do';
$dtbs = 'yourdatabase';
$user = 'the-new-user-you-created';
$pass = 'the-password-for-the-user';
$char = 'utf8';

$dsn = "mysql:host=$host;dbname=$dtbs;charset=$char;port=25060";

$options = array(
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::ATTR_EMULATE_PREPARES => false
);

$conn = new PDO($dsn, $user, $pass, $options);

This worked for me and I hope it helps. Cheers!

Hi @cagkana9f5cd873f5b8188e469,

If your connection is timing out which would mean most probably there is something wrong in your connection string.

From a quick look, I can see you haven’t specified a port for the connection. You’ll need to use the specific port in order to connect to your Managed Database.

You can first try to connect from your droplet to your Managed MySQL Database. There is a useful article written by DigitalOcean - HERE

Regards,
KDSys

by Mark Drake
If you're new to working with managed databases, the best way to perform certain tasks, like connecting to the database, may not be self-evident. In this guide, we will go over how to install client programs for a variety of database management systems, including PostgreSQL, MySQL, and Redis, on an Ubuntu 18.04 server. We'll also explain how to use these programs to connect to a managed database instance.

Hello @cagkana9f5cd873f5b8188e469 , have you created a new user for your application?

As explained in the official PHP website, at this moment, the native PHP MySQL module does not support the default authentication method used by MySQL 8 (caching_sha2_password).

For now, it is only possible to connect to a MySQL 8 database from PHP using the mysql_native_password method, but this should be fixed in the next PHP release or so.

For instructions, check steps 1 and 2 of this guide: https://www.digitalocean.com/community/tutorials/how-to-install-wordpress-managed-database-ubuntu-18-04#step-1-%E2%80%93-adding-the-mysql-software-repository-and-installing-mysql-client

by Mark Drake
WordPress is the most popular CMS (content management system) on the internet. It allows you to easily set up flexible blogs and websites on top of a MySQL backend with PHP processing. In this guide, we'll focus on getting a WordPress instance set up on a managed MySQL database on an Ubuntu 18.04 server.
Submit an Answer