Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
Connection pooling is not supported? Question Question
Can I access database (mysql) logs? Question Question

Question

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

Posted September 20, 2020 1.3k 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?

Add a comment
vlladoffwork
By:
vlladoffwork

Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
Connection pooling is not supported? Question Question
Can I access database (mysql) logs? Question Question

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
1 answer
bobbyiliev September 23, 2020

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

Reply Report
  • vlladoffwork September 23, 2020

    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

    Reply Report
    • bobbyiliev September 23, 2020

      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

      Reply Report

Related

Looking for something else?

Ask a new question Search for more help