0

I am trying to backup my database over the network with mysqldump and rsync. I wanted to ask if there is any way to know if the database has been modified since the last time I did my old dump, before doing a new dump or update the old one.

Thank you.

Alexander van Oostenrijk
  • 4,644
  • 3
  • 23
  • 37
Hachicha Moktar
  • 145
  • 2
  • 11
  • Perhaps you could dump the database on the local machine, then calculate a checksum over the file. Then you could compare that checksum with a checksum stored on the remove server. If they're different, you copy the new dump to the server and update the checksum. – Alexander van Oostenrijk Dec 26 '13 at 14:03
  • rsync already do that,comparing files before making the transfer.so we do not need checksum. i have a lot of databases to backup and i can't dump each one(huge amount of time) and then rsync it.i want to know before making the dump if it's necessary to do it or not. thank you – Hachicha Moktar Dec 26 '13 at 14:11
  • I see. In that case, I think you'll have to access each database through MySQL and find out when it was last modified. Please see answer below. – Alexander van Oostenrijk Dec 26 '13 at 14:16
  • If you compress the files while dumping (nice to save space on disk), consider using `gzip --rsyncable` which compresses while not upsetting rsync. – MatrixManAtYrService Jul 11 '18 at 21:55

1 Answers1

2

You could access each database that might need to be dumped and ask for the last modified time. It's available through the information_schema database:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

Also see here.

Community
  • 1
  • 1
Alexander van Oostenrijk
  • 4,644
  • 3
  • 23
  • 37