[rt-users] Backing up RT

[Konstantin Khomoutov]

On Wed, 05 Jan 2011 07:36:46 -0500
Andy Graybeal <andy.graybeal at casanueva.com> wrote:

>> We use mysqldump and logrotate to keep a week of DB dumps on the
>> local filesystem, we perform daily backups to a VTL and then tape,
>> and we also perform nightly SAN snapshots on the RT host's LUN
>> (making sure to lock the databases before the snapshot, and then
>> unlocking after so the MySQL DB files are in a consistent state
>> during the snap).
[...]
> If I ever ended up doing LVM snaps, do I need to lock the db first... 
> or can I get away with not locking the db with LVM snaps?
In this case locking is less important than flushing as you would be
backing up the filesystem and hence you will need the on-disk database
representation to be consistent before taking the snapshot. To do this,
you either have to stop the server just before taking a snapshot, or
write a complicated script which would work just like mysqlhotcopy but
would take the snapshot instead of copying the data.
Note that mysqlhotcopy explicitly states it works only with MyISAM and
ARCHIVE engines, and the comments on [1] hint that backing up InnoDB
this way is at least tricky and error-prone.

> You use logrotate to manage your mysqldumps!  Excellent idea.
> I need to learn logrotate.  I was wondering how I can keep mysqldumps 
> from each day and not lose control of them.  I'm excited.
One commonly used straightforward approach is to encode the formatted
timestamp into the names of generated backup files, like this:
$ mysqldump ... | gzip -c >/path/to/db-backup-$(date +'%F-%T').sql.gz

1. http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html