{"id":325,"date":"2010-07-18T12:16:06","date_gmt":"2010-07-18T17:16:06","guid":{"rendered":"http:\/\/chrismeyer.org\/wordpress\/?p=325"},"modified":"2010-07-18T12:16:06","modified_gmt":"2010-07-18T17:16:06","slug":"dreamhost-how-to-use-cronjobs-to-email-backup-mysql-databases","status":"publish","type":"post","link":"https:\/\/chrismeyer.org\/wordpress\/?p=325","title":{"rendered":"Dreamhost How to Use Cronjobs to Email Backup MySQL Databases"},"content":{"rendered":"

I’ve been using Dreamhost for a while (works great btw), I had been backing things up manually but I thought it was about time to start automated mysql backups. Looking around online I found these instructions<\/a> but they simply say to modify the instructions and add this mutt command<\/p>\n

mutt you@domain.com -a \/home\/username\/backups\/archives\/mysql_backup.$suffix.tar -s \"MySQL Backup\"<\/code><\/p>\n

to get e-mail backups working. In reality, this command will merely open up mutt and create a new message but it will not automatically send it without additional input. Buried online I found someone else who said to simply:<\/p>\n

mutt you@domain.com -s \"MySQL Backup\" < \/home\/username\/backups\/archives\/mysql_backup.$suffix.tar<\/code><\/p>\n

which does automatically e-mail you, but it just dumps the file into the e-mail body without attaching it as a document. What that statement is really doing is piping in body text and completing the e-mail prompts magically, which isn’t what I wanted to do. The solution is to:<\/p>\n

mutt you@domain.com -a \/home\/username\/backups\/archives\/mysql_backup.$suffix.tar -s \"MySQL Backup\" < emailbody.txt<\/code><\/p>\n

What this gives you is an attachment of your file, and then it pipes in an e-mail body (put it in the same location that this script is running from) and sends the message. You would also use echo statements to “type” whatever additional input you wanted in mutt and script the rest of the message completion [untested but it conceptually should work, YMMV]<\/p>\n

Full Script:
\n
\n#!\/bin\/bash
\ncd \/home\/username\/backups\/
\nmkdir mysql
\nsuffix=$(date +%y%m%d)
\nmysqldump --opt -uUser -ppass -h mysqlA.domain.com db_nameA > mysql\/db_nameA.$suffix.sql
\nmysqldump --opt -uUser -ppass -h mysqlB.domain.com db_nameB > mysql\/db_nameB.$suffix.sql
\ntar -cf archives\/mysql_backup.$suffix.tar mysql\/*
\nmutt you@domain.com -a \/home\/username\/backups\/archives\/mysql_backup.$suffix.tar -s \"MySQL Backup\" < emailbody.txt
\nrm -r mysql\/
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

I’ve been using Dreamhost for a while (works great btw), I had been backing things up manually but I thought it was about time to start automated mysql backups. Looking around online I found these instructions but they simply say to modify the instructions and add this mutt command mutt you@domain.com -a \/home\/username\/backups\/archives\/mysql_backup.$suffix.tar -s “MySQL […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-325","post","type-post","status-publish","format-standard","hentry","category-webdevelopment"],"_links":{"self":[{"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=325"}],"version-history":[{"count":3,"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/325\/revisions"}],"predecessor-version":[{"id":328,"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/325\/revisions\/328"}],"wp:attachment":[{"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chrismeyer.org\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}