[LUNI] MySQL partial backup

[Ramin K]

On 12/5/2010 4:19 PM, Gene Jannece wrote:
> Check out : http://sourceforge.net/projects/automysqlbackup/
>
> I use it a bunch.
>
> Gene

	Automysqlbackup is a very good starting point for building your own 
backup scripts and you can pretty much use it out of the box on anything 
smaller than 1GB. However it makes a number of assumptions about your 
system that may not be in your best interest. The few most glaring are

1. Backups are local
2. disk IO is plentiful
3. You're never going to populate a slave
4. Runs monthly, weekly, daily database dump serially instead of doing 
one dump and populating the other directories.

	The first two can be addressed by piping mysqldump through gzip (or 
bzip2) rather than dumping to disk, reading from disk, and finally gzip 
to disk again. Mysqldump and gzip are both single threaded so you're 
unlikely to swamp CPU. You're only writing compressed data to disk so IO 
is smaller and bottle-necked by the compression speed.

	Automysqlbackup dumps your databases individually instead of a single 
transaction. Makes it unsuitable for starting replication and replaying 
logs since you'll have databases from various points in time. Probably 
not an issue on a smaller database, but something to keep in mind.
	No support for -A, --single-transaction, etc, but simple enough to add.

	If you're in a larger environment with replication and other 
curly-queues I'd look at 
http://www.percona.com/software/percona-xtrabackup/ as a better solution.

Ramin