On Thu, 2009-03-05 at 11:25 +0000, Mark Rogers wrote:
What I want to do is write a script to backup some databases, but only if they've changed. I figured that the easiest way to get a list of databases that exist, as well as which have changed, is to look in /var/lib/mysql for directories with recently changed files.
I know that find /var/lib/mysql -type d -mtime 1 .. will find directories that have changed inside 24 hours, but it does not pick up directories whose contents have changed.
Any suggestions?
Backing up the files that comprise a database is generally OK if the database is not up and running at the time. By that I mean all the database server processes have been shut down, not just that there are no active transactions.
As you've spotted the mtime on a directory does not change just because the contents of a file inside that directory is changed. The directory itself is only considered changed if a file was added, removed or renamed.
You can run find -type f to find files that have been changed and then work out which directory they are in so as to back up that whole directory. I don't know how deep MySQL directories go so you may need an algorithm to be able to work up from a file to the top level directory.
You could simply loop through the database/directories:
for db in /var/lib/mysql/* do if [ `find $db -type f -mtime 1` ] then # Backup $db or add to list to be backed up. fi fi
You may also want to change '-mtime 1' to '-newer stamp-file' where stamp file is created or 'touched' at the start of each backup so instead of testing if the database has changed in the last 24 hours you are testing if it has changed since the last backup.
When it comes to backing up the files a tool like tar or rsync will work fine if the database is shut down. If it isn't then maybe an export/dump of the database could be taken and transferred to another system as the backup.
HTH, Steve.