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')"
You should follow me on twitter here or grab rss feed to keep track of new changes.
Featured Articles:
- 30 Handy Bash Shell Aliases For Linux / Unix / Mac OS X
- Top 30 Nmap Command Examples For Sys/Network Admins
- 25 PHP Security Best Practices For Sys Admins
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- Linux: 20 Iptables Examples For New SysAdmins
- Top 20 OpenSSH Server Best Security Practices
- Top 20 Nginx WebServer Best Security Practices
- 20 Examples: Make Sure Unix / Linux Configuration Files Are Free From Syntax Errors
- 15 Greatest Open Source Terminal Applications Of 2012
- My 10 UNIX Command Line Mistakes
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- The Novice Guide To Buying A Linux Laptop
{ 5 comments… read them below or add one }
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?
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.
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.
Same problem as Ron. Should I touch the file first ? Seems odd… Anyway, thanks for the tips! :)
hello,
how to get full backup of all database using this command ?
thank you