How to dump a remote database using mysqldump

mysqldump allows you to dump databases that are hosted on a remote machine. Let's see how to do that for a single, multiple and all databases stored on a remote server.

We need to know the MySQL user, password, host and port of the remote database. Let's assume the following values for the examples below:

• host: 14.212.331.87
• user: guru_user
• port: 3306 (MySQL default port)

Replace the values of host, user and port with your details.

Dump a single remote database

# Without compression
mysqldump -host 14.212.331.87 -u guru_user --port 3306 -p my_app > my_backup.sql

# With Gzip compression
mysqldump -host 14.212.331.87 -u guru_user --port 3306 -p my_app | gzip > my_backup.sql

Dump multiple remote databases

# Without compression
mysqldump -host 14.212.331.87 -u guru_user --port 3306 -p --databases my_app1 my_app2 > my_backups.sql

# With Gzip compression
mysqldump -host 14.212.331.87 -u guru_user --port 3306 -p --databases my_app1 my_app2 | gzip > my_backups.sql

Dump all remote databases

# Without compression
mysqldump -host 14.212.331.87 -u guru_user --port 3306 -p --all-databases > my_backups.sql

# With Gzip compression
mysqldump -host 14.212.31.87 -u guru_user --port 3306 -p --all-databases | gzip > my_backups.sql