≡ Menu

mysql backup

Rotate FTP Backup Using a Shell Script

I've already written about rotating sftp / ssh backup shell script to remove directories (old backup files). However, a few of our readers would like to know more about removing old backup directories using ftp. As usual, you need accurate date and time on local system and remote backup directory must be in dd-mm-yyyy or mm-dd-yyyy format. For example daily mysql backup should be stored in /mysql/dd-mm-yyyy format.

Sample Shell Script

Here is a simple and dirty shell script to remove old backups ( download link ):

#!/bin/bash
# call ./script.sh 03-2007 - to remove all March-2007 directories in 01-03-2007, 02-03-2007, 31-03-2007 format
# you must have ncftp ftp client installed on BSD / Linux box
BASE="/mysql" # base dir below that dd-mm-yyyy
[ $# -eq 0 ] && exit 1 || :
DELETE="$1"
echo "Getting old directories..."
ncftpls -u 'ftp-user-name' -p 'ftp-password' -x "-t" ftp://ftp.your-server.com${BASE} > /tmp/ftp.out
LIST="$(grep ${DELETE} /tmp/ftp.out)"
echo -n "Starting removal for ${DELETE}..."
for dir in $LIST
do
 rdir="${BASE}/${dir}"
# echo "Processing ${dir}..."
 ncftp -L -u 'ftp-user-name' -p 'ftp-password' ftp.your-server.com <<EOF
 cd $rdir
 rm *
 rmdir $rdir
 quit
EOF
done

Run the script as follows to remove all backup for Dec-2007, enter:
$ ./script.sh 12-2007

Related: Generate backup ftp script using php based wizard

Most time you have a limited space on the remote SFTP/ SSH backup server. Here is the script that periodically cleanup old backup files from the server i.e it will remove old directories.

Requirements

Script will automatically calculate date from today's date. By default it will keep only last 7 days backup on server. You can easily increase / decrease this limit. In order to run script you must meet the following criteria:

  • Remote SSH server with rm command execution permission
  • SSH Keys for password less login (see how to setup RSA / DSA keys for password less login)
  • Accurate date and time on local system (see how to synchronize clock using ntpdate ntp client)
  • Remote backup directory must be in dd-mm-yyyy or mm-dd-yyyy format. For example daily mysql backup should be stored in /mysql/mm-dd-yyyy format.

Sample Script Usage

Run the script as follows:
./rot.backup.sh 7 /mysql "rm -rf"
Where,

  • 7 : Remove last 7 days files
  • /mysql : Base directory to clean up. If todays date is 9/Oct/2007, it will remove last 7 days directory /mysql/02-10-2007, /mysql/01-10-2007, .... /mysql/26-09-2007, /mysql/25-09-2007. It means script will only keep last 7 days backup on remote sftp / ssh server.
  • rm -rf : Command to run on directory structure

Sample Shell Script

Install following script:

#!/bin/bash
if [ "$#" == "0" ];then
  echo "$0 upper-limit path {command}"
  exit 1
fi
### SSH Server setup ###
SSH_USER="vivek"
SSH_SERVER="nas.nixcraft.in"
START=7
DIR_FORMAT="%d-%m-%Y" # DD-MM-YYYY format
#DIR_FORMAT="%m-%d-%Y" #MM-DD-YYYY format
## do not edit below ##
LIMIT=$( expr $START + $1 )
## default CMD ##
CMD="ls"
SSH_PATH="."
[ "$3" != "" ] && CMD="$3" || :
[ "$2" != "" ] && SSH_PATH="$2" || :
DAYS=$(for d in $(seq $START $LIMIT);do date --date="$d days ago" +"${DIR_FORMAT}"; done)
for d in $DAYS
do
  ssh ${SSH_USER}@${SSH_SERVER} ${CMD} ${SSH_PATH}/$d
done

Run above script via cron tab (cronjob):
@daily /path/to/rot.ssh.script 7 "/html" "rm -rf"
@daily /path/to/rot.ssh.script 7 "/mysql" "rm -rf"

I’ve already written about MySQL backup using a shell script and consistent backup with Linux Logical Volume Manager (LVM) snapshots:

A snapshot volume is a special type of volume that presents all the data that was in the volume at the time the snapshot was created. This means you can back up that volume without having to worry about data being changed while the backup is going on, and you don’t have to take the database volume offline while the backup is taking place.

Niclas has posted a nice howto about consistent MySQL backups using Solaris UNIX ZFS snapshots:

In this article I will show you how to install MySQL on a ZFS file system and supply you with a script to make consistent snapshots of the databases. This script may not be 100% fit for busy sites but for most smaller places I think it is perfect.