I’ve been working on ways to automatically backup the database for the website I’m currently developing (therapyhourstracker.com (set to be released in May or June 2010)). After hunting around for useful ideas (including some great help from SHELLdorado) this is what I came up with:
mysqldump -u username -ppassword -h www.databasehost.com --all-databases | gzip > /home/user/backups/mysql_`date +"%Y-%m-%d"`.sql.gz echo | mutt -a backups/mysql_`date +"%Y-%m-%d"`.sql.gz -- [email protected]
You’ll need to replace the generic names with your specifics (In the first line: username, password, www.databasehost.com with the login information for your mysql database. /home/user/backups should become an existing directory on your server where you want to store the dumped files. In the second line backups/ should be the same directory on your server that you saved the dumped file to and [email protected] should be replaced with your email address).
The first line dumps and gzips the database into a file with today’s date in the name (line ends with the first ‘.sql.gz’ and a carriage return). The second line uses mutt to send me an email with the dumped file as an attachment.
Doesn’t get much cooler than that.