How to Import MySQL Databases into DigitalOcean Managed Databases

MySQL is an open source, object-relational database built with speed and reliability in mind. Its large and active developer community has created many third-party applications, tools, and libraries that expand MySQL’s functionality.


To import or export databases with the DigitalOcean Managed Databases MySQL engine, you will need:

  • A MySQL client, like the MySQL command line client or MySQL Secure Shell.

  • The database’s connection string; we recommend using the Flags format, which supplies the variables as individual flags that are easier to read and customize.

To get the database’s connection parameters from your control panel, visit the Databases page, open the database’s More menu, then select Connection details and click Flags.

Databases Overview screen showing connection string

Click Copy to copy the flags with the password. If you want to highlight and copy the text from the screen directly, click show-password first to copy the actual password on your clipboard.

To import a database, you will also need the exported database file stored locally on the same machine as your MySQL client. If your database file is on a remote server, you can transfer it with tools like sftp or rsync.

Import with the MySQL Command Line Client

To import a database with the MySQL command line client, the exported database file must be in sql format and must be stored locally on the same machine as your client.

Once you have the sql file of the database, import it using mysql as in the following command. Use your connection string and substitute the actual path to your database file.

mysql -u doadmin -p -h mysql-test-do-user-4915853-0.db.ondigitalocean.com -P 25060 your_database_name \
< /path/to/database_file_name.sql

The application will prompt you for your MySQL doadmin user password.

Adding < /path/to/database_file.sql after the connection parameters passes the file to the mysql command. Learn more about < and > in An Introduction to Linux I/O Redirection.

Import with MySQL Secure Shell

To import a database with MySQL Secure Shell, the exported database file must be in sql or JSON format. sql-format files must be stored locally on the same machine as your client. How you import the file depends on the format.

To import a sql-format database file, use the mysqlsh command with the -f flag and specify the path to the file.

mysqlsh -u doadmin -p -h mysql-test-do-user-4915853-0.db.ondigitalocean.com -P 25060 -D your_database_name \
-f /path/to/database_file_name.sql

The application will prompt you for your MySQL doadmin user password.

To import a JSON database file, first connect to the server using mysqlsh, then use the --import command and specify the path to the file.

    
        
            
--import /path/to/file.json

        
    

You can also specify a target database or column using this method. Learn more in Importing JSON documents with the –import command from the official MySQL documentation.

Export with mysqldump

To export your database, use mysqldump with your connection string as below. Specify a path to save the resulting sql file and to set the --set-gtid-purged flag to off as shown in the following command. The --set-gtid-purged flag determines whether to retain GTID data during the export process.

Warning
If you don’t set the --set-gtid-purged flag to off during export, importing the data may fail.
mysqldump -u doadmin -p -h mysql-test-do-user-4915853-0.db.ondigitalocean.com -P 25060 --single-transaction --set-gtid-purged=OFF your_database_name \
> /path/to/database_file.sql

The application prompts you for your MySQL doadmin user password.

Adding > /path/to/database_file.sql after the connection parameters saves the output of the command to the path you specify. Learn more about < and > in An Introduction to Linux I/O Redirection.

The mysqldump program has many flags that let you customize the resulting dump file, like changing the output format or modifying statements for MySQL version compatibility. Learn more in Using mysqldump for Backups reference from the official MySQL documentation.

Import and Export with PHPMyAdmin or Other MySQL Clients

There are many other clients you can use to import and export MySQL databases. Here are some additional import/export resources, including official documentation from other tools: