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.
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
.
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.
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.
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.
--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.
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: