Script For Automatic Database Backup

24
Nov
2013

Nothing is worse than losing your Database or webpage files because of system failure or VPS you are renting just thought, it will be great to deleta all your files just for fun- This acctually happened to me and my friend and many more which choose to rent a cheap Virtual Privatre Server for educational experience or just to host small webpages. We all got no warning, that they are going to delete our server and we didn't have chance to make backup- names of these VPS providers are- 
http://ssdvps.com/
http://urpad.net/
and many more, so be very carefull when choosing VPS provider.

But that is not the case, I want to share simple Bash cronjob script for doing database backup. There are more complicated scripts, which even can send dumps to your email, but since 1 of my database is ~50 MB I don't think that is something I want to do.

So- make simple .sh file, name it whatever you want and add those line to it.

#!/bin/bash
NOW=$(date +"%Y-%m-%d")
mysqldump -u User -h localhost -pPassword DBname > /path/to/place/where/we/want/to/save/filename-$NOW.sql

so, what does everything do?

NOW=$(date +"%Y-%m-%d") - simply creates variable $NOW, which contains date in format yyyy-mm-dd

User - Is databese user, you can use root, any user that have access to database.

localhost is just place where is hosted your databse, you can use kandrejevs.com:3306  for remote backups- e.g. you want to do backups from your laptop and save files on it.

Password - Is your database user password, by default password is asked in terminal, but since we want script to execute without us typing password- we add it in script. There is no space after -p

DBname is just database name, e.g. for this site it is kandrejevs.

after > is just path where we want to save SQL file and filename.sql, the $NOW variable simply adds timestamp to file names, so backups doesn't overwrite old ones- important, because if there will be empty databse, your backup files will be overwritten with empty ones and there wouldn't be point of having backup.

 

So, we have our script now, what to do to execute? Crontab ofcourse.

http://en.wikipedia.org/wiki/Cron

just add this line in end of your crontab:

0 8 * * Mon sh /path/to/script/filename.sh

And that is it, script will be executed every Monday at 8AM.