使用 Perl 的 DBI 模块确定 MySQL 状态
我正在使用 Perl 脚本转储 MySQL 数据库的内容。我使用的 Perl 模块是 CPAN 的 DBI。有什么方法可以判断数据库的状态自上次转储以来是否已更改，以便我不需要重新转储数据库？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您的所有表都使用 InnoDB 引擎，我的猜测是您可以检查 innodb_buffer_pool_write_requests：
如果自上次检查以来对任何 InnoDB 表进行了写入，则该值将会增加。
可能存在误报。快速检查显示该值在 a: 期间和之后增加：
但我相信如果发生任何写入，则该值必须更改。在上一次转储开始之前和当前转储完成之后检查它，如果它的值保持不变并且所有表都是 InnoDB，则转储应该相同。
也就是说......
如果您需要转储整个 MySQL 数据库并且您非常关心一致性，那么您几乎肯定希望使用 mysqldump：
或者，如果
SHOW TABLE STATUS显示所有表都在使用 InnoDB 引擎，它具有相同的效果，但可能会锁定您的表更短的时间。
由于多种原因，尝试编写自己的脚本来获得一致的转储几乎肯定是一个坏主意。 （很难知道您何时成功；这意味着很可能会出现错误；具有一致性的错误可能会产生微妙的破坏性影响，这是最糟糕的；您的脚本会变慢，并且可能会使用更多的 RAM。）
默认情况下，
mysqldump以标准 SQL 发出其输出。如果您想自己操作数据，可以通过添加--tab=filename来获得制表符分隔的输出。If all your tables are using the InnoDB engine, my guess is that you can check 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:
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:
or, if
SHOW TABLE STATUSshows that all your tables are using the InnoDB engine,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,
mysqldumpemits its output in standard SQL. If you want to manipulate the data yourself, you can get tab-delimited output by adding--tab=filename.如果您的服务器启用了二进制日志记录，您应该能够将
SHOW BINARY LOGS的输出与之前的运行进行比较，以查看是否有任何更改。If your server is has binary logging enabled, you should be able to compare output of
SHOW BINARY LOGSto a previous run to see if anything has changed.或者，自己做。在数据库中创建一个“日志表”，并在每次执行数据转储时插入表名称和时间戳。我想应该是几行DBI代码。
华泰
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