Related

WordPress database error: [Expression #1 of ORDER BY clause is not in SELECT list... Question Question
Private connection to Managed DB not working from within same region droplet. Question Question

Question

How To Connect Managed MySQL with PHP PDO

Posted October 31, 2019 3.6k 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?

Add a comment
cagkana9f5cd873f5b8188e469
By:
cagkana9f5cd873f5b8188e469
Add a comment

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
erikaheidi November 4, 2019

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

How To Install WordPress with a Managed Database on Ubuntu 18.04
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.
Reply Report
LeslieGoh February 28, 2020

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!

Reply Report
KFSys November 1, 2019

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

How To Connect to a Managed Database on Ubuntu 18.04
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.
Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help