DigitalOcean Managed Database: Create MySQL User and connect to MySQL Database using PHP
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.
- 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.
- 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).
- On your Terminal login to your MySQL database cluster, just copy-paste the connection on the flag.
- 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
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
$dbh = new PDO($dsn, $username, $password, $options);
echo "Connected successfully";
// Catch any errors
echo "Connection failed: " . $e->getMessage();
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. 😉