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

Posted August 23, 2019 7.3k views
DigitalOceanUbuntu 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?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Submit an Answer
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->ssl_set(NULL, NULL, "ca-certificate.crt", NULL, NULL);
$con->real_connect('host', 'your_user', 'your_password', 'your_database', 25060);

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 -P 25060  --ssl-mode=REQUIRED DB1 < DB1.sql