You could try the following:
1) Perform normal backup.
2) Run sql command "flush status". --Resets most status
variables to zero.
3) Next week prior to backup, run sql commands:
show status like 'Handler_delete'
show status like 'Handler_update'
show status like 'Handler_write'
If any of these values are greater than zero then a table has been
modified.
You should also note the server start date just in case a server
crahed
or restarted which will also reset the status variables to zero.
Handler_delete - Number of times a row was deleted from a table.
Handler_update - Number of requests to update a row in a table.
Handler_write - Number of requests to insert a row in a table.
Ed
-----Original Message-----
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Friday, February 06, 2004 9:18 AM
To: Gowtham Jayaram
Cc: Phil; Schwartz, Evelyn; [EMAIL PROTECTED]
Subject: Re: How to determine when a MySQL database was last modified?
mysqlshow gives the same results as SHOW TABLE STATUS, which,
unfortunately, doesn't seem to give created/updated dates for InnoDB
tables.
Michael
Gowtham Jayaram wrote:
> If offline tools works for you try './mysqlshow.exe
> -vi <db_name>'. This provides useful information such
> as 'Create Time' 'Update Time' and 'Check Time'.
>
> Gowtham.
>
> --- Phil <[EMAIL PROTECTED]> wrote:
>
>>Nice try... but 'show table status' just displays
>>NULL for Update_time -
>>maybe because they're InnoDB tables. Besides, I
>>didn't really want to
>>have to squirrel around all the tables to see if the
>>DB itself has been
>>changed.
>>
>>Since what I want to do doesn't seem possible I'll
>>carry on as usual...
>>backing everything up :( Also, I'll suggest it as an
>>enhancement.
>>Thanks.
>>
>>
>>On Fri, 2004-02-06 at 14:28, Schwartz, Evelyn wrote:
>>
>>>You can try the 'show table status' from mysql.
>>
>>There is an update_time that lists the last modified
>>date for the table.
>>
>>>
>>>I also found out that these types of commands work
>>
>>with perl DBD::mysql. You can treat the command
>>like a normal sql statement and the results are
>>returned like any other sql. Pretty cool.
>>
>>>
>>>IMHO I wouldn't bother with this. Just take the
>>
>>backup. As long as you only keep the most recent
>>backup online I don't see the harm. Why do the
>>extra work and risk not having backups?
>>
>>>
>>>Evelyn
>>>
>>> -----Original Message-----
>>> From: Phil [mailto:[EMAIL PROTECTED]
>>> Sent: Fri 2/6/2004 9:27 AM
>>> To: gerald_clark
>>> Cc: [EMAIL PROTECTED]
>>> Subject: Re: How to determine when a MySQL
>>
>>database was last modified?
>>
>>>
>>>
>>>
>>> Thanks. But I would have thought that such
>>
>>information would have been
>>
>>> kept automatically somewhere by the server, and
>>
>>it's just a case of how
>>
>>> to get at it. I have quite a few tables in each
>>
>>database so I don't
>>
>>> really want to have to maintain a timestamp on
>>
>>each update, and then go
>>
>>> around all of them at backup time :(
>>>
>>> Anyone got any other ideas?
>>>
>>>
>>> On Fri, 2004-02-06 at 14:09, gerald_clark wrote:
>>> > Add a timestamp field to each table.
>>> >
>>> > Phil wrote:
>>> >
>>> > >Hi,
>>> > >
>>> > >I have many smallish, discrete MySQL
>>
>>databases, each of which I would
>>
>>> > >like to backup individually (mysqldump seems
>>
>>fine for this). However,
>>
>>> > >there's no point re-backing up a database that
>>
>>has not changed since the
>>
>>> > >last time it was backed up. So how can I tell
>>
>>if when a MySQL database
>>
>>> > >was last modified, so that I can decide
>>
>>whether to run mysqldump on it
>>
>>> > >again or not? Any help with this would be much
>>
>>appreciated.
>>
>>> > >
>>> > >Thanks,
>>> > >Phil
>>> > >
>>> > >
>>> > >
>>> > >
>>> >
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>>>
>>>
>>>
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:
>>
>
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online.
> http://taxes.yahoo.com/filing.html
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
