How To Connect Managed MySQL with PHP PDO

Question

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?

Leslie Goh · Answer

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:

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.
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!

Leslie Goh · Answer

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:

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.
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!

Erika Heidi · Answer

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-–-adding-the-mysql-software-repository-and-installing-mysql-client

KFSys · Answer

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

Related

Question

How To Connect Managed MySQL with PHP PDO

Related