Quickly Backup / dump MySql / Postgres database to another remote server securely

by on October 1, 2007 · 5 comments· LAST UPDATED October 1, 2007

in , ,

Using UNIX pipe concept one can dump database to another server securely using ssh protocol. All you need remote execution rights for the 'dd' command, over SSH. This allows you to run database dumps across an encrypted channel.

Dump Postgres Database using ssh

Use pg_dump command command:
pg_dump -U USERNAME YOUR-DATABASE-NAME | ssh user@remote.server.com "dd of=/pgsql/$(date +'%d-%m-%y')"

Dump MySQL Database using ssh

Type the following command:
mysqldump -u USERnAME -p'PASSWORD' YOUR-DATABASE-NAME | ssh user@remote.server.com "dd of=/mysql/$(date +'%d-%m-%y')"

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 5 comments… read them below or add one }

1 Ron October 3, 2007 at 4:50 pm

How do you include the Postgre password in the command line and stop the system from responding that file not found on the remote server?

Reply

2 Cory April 13, 2008 at 10:18 pm

The MySQL backup command is not secure — anyone can view the password using `ps` on the local machine. It is preferred to use the .my.cnf file, with a [mysqldump] header.

Reply

3 melichades August 31, 2008 at 5:05 pm

mysqldump -u USERnAME -p’PASSWORD’ YOUR-DATABASE-NAME | ssh ….

this will lock your db-server for a very long time …

better do a mysqlcopy … –noindexes first, then rebuild the indexes, and then go on as described.

Reply

4 DSampaolo June 2, 2009 at 9:09 am

Same problem as Ron. Should I touch the file first ? Seems odd… Anyway, thanks for the tips! :)

Reply

5 gamezat February 12, 2013 at 12:25 pm

hello,
how to get full backup of all database using this command ?
thank you

Reply

Leave a Comment

Tagged as: , , , , , ,

Previous post:

Next post: