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

Posted on in Categories Backup, FreeBSD, Howto, Linux, MySQL, Postgresql, RedHat/Fedora Linux, Sys admin, Tips, UNIX last updated October 1, 2007

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 [email protected] "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 [email protected] "dd of=/mysql/$(date +'%d-%m-%y')"

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

5 comment

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

Leave a Comment