How to fetch the data from binary log file and insert in our desired table in MySQL?

If you're talking about the MySQL binary log file (mysql-bin), it wasn't designed to be read by anything other than MySQL - it's a transaction log file. The data in the log file will most of the time already be in your database by the time you read it.

Perhaps if you edit your answer to provide more information about what it is you're trying to achieve, you may get a better answer and solution.

EDIT:

Parsing the binary log file is going to give you more headaches - it's an internal file for MySQL and is known to change between releases. It also changes format depending on how the server is configured (row-based/statement-based/mixed format.) Server administrators can also disable binary logging completely.

If you can take the performance hit, you may be better off logging all queries - you can have these written to a file, or even to a database table (although in early versions of MySQL 5.1 there were severe performance hits for this; it may still be the case.) This logs all SQL queries received from clients, so you can check for the CREATE TABLE query and all statements amending data in this table.

http://dev.mysql.com/doc/refman/5.1/en/query-log.html

I am on my way of scripting a PHP code for Audit Trail, in this I encountered a situation that if there will be new table created then I will not be available with triggers for that new table and hence no tracking could be done for that, so if I code it to create three new triggers for this new table, then how will get the last change done in this table? Hence I found that Binary Log File can be helpfull for me in this case, to fetch the last change for this new table and insert it in tracking table... BUT HOW????

OM The Eternity 2010-03-26 09:22:41

I would argue that if someone is creating a new table in a database you want to audit, without your knowledge, your problems are far worse...

Andy Shellam 2010-03-26 09:29:06

Buddy It has to be like that, I know problem is worse, what what if in future new module is integrated with new table, that even by any other service provider? He will not be aware of this situation, hence I have to have a solution for this... please Help me out....

OM The Eternity 2010-03-26 09:34:19

Again, if a service provider is making changes to your database structure without your knowledge... you've got bigger issues to worry about. I've edited my answer, suggesting to use query logging, which is safer than parsing the binary log directly. However I would strongly suggest you review your permissions and change process.

Andy Shellam 2010-03-26 09:39:07

Thanks Andy For The Great Support... Now I really feel the "Strength" in your word "STRONGLY"....Happy Coding..

OM The Eternity 2010-03-26 09:45:39

I'm one of those strange people who is passionate about auditing and security ... have fun! ;-)

Andy Shellam 2010-03-26 09:53:47

Is there any way to automatically create a trigger on creation of new table in MySQL? http://stackoverflow.com/questions/2522538/is-there-any-way-to-automatically-create-a-trigger-on-creation-of-new-table-in-my

OM The Eternity 2010-03-26 10:37:50

ansaurus

tags:

views:

answers:

How to fetch the data from binary log file and insert in our desired table in MySQL?

related questions