Determine MySQL state with Perl's DBI module

Question

I'm using a Perl script to dump the contents of a MySQL db. The Perl module I'm using is CPAN's DBI. Is there any way to tell if the state of the db has changed since the last dump so that I don't need to re-dump the db?

Answer 1

If your server is has binary logging enabled, you should be able to compare output of SHOW BINARY LOGS to a previous run to see if anything has changed.

Answer 2

If all your tables are using the InnoDB engine, my guess is that you can check innodb_buffer_pool_write_requests:

SHOW STATUS LIKE 'innodb_buffer_pool_write_requests';

If there has been a write to any InnoDB table since the last time you checked it, that value will have increased.

There may be false positives. A quick check shows that that value increases during and after a:

START TRANSACTION; INSERT INTO [...]; ROLLBACK;

But I believe that if any writes have occurred, this value must change. Check it before the previous dump begins and after the current dump completes and if its value remains the same and all your tables are InnoDB, the dumps should be identical.

That said...

If you need to dump an entire MySQL database and you are at all concerned about consistency, you almost certainly want to generate that dump with mysqldump:

mysqldump databasename

or, if SHOW TABLE STATUS shows that all your tables are using the InnoDB engine,

mysqldump --single-transaction databasename

which has the same effect but will probably lock your tables for a lot shorter period.

Trying to write your own script to get a consistent dump is almost certainly a bad idea for a lot of reasons. (It'll be hard to know when you've succeeded; that means bugs are likely; bugs with consistency can have subtly destructive effects which are the worst; your script will be slower and probably use more RAM.)

By default, mysqldump emits its output in standard SQL. If you want to manipulate the data yourself, you can get tab-delimited output by adding --tab=filename.

Answer 3

Or, do it yourself. Create a "log table" in your DB and insert the table name and a timestamp each time you do the data-dump. Should be a few lines of DBI code I'm thinking.

HTH