Jump to content

How to Backup Mysql Databases Script


Recommended Posts

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

 

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

 

Mysql database backup was successfully completed. Your a demigod!

 

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

 

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

 

#!/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

Link to post
Share on other sites

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

Link to post
Share on other sites
Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...