Page 1 of 1

Script to Backup MySQL Databases

Posted: October 27th, 2007, 8:47 am
by Paul
Just copy the code and save it into a text file on your server (Let's call it /usr/local/sbin/dumpdatabases, but feel free to place it in any directory in your path, and call it whatever you want). Just make sure you change the list of databases at the top to reflect the ones you want to backup, and change the username and password for MySQL. Most other things should be ok, but you might need to change the pathnames if different on your system. Also make sure you do a chmod +x /usr/local/sbin/dumpdatabases to make it executable.

Code: Select all

#!/bin/sh

# List all of the MySQL databases that you want to backup in here, 
# each seperated by a space
databases="mysql forum"

# Directory where you want the backup files to be placed
backupdir=/backup

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/local/mysql/bin/mysqldump

# MySQL Username and password
userpassword=" --user=root --password=password"

# 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="n"
subject="Your MySQL Backup"
mailto="you@yourdomain.com"

# 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}/${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
   rm -f ${backupdir}/${database}.sql.gz
   $gzip ${backupdir}/${database}.sql
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
   for database in $databases
   do
      $uuencode ${backupdir}/${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
      $mail -s "$subject : $database" $mailto < ${backupdir}/${database}.sql.gz.uu
   done
fi

# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit
If you want to run this automatically through cron (recommended so you don't forget): just do a crontab -e and add the following line:

Code: Select all

0 3 * * * /usr/local/sbin/dumpdatabases
This will run it everyday at 3:00am. Feel free to change the time to something else if your databases aren't as busy at some other time.

Re: Script to Backup MySQL Databases

Posted: May 9th, 2011, 5:11 am
by sweety
nice sharing......

Re: Script to Backup MySQL Databases

Posted: January 17th, 2012, 6:12 am
by belindara
How does a database driven website operate files in the back end? I am trying to design a website with a very heavy database. Does anyone know how this works?

Re: Script to Backup MySQL Databases

Posted: March 12th, 2012, 4:00 am
by Angel09
Mysql backup , support for all MySQL character sets, store engines, and field and table types, ability to backup data to SQL files, PHP files, different servers, or different databases, and an easy-to-use interface. Typically, a software program intended to automatically backup and restore MySQL databases is a self-contained utility. When you initially run it, you'll be presented with a simple set-up wizard to guide you through choosing presets and schedules.

Re: Script to Backup MySQL Databases

Posted: March 15th, 2012, 12:24 am
by gsamuel
Thank you for this. I was actually searching on how I could pull something similar for my implementation but was rather sidelined because of the lack of any deep technical knowledge with programming.

In hindsight, a backup could look so simple but it spells how your website will be able to recover from a crash that will prove to be very hard to get over with. Automatic backups will really work to your advantage this time.

Re: Script to Backup MySQL Databases

Posted: April 21st, 2012, 12:11 pm
by bigbody
thanks for sharing i need it for my db and i have a complet backup of mydb thanks :)