4 ms·

You can't use rsync, rsnapshot, or any other tool that manipulates files and/or filesystems, to get a consistent backup of most databases. Databases come with t

by kijin 10y ago

You can't use rsync, rsnapshot, or any other tool that manipulates files and/or filesystems, to get a consistent backup of most databases. Databases come with their own tools and/or commands for creating a consistent "dump". Nothing else is guaranteed to be safe. I use mysqldump/pg_dump/etc. to create database dumps and copy them to backup storage, and rsync/rsnapshot to back up the app itself. There is no need to use a single tool to back up everything. If your app is well designed, there's not even any need to back up the database and the filesystem simultaneously. Even SQLite has a .dump command.

vacri 10y ago
> Nothing else is guaranteed to be safe. Well, stopping the database generally allows you to be able to back up static files.
koolba 10y ago
> You can't use rsync, rsnapshot, or any other tool that manipulates files and/or filesystems, to get a consistent backup of most databases. Sure you can and if you can't, then get a better database! A well functioning database should be able to deal with being inconsistently shutdown. If you can't take a snapshot of the disk then what's the difference between that and the power cord being pulled? The main reason not to do this on a DB server is that the recovery time may large. That doesn't mean it can't be done though. > Databases come with their own tools and/or commands for creating a consistent "dump". Nothing else is guaranteed to be safe. Sure they do and you should use those as well, that doesn't mean you can't do this too.
- cyphar 10y ago
  The issue is not that you have an atomic copy of the state at a certain point in time, which in a bad database couldn't be loaded. The problem is that when you make a full copy of a file you are copying linearly and if it's a big file a chunk could change while you're reading it (or multiple related chunks which you are in the middle of copying) -- then you have a very inconsistent state which a database can't recover from no matter if their updates are atomically written. The database could do some magic with unlinking, but that would cause massive performance problems.
  - koolba 10y ago
    That's a solved problem: http://linux.die.net/man/8/xfs_freeze http://linux.die.net/man/8/xfs_freeze Combine that with disk snapshotting and you're fine.
    - cyphar 10y ago
      Or just use a filesystem where snapshots are atomic by nature. :P

by kijin 10y ago

vacri 10y ago

> Nothing else is guaranteed to be safe. Well, stopping the database generally allows you to be able to back up static files.

koolba 10y ago

> You can't use rsync, rsnapshot, or any other tool that manipulates files and/or filesystems, to get a consistent backup of most databases. Sure you can and if you can't, then get a better database! A well functioning database should be able to deal with being inconsistently shutdown. If you can't take a snapshot of the disk then what's the difference between that and the power cord being pulled? The main reason not to do this on a DB server is that the recovery time may large. That doesn't mean it can't be done though. > Databases come with their own tools and/or commands for creating a consistent "dump". Nothing else is guaranteed to be safe. Sure they do and you should use those as well, that doesn't mean you can't do this too.

cyphar 10y ago
The issue is not that you have an atomic copy of the state at a certain point in time, which in a bad database couldn't be loaded. The problem is that when you make a full copy of a file you are copying linearly and if it's a big file a chunk could change while you're reading it (or multiple related chunks which you are in the middle of copying) -- then you have a very inconsistent state which a database can't recover from no matter if their updates are atomically written. The database could do some magic with unlinking, but that would cause massive performance problems.
- koolba 10y ago
  That's a solved problem: http://linux.die.net/man/8/xfs_freeze http://linux.die.net/man/8/xfs_freeze Combine that with disk snapshotting and you're fine.
  - cyphar 10y ago
    Or just use a filesystem where snapshots are atomic by nature. :P