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?
(The next problem is that the user I want to run the script as does not have access to the /var/lib/mysql directory at the moment so a more elegant solution would be nice.)
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?
Is it wise to be backing up mysql from the data pool directly ? What if the DB does something during your copy operation ? The DB would then be inconsistent surely.
If you have space on the server why not run a dump every night at a scheduled time and then rsync this dump file (therefore only moving changes) as your backup script. That way you have a convenient less than day old offline copy actually on the machine for the "whoops I didn't mean to drop that table" and your off machine backups synced via rsync/rsync-ssh for when the machine itself goes pop.
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.
Steve Fosdick wrote:
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.
My fault for not being clear on that one; I intend to use mysqldump (or mysqlhotcopy) to backup the database; the part I'm stuck on is working out which databases have changed first. Some of the databases are CMS-type packages that only change when their content does, so they need backing up but if done every day they waste space for no benefit.
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.
They only go 1 level (ie the db files are in a directory with the name of the database).
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
That should be what I need, thanks.
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.
Good idea, I had thought about that as being desirable but not got to the point of thinking how to do it.
-
Mark Rogers -
Steve Fosdick -
Wayne Stallwood