Question

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

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?


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

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.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

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