Remote backups, reading from and writing to the same file
freebsd at philip.pjkh.com
Thu Jan 12 15:37:59 PST 2006
> For a while I have been doing remote backups from my little server at home
> (which hosts some personal websites and also serves as my testing webserver)
> by tarring everything I wanted to be backed up and piping it to another
> machine on my network with nc(1), for example:
> On the recieving machine: nc -l 10000 > backup-`date +%Y-%m-%d`.tar.gz
> On my server: tar -c -z --exclude /mnt* -f - / | nc -w 5 -o aphax 10000
> (Some excludes for tar(1) are left out for simplicity's sake)
> Among the things being backed up are my mysql database tables. This made me
> wonder wether the backup could possibly get borked when mysql writes to any
> of the mysql tables while tar is reading from them.
> Do I really have to use MySQL's tools to do a proper SQL dump or stop MySQL
> (and any other services that may write to files included in my backup) before
> doing a backup? Do any of the more involved remote-backup solutions have ways
> of working around this? Or is it simply not possible to write to a file while
> it is being read?
The short answer is yes. The medium answer is I would if I were you :-)
The long answer (at least to the extent I know it) is...
You might be able to take a snapshot of the filesystem mysql's files are
on and back those up as they'd be consistent to themselves. But
everything I've read about backing up a database suggests that doing a
proper backup is the way to go.
If you really don't want to do that you might also be able to use one of
the various LOCK commands in Mysql to block all writes until you've copied
But really a mysqldump ... | gzip > file should result in a very very
small file. And you could pipe that over the network (or even start
mysqldump on your backup machine) if you didn't want the temp file issue.
You might also consider rsync. That would only copy files that have
changed. Might be handy if bandwidth is an issue. You can set it up to
keep backup copies of files that have changed as well. And it can run
More information about the freebsd-questions