Automatic Database Backup Script

from here.

Here is a DB backup that I use for daily automatic DB backups. This script can backup multiple DBs at the same time. The script below is set to backup 2 DBs. If you want to backup more just copy and paste the bottom section and change info to match that DB. If you just want to backup just 1 DB then delete the second section of the bottom section of the script.The functions of the script are commented in the script.

Please let me know if you need help setting this DB backup script up or have any questions.
Create a folder on the root of your web space called backups (don't place this in the public_html directory. It belongs just about that in the directory structure)
Create a folder named database_backups in the backups folder you just created.
create a folder named the same as your DB in the database_backups folder you just created.
Replace all of the text in the script enclosed in " " with your specific info.
Save the db_dump script and place in the backups folder you created.
CHMOD (permission) all files and folders in the backup directory to 700 including the backup directory
Creat a CRON job in cPanel to execute this script. Go to the CRON tab in cPanel and click on advanced. Place this text in the command line replacing ("accountname") /home/"accountname"/backups/db_dump.txt
Change the CRON time to run at your desired time and press commit changes


#==============================================
#Save the script below as db_dump.txt
#==============================================


#!/bin/bash

# Script Created by Fernis
# Last edited 8/25/07 by Fernis

# Script Function:
# This bash script backups up the "DBname" and "DBname" db everyday at 4am with a file name time stamp and tar.gz zips the file. 
# The "DBname" db will be saved in /backups/database_backups/"DBname"/
# The "DBname" db will be stored in /backups/database_backups/"DBname"/
# Db backups older than 30 days will be deleted.

#[Changes Directory]
cd /home/"accountname"/backups/

#[Old DB Deletion Script]
find /home/"accountname"/backups/database_backups -name "*.tar.gz" -mtime +30 -exec rm -f {} \;

#[Stamps the file name with a date]
TIMESTAMP=`date +%m-%d-%y-%H%M`


#[DB Backup Scripts]

# "DBname"
HOST=localhost
DBNAME="DBname"
USER="DBusername"
PASSWORD="DBpassword"
DUMP_PATH=/home/"accountname"/backups/database_backups/"DBname"
mysqldump --opt -c -e -Q -h$HOST -u$USER -p$PASSWORD $DBNAME > $DBNAME.sql
tar czpf $DUMP_PATH/$DBNAME.$TIMESTAMP.tar.gz $DBNAME.sql
rm -f $DBNAME.sql

# "DBname"
HOST=localhost
DBNAME="DBname"
USER="DBusername"
PASSWORD="DBpassword"
DUMP_PATH=/home/"accountname"/backups/database_backups/"DBname"
mysqldump --opt -c -e -Q -h$HOST -u$USER -p$PASSWORD $DBNAME > $DBNAME.sql
tar czpf $DUMP_PATH/$DBNAME.$TIMESTAMP.tar.gz $DBNAME.sql
rm -f $DBNAME.sql


#=======================================
#END OF BACKUP SCRIPT
#=======================================

Now to show you how to restore the DB.

Restoring Your Database Backup

To restore your database, simply extract the database file you want ( tar zxpf database_name.TIMESTAMP.tar.gz ) and then import the mysql file:

Shell Example:

mysql -u MYSQL_USER -p 'MYSQL_PASSWORD' DATABASE_NAME < database_name.TIMESTAMP.sql

Now, if you get any errors, you might need to drop the tables inside the database first, however, our dump command takes care of the DROP TABLE statements for you.

Leave a Comment

Before leaving a comment, please ensure you have read and understand my comments policy and my privacy policy. Any comment that does not abide by the comment policy will be deleted immediately.