This is a quick blog post to share with you how to do set up a son, father and grandfather database backup system on your ubuntu based server. I adapted this code from Alex Saavedra’s original post back in 2011 intending to dump everything.
This system I set up runs from /var/databases/ a directory I created just for database backups, I also created /var/databases/database.list to hold the databases to be backed up.
In my implementation I backup every day, month and year and this is transferred to a DSM backup server which then transfers the files to a google drive, so we have 3 copies.
I needed to adopt his post and have a system in which we can add as many databases as we wish (or remove them) and have them backup. As I said I am short on time so I will just share the code. You will need to create a couple of scripts in /usr/local/bin using nano or vim – here is the first (the actual backup script) I created /usr/local/bin/backupdb
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
#!/bin/bash # Created by: Alex Saavedra # Modified by: Stephen Speakman <www.stephenspeakman.co.uk> # Date created: 2011-05-11 # Last modified: 2016-10-28 # Purpose: Son, Father & Grandfather Backup Model. # Change log: # Syntax: # backup.sh <periodicity> <period> # Parameters: # <periodicity> = weekly, monthly, yearly # <period> = specific week day, month or year # Help SHELL=/bin/bash PATH=/root/.linuxbrew/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games export DISPLAY=:0.0 HELP="The backup is invoked as follows:n" HELP=$HELP" /root/backup.sh <type> <periodicity>n" HELP=$HELP"where:n" HELP=$HELP" periodicity = weekly, monthly, yearlyn" HELP=$HELP" period = specific day, month or yearn" fileItemString=$(cat /var/databases/database.list |tr "\n" " ") fileItemArray=($fileItemString) for i in "${fileItemArray[@]}" do # Make directory if it doesn't exist mkdir -p /var/databases/$i # Destination backup file construct. DST=/var/databases/$i/backup_$1_$2.tgz # SQL dump file name construct mkdir -p /tmp/$i SQL=/tmp/$i/backup_$1_$2.sql if [ -z "$2" ] then # In recent distributions you may not need the -e parameter. echo -e $HELP exit 1 fi # Start the MySQL dump echo "Starting $i date and time: $(date)" mysqldump --opt --add-drop-table -B -u dbbackup -pyourpassword --result-file=$SQL $i # Source file construct SRC=/opt/backups/$SQL # actual backup tar -cvzf $DST $SQL > /dev/null # Remove tmp file rm $SQL echo "Finished $i date and time: $(date)" echo "The backup file $DST was successfully created." ls -lh $DST done |
I then created a script named adddb in which you may add a database to your list for backup by using the syntax ‘addb ‘
|
1 2 3 4 5 6 7 8 |
#!/bin/bash filename=/var/databases/database.list value=$1 echo "$value" >> /var/databases/database.list echo "" echo "$value has been added to the database list." |
I also made a script to remove databases from the list for convenience ‘removedb ‘
|
1 2 3 4 5 6 7 8 9 10 11 |
#!/bin/bash filename=/var/databases/database.list pattern=$1 sed -i "/^$pattern/d" "$filename" #echo "sed -i '/^$pattern/d' '$filename'" echo "" echo "$pattern has been removed from the database list. BEWARE, this is a partial match." echo "removing mysite will remove both 'mysite' and 'mysite-testing' from the list." |
Following this I finally made a script to list the databases currently being backed up ‘listdbs’
|
1 2 |
#!/bin/bash cat /var/databases/database.list |
Now following that you also need to set up the cron jobs to run the scripts, the original article didn’t work for me, a bit of debugging shown that the date parameters were invalid for my version of ubuntu. I fixed the cron jobs and they are as below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
# Edit this file to introduce tasks to be run by cron. # # Each task to run has to be defined through a single line # indicating with different fields when the task will be run # and what command to run for the task # # To define the time you can provide concrete values for # minute (m), hour (h), day of month (dom), month (mon), # and day of week (dow) or use '*' in these fields (for 'any').# # Notice that tasks will be started based on the cron's system # daemon's notion of time and timezones. # # Output of the crontab jobs (including errors) is sent through # email to the user the crontab file belongs to (unless redirected). # # For example, you can run a backup of all your user accounts # at 5 a.m every week with: # 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/ # # For more information see the manual pages of crontab(5) and cron(8) # # m h dom mon dow command SHELL=/bin/bash PATH=/root/.linuxbrew/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games ## # Son, Father & Grandfather Database Backups ## 00 1 * * * /usr/local/bin/backupdb weekly `date +"\%u"` > /tmp/cron.log 2>&1 00 1 1 * * /usr/local/bin/backupdb monthly `date +"\%m"` > /tmp/cron.log 2&>1 00 3 1 1 * /usr/local/bin/backupdb yearly `date +"\Y"` > /tmp/cron.log 2&>1 |