My DigitalOcean Backup Scheme

DigitalOcean Droplets are so cheap for hobbyist developers, there is really nothing better out there starting at just $5 per month! For an extra dollar per month, they will even take routine backups of your DigitalOcean Droplet. But what if you need to restore one project to a week-old snapshot, but you don't want to restore ALL projects to a week-old snapshot?

That's where a scheduled backup script can really come in handy. These backup scripts dump each database to unique file names, for easy archival and also easy retrieval for another development environment. A notification email is also sent to a configurable email address.

The contents of my root user's crontab

  1. # database backup daily at 12:00am
  2. 0 0 * * * /root/Scripts/mysql_daily_backup.sh
  3.  
  4. # database backup monthly on 1st day of month at 12:10am
  5. 10 0 1 * * /root/Scripts/mysql_monthly_backup.sh
  6.  
  7. # webroot backup monthly on 1st day of month at 12:20am
  8. 20 0 1 * * /root/Scripts/www_monthly_backup.sh

Daily MySQL Backup Script

The root user crontab automates the process of backing up each system Mysql database to individual sql.gz files with the following script located at /root/Scripts/mysql_daily_backup.sh:

  1. #!/bin/bash
  2.  
  3. # Run this script on a daily cron to dump all databases
  4.  
  5. # Original script courtesy of Sonia Hamilton
  6. # http://www.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/
  7.  
  8. # Modified by EEJ to support:
  9. # - Email logging
  10. # - Rackspace Cloud Backup incremental backup
  11.  
  12. # Updated: 8/6/2015
  13.  
  14. # Set this to MySQL root user
  15. USER="root"
  16.  
  17. # Set this to MySQL root password
  18. PASSWORD="xxxxxxxx"
  19.  
  20. # mkdir these folders if they don't yet exist
  21. OUTPUTDIR="/var/backup/db"
  22.  
  23. # Formal Backup title
  24. BACKUPTITLE="JenkProd Daily Database Backup"
  25.  
  26. # Email address for log file
  27. MAILTO="ericjenkins@ericjenkins.net"
  28.  
  29. # From address
  30. MAILFROM="JenkProd <noreply@ericjenkins.net>"
  31.  
  32. # Output file format (prefixed by dbname, suffixed by .sql.gz)
  33. FILEDESC="_daily"
  34.  
  35. # Log file path
  36. LOGDIR="/root/log"
  37.  
  38. # Log file name format (suffixed by date.log)
  39. LOGFILEPREFIX="mysql_daily_backup_"
  40.  
  41. ###############################################################################
  42.  
  43. # Linux command paths
  44. MYSQLDUMP="$(which mysqldump) --single-transaction --routines --triggers --events"
  45. MYSQL="$(which mysql)"
  46. MAIL="$(which mail)"
  47.  
  48. # Get date in yyyymmdd format
  49. DATE="$(date +%Y.%m.%d)"
  50. LOGDATE="$(date +%m/%d/%Y)"
  51.  
  52. # Set a couple variables
  53. LOGFILE=$LOGFILEPREFIX""$DATE".log"
  54.  
  55. # Record start time/date
  56. TIME_START=`date +%s`
  57. DATE_START=`date +%c`
  58. echo -e "*** $BACKUPTITLE started at: $DATE_START ***" &gt;&gt; $LOGDIR/$LOGFILE
  59. echo -e "\nOutput directory: $OUTPUTDIR\n" &gt;&gt; $LOGDIR/$LOGFILE
  60.  
  61. # Get a list of databases
  62. DATABASES=`$MYSQL --user=$USER --password=$PASSWORD \
  63. -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
  64.  
  65. # Dump each database in turn
  66. for DB in $DATABASES; do
  67. OUTPUTFILE=$DB""$FILEDESC".sql.gz"
  68. OUTPUTFILEBAK=$DB""$FILEDESC".bak.sql.gz"
  69. echo -en "Creating $OUTPUTFILE ... " &gt;&gt; $LOGDIR/$LOGFILE
  70. mv $OUTPUTDIR/$OUTPUTFILE $OUTPUTDIR/$OUTPUTFILEBAK &gt;&gt; $LOGDIR/$LOGFILE
  71. $MYSQLDUMP --log-error=$LOGDIR/$LOGFILE --user=$USER \
  72. --password=$PASSWORD --databases $DB | gzip --rsyncable \
  73. &gt; $OUTPUTDIR/$OUTPUTFILE
  74. echo -e "Done." &gt;&gt; $LOGDIR/$LOGFILE
  75. done
  76.  
  77. # Delete files older than 2 days
  78. echo -e "\nDeleting files older than 2 days ..." &gt;&gt; $LOGDIR/$LOGFILE
  79. find $OUTPUTDIR/*$FILEDESC*.sql.gz -type f -mtime +2 \
  80. -exec rm -v {} \; &gt;&gt; $LOGDIR/$LOGFILE
  81. find $LOGDIR/$LOGFILEPREFIX* -type f -mtime +2 \
  82. -exec rm -v {} \; &gt;&gt; $LOGDIR/$LOGFILE
  83. echo -e "... Done." &gt;&gt; $LOGDIR/$LOGFILE
  84.  
  85. # Set File Permissions
  86. chgrp gitusers $OUTPUTDIR/*
  87. chgrp gitusers $LOGDIR/*
  88.  
  89. # List the contents of the output directory
  90. echo -e "\nContents of $OUTPUTDIR:" &gt;&gt; $LOGDIR/$LOGFILE
  91. ls -lah $OUTPUTDIR &gt;&gt; $LOGDIR/$LOGFILE
  92.  
  93. # Log system disk usage
  94. echo -e "\nSystem Disk Usage:" &gt;&gt; $LOGDIR/$LOGFILE
  95. df -h &gt;&gt; $LOGDIR/$LOGFILE
  96.  
  97. # Record script end time and time elapsed
  98. TIME_END=`date +%s`
  99. DATE_END=`date +%c`
  100. TIME_ELAPSED=$((TIME_END - TIME_START))
  101. echo -e "\n*** $BACKUPTITLE completed at: $DATE_END ***" &gt;&gt; $LOGDIR/$LOGFILE
  102. echo -e "Elapsed time:\t $TIME_ELAPSED seconds\n" &gt;&gt; $LOGDIR/$LOGFILE
  103.  
  104. # Send email of log file
  105. $MAIL -r "$MAILFROM" -s "$BACKUPTITLE completed on $LOGDATE" "$MAILTO" \
  106. &lt; $LOGDIR/$LOGFILE
  107.  
  108. </noreply@ericjenkins.net>

Source: MySQL database backup script - QWeb Ltd

Monthly MySQL Backup Script

  1. #!/bin/bash
  2.  
  3. # Run this script on a monthly cron to dump all databases
  4.  
  5. # Original script courtesy of Sonia Hamilton
  6. # http://www.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/
  7.  
  8. # Modified by EEJ to support:
  9. # - Email logging
  10. # - Append date to filename and delete after X days
  11.  
  12. # Updated: 8/6/2015
  13.  
  14. # Set this to MySQL root user
  15. USER="root"
  16.  
  17. # Set this to MySQL root password
  18. PASSWORD="xxxxxxxx"
  19.  
  20. # mkdir these folders if they don't yet exist
  21. OUTPUTDIR="/var/backup/db"
  22.  
  23. # Number of days to keep backups
  24. DAYSKEPT="150"
  25.  
  26. # Formal Backup title
  27. BACKUPTITLE="JenkProd Monthly Database Backup"
  28.  
  29. # Email address for log file
  30. MAILTO="ericjenkins@ericjenkins.net"
  31.  
  32. # From address
  33. MAILFROM="JenkProd <noreply@ericjenkins.net>"
  34.  
  35. # Output file format (prefixed by dbname, suffixed by [date].sql.gz)
  36. FILEDESC="_monthly_"
  37.  
  38. # Log file path
  39. LOGDIR="/root/log"
  40.  
  41. # Log file name format (suffixed by date.log)
  42. LOGFILEPREFIX="mysql_monthly_backup_"
  43.  
  44. ###############################################################################
  45.  
  46. # Linux command paths
  47. MYSQLDUMP="$(which mysqldump) --single-transaction --routines --triggers --events"
  48. MYSQL="$(which mysql)"
  49. MAIL="$(which mail)"
  50.  
  51. # Get date in yyyymmdd format
  52. DATE="$(date +%Y.%m.%d)"
  53. LOGDATE="$(date +%m/%d/%Y)"
  54.  
  55. # Set a couple variables
  56. LOGFILE=$LOGFILEPREFIX""$DATE".log"
  57.  
  58. # Record start time/date
  59. TIME_START=`date +%s`
  60. DATE_START=`date +%c`
  61. echo -e "*** $BACKUPTITLE started at: $DATE_START ***" &gt;&gt; $LOGDIR/$LOGFILE
  62. echo -e "\nOutput directory: $OUTPUTDIR\n" &gt;&gt; $LOGDIR/$LOGFILE
  63.  
  64. # Get a list of databases
  65. DATABASES=`$MYSQL --user=$USER --password=$PASSWORD \
  66. -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
  67.  
  68. # Dump each database in turn
  69. for DB in $DATABASES; do
  70. OUTPUTFILE=$DB""$FILEDESC""$DATE".sql.gz"
  71. echo -en "Creating $OUTPUTFILE ... " &gt;&gt; $LOGDIR/$LOGFILE
  72. $MYSQLDUMP --log-error=$LOGDIR/$LOGFILE --user=$USER \
  73. --password=$PASSWORD --databases $DB | gzip &gt; $OUTPUTDIR/$OUTPUTFILE
  74. echo -e "Done." &gt;&gt; $LOGDIR/$LOGFILE
  75. done
  76.  
  77. # Delete files older than DAYSKEPT
  78. echo -e "\nDeleting files older than $DAYSKEPT days ..." &gt;&gt; $LOGDIR/$LOGFILE
  79. find $OUTPUTDIR/*$FILEDESC*.sql.gz -type f -mtime +$DAYSKEPT \
  80. -exec rm -v {} \; &gt;&gt; $LOGDIR/$LOGFILE
  81. find $LOGDIR/$LOGFILEPREFIX* -type f -mtime +$DAYSKEPT \
  82. -exec rm -v {} \; &gt;&gt; $LOGDIR/$LOGFILE
  83. echo -e "... Done." &gt;&gt; $LOGDIR/$LOGFILE
  84.  
  85. # Set File Permissions
  86. chgrp gitusers $OUTPUTDIR/*
  87. chgrp gitusers $OUTPUTDIR/*
  88.  
  89. # List the contents of the output directory
  90. echo -e "\nContents of $OUTPUTDIR:" &gt;&gt; $LOGDIR/$LOGFILE
  91. ls -lah $OUTPUTDIR &gt;&gt; $LOGDIR/$LOGFILE
  92.  
  93. # Log system disk usage
  94. echo -e "\nSystem Disk Usage:" &gt;&gt; $LOGDIR/$LOGFILE
  95. df -h &gt;&gt; $LOGDIR/$LOGFILE
  96.  
  97. # Record script end time and time elapsed
  98. TIME_END=`date +%s`
  99. DATE_END=`date +%c`
  100. TIME_ELAPSED=$((TIME_END - TIME_START))
  101. echo -e "\n*** $BACKUPTITLE completed at: $DATE_END ***" &gt;&gt; $LOGDIR/$LOGFILE
  102. echo -e "Elapsed time:\t $TIME_ELAPSED seconds\n" &gt;&gt; $LOGDIR/$LOGFILE
  103.  
  104. # Send email of log file
  105. $MAIL -r "$MAILFROM" -s "$BACKUPTITLE completed on $LOGDATE" "$MAILTO" \
  106. &lt; $LOGDIR/$LOGFILE
  107.  
  108. </noreply@ericjenkins.net>

 Monthly Webroot Backup Script

  1. #!/bin/bash
  2.  
  3. # Create the directory specified by variable OUTPUTDIR
  4. # Then run this script on a daily cron to backup directory specified by INPUTDIR
  5.  
  6. # Original script courtesy of Sonia Hamilton
  7. # http://www.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/
  8.  
  9. # Modified by EEJ to support:
  10. # - Email logging
  11. # - Easier custom configuration
  12.  
  13. # Updated: 8/6/2015
  14.  
  15. # Path to parent directory of sites to archive
  16. INPUTDIR="/var/www"
  17.  
  18. # mkdir this folder if it doesn't yet exist
  19. OUTPUTDIR="/var/backup/www"
  20.  
  21. # Number of days of backups to keep
  22. DAYSKEPT="150"
  23.  
  24. # Formal Backup title
  25. BACKUPTITLE="JenkProd Monthly Webroot Backup"
  26.  
  27. # Email address for log file
  28. MAILTO="ericjenkins@ericjenkins.net"
  29.  
  30. # From address
  31. MAILFROM="JenkProd <noreply@ericjenkins.net>"
  32.  
  33. # Output file format (prefixed by foldername, suffixed by [date].tgz)
  34. FILEDESC="_monthly_"
  35.  
  36. # Log file path
  37. LOGDIR="/root/log"
  38.  
  39. # Log file name format (suffixed by date.log)
  40. LOGFILEPREFIX="www_monthly_backup_"
  41.  
  42. ################################################################################
  43.  
  44. # Linux command paths
  45. MAIL="$(which mail)"
  46.  
  47. # Get date in yyyymmdd format
  48. DATE="$(date +%Y.%m.%d)"
  49. LOGDATE="$(date +%m/%d/%Y)"
  50.  
  51. # Set a couple variables
  52. LOGFILE=$LOGFILEPREFIX""$DATE".log"
  53.  
  54. # Record start time/date
  55. TIME_START=`date +%s`
  56. DATE_START=`date +%c`
  57. echo -e "*** $BACKUPTITLE started at: $DATE_START ***" &gt;&gt; $LOGDIR/$LOGFILE
  58. echo -e "\nInput path: $INPUTDIR" &gt;&gt; $LOGDIR/$LOGFILE
  59. echo -e "Output directory: $OUTPUTDIR\n" &gt;&gt; $LOGDIR/$LOGFILE
  60.  
  61. # Compress and archive each site in turn
  62. # Split into 2GB parts if necessary
  63. FOLDERS=`ls $INPUTDIR | grep -v cgi-bin`
  64. for FOLDER in $FOLDERS; do
  65. OUTPUTFILE=$FOLDER""$FILEDESC""$DATE".tgz"
  66. echo -en "Creating $OUTPUTFILE ... " &gt;&gt; $LOGDIR/$LOGFILE
  67. tar -cz $INPUTDIR/$FOLDER | split -b 1920M - $OUTPUTDIR/$OUTPUTFILE"_"
  68. echo -e "Done." &gt;&gt; $LOGDIR/$LOGFILE
  69. done
  70.  
  71. # Delete files older than DAYSKEPT
  72. echo -e "\nDeleting files older than $DAYSKEPT days..." &gt;&gt; $LOGDIR/$LOGFILE
  73. find $OUTPUTDIR/*$FILEDESC* -type f -mtime +$DAYSKEPT \
  74. -exec rm -v {} \; &gt;&gt; $LOGDIR/$LOGFILE
  75. find $LOGDIR/$LOGFILEPREFIX* -type f -mtime +$DAYSKEPT \
  76. -exec rm -v {} \; &gt;&gt; $LOGDIR/$LOGFILE
  77. echo -e "... Done." &gt;&gt; $LOGDIR/$LOGFILE
  78.  
  79. # Set File Permissions
  80. chgrp gitusers $LOGDIR/*
  81.  
  82. # Log the contents of the output directory
  83. echo -e "\nContents of $OUTPUTDIR:" &gt;&gt; $LOGDIR/$LOGFILE
  84. ls -lah $OUTPUTDIR &gt;&gt; $LOGDIR/$LOGFILE
  85.  
  86. # Log system disk usage
  87. echo -e "\nSystem Disk Usage:" &gt;&gt; $LOGDIR/$LOGFILE
  88. df -h &gt;&gt; $LOGDIR/$LOGFILE
  89.  
  90. # Record script end time and time elapsed
  91. TIME_END=`date +%s`
  92. DATE_END=`date +%c`
  93. TIME_ELAPSED=$((TIME_END - TIME_START))
  94. echo -e "\n*** $BACKUPTITLE completed at: $DATE_END ***" &gt;&gt; $LOGDIR/$LOGFILE
  95. echo -e "Elapsed time:\t $TIME_ELAPSED seconds\n" &gt;&gt; $LOGDIR/$LOGFILE
  96.  
  97. # Send email of log file
  98. $MAIL -r "$MAILFROM" -s "$BACKUPTITLE completed on $LOGDATE" "$MAILTO" \
  99. &lt; $LOGDIR/$LOGFILE
  100.  
  101. </noreply@ericjenkins.net>