Introduction

To dump a remote MySQL database from your local machine, run mysqldump locally and point it at the remote host with the correct credentials. The basic idea is simple, but production-safe usage also depends on network access, authentication, consistency options, and how you protect credentials.

The Basic Command

The common form is:

mysqldump -h db.example.com -u backup_user -p my_database > my_database.sql

This means:

• '-h remote host'
• '-u MySQL user'
• '-p prompt for password'
• 'my_database database name'
• '> write the dump to a local file'

When you run it, mysqldump connects from your local machine to the remote database server and writes the SQL backup file locally.

Use Better Defaults for InnoDB

For many real systems, especially InnoDB-backed databases, a safer backup command is:

1mysqldump \
2  -h db.example.com \
3  -u backup_user \
4  -p \
5  --single-transaction \
6  --quick \
7  --routines \
8  --triggers \
9  my_database > my_database.sql

--single-transaction gives a consistent snapshot for transactional tables without locking the whole database the way older workflows often did. --quick streams rows instead of buffering too much client-side memory.

Those options are much more appropriate than a bare minimal dump for many production databases.

Use a Non-Default Port If Needed

If the server uses a custom port, add -P:

mysqldump -h db.example.com -P 3307 -u backup_user -p my_database > my_database.sql

Remember that -P is uppercase for port. Lowercase -p is for password prompting.

Prefer Password Prompting or Config Files

Do not put the password directly in the shell command unless you understand the exposure risk.

Less safe:

mysqldump -h db.example.com -u backup_user -psecret my_database > my_database.sql

That can leak into shell history or process listings. Better options are:

• use -p and enter the password interactively
• use a MySQL option file such as ~/.my.cnf
• use environment-controlled automation secrets in CI or ops tooling

Security practices matter because database dump credentials are usually powerful.

Use SSH Tunneling When Direct MySQL Access Is Blocked

Many production databases do not allow public remote MySQL connections. In that case, tunnel through SSH instead of opening the database port broadly.

ssh -L 3307:127.0.0.1:3306 [email protected]

Then, in another terminal:

mysqldump -h 127.0.0.1 -P 3307 -u backup_user -p my_database > my_database.sql

This is often safer than exposing MySQL directly to the internet.

Compress Large Dumps

For larger databases, compression saves bandwidth and disk space.

mysqldump -h db.example.com -u backup_user -p --single-transaction my_database | gzip > my_database.sql.gz

This is a practical default for remote backups over slower links.

Dump Specific Tables or Skip Some Tables

You do not have to dump the entire database.

Dump specific tables:

mysqldump -h db.example.com -u backup_user -p my_database users orders > partial.sql

Ignore a table:

mysqldump -h db.example.com -u backup_user -p \
  --ignore-table=my_database.audit_log \
  my_database > filtered.sql

This is useful when you want a focused backup or want to exclude huge ephemeral tables.

Common Pitfalls

The most common mistake is assuming the database is reachable remotely when firewall rules or cloud security groups block the connection. Another is running mysqldump without --single-transaction on large transactional databases and then being surprised by consistency or locking behavior. Developers also put passwords directly into the command line, which is convenient but unsafe. Finally, many teams forget that application servers and database servers are often only reachable through SSH tunneling or a bastion host, not direct public database access.

Summary

• Run mysqldump locally with the remote host, user, and database name.
• Use --single-transaction and --quick for safer large InnoDB backups.
• Add -P if the remote server uses a non-default port.
• Avoid exposing passwords on the command line.
• Use SSH tunneling when direct remote MySQL access is restricted.