On Wed, 05 Jan 2011 07:36:46 -0500 Andy Graybeal <[email protected]> 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
