DigitalOcean Managed Database: Create MySQL User and connect to MySQL Database using PHP

Photo by Campaign Creators on Unsplash

The password given by Managed Database Cluster is in caching_sha2_password format. The problem is you cannot use the given password to connect to your PHP files. To solve the problem follow the steps below to have the right access to database clusters using PHP.

  1. Open your DO account and go to your database cluster, on the settings tab you will see a trusted source. You need to add your droplet or your IP address to be able to access the database.
  2. On your DO account go to Users and Databases tabs, Add New User (you can also use the doadmin if you don’t want to create a new user, but for me, I prefer to create a new user).
  3. On your Terminal login to your MySQL database cluster, just copy-paste the connection on the flag.
  4. Once you are in MySQL shell enter the following:
ALTER USER 'doadmin'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'yourpassword'

Once you have successfully altered your password, you can now use it as database credentials on your PHP Database connection.
You can try it using this script:
This script is using the MySQL PDO extension

<?php
ini_set('display_errors', 1); //This will display error if any you can remove this after
$servername = "<Your Managed Database Host URL>";
$username = "<Your Database Username>";
$password = "<Your Database Password>"; //The one you set in ALTER USER
$dbname = "<Your Database Name>";
$port = "<Your Managed Database Port>";
$options = array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);

$dsn = 'mysql:host=' . $servername . ';port=' . $port. ';dbname=' . $dbname;
// Create a new PDO instanace
try{
$dbh = new PDO($dsn, $username, $password, $options);

echo "Connected successfully";
}
// Catch any errors
catch(PDOException $e){
echo "Connection failed: " . $e->getMessage();
}
?>

References:
https://www.digitalocean.com/community/tutorials/how-to-connect-to-managed-database-ubuntu-18-04#connecting-to-a-managed-mysql-database

If you have any questions, feel free to use the comment section below. I will try my best to help and solve any further issues. 😉

--

--

--

I'm a Software Developer with experience in Web Development and Mobile App Development and a bit knowledgeable in Server Management.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

CS373 Fall 2021: Blog 12

Day 9–10/30/20

Tech Bits #1: An NGINX DNS resolution issue

My CLI Project was a wa

Fabricate a build & Deployment pipeline

Setup Guide to Fedora Rawhide

7 Things You Should Never Do in the Morning

CSS Selectors

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
My Code Snippets

My Code Snippets

I'm a Software Developer with experience in Web Development and Mobile App Development and a bit knowledgeable in Server Management.

More from Medium

Beginning with GitHub

Github

Firebase Emulator Suite — Using Cloud Functions Emulator

Writing a Deployable Application — Web Edition

[wordpress] REST API — update/create/read Post, Custom Post type(CPT) with ACF