Magento DB naming and easy backup


Introduction

I recently revised my Magento server directory structures and decided I would automate my backup process and cleanup process so that my Database backup structure was both consistent and would automatically be backed up when I created a new installation of a site/db or installed a new Magento software version on my Magento App server.

The first step was to adopt a logical naming convention for every Database, I chose to use the following format…

mg-<code version>-<storecode>-<environment>

So for those who are now confused, some examples:

  • mg-17-td-dev
    • “mg-” the prefix for the magento DB
    • “17” – v1.7 of the code base,
    • “td” – is the store code and (i.e.  dev.testdomain.com = “td”)
    • “dev” – is the development environment.
  • mg-14-rd-prod
    • “mg-” the prefix for the magento DB
    • “14” – v1.4 of the code base,
    • “rd” – is the store code and
    • “prod” – is the production environment.

By using “mg-” as the prefix, I can now get a list of the databases in a shell script and pass these to a function that then backups each DB twice a day everyday so I have copies in the event of a crash, I also have a simple way to identify both the DB, the store its for and the environment.The command to get a list of DB’s is below:

mysqlshow | grep mg | tr -d '\|' | tr -d ' '

Automatic backups

To perform the automatic backups I use the following script. it includes sufficient logging to a file in “/logs” which I use religiously to make sure everything is running correctly, and when it does not I have a log to help out.

The script creates a compressed SQL file in the /data/db-backups directory that is date and time stamped for each DB. I run the script as the root,  from root’s CRONtab and make sure root has full access to the DB server.

#!/bin/bash
#
# use mysqldump to Dump DB and compress it on the fly to a mounted partition
#
BACKUP_DIR="/data/db-backups"
mkdir -p $BACKUP_DIR
chmod 777 $BACKUP_DIR
SERIAL="`date +%Y%m%d-%H%M%S`"
#=====================================
# Log Functions
#
function LogStart
{
echo "====== Log Start =========" >> $LF
echo "Time: `date`" >> $LF
echo " " >> $LF
}
function LogEnd
{
echo " " >> $LF
echo "Time: `date`" >> $LF
echo "====== Log End   =========" >> $LF
}

#=====================================
#
#
function GetDBList
{
echo "Calling GetDBList()" >> $LF
mysqlshow | grep mg | tr -d '\|' | tr -d ' ' > $DBLIST
}

#=====================================
#
#
function DoBackup
{
echo "Calling DoBackup()" >> $LF

DBFILE=$BACKUP_DIR/db-$DB-$SERIAL.sql
echo "Host [$H]" >> $LF
echo "DB File [$DBFILE]" >> $LF
if [ -a  $DBFILE ]
then
mv $DBFILE $DBFILE.`date '+%M%S'`
fi
echo "Dumping ${DB}" >> $LF
mysqldump -B ${DB}  --add-drop-database --add-drop-table >> ${DBFILE}
echo "grant all privileges on ${DB}.* to magento;" >> ${DBFILE}
echo "Zipping up file!" >> $LF
gzip ${DBFILE}
echo "Done!" >> $LF
}

FILE_DATE=`date '+%Y-%m-%d'`
LF_DIR=/logs/db-backup
LF=$LF_DIR/db-backup-$FILE_DATE.log
mkdir -p $LF_DIR
chmod 777 $LF_DIR
touch $LF
chmod 664 $LF

DBLIST=/tmp/dblist-$FILE_DATE.list

LogStart

#=====================================
#
# MAIN Code Start

GetDBList
while read line
do
echo "Backuping up: $line"
H="localhost"
DB=$line
DoBackup
done < $DBLIST
echo "All backups Completed" >> $LF
LogEnd
#
# EOF

This concept of naming Databases and the script above has proven to give me great flexibility when building “dev” / “test” and “demo” environments and domains for my sites before they go live in the “prod” environment.

Don’t forget to read my Article on “Magento Multi-store Apache configuration”

Enjoy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s