Question

How to export large mysql database from digitalocean cloud server into DigitalOcean Managed Databases?

Posted September 20, 2020 258 views
MySQLDigitalOcean Managed MySQL Database

Hi. There is a local mysql database on a cloud server with a size of 52GB+
How do I export it to DigitalOcean Managed Databases?

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.

×
1 answer

Hi there @vlladoffwork,

I would usually use one of the tools listed on the MySQL website for exporting databases. I would usually default to the mysqldump tool here:

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

Example usage:

  • mysqldump -h your_host -u your_user -p your_db_name > db-name.sql

However, for some extra performance I might sometimes use mysqlpump instead:

https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html

The syntax is quite similar:

  • mysqlpump -h your_host -u your_user -p your_db_name > db-name.sql

Once you have the databased exported into a .sql file, you can import it with the mysql command into your Managed MySQL cluster.

Syntax:

  • mysql -h your_host -u your_user -p your_db_name < db-name.sql

Hope that this helps!
Regards,
Bobby

  • Hi! Thx for answer. There is one problem. Records are actively added to the database. Dumping exporting and importing will take n time during which I will lose some of the data

    • Hi there @vlladoffwork,

      One way to avoid data inconsistency is to do the following:

      • Set your current database server to read-only or put your application/website in a maintenance mode.
      • Create a backup of your database.
      • Import the backup to your new database server.
      • Update your website configuration to point to the new database server.
      • Finally Verify that the database is functioning as expected.

      Before scheduling the downtime, I could suggest doing a test run with export and import and timing the whole process, that way you will then be able to know exactly how long of a maintenance window you should schedule.

      Hope that this helps!
      Regards,
      Bobby

Submit an Answer