Tracking record insertion and deletion

[Dan Greenblatt]

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Thanks in advance,
Dan

[Siemel Naran]

"Dan Greenblatt" <an******@yahoo .com> wrote in message

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Perhaps the simplest suggestion is to log the SQL statements. Within the
command center you can say "tee c:\mylog\statem ents.txt". You can also
start mysql with the --tee option.

Then of course you have to parse the tee file. If so desired you can store
the pieces of statements into another MySql table (but don't use tee as you
want to log these insert statements). It all depends what you want to do
with the information.

[David L]

if it won't impact peformance too much i'd suggest setting up a log
table, and append to it straight after (or before) such changes. I'm
starting to do this with my projects and it's been great for tracking
who did what when, plus exactly what has been changed. Great also for
debugging. A log file will probably give you more info though - depends
i guess.
Dan Greenblatt wrote:

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Thanks in advance,
Dan

[Mike Chirico]

"Dan Greenblatt" <an******@yahoo .com> wrote in message
news:a2******** *************** ***@posting.goo gle.com...

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Hope this helps:

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

will show the create statements, updates, and deletes.

Reference: ( TIP 24: and TIP 2) in
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

It's also possible to write a C api that will automatically extract this
information. For an example program you may want to download

http://prdownloads.sourceforge.net/s...ar.gz?download

and take a look at "selectbinlog.c ". Or, a perl script can be written as
well...(See TIP 4: ) for the basics on perl with mysql.

This can contain a lot of information. Instead of putting this information
into a mysql table, you may want to put it into a BerkeleyDB file. If you're
running Linux or Unix you probably have this installed. BerkeleyDB is used
by MySQL. It's not really a database but a fast hash or b-tree, depending
on format option selected. For an example of how to write BerkeleyDB api's
see the following:
http://prdownloads.sourceforge.net/c...ar.gz?download

The latest version of BerkeleyDB allows for encryption, so that this data
can be protected. I use BerkeleyDB for this type of logging because I need
to do extremely quick lookups on this information.

Regards,

Mike Chirico

[Siemel Naran]

"Mike Chirico" <mc******@comca st.net> wrote in message
news:zsOdnWTnUK pN0V7dRVn-

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

Do you have to turn on binlog events too? Can you use the command on the
tee file?

[Mike Chirico]

"Siemel Naran" <Si*********@RE MOVE.att.net> wrote in message
news:u8******** *************@b gtnsc05-news.ops.worldn et.att.net...

"Mike Chirico" <mc******@comca st.net> wrote in message
news:zsOdnWTnUK pN0V7dRVn-

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

Do you have to turn on binlog events too? Can you use the command on the
tee file?

I've always used the "tee file" for a single session. I think it returns
everything include the results of the select, delete statements? I don't
know of an easy way to parse the tee file, or to get information on all
users. The "show binlog events" has options for listing start positions
and limiting the number of events. For instance, "mysql> show binlog event
from 201 limit 2;" will show the 2 events starting at position 201, which is
handy. True, the results or return rows of a query are not shown; but, I
think this is a big advantage for a heavily used database?

I've never done anything special to turn on binlog events. However, doing a
"mysql> reset master" will delete and clear the log files.

FYI: I think in version 5 of Mysql these log files will be "all" binary.
Whereas in version 4-alpha, I can currently, using Linux, do the "strings"
command on these files "$ strings /usr/local/var/third-fl-71-bin.000001" and
get readable output, but, then there is no position tracking.

Regards,

Mike Chirico

[Mike Chirico]

"Siemel Naran" <Si*********@RE MOVE.att.net> wrote in message
news:u8******** *************@b gtnsc05-news.ops.worldn et.att.net...

"Mike Chirico" <mc******@comca st.net> wrote in message
news:zsOdnWTnUK pN0V7dRVn-

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

Do you have to turn on binlog events too? Can you use the command on the
tee file?

/etc/my.cnf

[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin = /usr/local/var/mysqlLOGb.log
or just

log-bin
Regards,

Mike Chirico

[Siemel Naran]

"Mike Chirico" <mc******@comca st.net> wrote in message
news:4_GdnQgFSZ mPxF7dRVn-

Do you have to turn on binlog events too? Can you use the command on the tee file?
I've always used the "tee file" for a single session. I think it returns
everything include the results of the select, delete statements? I don't
know of an easy way to parse the tee file, or to get information on all
users. The "show binlog events" has options for listing start positions
and limiting the number of events. For instance, "mysql> show binlog

event from 201 limit 2;" will show the 2 events starting at position 201, which is handy. True, the results or return rows of a query are not shown; but, I
think this is a big advantage for a heavily used database?

I've never done anything special to turn on binlog events. However, doing a "mysql> reset master" will delete and clear the log files.

FYI: I think in version 5 of Mysql these log files will be "all" binary.
Whereas in version 4-alpha, I can currently, using Linux, do the "strings"
command on these files "$ strings /usr/local/var/third-fl-71-bin.000001" and get readable output, but, then there is no position tracking.

Thanks for your answers.

[Dan Greenblatt]

Excellent !! This looks like exactly the kind of thing I need..
Thanks for all the great info -
Dan
"Mike Chirico" <mc******@comca st.net> wrote in message news:<2e******* *************@c omcast.com>...

"Siemel Naran" <Si*********@RE MOVE.att.net> wrote in message
news:u8******** *************@b gtnsc05-news.ops.worldn et.att.net...

"Mike Chirico" <mc******@comca st.net> wrote in message
news:zsOdnWTnUK pN0V7dRVn-

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

Do you have to turn on binlog events too? Can you use the command on the
tee file?

/etc/my.cnf

[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin = /usr/local/var/mysqlLOGb.log
or just

log-bin
Regards,

Mike Chirico

[Dan Greenblatt]

Hi David -
Would you be able to clarify your suggestion? Specifically, what do
you put in your 'log tables' ? What gets appended to it?
Thanks,
Dan
David L <[e-mail] m> wrote in message news:<85******* **********@news-server.bigpond. net.au>...

if it won't impact peformance too much i'd suggest setting up a log
table, and append to it straight after (or before) such changes. I'm
starting to do this with my projects and it's been great for tracking
who did what when, plus exactly what has been changed. Great also for
debugging. A log file will probably give you more info though - depends
i guess.
Dan Greenblatt wrote:

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Thanks in advance,
Dan