How do I migrate a MySQL database from a Droplet to a Managed Database?

Hi! You need to:

1. Install Mysql 8 in your droplet (to connect with managed database)
2. Create (or change an existent) an user with auth plugin mysql_native_password

To create a new user:

First, you need to connect Mysql using a compatible client (in my case, TablePlus) with Digital Ocean default user (doadmin), and them, run the command bellow:

CREATE USER 'your_new_user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

If you have created an user in DO interface, you can do this:

ALTER USER your_user IDENTIFIED WITH mysql_native_password BY 'your_password';

Finally, make GRANT ALL for your database:

GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'host'

Still in the TablePlus, you can import your tables from old database to new one.

In PHP connection:

First, you have to download ca-certificate.crt to php file folder. After do this, run the code bellow:

$con = mysqli_init();
$con->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$con->ssl_set(NULL, NULL, "ca-certificate.crt", NULL, NULL);
$con->real_connect('host', 'your_user', 'your_password', 'your_database', 25060);
$con->close();

And done! That’s all!

Note: PHP does not support auth plugin caching_sha2_password yet (default in Mysql 8). In future, maybe, but not yet. Sorry for my English! ;)

Quick note

to export your database from mysql 7 run the following command (if you are using mysql 8)

mysqldump --column-statistics=0 -u root -p -h MYHOST --databases DB1 > DB1.sql

And Import database to mysql 8

mysql -u doadmin -p -h private-MYHOST.ondigitalocean.com -P 25060  --ssl-mode=REQUIRED DB1 < DB1.sql