Automated database backups

Automated Database Backups

Postgres

Assuming we are using Linux, create a file called .pgpass in the user’s home directory with the following details:

hostname:port:database:username:password

Then from the command-line run the following command:

pg_dump –U user dbname | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz

Mysql

Assuming we are using Linux, create a file called ~/.my.cnf in the user’s home directory with the following details:

[mysqldump]

user=mysqluser

password=secret

Then from the command-line

mysqldump –u user dbname | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz

Crontab

If the command is successful, then create and add this command to a script, for example runBackup.sh

Then schedule the job to run by editing your crontab:

crontab -e

For example, to run once a week on Saturday at 2AM:

0 2 * * 6 ~/runBackup.sh

 

References

http://stackoverflow.com/questions/2893954/how-to-pass-in-password-to-pg-dump

http://www.postgresql.org/docs/current/static/libpq-pgpass.html

http://stackoverflow.com/questions/9293042/mysqldump-without-the-password-prompt