How do I connect to a DO managed MySQL DB from a PHP script using mysqli_connect ?

August 31, 2019 143 views
MySQL Ubuntu 16.04

Hi,

I created a managed MySQL cluster, but I’m having problems connecting to it from my PHP script. I can’t seem to find a tutorial online yet.

How do I connect to a DO managed MySQL DB from PHP using mysqli_connect ?

Normally, I connect using syntax like:

$con = mysqli_connect("hostname", "user", "password", "database");
mysqli_set_charset($con, "utf8");
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

Any help is much appreciated.

Regards,

Wyatt

1 Answer

Hello,

I’ve just tested your snippet and there are two things that I’ve noticed:

  • Make sure to append the port number to your host otherwise it would not work as the Managed databases are listening on a non-default MySQL port

  • Make sure to update the Auth type of the Database User and set it to mysql_native_password

To do that you could do is just create a new user with mysqlnativepassword

CREATE USER 'your_user'@your_server_ip IDENTIFIED WITH mysql_native_password BY 'your_password';

Or you could adjust this for your current user with:

ALTER USER myuser IDENTIFIED WITH mysql_native_password BY 'mypassword';

After that you should be able to connect as normal.

Hope that this helps!
Regards,
Bobby

Have another answer? Share your knowledge.