CODEWITHSUNDEEP
mysqlmysql-workbenchdigital-oceandatabase-backups
CliffTheCoder
CliffTheCoder

Reputation: 444

How to Export MySQL Database From Digital Ocean Managed Database

I have a MySQL database hosted on Digital Ocean Managed Database service. I lost my previous dev machine and did not push the Schema to git along with application files. I am desperate to get back the SQL Schema in my local machine for further Development but have so far been usuccessfull. I have tried so many commands from different suggestions but they all failed.

I connect to the DB via the MySQL Shell using the command:

mysql -u username -password-h example-test-do-user-7878789-0.b.db.ondigitalocean.com -P 25060 -D example_db

All queries are executed successfully via this shell method. When trying to Dump the Schema to my machine, I use the following command:

mysql -u username -p -h mysql-test-do-user-4915853-0.db.ondigitalocean.com -P 25060 your_database_name \> database_file_name.sql

But I get the error as follows: mysqldump: unknown variable 'set-gtid-purged=OFF'

if I remove that part and try again, I get the error:

mysqldump: Got error: 2003: "Can't connect to MySQL server on 'localhost' (10061 "Unknown error")" when trying to connect

which doesnt make sense to me. So, without further ranting; How to I achieve my intended result.

NOTE: I did try Mysql workbench, but it won't connect all together

Upvotes: 1

Views: 2741

Answers (2)

Jesse Nickles
Jesse Nickles

Reputation: 1849

I noticed this issue too on DigitalOcean and it's because the database hostname contains special characters which confuses mysqldump .. it's better to save the hostname as a variable.

This is the bash function we use in SlickStack:

function ss_mysqldump_user {
    command mysqldump --user="${DB_USER}" --password="${DB_PASSWORD}" --host="${DB_HOST}" --protocol=tcp --port="${DB_PORT}" --no-create-db --no-tablespaces --single-transaction --dump-date --force "$@"
}

Doing this prevents the hostname from being misinterpreted.

More info:

https://slickstack.io/forum/topic/mysqldump-from-managed-remote-database-server

Upvotes: 0

CliffTheCoder
CliffTheCoder

Reputation: 444

I can appreciate trying to achieve what you want via mysql shell. I stumbled across the same problem and for quite some tie did not find the help i needed, then I tried doing it using mysql workbench. Try it and you will find that its easier than usin the command-line approach. MySQL Workbench has a good GUI based database miguration capability. You can migrate you Do Managed DB directly into you local mysql server or export a schema dump. If you decide to go wit this approach, just make sure you add your machine's IP as a trusted source, else you will not be able to connect.

Upvotes: 1

Related Questions