Backing up SugarCrm and MySql

When running SugarCrm in a production environment, you need to do backup. There are many opinions about how to do backup:

Only wimps use tape backup: real men just upload their important stuff on ftp, and let the rest of the world mirror it ;)

(Torvalds, Linus (1996-07-20). Post. linux.dev.kernel newsgroup. Google Groups. Retrieved on 2006-08-28.)

However the above might not be what your boss want, so we need to schedule backup.

Backing up SugarCrm is basically 2 separate operations.

1. zipping  up your working site directory structure
2. dumping the associated SugarCrm database

On a Linux system #1 can be done with the tar command (or any other file packer of preference).

tar -czf SITE_DIRECTORY.tar.gz SITE_DIRECTORY

For #2 it depends on the database that you are using. In the remaining part of this posting I will be using MySQL and InnoDB as storage engine.

There are numerous scopes for backing up a database:

• full backup
• incremental backup
• block level backup

Normally I prefer the full backup option combined with a block level backup plan for incremental backup.

Simple full backup can be controlled using AutoMySQLBackup. There are however additional parameters that I like to add to the mysqldump command used inside the script. If you are using any kinds of functions/stored procedures in the database, then you want to add --triggers --routines to the mysqldump command. I also like to use the --single-transaction option on InnoDB storage engine (this is also mentioned here as an upcoming feature for AutoMySQLBackup).

The above mentioned options/parameters will change the lines in the script from 

mysqldump --user=XXX -- password=YYY ... 

to

mysqldump --user=XXX -- password=YYY --triggers --routines --single-transaction ...

For incremental backup please see the ibbackup command or the mysqlbackup command.

The majority of our servers are running on Amazon EC2. We use a combination of running full backups nightly as well as Amazon Elastic Block Store (EBS) (block level backup) snapshots through the day. This requires a slightly special setup with regards to your filesystem of choice. You can read more at Creating Consistent EBS Snapshots with MySQL and XFS on EC2 or if you prefer CentOS then you can have a look at my blog posting Setting up Centos 5.6 with PHP 5.3 on Amazon EC2 with ec2-consistent-snapshot.