Script to Backup MySQL Databases

Struggling with PHP or HTML? Got any tips? Drop in here to share your questions and answers. Newbies and gurus welcome!
Post Reply
User avatar
Paul
Development Team Leader
Development Team Leader
Posts: 1132
Joined: October 20th, 2007, 2:23 pm

Script to Backup MySQL Databases

Post 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.
User avatar
sweety
Rank 0
Rank 0
Posts: 3
Joined: May 9th, 2011, 5:07 am

Re: Script to Backup MySQL Databases

Post by sweety »

nice sharing......
User avatar
belindara
Rank 0
Rank 0
Posts: 1
Joined: January 16th, 2012, 1:52 am

Re: Script to Backup MySQL Databases

Post 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?
User avatar
Angel09
Rank 0
Rank 0
Posts: 1
Joined: March 12th, 2012, 3:53 am

Re: Script to Backup MySQL Databases

Post 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.
User avatar
gsamuel
Rank 0
Rank 0
Posts: 3
Joined: March 14th, 2012, 7:24 pm

Re: Script to Backup MySQL Databases

Post 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.
User avatar
bigbody
Rank 0
Rank 0
Posts: 3
Joined: April 21st, 2012, 12:08 pm

Re: Script to Backup MySQL Databases

Post by bigbody »

thanks for sharing i need it for my db and i have a complet backup of mydb thanks :)
Post Reply