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

August 23, 2019 263 views
DigitalOcean Ubuntu 18.04

I’m pretty novice at using anything in the cloud. What I have right now is a Droplet that hosts a few databases that are running on MySQL 5.7. I’ve also got a managed database cluster now running MySQL 8. I want to migrate my databases from the Droplet to the database cluster. I can’t find any documentation to help me with that. I assume that’s because managed MySQL databases is a pretty new offering.

Any ideas?

2 Answers

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
Have another answer? Share your knowledge.