Magento DB naming and easy backup


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. = “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.

# use mysqldump to Dump DB and compress it on the fly to a mounted partition
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

echo "Host [$H]" >> $LF
echo "DB File [$DBFILE]" >> $LF
if [ -a  $DBFILE ]
mv $DBFILE $DBFILE.`date '+%M%S'`
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'`
mkdir -p $LF_DIR
chmod 777 $LF_DIR
touch $LF
chmod 664 $LF



# MAIN Code Start

while read line
echo "Backuping up: $line"
done < $DBLIST
echo "All backups Completed" >> $LF

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”


Leave a Reply

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

You are commenting using your 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