Remote backups, reading from and writing to the same file

Philip Hallstrom freebsd at
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 
them over.

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 
over ssh.


More information about the freebsd-questions mailing list