Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to Backup Mysql Databases Script
#1

This script will backup your mysql databases, gzip them up, ftp them off site, email you confirmation. For emailing you you have to create a file with the text for the body of the email. The email file used in this script is

 

Quote:/usr/local/logs/sqlbackup
sqlbackup could have something like this in it. 

Quote:Mysql database backup was successfully completed. Your a demigod!
 

To do several databases just do like this to backup 4 databases named db1 - db4

 

Quote:databases="db1 db2 db3 db4"
The Actual Script!!! 

Quote:#!/bin/sh 

# This script will backup one or more mySQL databases

# and then optionally email them and/or FTP them

 

# This script will create a different backup file for each database by day of the week

# i.e. 1-dbname1.sql.gz for database=dbname1 on Monday (day=1)

# This is a trick so that you never have more than 7 days worth of backups on your FTP server.

# as the weeks rotate, the files from the same day of the prev week are overwritten.

 

############################################################

#===> site-specific variables - customize for your site

 

# List all of the MySQL databases that you want to backup in here,

# each seperated by a space

databases="<db name here>"

 

# Directory where you want the backup files to be placed

backupdir=/usr/local/backup

 

# MySQL dump command, use the full path name here

mysqldumpcmd=/usr/local/mysql/bin/mysqldump

 

# MySQL Username and password

userpassword=" --user=<username here> --password=<password here>"

 

# MySQL dump options

dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

 

# Unix Commands

gzip=/bin/gzip

uuencode=/usr/bin/uuencode

mail=/bin/mail

 

# Send Backup?  Would you like the backup emailed to you?

# Set to "y" if you do

sendbackup="y"

subject="mySQL Backup"

mailto="<email here>"

 

#===> site-specific variables for FTP

ftpbackup="n"

ftpserver="<ftp server here>"

ftpuser="<login here>"

ftppasswd="<pass here>"

# If you are keeping the backups in a subdir to your FTP root

ftpdir="forums"

 

#===> END site-specific variables - customize for your site

############################################################

 

# Get the Day of the Week (0-6)

# This allows to save one backup for each day of the week

# Just alter the date command if you want to use a timestamp

DOW=`date +%C%y%m%d`

 

# Create our backup directory if not already there

mkdir -p ${backupdir}

if [ ! -d ${backupdir} ]

then

echo "Not a directory: ${backupdir}"

exit 1

fi

 

# Dump all of our databases

echo "Dumping MySQL Databases"

for database in $databases

do

$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${DOW}-${database}.sql

done

 

# Compress all of our backup files

echo "Compressing Dump Files"

for database in $databases

do

rm -f ${backupdir}/${DOW}-${database}.sql.gz

$gzip ${backupdir}/${DOW}-${database}.sql

done

 

# Send the backups via email

if [ $sendbackup = "y" ]

then

for database in $databases

do

#      $uuencode ${backupdir}/${DOW}-${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu

#      $mail -s "$subject : $database" $mailto < ${backupdir}/${DOW}-${database}.sql.gz.uu

$mail -s "$subject : $database" $mailto </usr/local/logs/sqlbackup

  done

fi

 

# FTP it to the off-site server

echo "FTP file to $ftpserver FTP server"

if [ $ftpbackup = "y" ]

then

for database in $databases

do

      echo "==> ${backupdir}/${DOW}-${database}.sql.gz"

ftp -n $ftpserver <<EOF

user $ftpuser $ftppasswd

bin

prompt

cd $ftpdir

lcd ${backupdir}

put ${DOW}-${database}.sql.gz

quit

EOF

done

fi

 

# And we're done

ls -l ${backupdir}

echo "Dump Complete!"

exit
 

Note to attach a backup to your email you have to use "uuencode" which I personally do not do as I would rather not attach several megs or hundreds of megs to my email.

 

Strabo

Reply
#2

can this ftp to a ftp server using implicit SSL on a non standard port ?

 

if so how,

 

excellent tutorial by the way strabo :)

 

cheers

 

anyweb

Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)